MySQL 表字符集转换

最近遇到了个关于修改表字符集的问题。原因是线上库表为utf8字符集碰到生僻字导致了应用报错的问题。在商量后决定修改整个库的字符集由utf8转成uft8mb4。修改字符集当时有两个方案一个是将库进行迁移到utf8mb4
的库上，另一种使用SQL将所有表进行字符集转换。这次线上虽然决定使用迁移的方法，但这里想加深了解下关于SQL转换的方法，所以有了这篇记录。

SQL语法

整表进行转换

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

复制

为了将表的默认字符集和所有的字符串 (char,varchar,text
) 转换成新的字符集就可以使用上面这个SQL。它不仅可以转换字符集还可以转换字符串的collation
。

当不指定collation
会使用和character set
相匹配的collation
。所以想指定则需要自己显示的写到SQL中。

当想使用默认字符集(character_set_database
)可以使用如下SQL

alter table tbl_name convert to character set default;

复制

只对一列的字符集转换

ALTER TABLE t1 CHANGE col  col  TEXT CHARACTER SET utf8;

复制

如果你只想修改一个表中的某一列的字符集，则可以使用这个SQL，但是想修改多个列就需要写相应列的SQL了

如果只是想转换默认字符集

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;

复制

他不会将已存的数据进行转换只会将字符集修改过来。

若只想修改表的默认字符集

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

复制

该SQL会指定该表后面加列在未显性指定字符集情况下使用的默认字符集。

注意点

1. 在进行转换时需要注意有每个字符存储占小的字符集(latin1,utf8)转成较大的字符集(utf8mb4)时注意存储空间不足的问题
2. 在开启foreign_key_checks
   时，是不允许对存在外键的表进行字符集转换操作的。所以，必须在开启foreign_key_checks
   参数将相关的父子表都完成字符集转换。否则ON DELETE CASCADE
   则 ON UPDATE CASCADE
   操作可能会损坏引用表中的数据，因为在这些操作期间会发生隐式转换（错误 #45290、错误 #74816）。