在關系型資料庫中,oracle/sql_server/db2都有對資料進行區分大小寫,不過mysql有點奇怪,對資料不區分大小寫,詳細情況請看下面解析.
MySQL在Linux下資料庫名、表名、列名、别名大小寫的預設規則是這樣的：
1.資料庫名與表名是嚴格區分大小寫的.
2.表的别名是嚴格區分大小寫的.
3.列名與列的别名在所有的情況下均是忽略大小寫的.
4.字段内容(即資料)預設情況下是大小寫不敏感的.
5.變量名(函數和存儲過程)也是嚴格區分大小寫的.
是以不禁我們就會想去修改,而控制這些的究竟又是什麼呢?正是字元集和字元集的校對規則限制的.
字元集和校對規則是什麼
字元集是我們字元解析的編碼表,在計算機底層裡,任何字元都隻是無法直接解析的代碼,需要這些編碼表來解析究竟是什麼字.最基礎的正是ASCII碼表,但是這個表字元太少,用來代表英文和一些日常标點符号就還好,但是要來代表全世界那麼多的文字,就顯然不夠用了,是以就出了各種字元集來解析各種文字,例如中文簡體的gb2312,中文繁體的big5,最出名的萬國碼utf8,還有支援emoji表情的utf8mb4等.
我們常說的通路亂碼,正是由于字元編碼不對稱導緻的,可能是你和伺服器之間,也可能是伺服器與資料庫之間,也可能是代碼内部之間沒有轉換,等等.
字元集是通用的,存在于計算機世界的各種環境,資料庫隻是其中之一,而校對規則,是針對mysql來說的,規則也都是固定的.可能有人會覺得懵逼,有了字元集,為什麼還要校對規則,通俗易懂點說,字元之間有差異,但是靠什麼去展現差異呢(例如排序和分組的操作),那就是這個校對規則的存在意義.例如:不區分大小寫的話,A和a是同樣意義的,而校對規則嚴格之後,區分了大小寫,A和a就不一樣了.
字元集
我們可以用下面的指令來看看支援哪些字元集和校對規則.
#檢視支援哪些字元集,節選
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec) 一般來說,mysql預設還是支援較多字元集,然而大部分情況我們還是用utf8比較多一些.原因是看到最後一列Maxlen中,代表的是使用這種字元集後的一個字元占用的最大位元組數.big5(中文繁體)和gb2312(中文簡體)雖然占的少,隻有2位元組,但是通用性不佳,utf16雖然很強大,但是占的位元組數略多,占用了4位元組,況且也不見得用得了那麼多,折衷之下還是utf8好用一些,一個字元占用3位元組.
更改mysql的預設字元集,可以在配置檔案my.cnf裡添加
#要在[mysqld]子項添加
[mysqld]
#全局預設字元集類型,按需求設定
character-set-server = utf8
#改完之後重新開機,進入mysql看一下,(不重新開機也行,一個個慢慢改吧)
mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+
8 rows in set (0.00 sec) 字元校對規則
說完字元集,就來看看字元的校對規則有哪些.
#顯示utf8字元集下有哪些校對規則,節選
mysql> SHOW COLLATION like 'utf8\_%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.00 sec) 校對規則有很多,但是大部分我們用不到,大家也看到其他規則很多sortlen是8,比前兩個比較常用的都多,會比較費資源是肯定的.
每個字元集有一個預設校對規則,例如,utf8預設校對規則是utf8_general_ci.并存在校對規則命名約定:它們以其相關的字元集名開始,通常包括一個語言名,并且以_ci（大小寫不敏感）,_cs（大小寫敏感）或_bin（二進制/大小寫也敏感）結束。
是以說,mysql的utf8字元集就預設對資料的大小寫不敏感了.
更改mysql的預設字元校對規則,也可以在配置檔案my.cnf裡添加
#要在[mysqld]子項添加
[mysqld]
#全局預設字元校對規則,按需求設定
collation_server = utf8_bin
#改完之後重新開機,進入mysql看一下,(不重新開機也行,一個個慢慢改吧)
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_bin |
| collation_server | utf8_bin |
+----------------------+-----------------+
3 rows in set (0.00 sec)
#當然你也可以視實際情況隻改庫級别或連結級别,是允許不一樣的,不過你要知道其中風險就是了
collation_database = utf8_bin
#需要特别額外說明的是,連接配接級别的字元校對規則,是由用戶端決定的,靠更改配置檔案是不生效的,
#是以要想更改,隻能通過指令方式執行
mysql> set collation_connection = utf8_bin 注意
要注意的是,這些更改都隻對後續建立的資料庫表和使用者生效,已經存在的資料庫是不受影響的,想要更改現有表的定義,那就必須更改表結構,或者重建資料庫.
更改的方法就是alter,建立的方法如果是預設就不需要幹什麼,如果不預設就要人為指定了.
#更改表的字元集和字元校對規則
mysql> ALTER TABLE 表名 MODIFY COLUMN 字段名 varchar(50) CHARACTER SET utf8 COLLATE utf8_bin; 需要特别注意的是,對已有資料庫的表更改字元校對規則并不影響表的使用,隻要表的資料不存在沖突就可以,隻是部分查詢和排序/分組的操作會造成差距,但是字元集亂改的話,會直接造成資料亂碼,是以要非常謹慎,有時候還不如重建資料庫比較實際.
#建立資料庫時指定字元編碼和校對規則,以後在這個庫建立的表的字段預設都遵循這兩個規則
mysql> CREATE DATABASE 資料庫名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
#建立一個表,指定使用字元集和校對規則,其他表不指定則走資料庫的預設配置
mysql> CREATE TABLE 表名(字段名 varchar(5)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
#建立一個表,指定不同字段有不同的字元集和校對規則,其他不指定則走資料庫的預設配置
CREATE TABLE `表名` (
`字段1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`字段2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL
); #要在[mysqld]子項添加
[mysqld]
#表名的大小寫敏感選項,0為大小寫敏感,1為大小寫不敏感
lower_case_table_names = 1
#重新開機生效