【mysql笔记】区分大小写（数据库名、表名、表别名）

mysql中可以通过设置lower_case_table_names参数来控制数据库名、表名、表别名 是否区分大小写：

lower_case_table_names的值	是否区分大小写	数据库中的存储是否区分大小写
0	是	是
1	否	否，小写形式存储
2	是	table names are stored as given but compared in lowercase

Linux（区分大小写）

Windows（不区分大小写）

Mac OS X（不区分大小写）

注释：

      1） 如果你使用的是InnoDB引擎，最好把lower_case_table_names设置为1，强制把数据库名、表名转换为小写字母存储。

【lower_case_table_name=0 】

HOSTNAME [test] [17:08:56]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.07 sec)

HOSTNAME [test] [17:08:31]> create table t1(id int);
Query OK, 0 rows affected (0.33 sec)

HOSTNAME [test] [17:08:39]> create table T1(name int);   
Query OK, 0 rows affected (0.12 sec)

HOSTNAME [test] [17:08:47]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| T1             |
| t1             |
+----------------+
2 rows in set (0.03 sec)

【lower_case_table_name=1 】

HOSTNAME [test] [17:05:10]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.06 sec)

HOSTNAME [test] [17:05:13]> create table T1(name int);
Query OK, 0 rows affected (0.18 sec)

HOSTNAME [test] [17:05:17]> show tables;      
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.03 sec)

HOSTNAME [test] [17:05:22]>

【把lower_case_table_name=0 修改为 lower_case_table_name=1 】

在Unix/Linux平台上，如果你要把lower_case_table_name=0 修改为lower_case_table_name=1，请在stop mysqld和restart之前，把所有的数据库名和表名都转换成小写的。

HOSTNAME [test] [17:10:14]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.04 sec)

HOSTNAME [test] [17:10:16]> desc T1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.12 sec)

HOSTNAME [test] [17:10:28]> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.07 sec)

HOSTNAME [test] [17:10:31]> drop table T1;
Query OK, 0 rows affected (0.08 sec)

HOSTNAME [test] [17:11:04]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| T1             |
+----------------+
1 row in set (0.08 sec)

HOSTNAME [test] [17:11:07]> desc t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
HOSTNAME [test] [17:11:10]> desc T1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

• lower_case_table_names

If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase
on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This
option also applies to database names and table aliases. For additional information, see Section 8.2.2, “Identifier Case Sensitiv-
MySQL Server Administration
412
ity”.
You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows
or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases,
index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.
If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.
The setting of this variable has no effect on replication filtering options. This is a known issue which is fixed in MySQL 5.6.
See Section 15.2.3, “How Servers Evaluate Replication Filtering Rules”, for more information.
You should not use different settings for lower_case_table_names on replication masters and slaves. In particular, you
should not do this when the slave uses a case-sensitive file system, as this can cause replication to fail. This is a known issue
which is fixed in MySQL 5.6.