实验：
lower_case_table_names=0

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+



create table t2 (name1 varchar(20),name2 varchar(20));
create table t3 (name1 varchar(20),name2 varchar(20),primary key(name1,name2));


insert into t2 values ('chen','hao');
insert into t2 values ('CHEN','HAO');


mysql> select * from t2 where name1='CH';
+-------+-------+
| name1 | name2 |
+-------+-------+
| ch  | ha   |
| CH  | HA   |
+-------+-------+
2 rows in set (0.00 sec)


mysql> select * from t2 where name1='ch';
+-------+-------+
| name1 | name2 |
+-------+-------+
| ch  | ha   |
| CH  | HA   |
+-------+-------+


create table T2 (name1 varchar(20),name2 varchar(20));




insert into t3 values ('ch','ha');
insert into t3 values ('CH','HA');


mysql> insert into t3 values ('ch','ha');
Query OK, 1 row affected (0.00 sec)


mysql> insert into t3 values ('CH','HA');
ERROR 1062 (23000): Duplicate entry 'CH-HA' for key 'PRIMARY'

小结：=0时，区分大小写，所有t2,T2表是不一样的，都可以创建。数据存储时，是实际录入大小存储的，查询时是不区分的，lower_case_table_names只对对象名有效


lower_case_table_names=1

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+


create table t4 (name1 varchar(20),name2 varchar(20));
create table t5 (name1 varchar(20),name2 varchar(20),primary key(name1,name2));

insert into t4 values ('ch','ha');
insert into t4 values ('CH','HA');


mysql> select * from t4 where name1='CH';
+-------+-------+
| name1 | name2 |
+-------+-------+
| ch  | ha   |
| CH  | HA   |
+-------+-------+

mysql> select * from t4 where name1='ch';
+-------+-------+
| name1 | name2 |
+-------+-------+
| ch  | ha   |
| CH  | HA   |
+-------+-------+


create table T4 (name1 varchar(20),name2 varchar(20));


mysql> create table T4 (name1 varchar(20),name2 varchar(20));
ERROR 1050 (42S01): Table 't4' already exists
mysql> 


insert into t5 values ('ch','ha');
insert into t5 values ('CH','HA');


mysql> create table T4 (name1 varchar(20),name2 varchar(20));
ERROR 1050 (42S01): Table 't4' already exists
mysql> insert into t5 values ('ch','ha');
Query OK, 1 row affected (0.00 sec)


mysql> insert into t5 values ('CH','HA');
ERROR 1062 (23000): Duplicate entry 'CH-HA' for key 'PRIMARY'




小结：不区分大小写，t5与T5是一样的，只能创建一个表，数据存储时，是实际录入大小存储的，查询时是不区分的，lower_case_table_names只对对象名有效


查询是指定校对规则来查询：


 mysql>  select * from t4 where name1=('CH' collate utf8_bin);
+-------+-------+
| name1 | name2 |
+-------+-------+
| CH  | HA   |
+-------+-------+
1 row in set (0.00 sec)


mysql>  select * from t4 where name1=('ch' collate utf8_bin);
+-------+-------+
| name1 | name2 |
+-------+-------+
| ch  | ha   |
+-------+-------+
1 row in set (0.00 sec)


mysql> select * from t4 where name1='ch';
+-------+-------+
| name1 | name2 |
+-------+-------+
| ch  | ha   |
| CH  | HA   |
+-------+-------+

总结：是否区分大小写和校对规则有关，默认设的规则是大小写不敏感的。
xxx_bin：将字符串中的第一个字符用二进制数据存储，区分大小写
xxx_general_ci: ci不分区大小写

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 |
默认是采用utf8_general_ci校对规则，因此不区分大小写，可以指定utf8_bin校对规则，可以区分大小写


如果希望存储和查询都是区分大小写的，则用下面的参数控制：
collation_server=utf8_bin  存储和查询都是区分大小写的
lower_case_table_names=1   表名区分大小的