数据由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 |
+------+
转载于:https://blog.51cto.com/amnesiasun/1931489