修改数据库的字符集和排序规则
atler database jira character set utf8 collate utf8_bin;
生成修改所有表的排序规则的语句
SELECT CONCAT(‘ALTER TABLE ‘,TABLE_SCHEMA,’.’, TABLE_NAME, ’ COLLATE=utf8_bin;’) ‘SQL’
FROM information_schema.TABLES
WHERE TABLE_COLLATION <> ‘utf8_bin’ and TABLE_SCHEMA =‘jira’;
生成修改所有字符串类型字段的排序规则的语句
SELECT CONCAT(‘ALTER TABLE ‘, TABLE_SCHEMA,’.’,TABLE_NAME, ’ MODIFY COLUMN ‘,COLUMN_NAME,’ ‘,COLUMN_TYPE,’ CHARACTER SET utf8 COLLATE utf8_bin;’) ‘SQL’
FROM information_schema.COLUMNS
WHERE DATA_TYPE=‘varchar’ AND COLLATION_NAME <> ‘utf8_bin’ and TABLE_SCHEMA =‘jira’;
执行批量生成的语句,修改完毕。