关于mysql 编码一些验证
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 | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
客户端使用CRT，
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| tpcc |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1 |
| test2 |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from test1;
Empty set (0.01 sec)
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(2) | NO | PRI | NULL | |
| name| varchar(10) | YES| | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test1 values(1,'测试'),(2,'测试2'),(3,'测试3');
Query OK, 3 rows affected (0.00 sec)
Records: 3Duplicates: 0Warnings: 0
mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
|1 | 测试 |
|2 | 测试2 |
|3 | 测试3 |
+----+---------+
3 rows in set (0.00 sec)
（1）客户端工具utf8，client，connection，results 字符集为gbk
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%character%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> select * from test1;
+----+-------+
| id | name|
+----+-------+
|1 | |
|2 | 2 |
|3 | 3 |
+----+-------+
3 rows in set (0.01 sec)
结论出现错误，但是没有乱码，应该是客户端工具不能识别，修改工具编码格式gb2312后如下
mysql> select * from test1;
+----+-------+
| id | name|
+----+-------+
|1 | 测试 |
|2 | 测试2 |
|3 | 测试3 |
+----+-------+
3 rows in set (0.00 sec)
插入几笔
mysql>insert into test1 values (4,'浴4');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test1;
+----+-------+
| id | name|
+----+-------+
|1 | 测试 |
|2 | 测试2 |
|3 | 测试3 |
|4 | 浴4 |
+----+-------+
4 rows in set (0.00 sec)
修改客户端工具编码
mysql> select * from test1;
+----+-------+
| id | name|
+----+-------+
|1 | |
|2 | 2 |
|3 | 3 |
|4 | ?4 |
+----+-------+
4 rows in set (0.00 sec)
出现乱码
修改client，connection，results字符集为utf8
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
|1 | 测试 |
|2 | 测试2 |
|3 | 测试3 |
|4 | 浴4 |
+----+---------+
4 rows in set (0.00 sec)
正常。
（2）修改client gbk
mysql> show variables like '%character%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | gbk |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
|1 | 测试 |
|2 | 测试2 |
|3 | 测试3 |
|4 | 浴4 |
+----+---------+
4 rows in set (0.00 sec)
mysql> insert into test1 values (5,'测试5');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+----+------------+
| id | name |
+----+------------+
|1 | 测试 |
|2 | 测试2 |
|3 | 测试3 |
|4 | 浴4 |
|5 | 娴嬭瘯5 |
+----+------------+
5 rows in set (0.00 sec)
出现乱码，过程，首先经过client的gbk编码经过，connection进入数据库，因为connection是utf8编码，所以需要转换，因为utf8是3字节编码，转换后，进入数据库，查询的时候经过connection传出客户端显示，因为results是utf8，所以无法正常转出。
接着修改results为gbk
mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
|1 | |
|2 | 2 |
|3 | 3 |
|4 | ?4 |
|5 | 测试5 |
+----+---------+
5 rows in set (0.00 sec)
第五个结果正常，因为经过connection 到results需要从utf8转换成gbk，可以正常显示。
特例，超过3字节
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (6,'测试长字符经过');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+----+--------------------------------+
| id | name |
+----+--------------------------------+
|1 | 测试 |
|2 | 测试2 |
|3 | 测试3 |
|4 | 浴4 |
|5 | 娴嬭瘯5 |
|6 | 娴嬭瘯闀垮瓧绗︾粡杩 |
+----+--------------------------------+
6 rows in set (0.00 sec)
mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+----+----------------------+
| id | name |
+----+----------------------+
|1 | |
|2 | 2 |
|3 | 3 |
|4 | ?4 |
|5 | 测试5 |
|6 | 测试长字符经 |
+----+----------------------+
6 rows in set (0.00 sec)
结论：第六个已经少了一个汉字
因为经过connection utf8到results gbk的转换是，从3字节到2字节，会丢失一部分数据，因此不完整了。
总结：client ，connection，results需要一致，才能保证输入和输出完整。
尽量这3个值和服务器系统编码一致。
有不正确和需要补充的，请大家指出，谢谢！ 全都utf8，
所有不执行符合标准的配置，统统发工作错误提示单，同时相关负责人直接扣20块钱工资 回复 2# shang2010
不给切换 字符集 直接utf8如果想用其他的做数据迁移 一句话 不给改
6666666，就是想测试哪种情况下会有什么的现象？回复 2# shang2010
学习了mysql很神奇啊 三码合一
\s
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
页:
[1]