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

原创
2015/09/17 16:13
阅读数 2.5K

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

1.The database level
2.The table level
3.The column level

https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database 这篇文章说得比较详细。

那天遇到的问题是这样子的,
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'
    OR
    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;        
end;
//
/*切换回系统默认的命令结束标志*/
delimiter ;
-- 执行存储过程
call alter_table_character();
-- 修改数据库的校对规则
set names 'utf8' collate 'utf8_general_ci';

-- 查询修改的结果,其实还可以用下面的语句生成相应的SQL,执行这个SQL来完成修改,当然没有上面的存储过程效率高。

-- 查看数据库的校对规则,结果全都为:utf8_general_ci,表示已修改
show variables like 'collation_%';
-- 查看数据库的校对规则,没有数据表明已全部修改。
SELECT
	CONCAT('alter table ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as new_sql
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_SCHEMA = 'wsm_aliyun'
AND TABLE_NAME LIKE 'wsm_%' -- 数据库名称
AND TABLE_COLLATION = 'utf8_unicode_ci';


-- 查询列结果,没有数据表明已全部修改。
SELECT
	CONCAT(
		'ALTER TABLE `',
		table_name,
		'` MODIFY `',
		column_name,
		'` ',
		DATA_TYPE,
		'(',
		CHARACTER_MAXIMUM_LENGTH,
		') CHARACTER SET UTF8 COLLATE utf8_general_ci;'
	) as new_sql
FROM
	information_schema.COLUMNS
WHERE
	TABLE_SCHEMA = 'wsm_aliyun' -- 数据库名
AND TABLE_NAME LIKE 'wsm_%'
AND DATA_TYPE = 'varchar'
AND CHARACTER_SET_NAME = 'utf8'
AND COLLATION_NAME = 'utf8_unicode_ci';



展开阅读全文
打赏
2
10 收藏
分享
加载中
更多评论
打赏
0 评论
10 收藏
2
分享
返回顶部
顶部