Linux mysql table name Case sensitivity settings and experimental testing

Modify my. ini file. Add the following set-variable = lower_case_table_names = 0 (0: Case Sensitive; 1: Case Insensitive) to the mysqld section and restart the mysql service.

1. lower_case_tables_name = 0 (default in linux)
Start the mysql tutorial directly and create tables mytable and mytable in the mytest database.

Mysql> use mytest;
Mysql> create table mytable (id int not null, name varchar (10), dt date );
Mysql> create table mytable (id int not null, name varchar (10), dt date );
Mysql> show tables;
+ ------------------ +
| Tables_in_mytest |
+ ------------------ +
| Mytable |
| Mytable |

+ ------------------ +
By default, you can create both mytable and mytable tables.

2. lower_case_tables_name = 1 (we need to set this case)
Go to the etc directory and edit the my. cnf file.
[Root @ mysqlserver etc] # vi my. cnf
Find [mysqld]
Add a row at the end of its global variable: lower_case_table_names = 1
Save, exit, and restart mysql
[Root @ mysqlserver etc] # service mysqld restart;
Go to mysql
[Root @ mysqlserver mysql] # bin/mysql
Mysql> use mytest;
Mysql> select * from mytable;
Mysql> select * from mytable;
The results of the two queries are the same. Both queries are mytable tables, but mytable tables cannot be found (as you can see, but cannot be found)
Let's try again to create a mytable table.
Mysql> create table mytable (id int not null, name varchar (10), dt date );
Error 1050 (42s01): table 'mytable' already exists
The table already exists.

Similarly, we can try to create the mytest Library
Mysql> create database mytest;
Error 1007 (hy000): can't create database 'mytest'; database exists
You will also be prompted that the database already exists.

In this case, table names and database names are case insensitive.

Note: Before setting lower_case_tables_name to 1 in unix, you must first convert the tutorial name and Table Name of the old database to lowercase before restarting mysqld. Otherwise, the previous table cannot be found.