Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to understand the parameters of MySQL about the case of table names. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

View existing case parameters

[mysql@localhost percona] $bin/mysql-- defaults-file=/u01/mysql_data/my.cnf-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 6

Server version: 5.7.17-11-log Percona Server (GPL), Release 11, Revision f60191c

Mysql > show variables like'% lower%'

+-+ +

| | Variable_name | Value |

+-+ +

| | lower_case_file_system | OFF |

| | lower_case_table_names | 1 | |

+-+ +

2 rows in set (0.00 sec)

The value of lower_case_table_names is 1, which means that the table name of the database is case-insensitive.

Mysql > use test

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | b |

| | c |

| | cpu_stat |

| | p |

| | support_his |

| | t |

| | vbuttt | |

| | z |

+-+

8 rows in set (0.00 sec)

Mysql > select * from t

+-- +

| | a |

+-- +

| | 1 |

| | 2 |

| | 3 |

+-- +

3 rows in set (0.04 sec)

Mysql > select * from T

+-- +

| | a |

+-- +

| | 1 |

| | 2 |

| | 3 |

+-- +

3 rows in set (0.00 sec)

This parameter is static and cannot be modified online. You need to modify the configuration file.

Mysql > set global lower_case_table_names=0

ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

Shut down the database

[mysql@localhost percona] $bin/mysqladmin-uroot-S / u01/mysql_data/mysql.sock shutdown-p

Modify parameters

[mysql@localhost percona] $vim / u01/mysql_data/my.cnf

[mysqld]

Lower_case_table_names = 0

Restart the database

[mysql@localhost percona] $bin/mysqld_safe-- defaults-file=/u01/mysql_data/my.cnf &

[mysql@localhost percona] $bin/mysql-- defaults-file=/u01/mysql_data/my.cnf-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 4

Server version: 5.7.17-11-log Percona Server (GPL), Release 11, Revision f60191c

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > use test

Database changed

Mysql > show variables like'% lower%'

+-+ +

| | Variable_name | Value |

+-+ +

| | lower_case_file_system | OFF |

| | lower_case_table_names | 0 | |

+-+ +

2 rows in set (0.01sec)

Mysql > select * from T

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

Mysql > select * from t

+-- +

| | a |

+-- +

| | 1 |

| | 2 |

| | 3 |

+-- +

3 rows in set (0.02 sec)

Note: it is not recommended to modify this parameter on the production library, which may cause the existing library to be unavailable.

The parameter lower_case_file_system determines the case of the file name in the operating system. It is read-only and cannot be modified.

[mysql@localhost percona] $cd / u01/mysql_data/test/

[mysql@localhost test] $ls

B.frm b.ibd c.frm c.ibd cpu_stat.frm cpu_stat.ibd db.opt p.frm p.ibd support_his.frm support_his.ibd t.frm t.ibd v_t.frm z.frm z.ibd

After reading the above, do you have any further understanding of how to understand MySQL's parameters about table name case? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.