mysql的表的大小寫區分

mysql的表的大小寫區分

在linux的環境下，在mysql的配置裡會對大小寫有一定的要求的。尤其是一些雲平台提供的固定環境是配置好這些要求，所以需要對這些有一定的了解。（比如阿裡雲就會設定lower_case_table_names=1，具體分析在後面）

 

1、表名大小寫

Linux默認情況下區分大小寫(另外，MySQL在Windows下數據庫名、表名、列名、別名都不區分大小寫,即使設置lower_case_table_names=0，在查詢時還是不會區分大小寫。只是在導入導出時會對大小寫有區別。原因是大小寫的功能是在操作系統的文件系統對大小對區別的基礎上實現的。WINDOWS的文件系統查詢時不區分文件大小寫。)

（1）變量含義解釋

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.)

（2）變量更新

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)

因為修改了配置lower_case_table_names=1，原來在mysql的show tables裡面有表的記錄, select操作時會顯示查不到表

+--------------------------------+
| Tables_in_testdb |
+--------------------------------+
| TABLE1|
| TABLE2|

但是
mysql> select * from TABLE1;
ERROR 1146 (42S02): Table 'testdb.TABLE1' doesn't exist
主要原因是大小寫敏感以及文件系統以小寫文件名寫入，而原來的表名是大寫的。

2、表字段數據大小寫

表字段數據的校對，bin與cs區分大小寫，ci不區分大小寫
MySQL對校對約定的命名方式如下：
*_ci:case insensitive collation，不區分大小寫
*_cs:case sensitive collation，區分大小寫
*_bin: 表示的是binary case sensitive collation，將字符串每個字符串用二進制數據編譯存儲。 區分大小寫，而且可以存二進制的內容

（1）表字段數據區分大小寫

指定校對為區分大小寫

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)

（2）表字段數據不區分大小寫

指定校對為忽略大小寫

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)

不過如果字段類型是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)