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