8

MySql批量修改表和表内字段的字符集和排序规则

 1 year ago
source link: https://mirari.cc/2020/07/22/MySql%E6%89%B9%E9%87%8F%E4%BF%AE%E6%94%B9%E8%A1%A8%E5%92%8C%E8%A1%A8%E5%86%85%E5%AD%97%E6%AE%B5%E7%9A%84%E5%AD%97%E7%AC%A6%E9%9B%86%E5%92%8C%E6%8E%92%E5%BA%8F%E8%A7%84%E5%88%99/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

MySql批量修改表和表内字段的字符集和排序规则

2020-07-22 学习笔记

0 1k 1 分钟

在将测试库的新增表通过Navicat向阿里云的MySql数据库复制后，发现前端页面请求查询失败。

目标数据库的默认排序规则是utf8mb4_0900_ai_ci，已存在的表都使用了这个编码，而来源数据库的排序规则是utf8mb4_unicode_ci，新复制的表都用的是后者。

数据库进行多表关联查询时，如果两张表的字符集或者排序规则不一致，就会报错。

从Navicat里手动修改编码效率很低，若只是改一下表，也用不了多久，但问题是只改表是不行的，表内所有varchar的编码并不会跟着表走。

因此还是需要走批量操作的路子。

批量修改字段

SELECT
	CONCAT(
		'ALTER TABLE `',
		TABLE_NAME,
		'` MODIFY `',
		COLUMN_NAME,
		'` ',
		DATA_TYPE,
		'(',
		CHARACTER_MAXIMUM_LENGTH,
		') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
		( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
		';' 
) 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = '数据库名' 
	AND (
	DATA_TYPE = 'varchar' 
	OR DATA_TYPE = 'char')

批量修改表

SELECT
	CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = '数据库名';

将以上SQL语句中的utf8mb4、utf8mb4_unicode_ci、数据库名分别改成自己需要的值，成功执行后，将执行结果即SQL语句复制出来，再执行这些SQL语句即可。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK