簡單認識mysql字符集與校對規則

下文主要給大家帶來簡單認識mysql字符集與校對規則，希望這些文字能夠帶給大家實際用處，這也是我編輯簡單認識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;

    需要特別注意的是,對已有數據庫的表更改字符校對規則并不影響表的使用,只要表的數據不存在沖突就可以,只是部分查詢和排序/分組的操作會造成差距,但是字符集亂改的話,會直接造成數據亂碼,所以要非常謹慎,有時候還不如重建數據庫比較實際.

    當然,假如你確實有需要,你也可以指定庫和表用什么字符編碼和校對規則,例如突然要做一個指定是utf8mb4的庫或表.

#創建數據庫時指定字符編碼和校對規則,以后在這個庫新建的表的字段默認都遵循這兩個規則
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 
);

    開頭也列出了,mysql默認數據庫名與表名是嚴格區分大小寫的,如果僅是對數據庫名與表名大小寫敏感進行修改,則my.cnf有一個專門的配置選項,直接配置完成后,重啟就會忽略數據庫名與表名的大小寫規則,兩個一起生效,lower_case_table_names

#要在[mysqld]子項添加
[mysqld]
#表名的大小寫敏感選項,0為大小寫敏感,1為大小寫不敏感
lower_case_table_names = 1
#重啟生效

對于以上關于簡單認識mysql字符集與校對規則，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。