【转载】mysql数据数据表的排序规则修改
在工作中同事遇到的问题,是找一种简便的方法批量修改数据表字段的排序规则,在MySQL中叫collation,和编码CHARACTER一起出现的。collation有三种级别,分辨是数据库级别,数据表级别和字段级别。
database、table、column
问题是,mysql表外键和关联的主键不能建立关系,因为排序规则的问题。utf8_bin,
网上搜到的解决办法,都提到了修改数据表级别collation排序规则。但是我遇到的场景是数据表级别已经是utf8_unicode_ci,而字段级别是utf8_general_ci,(这里我们关心的字段类型是varchar)。
由于需要修改的字段太多了,手工修改肯定是费时费力的。自然也想到了用脚本的方式批量修改,但是发现这种通过查找MySQL信息表、过滤、拼接生成批量修改的语句太好用了,而且还能做到针对varchar类型。
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_unicode_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'database'
AND DATA_TYPE = 'varchar'
AND
(
CHARACTER_SET_NAME != 'utf8'
OR
COLLATION_NAME != 'utf8_unicode_ci'
);
database需要改成实际数据库名字。需要注意的是,如果要修改的字段存在外键关系,那就要小心处理,删除外键,修改collation后再把外键关系加回来。
TABLE_CATALOG varchar 512 utf8 utf8_general_ci
TABLE_SCHEMA varchar 64 utf8 utf8_general_ci
TABLE_NAME varchar 64 utf8 utf8_general_ci
COLUMN_NAME varchar 64 utf8 utf8_general_ci
ORDINAL_POSITION bigint 21
COLUMN_DEFAULT longtext utf8 utf8_general_ci
IS_NULLABLE varchar 3 utf8 utf8_general_ci
DATA_TYPE varchar 64 utf8 utf8_general_ci
CHARACTER_MAXIMUM_LENGTH bigint 21
CHARACTER_OCTET_LENGTH bigint 21
NUMERIC_PRECISION bigint 21
NUMERIC_SCALE bigint 21
CHARACTER_SET_NAME varchar 32 utf8 utf8_general_ci
COLLATION_NAME varchar 32 utf8 utf8_general_ci
COLUMN_TYPE longtext 0 utf8 utf8_general_ci
COLUMN_KEY varchar 3 utf8 utf8_general_ci
EXTRA varchar 27 utf8 utf8_general_ci
PRIVILEGES varchar 80 utf8 utf8_general_ci
COLUMN_COMMENT varchar 255 utf8 utf8_general_ci
PRI主键约束;
UNI唯一约束;
MUL可以重复。
---------------------
作者:privateobject
来源:CSDN
原文:https://blog.csdn.net/privateobject/article/details/78363809
版权声明:本文为博主原创文章,转载请附上博文链接!
上一篇: MySQL数据表的增加、修改和删除
下一篇: SpringMVC中乱码解决方案