数据由 Oracle 迁入 MySQL , 由于之前 Oracle 区分大小写, MySQL 的配置使用了默认配置, 导致一些数据导入失败, 有的唯一键报错, 冲突
将测试过程记录在下面
数据库版本: MySQL 5.7.11
校对规则一般有这些特征:
两个不同的字符集不能有相同的校对规则
每个字符集有一个默认校对规则例如, utf8 默认校对规则是 utf8_general_ci
存在校对规则命名约定: 它们以其相关的字符集名开始, 通常包括一个语言名, 并且以_ci(大小写不敏感)_cs(大小写敏感) 或_bin(二元) 结束
查看支持的校验规则:
- mysql > SHOW COLLATION
- like 'utf8%';
- +
- --------------------------+---------+-----+---------+----------+---------+
- | Collation | Charset | Id |
- Default
- | Compiled | Sortlen |
- +
- --------------------------+---------+-----+---------+----------+---------+
- | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
- | utf8_bin | utf8 | 83 | | Yes | 1 |
- | utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
- ...
- | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
- | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
- | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
- | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
查看本地的校验规则:
- mysql> show global variables like '%coll%';
- +
- ----------------------+--------------------+
- | Variable_name | Value |
- +
- ----------------------+--------------------+
- | collation_connection | utf8mb4_unicode_ci |
- | collation_database | utf8mb4_unicode_ci |
- | collation_server | utf8mb4_unicode_ci |
- +
- ----------------------+--------------------+
生产中数据库使用的编码为 utf8mb4, 校验规则为 utf8mb4_unicode_ci, 对大小写不敏感
如果需要大小写敏感, 需要将排序规则修改为 utf8mb4_bin.
测试后结果: 修改数据库配置后, 不会对已经存在的表造成影响, 如要生效需要修改特定列的排序规则优先级大概是这样: 列 > 表 > 数据库 > 服务器
有两种方法使查询区分大小写:
第一种方法为修改列级别的校验规则为 utf8mb4_bin
T 表
- CREATE TABLE `T` (
- `name` varchar(20) COLLATE
- utf8mb4_unicode_ci
- DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
- mysql> select * from T;
- +------+
- | name |
- +------+
- | YOU |
- | You |
- | you |
- | you |
- | yOU |
- +------+
T2 表: 将列校对规则修改为 utf8mb4_bin
- CREATE TABLE `T2` (
- `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
- mysql> select * from T2;
- +------+
- | name |
- +------+
- | yOU |
- | you |
- +------+
查询:
T:(未区分大小写)
- mysql> select * from T where name = 'you';
- +------+
- | name |
- +------+
- | YOU |
- | You |
- | you |
- | you |
- | yOU |
- +------+
T2:(已经区分大小写)
- mysql> select * from T2 where name = 'you';
- +------+
- | name |
- +------+
- | you |
- +------+
第二种方法: 不修改配置, 表结构, 而使用如下的查询语句:
T:(未修改表)
- mysql> select * from T where name = binary'you';
- +------+
- | name |
- +------+
- | you |
- | you |
- +------+
来源: http://www.linuxidc.com/Linux/2018-01/150617.htm