MySQL 关于表名大小写的参数
查看现有的大小写参数[mysql@localhost percona]$ bin/mysql --defaults-file=/u01/mysql_data/my.cnf -uroot -pEnter password:...
·
查看现有的大小写参数
[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)
lower_case_table_names的值为1，代表数据库的表名不区分大小写
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| b |
| c |
| cpu_stat |
| p |
| support_his |
| t |
| v_t |
| 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)
这个参数是静态参数，不能在线修改，需要修改配置文件
mysql> set global lower_case_table_names=0;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable
关闭数据库
[mysql@localhost percona]$ bin/mysqladmin -uroot -S /u01/mysql_data/mysql.sock shutdown -p
修改参数
[mysql@localhost percona]$ vim /u01/mysql_data/my.cnf
[mysqld]
lower_case_table_names = 0
重启数据库
[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.01 sec)
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)
注意：不建议在生产库上面修改这个参数，可能导致现有的库不能使用
参数lower_case_file_system决定操作系统中文件名的大小写，是只读的，不能修改
[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
[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)
lower_case_table_names的值为1，代表数据库的表名不区分大小写
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| b |
| c |
| cpu_stat |
| p |
| support_his |
| t |
| v_t |
| 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)
这个参数是静态参数，不能在线修改，需要修改配置文件
mysql> set global lower_case_table_names=0;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable
关闭数据库
[mysql@localhost percona]$ bin/mysqladmin -uroot -S /u01/mysql_data/mysql.sock shutdown -p
修改参数
[mysql@localhost percona]$ vim /u01/mysql_data/my.cnf
[mysqld]
lower_case_table_names = 0
重启数据库
[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.01 sec)
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)
注意：不建议在生产库上面修改这个参数，可能导致现有的库不能使用
参数lower_case_file_system决定操作系统中文件名的大小写，是只读的，不能修改
[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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2135770/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-2135770/
无需安装部署，在线快速体验 Byzer
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)