MySQL默认大小写规则

Linux环境下：
数据库名与表名是严格区分大小写的；
表的别名是严格区分大小写的；
列名与列的别名在所有的情况下均是忽略大小写的；
变量名也是严格区分大小写的；
Windows环境下：
数据库名、表名、列名、别名大小写都不区分。
默认规则下的查询：
CREATE TABLE NAME(name VARCHAR(10)); 对这个表，缺省情况下，下面两个查询的结果是一样的：
SELECT * FROM TABLE NAME WHERE name='clip';
SELECT * FROM TABLE NAME WHERE name='Clip';

让MySQL对大小写敏感解决方案

方法一：修改my.ini 文件
修改MySql Server安装目录下的 my.ini 文件，在mysqld节下加入下面一行 set-variable=lower_case_table_names=0 （0：大小写敏感；1：大小写不敏感）。最后重启一下MySql服务即可。
方法二：设置表或行的collation
设置表或行的collation，使其为binary或case sensitive。
在MySQL中，对于Column Collate其约定的命名方法如下：

_bin: 表示的是binary case sensitive collation，区分大小写的
_cs: case sensitive collation，区分大小写
_ci: case insensitive collation，不区分大小写

_bin

########### 
# Start binary collation example 
########### 
mysql> create table case_bin_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_bin; 
Query OK, 0 rows affected (0.02 sec) 
 
mysql> INSERT INTO case_bin_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); 
Query OK, 5 rows affected (0.00 sec) 
Records: 5 Duplicates: 0 Warnings: 0 
 
mysql> SELECT * FROM case_bin_test WHERE word LIKE 'f%'; 
+---------+ 
| word | 
+---------+ 
| froogle | 
| flickr | 
+---------+ 
2 rows in set (0.00 sec) 
 
mysql> SELECT * FROM case_bin_test WHERE word LIKE 'F%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.00 sec) 
 
########### 
# End 
###########

_cs

########### 
# Start case sensitive collation example 
########### 
 
mysql> create table case_cs_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_general_cs; 
Query OK, 0 rows affected (0.08 sec) 
 
mysql> INSERT INTO case_cs_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); 
Query OK, 5 rows affected (0.00 sec) 
Records: 5 Duplicates: 0 Warnings: 0 
 
mysql> SELECT * FROM case_cs_test WHERE word LIKE 'F%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.00 sec) 
 
mysql> SELECT * FROM case_cs_test WHERE word LIKE 'f%'; 
+---------+ 
| word | 
+---------+ 
| froogle | 
| flickr | 
+---------+ 
2 rows in set (0.00 sec) 
 
########### 
# end 
###########

方法三：查询时指定collation

mysql> create table case_test (word VARCHAR(10)) CHARACTER SET latin1; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> INSERT INTO case_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); 
Query OK, 7 rows affected (0.01 sec) 
Records: 7 Duplicates: 0 Warnings: 0 
mysql> SELECT * FROM case_test WHERE word LIKE 'f%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| froogle | 
| flickr | 
| FlicKr | 
+---------+ 
6 rows in set (0.01 sec) 
 
mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'F%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.05 sec) 
 
mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'f%'; 
+---------+ 
| word | 
+---------+ 
| froogle | 
| flickr | 
+---------+ 
2 rows in set (0.00 sec) 
 
mysql> SELECT * FROM case_test WHERE word LIKE 'f%' COLLATE latin1_bin; 
+---------+ 
| word | 
+---------+ 
| froogle | 
| flickr | 
+---------+ 
2 rows in set (0.00 sec) 
 
mysql> SELECT * FROM case_test WHERE word LIKE 'F%' COLLATE latin1_bin; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.01 sec) 
 
mysql> SELECT * FROM case_test WHERE word LIKE 'F%' COLLATE latin1_general_cs; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.04 sec)

方法四：使用binary属性
对于CHAR、VARCHAR和TEXT类型，BINARY属性可以为列分配该列字符集的校对规则。
BINARY属性是指定列字符集的二元校对规则的简写。
排序和比较基于数值字符值。因此也就自然区分了大小写。

#建表时使用：
create  table  table_name( a varchar (20) binary) ;
#修改字段属性：
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin ;
#查询时使用
select  * from  table_name where  binary  a like  'a%'   
select  * from  table_name where  binary  a like  'A%'

实际场景：批量查询后插入

问题出现
数据库：关键词上有唯一索引。

CREATE TABLE `keyword` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键（无意义）',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '关键字',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_name` (`name`) USING BTREE
);

方法说明：根据一批关键词的名称查询是否存在，如果不存在则插入。以下是简化代码：

/**
 * 查找并保存关键词
 *
 * @param keywordNames
 * @desc 该方法的主要目的是为了丰富关键词，不存在的维护到数据库中，个别失败情况不影响上游业务。
 */
public void findOrSaveKeyword(List<String> keywordNames) {    // -01- ["XIU","shao"]
    if (CollectionUtils.isEmpty(keywordNames)) {
        return;
    }
 
    // select * from keyword WHERE name in (keywordNames);
    List<KeywordDTO> dtos = keywordService.batchGetByNames(keywordNames);   // -02- ["xiu","shao"]
 
    List<String> list = dtos.stream().map(KeywordDTO::getName).collect(Collectors.toList());
    keywordNames.removeAll(list);     // -03- ["XIU"]
 
    if (CollectionUtils.isEmpty(keywordNames)) {
        return;
    }
 
    // insert into keyword (name) values ();
    List<KeywordDTO> dtoList = iKeywordService.batchSaveKeywords(keywordNames);   // -04- "XIU"和"xiu"冲突
}

代码执行中经常会出现唯一索引冲突的报错。
最初在建表的时候没有考虑大小写敏感，查询和插入也是直接使用的批量查询和批量插入（// select * from keyword WHERE name in (...); // insert into keyword (name) values (...);）。
根据上文代码块注释可以重现报错过程：

[-01-]步：传入的参数keywordNames值为 ["XIU","shao"]。
[-02-]步：数据库中已经存在["xiu","shao"]，因为不区分大小写，所以小写的xiu会作为结果返回。
[-03-]步：使用removeAll方法去重数据库中已经存在的关键词。这个方法默认是区分大小写的，所以removeAll后，keywordNames中还剩下["XIU"]。
[-04-]步：执行插入操作，插入"Xiu"和数据库中存在的"xiu"冲突报错。

解决方案考虑

产品需求：关键词不区分大小写。
- 如：League of Legends 和 LEAGUE OF LEGENDS 和 league of legends。本质上是同一个含义的关键词，如果识别为不同的关键词存储会造成同义的关键词膨胀，不利于运营使用。
入口控制：（人为限制保证创建关键词的时候只保留一个同义关键词）
- 实际场景中识别插入的关键词很大的数量是算法根据内容识别及其相关信息产生，大小写的值来源无法控制，没有办法统一进行规范。
逻辑中断：（现在的逻辑只要有冲突报错，所有关键词都无法保存）
- 批量插入的时候如果有部分关键词因为冲突插入失败不应该影响其他关键词的插入。

方案选择：
基于以上几点，没有选择上文中的将关键词作为大小写敏感字段的方式进行处理。而是正常插入只保留第一次插入的同义关键词，同时使用了IGNORE关键字保证部分关键词插入错误不影响其他关键词的插入。

将批量插入语句insert into keyword (name) values (...);替换为： insert ignore into keyword (name) values (...);

MySQL大小写导致的批量查询后插入报错

MySQL默认大小写规则

让MySQL对大小写敏感解决方案

_bin

_cs

实际场景：批量查询后插入