欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【转载】mysql数据数据表的排序规则修改

程序员文章站 2022-05-30 23:46:46
...

在工作中同事遇到的问题,是找一种简便的方法批量修改数据表字段的排序规则,在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 
版权声明:本文为博主原创文章,转载请附上博文链接!