mysql字段值如何区分大小写

2022-09-29 23:15:27

今天做sql查询，发现字段值没区分大小写

mysql> select guid,type,parent_guid from api_assets where guid='3rfI2PsSrCz91mTMDgrZjE';
+------------------------+--------+------------------------+
| guid | type | parent_guid |
+------------------------+--------+------------------------+
| 3rfI2PsSrCz91mTMDgrZjE | Window | 3rfI2PsSrCz91mTMDgry9E |
| 3rfI2PsSrCz91mTMDgrzje | Member | 3rfI2PsSrCz91mTMDgrzj1 |
| 3rfI2PsSrCz91mTMDgrzjE | Plate | 3rfI2PsSrCz91mTMDgrzjU |
+------------------------+--------+------------------------+

系统：

　　win7

数据库版本

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+

解决方法

　　1.查询时指定大小写敏感

　　　　在查询时指定大小写“敏感”，加关键字“BINARY”

mysql> select guid,type,parent_guid from api_assets where BINARY guid='3rfI2PsSrCz91mTMDgrZjE';
+------------------------+--------+------------------------+
| guid | type | parent_guid |
+------------------------+--------+------------------------+
| 3rfI2PsSrCz91mTMDgrZjE | Window | 3rfI2PsSrCz91mTMDgry9E |
+------------------------+--------+------------------------+

mysql> select guid,type,parent_guid from api_assets where guid= BINARY '3rfI2PsSrCz91mTMDgrZjE';
+------------------------+--------+------------------------+
| guid | type | parent_guid |
+------------------------+--------+------------------------+
| 3rfI2PsSrCz91mTMDgrZjE | Window | 3rfI2PsSrCz91mTMDgry9E |
+------------------------+--------+------------------------+

　　2.定义表结构时指定字段大小写敏感

　　关键字“BINARY”指定guid字段大小写敏感

CREATE TABLE `api_assets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `guid` varchar(255) BINARY NOT NULL,
  ……
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL允许在大多数字符串类型上使用BINARY关键字，用于指明所有针对该字段的运算是大小写敏感的　

　　3.修改排序规则（COLLATION）

mysql> show variables like 'collation\_database';
+--------------------+-----------------+
| Variable_name | Value |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+

　　Collation以 "_ci"结尾的不区分大小写（ci——Case Ignore），以"_bin"或者"_cs"结尾的区分大小写

　　将Collation改为 utf8_bin（大小写敏感的）

　　可以为库、表、列指定Collation。

　　优先级为列>表>库

　　eg:

mysql> CREATE DATABASE test COLLATE utf8_bin;
Query OK, 1 row affected

mysql> use test;
Database changed
mysql> show variables like 'collation\_database';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| collation_database | utf8_bin |
+--------------------+----------+

mysql> select guid,type,parent_guid from api_assets where
guid='3rfI2PsSrCz91mTMDgrZjE';
+------------------------+--------+------------------------+
| guid | type | parent_guid |
+------------------------+--------+------------------------+
| 3rfI2PsSrCz91mTMDgrZjE | Window | 3rfI2PsSrCz91mTMDgry9E |
| 3rfI2PsSrCz91mTMDgrzje | Member | 3rfI2PsSrCz91mTMDgrzj1 |
| 3rfI2PsSrCz91mTMDgrzjE | Plate | 3rfI2PsSrCz91mTMDgrzjU |
+------------------------+--------+------------------------+
3 rows in set

mysql> ALTER TABLE `api_assets` DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> select guid,type,parent_guid from api_assets where guid='3rfI2PsSrCz91mTMDgrZjE';
+------------------------+--------+------------------------+
| guid | type | parent_guid |
+------------------------+--------+------------------------+
| 3rfI2PsSrCz91mTMDgrZjE | Window | 3rfI2PsSrCz91mTMDgry9E |
| 3rfI2PsSrCz91mTMDgrzje | Member | 3rfI2PsSrCz91mTMDgrzj1 |
| 3rfI2PsSrCz91mTMDgrzjE | Plate | 3rfI2PsSrCz91mTMDgrzjU |
+------------------------+--------+------------------------+
3 rows in set

mysql> ALTER TABLE `api_assets` MODIFY COLUMN `guid` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER `id`;
Query OK, 3344 rows affected
Records: 3344 Duplicates: 0 Warnings: 0

mysql> select guid,type,parent_guid from api_assets where guid='3rfI2PsSrCz91mTMDgrZjE';
+------------------------+--------+------------------------+
| guid | type | parent_guid |
+------------------------+--------+------------------------+
| 3rfI2PsSrCz91mTMDgrZjE | Window | 3rfI2PsSrCz91mTMDgry9E |
+------------------------+--------+------------------------+
1 row in set