mysql 字符集测试_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