MySQL 关于表名大小写的参数

Love The Way You Lie 2024-04-18 01:22 54阅读 0赞

查看现有的大小写参数  
\[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决定操作系统中文件名的大小写，是只读的，不能修改  
![Image 1][]  
  
\[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/

[Image 1]: