Considerations for mysql to modify case parameters
Reason: the value of the original parameter lower_case_table_names in the database is 0, which needs to be modified to case-insensitive, that is, 1. However, after the revision, it was found that the table of the original uppercase letters could not be found.
Modification process
1, modify lower_case_table_names=1 in the parameter file
2. Restart the mysql service
Problem: found that after the modification, the tables of some uppercase letters in the database could not be found.
Test:
1, first set the library to be case-sensitive
Mysql > show variables like 'lower_case_table_names'
+-+ +
| | Variable_name | Value |
+-+ +
| | lower_case_table_names | 0 | |
+-+ +
1 row in set (0.00 sec)
2, create 2 tables with uppercase letters
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)
3. Set lower_case_table_names = 1 in the modified parameter / etc/my.cnf
Restart the database
View the value of the parameter at this time
Mysql > show variables like 'lower_case_table_names'
+-+ +
| | Variable_name | Value |
+-+ +
| | lower_case_table_names | 1 | |
+-+ +
1 row in set (0.00 sec)
4. View the database
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
The question is:
It is found that when lower_case_table_names = 1 is modified, the previously created table names are not recognized.
Conclusion:
1. You can't change the case of the database at will, otherwise the uppercase table name will not be recognized.
2. The modification of lower_case_table_names parameters is non-dynamic and the database must be restarted.
How to modify the case format:
If the original database is case-sensitive, if you want to convert to case-insensitive, you mainly need to take the following three steps.
1. Export database data through mysqldump
2, change lower_case_table_names = 1 in the my.cnf file, and restart the mysql database.
3. Import the exported data into the mysql database
