MySQL大小寫問題

svoid發表於2014-07-03
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章