MySQL8.0学习记录12 - 列字符集转换

二进制转字符列

二进制格式的列也可以转换为char、varchar这样的字符列，需要注意的是，二进制列的当前内容字符集信息很重要，而且只应该包含一种字符集的数据。如下例子：

create table t_binary_to_char(
	a varbinary(100)
);

insert into t_binary_to_char values (convert('中文测试' using gb18030));

如果将上面的二进制列转成字符列，目标列的字符集是gb18030就没有问题：

alter table t_binary_to_char modify a varchar(100) character set gb18030;
select * from t_binary_to_char;

输出如下：

a   |
----+
中文测试|

但是直接转成utf8mb4就有问题：

alter table t_binary_to_char modify a varchar(100) character set utf8mb4;
select * from t_binary_to_char

Incorrect string value: '\xD6\xD0\xCE\xC4\xB2\xE2...' for column 'a' at row 1

---

相反，如果一开始存的是utf8mb4字符集的二进制数据，虽然可以转成gb18030，但是数据乱码：

a     |
------+
涓枃娴嬭瘯|

对于这样乱码的情况，其实是列的字符集是gb18030，但是实际内容是utf8mb4字符集的内容，有个解决方式就是先转二进制，再转正确字符集：

select a,convert(convert(a using binary) using utf8mb4) 
from  t_binary_to_char;

a     |convert(convert(a using binary) using utf8mb4)|
------+----------------------------------------------+
涓枃娴嬭瘯|中文测试                                          |

binary 列注意去掉补全的0

create table t_binary_to_char(
	a BINARY(50)
);
insert into t_binary_to_char values ('中文测试');

select convert(a using utf8mb4) as txt,
CHARACTER_LENGTH(convert(a using utf8mb4)) len 
from  t_binary_to_char;

可以看到字符的长度远大于4:

txt                                       |len|
------------------------------------------+---+
中文测试                                      | 42|

这时候可以使用TRIM函数去掉多余的0x00

select 
TRIM(TRAILING 0x00 FROM convert(a using utf8mb4)) as txt,
CHARACTER_LENGTH (TRIM(TRAILING 0x00 FROM convert(a using utf8mb4))) as len
from  t_binary_to_char;

txt |len|
----+---+
中文测试|  4|

一次修改表所有字符列字符集

可以通过 ALTER TABLE … CONVERT TO CHARACTER SET charset ,修改一个表所有的字符列。

create table t_char(
	a BINARY(50),
	b varchar(50)
);

insert into t_char values ('中文测试','中午测试');

ALTER TABLE t_char CONVERT TO CHARACTER SET gb18030;

select COLUMN_NAME ,DATA_TYPE ,CHARACTER_SET_NAME  from information_schema.`COLUMNS` c  where TABLE_NAME  = 't_char';

可以看到binary不会改变，生效的只有真正的字符列：

COLUMN_NAME|DATA_TYPE|CHARACTER_SET_NAME|
-----------+---------+------------------+
a          |binary   |                  |
b          |varchar  |gb18030           |