修改mysql所有表排序规则

#改变字段数据SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',COLUMN_NAME '字段',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',

浪子尘晨

2091人浏览 · 2021-09-09 15:49:21

浪子尘晨 · 2021-09-09 15:49:21 发布

#改变字段数据
SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',COLUMN_NAME '字段',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`COLUMNS` 
WHERE
   TABLE_SCHEMA RLIKE 'ndc_saas_uc'
   AND 	COLUMN_TYPE RLIKE 'varchar';

#修改表排序规则
SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`TABLES`
WHERE
	TABLE_SCHEMA RLIKE 'ndc_business_4028818d6c64e701016c658cab1e0000';

运行完之后，执行 “修改sql” 就可以了