陈老师:1415968548 郑老师:2735197625 乐老师:354331153
客服热线:
19941464235/19906632509(微信同号)

客服微信

腾讯云TDSQL-数据库字符集

作者:潘峰
原创
发布时间:2021-05-21 09:58
浏览量:312

作者:潘峰


--显示数据库支持的字符集

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 |+----------------------------------+