客服微信
作者：潘峰
--显示数据库支持的字符集
show char set;
1.针对每一种声明支持的字符集（支持的字符集应当包含utf8），创建一张表，并测试：
--根据show char set 执行的结果 选如下字符集创建表
CREATE TABLE `test` (`id` int auto_increment primary key，`name` binary(16),`gbk` varchar(2) CHARACTER SET gbk DEFAULT NULL,`utf8` varchar(2) CHARACTER SET utf8 DEFAULT NULL,`latin_utf8` varchar(6) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
a)测试增删改查操作；
insert into test (name,gbk,utf8,latin_utf8) values ('liudehua','ab','cd','df');insert into test (name,gbk,utf8,latin_utf8) values ('liudehua','ab','cd','df');insert into test (name,gbk,utf8,latin_utf8) values ('zhangxueyou','ef','c1','ji');delete from test where latin_utf8='ji';select * from test;+----+------------------+------+------+------------+| id | name | gbk | utf8 | latin_utf8 |+----+------------------+------+------+------------+| 1 | liudehua | ab | cd | df || 2 | liudehua | ab | cd | df |+----+------------------+------+------+------------+update test set utf8='中国' where id = 2 and utf8='cd';select * from test;+----+------------------+------+--------+------------+| id | name | gbk | utf8 | latin_utf8 |+----+------------------+------+--------+------------+| 1 | liudehua | ab | cd | df || 2 | liudehua | ab | 中国 | df |+----+------------------+------+--------+------------+
b)对于支持utf8、gbk等含中文的情况，测试汉字“中国”增删改查操作；
insert into test (name,gbk,utf8,latin_utf8) values ('中国','中国','中国',_latin1'中国');insert into test (name,gbk,utf8,latin_utf8) values ('中国','中国','中国',_latin1'中国');delete from test where utf8='中国' and id =4;update test set utf8='日本',gbk='日本' where gbk='中国' and utf8='中国';select * from test;+----+------------------+--------+--------+---------------+| id | name | gbk | utf8 | latin_utf8 |+----+------------------+--------+--------+---------------+| 1 | liudehua | 日本 | 日本 | df || 5 | 中国 | 日本 | 日本 | ä¸å›½ |+----+------------------+--------+--------+---------------+
c)对于支持查询二进制值的情况，测试查询汉字“中国”的编码值，确定以正确的字符集存储；
select hex(name),hex(gbk),hex(utf8),hex(latin_utf8) from test;+----------------------------------+----------+--------------+-----------------+| hex(name) | hex(gbk) | hex(utf8) | hex(latin_utf8) |+----------------------------------+----------+--------------+-----------------+| 6C697564656875610000000000000000 | C8D5B1BE | E697A5E69CAC | 6466 || E4B8ADE59BBD00000000000000000000 | C8D5B1BE | E697A5E69CAC | E4B8ADE59BBD || E4B8ADE59BBD00000000000000000000 | D6D0B9FA | E4B8ADE59BBD | E4B8ADE59BBD |+----------------------------------+----------+--------------+-----------------+
2.针对声明支持两种或更多字符集的情况，测试：
a)如果声明支持存储字符集转换，在支持的范围内测试存储字符集转换；
set names latin1;show variables like 'character_set%';+--------------------------+---------+| Variable_name | Value |+--------------------------+---------+| character_set_client | latin1 || character_set_connection | latin1 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | latin1 || character_set_server | utf8mb4 || character_set_system | utf8 |+--------------------------+---------+select * from test;+----+------------------+------+------+------------+| id | name | gbk | utf8 | latin_utf8 |+----+------------------+------+------+------------+| 1 | liudehua | ?? | ?? | df || 5 | 中国 | ?? | ?? | 中国 || 6 | 中国 | ?? | ?? | 中国 |+----+------------------+------+------+------------+
b)如果声明支持连接字符集和存储字符集不一致，测试连接字符集与存储字符集不相同时，数据增、删、改、查操作是否合理处理，连接字符集与存储字符集都支持的汉字在增、删、改、查时是否正确转换；
MySQL [test]> set session character_set_connection=latin1;MySQL [test]> select * From test;+----+------------------+--------+--------+---------------+| id | name | gbk | utf8 | latin_utf8 |+----+------------------+--------+--------+---------------+| 1 | liudehua | 日本 | 日本 | df || 5 | 中国 | ?? | 日本 | ä¸å›½ || 6 | 中国 | 中国 | 中国 | ä¸å›½ |+----+------------------+--------+--------+---------------+update test set gbk='美国' WHERE id=6;MySQL [test]> select * from test;+----+------------------+--------+--------+---------------+| id | name | gbk | utf8 | latin_utf8 |+----+------------------+--------+--------+---------------+| 1 | liudehua | 日本 | 日本 | df || 5 | 中国 | ?? | 日本 | ä¸å›½ || 6 | 中国 | ?? | 中国 | ä¸å›½ |+----+------------------+--------+--------+---------------+
3.如果声明支持二进制字符串，测试插入和查询出的二进制字符串的二进制数值是否完全一致。
insert into test (name,gbk,utf8,latin_utf8) values (UNHEX('4D7953514C'),'中国','中国',_latin1'中国');select * from test;+----+------------------+--------+--------+---------------+| id | name | gbk | utf8 | latin_utf8 |+----+------------------+--------+--------+---------------+| 1 | liudehua | 日本 | 日本 | df || 5 | 中国 | ?? | 日本 | ä¸å›½ || 6 | 中国 | ?? | 中国 | ä¸å›½ || 7 | MySQL | 中国 | 中国 | ä¸å›½ |+----+------------------+--------+--------+---------------+select hex(name) from test where id = 7;+----------------------------------+| hex(name) |+----------------------------------+| 4D7953514C0000000000000000000000 |+----------------------------------+
