MySQL大小写问题

数据库名、表名、表别名大小写

1. window下均不区分大小写(Why？)
2. Linux默认情况下区分大小写

 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> set global lower_case_table_names=1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

修改my.cnf后重启数据库
lower_case_table_names=1

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.01 sec)

mysql> use test;
Database changed

mysql> create database Test;
ERROR 1007 (HY000): Can't create database 'test'; database exists

mysql> create table T  as select * from t;
ERROR 1050 (42S01): Table 'T' already exists 

变量含义解释

lower_case_file_system：数据库所在的文件系统对文件名大小写敏感度。ON表示大小写不敏感 OFF表示敏感

lower_case_table_names：表名大小写敏感度

• 0表示使用Create语句指定的大小写保存文件
• 1表示大小写敏感 文件系统以小写保存
• 2表示使用Create语句指定的大小写保存文件，但MySQL会将之转化为小写(?)

(当Linux设置为2时，错误日志显示[Warning] lower_case_table_names was set to 2, even though your the file system '/home/mysql/master_a/data/' is case sensitive. Now setting lower_case_table_names to 0 to avoid future problems.)

列名大小写

列名列别名均不区分大小写

字段值大小写

1、表与行的collation，bin与cs区分大小写，ci不区分大小写

MySQL对collation约定的命名方式如下：

• *_ci:case insensitive collation，不区分大小写
• *_cs: case sensitive collation，区分大小写
• *_bin: 表示的是binary case sensitive collation，区分大小写的

 # 指定collate为区分大小写
mysql> create table b( id varchar(10)) default charset=utf8 default collate=utf8_bin; 
Query OK, 0 rows affected (0.03 sec)sec)

mysql> insert into b values ('A'),('a'),('B');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from b where id like 'a';
+------+
| id   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select id,count(*) from b group by id;
+------+----------+
| id   | count(*) |
+------+----------+
| A    |        1 |
| B    |        1 |
| a    |        1 |
+------+----------+
3 rows in set (0.03 sec) 

 # 指定collate为忽略大小写
mysql> create table t (name varchar(10)) default charset=utf8 default collate=utf8_general_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values('a'),('A'),('B'),('b'),('c');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t where name like 'a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.01 sec)

mysql> select name,count(*) from t group by name;
+------+----------+
| name | count(*) |
+------+----------+
| a    |        2 |
| B    |        2 |
| c    |        1 |
+------+----------+
3 rows in set (0.04 sec) 

2、字段指定binary

 mysql> alter table t change name name varchar(10) binary;
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t where name like 'A';
+------+
| name |
+------+
| A    |
+------+
1 row in set (0.00 sec)

mysql> select name,count(*) from t group by name;
+------+----------+
| name | count(*) |
+------+----------+
| A    |        1 |
| B    |        1 |
| a    |        1 |
| b    |        1 |
| c    |        1 |
+------+----------+
5 rows in set (0.01 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29733787/viewspace-1207190/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29733787/viewspace-1207190/