mysql case 比较大小写_MySQL区分大小写 （lower_case_table_names)

1、参数含义：

lower_case_table_names： 此参数不可以动态修改，必须重启数据库

lower_case_table_names = 1  表名存储在磁盘是小写的，但是比较的时候是不区分大小写

lower_case_table_names=0  表名存储为给定的大小和比较是区分大小写的

lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的

2、修改  参数lower_case_table_names 产生的问题

mysql> show variables like 'lower_case_table_names';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| lower_case_table_names | 0     |

+------------------------+-------+

1 row in set (0.00 sec)

在mysql系统中原来的参数是lower_case_table_names=0 ，就是存储的时候区分大小写，创建了表Tt  tT

mysql>  create table Tt(id int);

Query OK, 0 rows affected (0.10 sec)

mysql>  create table tT(id int);

Query OK, 0 rows affected (0.03 sec)

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| Tt             |

| tT             |

+----------------+

2 rows in set (0.00 sec)

修改参数 /etc/my.cnf 中设置 lower_case_table_names = 1

mysql> show  variables like 'lower_case_table_names';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| lower_case_table_names | 1     |

+------------------------+-------+

1 row in set (0.00 sec)

重启数据库

然后查看数据库

mysql> select * From tT;

ERROR 1146 (42S02): Table 'test.tt' doesn't exist

mysql> select * From tT;

ERROR 1146 (42S02): Table 'test.tt' doesn't exist

你可以发现当修改lower_case_table_names = 1后，之前创建的表名都不识别了

---创建表后，你可以发现表名不区分大小写

mysql> create table Tt(id int);

Query OK, 0 rows affected (0.23 sec)

mysql> show tables;

+-------------+

| Tables_in_t |

+-------------+

| tt          |

+-------------+

1 row in set (0.00 sec)

从此可以看出lower_case_table_names = 1，创建表名，不区分大小写

结论：不能随意修改数据库的大小写，否则表名会认不出来

lower_case_table_names 参数的修改是非动态的，必须重启数据库

3、如何修改大小写格式

如果原来所建立库及表都是对大小写敏感的，想要转换为对大小写不敏感，主要需要进行如下3步：

1.将数据库数据通过mysqldump导出;

2.在my.cnf中更改lower_case_tables_name = 1，并重启mysql数据库。

3.将导出的数据导入mysql数据库中。

On Windows the default value is 1. On OS X, the default value is 2.

If you are usingInnoDBtables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

In previous versions of MySQL, using different settings forlower_case_table_nameson replication masters and slaves could cause replication to fail when the slave used a case-sensitive file system. This issue is resolved in MySQL 5.6.1.

You shouldnotsetlower_case_table_namesto 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or OS X). It is an unsupported combination that could result in a hang condition when running anINSERT INTO ... SELECT ... FROM tbl_nameoperation with the wrongtbl_nameletter case. WithMyISAM, accessing table names using different letter cases could cause index corruption.

原文链接：https://blog.csdn.net/wll_1017/article/details/55105180