Mysql建庫字元集和排序規則及說明

2022-12-11 14:01:52

概述

我們在使用mysql的時候，尤其是在建立資料庫的時候，我們需要選擇當前資料庫的字元集和排序規則，這樣你庫裡面的建立資料表的時候預設的編碼方式就是庫選擇好的。

我們這裡舊按照navicat建立資料庫的來說。（主要講述中國的使用習慣）

由於漢字的特殊性，數量多等原因，我們一般在寫程式碼或者寫其它檔案的時候都是選擇***UTF-8***的編碼方式，所以我們建立資料庫的時候一般是選擇UTF-8.但是我們可以看到資料床架的時候，字元集的選擇有：utf8 以及 utf8mb4 這兩種方式。

那麼兩種因該是選擇哪一種呢，下面稍後看我給你吹（一般是utf8mb4）。

字元集選擇結束之後我們還需要選擇這個排序規則，我們都知道字元集是儲存資料時候需要的編碼方式，那麼這個排序規則是幹什麼用的呢？

排序規則

是指對指定字元集下不同字元的比較規則。其特徵有以下幾點

1.兩個不同的字元集不能有相同的排序規則
2.兩個字元集有一個預設的排序規則
3.有一些常用的命名規則。如_ci結尾表示大小寫不敏感（caseinsensitive）,_cs表示大小寫敏感（case sensitive）,_bin表示二進位制的比較（binary）.

那麼排序的規則應該怎麼選擇，也看下面我給你吹。

字元集的選擇

概述中我們已經說了，在中國一般使用的編碼方式大部分都是UTF-8，但是mysql的資料庫中給出了兩種選“utf8”,一個是“utf8mb4”。

根據查詢資料得知mysql的“utf8”並不是我們常見到的UTF-8，反而“utf8mb4”是我們常用的UTF-8。

為什麼會這樣呢？

根據查詢得知MYSQL中的“utf8”編碼只支援每個字元最大3個位元組的編碼方式，而我們通常使用的UTF-8是每個字元最大4個位元組的編碼方式。

這個問題在我們中國看來就是一個bug，但是MYSQL並沒有對這個bug進行修復，而是在2010年增加了一個新的字元集“utf8mb4”，這個才對應了我們常使用的UTF-8。

所以在我們這邊，尤其是儲存漢字的時候，需要使用的編碼方式是“utf8mb4”。

#查詢utf8和utf8mb4長度資訊
SHOW CHARSET LIKE 'utf8%';
# 修改表的字元集
ALTER TABLE dm_user CONVERT TO CHARACTER SET utf8mb4;

排序規則

編碼規則我就按照字元集是“utf8mb4”的繼續說明，我們要說的字元集主要就三個：

utf8mb4_bin
utf8mb4_general_ci
utf8mb4_unicode_ci

1. utf8mb4_bin

bin的意思就是二進位制的意思，也就是說排序查詢的規則是按照二進位制的方式進行查詢的，這裡就會涉及到我們常常說到的資料庫裡面的大小寫區分。

比如說：

a的二進位制編碼是： 01100001
A的二進位制編碼是： 01000001

那麼我們在執行

SELECT * FROM dm_user WHERE username='a';
SELECT * FROM dm_user WHERE username='A';

這兩條sql語句的結果是不一樣的，因為a和A的二進位制編碼是不一樣的。

2. utf8mb4_unicode_ci 和 utf8mb4_general_ci

首先要說明的是，這兩種排序規則都是不區分大小寫的，也就是說在這兩種排序規則下，a和A是一樣的。

根據資料瞭解，這兩種排序規則要從兩個方面來看，一個是準確性，一個是效能。兩種排序規則的說明連結

準確性

utf8mb4_unicode_ci 是基於標準的Unicode來排序和比較，能夠在各種語言之間精確排序。
utf8mb4_general_ci 沒有實現Unicode排序規則，在遇到某些特殊語言或字元是，排序結果可能不是所期望的。

utf8mb4_unicode_ci使用unicode規則進行排序和比較，它使用相當複雜的演演算法在各種語言中以及在使用各種特殊字元時進行正確排序。

這些規則需要考慮到特定語言的約定，並不是每個人都按照我們所說的“字母順序”對字元進行排序。就拉丁語（即“歐洲”）而言，MySQL中的Unicode排序和簡化的utf8mb4_general_ci排序沒有太大區別，但仍有一些區別：

例如：Unicode排序規則按使用這些字元的人通常希望的方式將“ß”排序為“ss”，將“Œ”排序為“OE”，而utf8mb4_general_ci將它們排序為單個字元（可能分別類似於“s”和“e”）。

一些Unicode字元被定義為可忽略字元，這意味著它們不應計入排序順序，而應將比較移到下一個字元。utf8mb4_unicode_ci可以正確處理這些問題。在非拉丁語言（如亞洲語言或不同字母表的語言）中，Unicode排序和簡化的utf8mb4_ci排序之間可能有更多的差異。utf8mb4_-general_ci的適用性將在很大程度上取決於所使用的語言。對某些語言來說，這是相當不夠的。

效能