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

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


lower_case_table_names的值是否区分大小写数据库中的存储是否区分大小写
0
1否,小写形式存储
2table 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.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值