简单记录未做代码验证
image.png
hex(col) 返回字符集直接从database charset拿,跳过了转换,直接返回二进制,可以用于测试数据库存储的是什么字符集。(需要后期代码验证)
hex('去') 在client charset和connect charset设置一致的情况下,不需要转换,hex直接返回输入字符的二进制,可以用于测试终端到底是字符集。(需要后期代码验证)
set names 设置 client charset/connect charset/result charset
诊断乱码,
1、可以先查询client charset/connect charset/result charset是否一致,不一致先设置为一致。
2、然后查看数据库存储的字符集是什么
3、然后查看client charset/connect charset/result charset是否和它一致,然后查看终端字符集是否一致。
终端UTF8
mysql> select hex(name),name,hex('去') from testchr;
+-----------+------+-----------+
| hex(name) | name | hex('?') |
+-----------+------+-----------+
| E58EBB | ? | C8A5 |
| C8A5 | ? | C8A5 |
| C8A5 | ? | C8A5 |
+-----------+------+-----------+
mysql> show variables like '%char%';
+---------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------+
| character_set_client | utf8 |
| 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 | /newdata/mysql3306/install/share/charsets/ |
| ft_query_extra_word_chars | OFF |
+---------------------------+--------------------------------------------+
9 rows in set (0.01 sec)
mysql>
1、终端 GBK->names GBK->存储utf8
转换正常
2、终端 GBK->names utf8->存储utf8 把GBK当做UTF8存储,UTF8有2字节字符。
GBK存入utf8
mysql> select hex(name) from testchr;
+-----------+
| hex(name) |
+-----------+
| E58EBB |
| C8A5 |
+-----------+
2 rows in set (0.00 sec)
3、终端UTF8->names gbk->存储utf8
终端转入names gbk 失败
mysql> insert into testchr values('去');
ERROR 1406 (22001): Data too long for column 'name' at row 1
4、终端UTF8->names gbk->存储GBK,因为GBK没有1字节的,也就是将UTF8当做GBK想存入数据库的GBK中。
转换失败
mysql> insert into testchr2 values('去');
ERROR 1366 (HY000): Incorrect string value: '\xBB' for column 'name' at row 1
5、终端GBK->names utf8->储存gbk,因为C8A5不是有效的UTF8字符,也就是想把GBK当做UTF8做转换存入GBK。
转换失败
mysql> insert into testchr2 values('去');
ERROR 1366 (HY000): Incorrect string value: '\xC8\xA5' for column 'name' at row 1
做好几种字符集一致即可。
6、终端utf8,更改character_set_connection测试
mysql> show variables like '%char%';
+---------------------------+--------------------------------------------+
| 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 | /newdata/mysql3306/install/share/charsets/ |
| ft_query_extra_word_chars | OFF |
+---------------------------+--------------------------------------------+
9 rows in set (0.01 sec)
mysql> set character_set_connection=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select ('去');
+-----+
| 去 |
+-----+
| 去 |
+-----+
1 row in set (0.00 sec)
mysql> select hex('去');
+------------+
| hex('去') |
+------------+
| C8A5 |
+------------+
1 row in set (0.00 sec)
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select hex('去');
+------------+
| hex('去') |
+------------+
| E58EBB |
+------------+
1 row in set (0.00 sec)