MySQL 字段内容区分大小写

数据由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 |

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/8e369a1200fa1904b75d77819d26843c.html