MySQL排序规则批量修改

排序规则选择

mysql中utf8mb4_general_ci与utf8mb4_unicode_ci有什么区别呢？
为什么大家都使用utf8mb4_general_ci而不是utf8mb4_unicode_ci呢？

1. 使用utf8mb4可以支持emoji表情存储，utf8不支持。
2. utf8_bin和utf8_general_ci两者区别。
   ci是 case insensitive, 即 “大小写不敏感”, a 和 A 是一样的;
   bin 是二进制, a 和 A 不同.
   utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
   utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果
   utf8_bin 字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容
3. utf8mb4_unicode_ci和utf8mb4_general_ci两者区别。
   utf8mb4_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
   utf8mb4_general_ci校对速度快，但准确度稍差。
   utf8mb4_unicode_ci准确度高，但校对速度稍慢。
   如果你的应用有德语、法语或者俄语，请一定使用utf8mb4_unicode_ci。
   一般用utf8mb4_general_ci就够了，到现在也没发现问题。
   对于数据库的使用，utf8mb4_general_ci 已经足够的准确，并且相较与 utf8mb4_unicode_ci速度上有优势，固可放心采用之。

-- 修改数据库排序规则
ALTER DATABASE base CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
-- 修改表的默认排序规则，表中字段排序规则不变
ALTER TABLE hello DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 修改表和表中字段的排序规则
ALTER TABLE hello CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 修改表中某个字段的排序规则
ALTER TABLE hello MODIFY name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci default '' null comment '姓名' ;

如果一个数据库很多表都需要修改排序规则怎么办？

-- 查出指定库中所有不符合要求的排序规则表，并生成修正SQL
SELECT TABLE_SCHEMA                                                            '库名',
       TABLE_NAME                                                              '表名',
       TABLE_COLLATION                                                         '当前排序规则',
       CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,
              ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') '修正SQL'
from information_schema.tables
where TABLE_SCHEMA = 'base'
  and TABLE_COLLATION != 'utf8mb4_general_ci';

复制修正SQL，批量执行。

批量修改指定库中的所有表字段排序规则

-- 批量拼接修改字段排序规则SQL
SELECT TABLE_SCHEMA                  '数据库',
       TABLE_NAME                    '表',
       COLUMN_NAME                   '字段',
       COLUMN_TYPE                   '字段类型',
       COLUMN_DEFAULT                '字段默认值',
       COLUMN_COMMENT                '字段注释',
       COLUMN_KEY                    '主键,唯一索引,非唯一索引',
       IS_NULLABLE                   '字段是否可为NULL',
       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 ',
              IF(IS_NULLABLE = 'NO', 'not null ', ''),
              IF(COLUMN_DEFAULT is null, ' ', CONCAT('default \'', COLUMN_DEFAULT, '\' ')), 'comment \'',
              COLUMN_COMMENT, '\';') '修正SQL'
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = 'base'
  and COLLATION_NAME RLIKE 'utf8_general';