原文链接:http://my.oschina.net/xuqiang/blog/507629
记录一个在工作中遇到的问题,也不算是问题,为的是找一种简便的方法批量修改数据表字段的排序规则,在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'
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';
相关推荐
在处理MySQL数据库时,遇到字符集编码不一致导致的数据乱码问题非常常见。这不仅会影响数据的正确显示,还可能导致数据丢失或损坏。根据提供的标题、描述、标签以及部分内容来看,本文将围绕MySQL乱码问题进行深入...
批量更改数据库字符集: 可通过执行查询语句找出所有字符集不是UTF-8的数据库,然后使用ALTER DATABASE语句批量更改。 网站打开慢的排查方法及解决示例: 排查方法包括使用浏览器的开发者工具查看加载时间,使用...
本文档提供了Linux运维中关于MySQL数据库基础操作的企业面试题,题干主要涉及MySQL的日常管理与操作,适合数据库管理员或相关从业人员进行面试准备。以下知识点是根据文档内容提炼而成。 1. 启动与停止MySQL服务 -...
本文档将深入探讨MySQL中一系列实用且关键的SQL命令,旨在帮助数据库管理员、开发者以及数据分析师更好地理解和操作MySQL数据库。 #### 显示字符集与校对规则信息 - `SHOW CHARACTER SET [like_or_where]`:此命令...
2. **创建数据库**:在连接后,可以创建新的数据库,并设定字符集和校对规则。 3. **管理表**:在选定的数据库中,可以添加、修改或删除表,设置字段类型、长度、默认值、索引等属性。 4. **执行SQL语句**:Adminer...
MySQL数据库的远程连接是数据库管理员和开发者经常遇到的需求,它允许用户从远程计算机访问数据库服务器。本文将详细讲解两种常用的方法来实现MySQL的远程连接。 首先,第一种方法是通过命令行工具直接连接。在终端...
- **datadir=path**:数据文件存放目录,是MySQL数据库文件(如表空间文件)的主要存储位置。 - **pid-file=filename**:指定MySQL进程ID文件的名称及位置,主要用于记录MySQL服务启动后的进程ID。在Linux系统中,这...
- **`collation_server=utf8_general_ci`**:设定MySQL服务端默认校对规则为`utf8_general_ci`,表示不区分大小写的字符比较方式。 - **`default_storage_engine=InnoDB`**:指定数据库的默认存储引擎为InnoDB,...
#### 十二、MySQL数据库备份方法 - **工具**: 使用 `mysqldump` 命令行工具。 - **示例**: `mysqldump -u username -p dbname > backup.sql` 将数据库导出到SQL文件中。 #### 十三、分区表的概念与优势 - **定义*...
这个工具通常适用于需要频繁从Excel表格中导入数据到SQL Server、MySQL、Oracle等数据库的情况,例如在数据分析、报表制作或者数据库管理中。 Excel是一款广泛使用的电子表格软件,它方便用户进行数据的组织、计算...
- **设置建议**:校对规则应与字符集相匹配,例如`utf8mb4_unicode_ci`。 9. **enable-named-pipes** - **功能描述**:启用Windows系统中名为管道的通信方式。 - **设置建议**:仅当确实需要时才开启,以减少...