mysql 字符集研究与测试
mysql默认字符集设置
mysql> show variables like '%character%';
+--------------------------+-------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /www/wdlinux/mysql-5.1.63/share/mysql/charsets/ |
+--------------------------+-------------------------------------------------+
character_set_system = utf8 设置的时mysql的元字符集 比如(表名,库名,sql语句的字符设置),他是只读的,不需要更改。
character_set_database 设置的时当前选中数据库的字符集,他回随着选择数据库不同而不同
use t
mysql> show variables like '%character_set_database%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)
mysql> use y
Database changed
mysql> show variables like '%character_set_database%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| character_set_database | gbk |
+------------------------+-------+
1 row in set (0.00 sec)
character_set_server,当你建库或者见表时没有指定字符集,就是用这个参数设置
mysql> show create database t;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| t | CREATE DATABASE `t` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database y;
+----------+-----------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------+
| y | CREATE DATABASE `y` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table user;
+-------+-----------------------------------------------------------------------
----------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
----------------------+
| user | CREATE TABLE `user` (
`name` char(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
----------------------+
1 row in set (0.00 sec)
mysql> insert into user(name) values("攻城市php");
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from user;
+--------+
| name |
+--------+
| ???php |
+--------+
1 row in set (0.00 sec)
set names 设置是 character_set_client,character_set_connection,character_set_results的字符集,下面我们做个实验,说明这三个参数用处
mysql> create database b default character set utf8;
Query OK, 1 row affected (0.01 sec)
mysql> show create database b ;
+----------+------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------+
| b | CREATE DATABASE `b` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use b
Database changed
mysql> create table user(name char(30));
Query OK, 0 rows affected (0.11 sec)
mysql> show create table user;
+-------+-----------------------------------------------------------------------
--------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------+
| user | CREATE TABLE `user` (
`name` char(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------+
1 row in set (0.00 sec)
mysql> insert into user(name) values("张三"),("李四");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from user;
+--------+
| name |
+--------+
| 张三 |
| 李四 |
+--------+
2 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where name='张三';
Empty set (0.00 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where name='张三';
+--------+
| name |
+--------+
| 张三 |
+--------+
1 row in set (0.00 sec)
大家可以通过php脚本测试一下,命令行工具一般测不出结果、
mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%character%';
+--------------------------+-------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /www/wdlinux/mysql-5.1.63/share/mysql/charsets/ |
+--------------------------+-------------------------------------------------+
8 rows in set (0.00 sec)
mysql> select * from user where name='张三';
+------+
| name |
+------+
| ÕÅÈý |
+------+
1 row in set (0.00 sec)
看看结果,发现是乱码,自己思考一下为什么?
mysql> set password for 'root'@'localhost'=password('*****');
最后引用网上的一张图解释下,
eof