mysql的varchar值如何区分大小写？和校对规则有关

查看数据库的字符集和校对规则：

SHOW COLLATION WHERE CHARSET = 'utf8mb4';

SHOW COLLATION WHERE CHARSET = 'utf8';

Collation后缀的含义：

mysql> set names 'utf8mb4' collate 'utf8mb4_0900_ai_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'ā' = 'á';
+-------------+
| 'ā' = 'á'   |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> set names 'utf8mb4' collate 'utf8mb4_0900_as_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'ā' = 'á';
+-------------+
| 'ā' = 'á'   |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

只有_cs和_bin校对规则的才能区分大小写

创建表：

CREATE TABLE `lmwy_product`.`aa10` 
(
    `id` VARCHAR (20), `code` VARCHAR (20)
) ENGINE = INNODB CHARSET = utf8 COLLATE = utf8_bin ;

CREATE TABLE `lmwy_product`.`aa11`
( 
    `id` VARCHAR(20), `code` VARCHAR(30) 
) ENGINE=INNODB CHARSET=utf8;

添加记录：

INSERT INTO `lmwy_product`.`aa10` (`id`, `code`) VALUES ('1', 'aaaa'); 

INSERT INTO `lmwy_product`.`aa11` (`id`, `code`) VALUES ('1', 'aaaa'); 

查询测试：

# aa10 utf8 utf8_bin, 无结果，区分大小写
SELECT * FROM aa10 WHERE aa10.`code` = 'aaAA';
# 修改aa10为utf8 utf8_general_ci 再添加新记录bbbb，无结果，仍旧区分大小写
SELECT * FROM aa10 WHERE aa10.`code` = 'BBbb';


# aa11 utf8 utf8_general_ci, 返回一条记录，不区分大小写
SELECT * FROM aa11 WHERE aa11.`code` = 'aaAA';
# 添加binary关键字, 无结果，区分大小写
SELECT * FROM aa11 WHERE BINARY aa11.`code` = 'aaAA';
# 修改aa11为utf8 utf8_bin 再添加新记录bbbb，返回一条记录，仍旧不区分大小写
SELECT * FROM aa11 WHERE aa11.`code` = 'bbBB';
# 添加binary关键字, 无结果，区分大小写
SELECT * FROM aa11 WHERE BINARY aa11.`code` = 'bbBB';

关联查询 通过在where 添加关键字 binary 来设置区分大小写：

SELECT 
  * 
FROM
  aa11 
  LEFT JOIN aa21 
    ON aa11.`code` = aa21.`code` 
WHERE BINARY aa11.`code` = 'aaAA' ;