- 论坛徽章:
- 0
|
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: 3 Duplicates: 0 Warnings: 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个值和服务器系统编码一致。
有不正确和需要补充的,请大家指出,谢谢! |
|