mysql数据库批量查询修改_批量修改mysql表、表、数据库的字符校对规则

最新推荐文章于 2023-12-02 11:06:27 发布

韩睿芝

最新推荐文章于 2023-12-02 11:06:27 发布

阅读量377

点赞数

文章标签： mysql数据库批量查询修改

本文链接：https://blog.csdn.net/weixin_32108979/article/details/113612152

版权

记录一个在工作中遇到的问题，也不算是问题，为的是找一种简便的方法批量修改数据表字段的排序规则，在MySQL中叫collation，常常和编码CHARACTER一起出现的。collation有三种级别，分辨是数据库级别，数据表级别和字段级别。

1.The database level

2.The table level

3.The column level

那天遇到的问题是这样子的，

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=‘，主要是因为迁移数据库时候没有把collation规则及时修改过来。

网上搜到的解决办法，都提到了修改数据表级别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.COLUMNSWHERE TABLE_SCHEMA = ‘database‘AND DATA_TYPE = ‘varchar‘AND(

CHARACTER_SET_NAME != ‘utf8‘

COLLATION_NAME != ‘utf8_unicode_ci‘);

database需要改成实际数据库名字。需要注意的是，如果要修改的字段存在外键关系，那就要小心处理，删除外键，修改collation后再把外键关系加回来。

摘自http://segmentfault.com/a/1190000002570642

==========================以上网络引用，下面内容是我修改后的SQL，感谢郑同学帮忙=======================================

-- 修改数据库表校对规则SQL，执行时将表中列的校对规则一并修改。

delimiter//

drop procedure if exists `alter_table_character` //-- 若已存在则删除

create procedure `alter_table_character`()

begin

declare f_name varchar(100);

declare b int default 0; /*是否达到记录的末尾控制变量*/

-- 注意修改下面的数据库名称 wsm_aliyun

declare table_name cursor for SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = ‘wsm_aliyun‘ and TABLE_NAME like ‘wsm_%‘ AND TABLE_COLLATION = ‘utf8_unicode_ci‘;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;

OPEN table_name;

REPEAT

FETCH table_name INTO f_name; /*获取第一条记录*/

SET @STMT :=CONCAT("ALTER TABLE ",f_name," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;");

PREPARE STMT FROM @STMT;

EXECUTE STMT;

-- INSERT into TestTable(name) VALUES (f_name);

-- ALTER TABLE f_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

UNTIL b = 1

END REPEAT;

close table_name;