MySQL預設字元集設定詳情

2022-09-20 22:03:22

檢視各版本預設字元集

在MySQL 8.0版本之前，預設字元集為latin1 ，utf8字元集指向的是utf8mb3 。網站開發人員在資料庫設計的時候往往會將編碼修改為utf8字元集。如果遺忘修改預設的編碼，就會出現亂碼的問題。從MySQL8.0開始，資料庫的預設編碼將改為utf8mb4 ，從而避免上述亂碼的問題。

使用以下命令可以檢視MySQL的預設字元集：

show variables like 'character%';
# 或者
show variables like '%char%';

MySQL:8.0.30 中執行：

MySQL:5.7.29 中執行

引數解釋：

#伺服器級別的預設字元集，建立資料庫沒有顯示的指定字元集就使用該級別的預設字元集
character_set_server=latin1
#資料庫級別的預設字元集，建立表沒有顯示的指定字元集就使用該級別的預設字元集
character_set_database=latin1

修改MySQL5中的預設字元集

因為我的MySQL是docker部署，所以修改方式略微有一些不一樣

先檢視容器ID：

docker ps

根據容器ID進入容器：

docker exec -it e14 /bin/bash

進入 /etc/mysql/mysql.conf.d/ 目錄

cd /etc/mysql/mysql.conf.d/

因為docker容器內未安裝vim，所以需要先安裝vim

#使用國內映象

cp /etc/apt/sources.list /etc/apt/sources.list.bak

echo "" > /etc/apt/sources.list
echo "deb http://mirrors.aliyun.com/debian buster main" >> /etc/apt/sources.list ;
echo "deb http://mirrors.aliyun.com/debian-security buster/updates main" >> /etc/apt/sources.list ;
echo "deb http://mirrors.aliyun.com/debian buster-updates main" >> /etc/apt/sources.list;

更新apt-get指令：

apt-get update

安裝vim：

 apt-get  install  vim

在上面的目錄下編輯mysqld.cnf：

vim mysqld.cnf

插入如下內容：

character_set_server=utf8

重啟容器
再次進入容器檢視預設編碼

但是原庫、原表的設定不會發生變化，引數修改只對新建的資料庫生效。

已有庫&表字元集的變更

MySQL5.7版本中，以前建立的庫，建立的表字元集還是latin1

修改已建立資料庫的字元集：

alter database dbtest1 character set 'utf8';

修改已建立資料表的字元集：

alter table t_emp convert to character set 'utf8';

注意：但是原有的資料如果是用非'utf8'編碼的話，資料本身編碼不會發生改變。已有資料需要導出或刪除，然後重新插入。

各級別的字元集

MySQL有4個級別的字元集和比較規則，分別是:

伺服器級別
資料庫級別
標級別
列級別

執行如下SQL語句：

show variables like 'character%';

character_set_server ：伺服器級別的字元集
character_set_database ：當前資料庫的字元集
character_set_client ：伺服器解碼請求時使用的字元集
character_set_connection ：伺服器處理請求時會把請求字串從character_set_client 轉為character_set_connection
character_set_results ：伺服器向用戶端返回資料時使用的字元集

我們介紹的這4個級別字元集和比較規則的聯絡如下：

如果建立或修改列時沒有顯式的指定字元集和比較規則，則該列預設用表的字元集和比較規則
如果建立表時沒有顯式的指定字元集和比較規則，則該表預設用資料庫的字元集和比較規則
如果建立資料庫時沒有顯式的指定字元集和比較規則，則該資料庫預設用伺服器的字元集和比較規則

字元集與比較規則

utf8 與 utf8mb4

utf8 字元集表示一個字元需要使用1～4個位元組，但是我們常用的一些字元使用1～3個位元組就可以表示了。而字元集表示一個字元所用的最大位元組長度，在某些方面會影響系統的儲存和效能，所以設計MySQL的設計者偷偷的定義了兩個概念：

utf8mb3 ：閹割過的utf8 字元集，只使用1～3個位元組表示字元（表情符號等會亂碼）
utf8mb4 ：正宗的utf8 字元集，使用1～4個位元組表示字元

比較規則

MySQL版本一共支援41種字元集，其中的Default collation 列表示這種字元集中一種預設的比較規則，裡面包含著該比較規則主要作用於哪種語言，比如utf8_polish_ci 表示以波蘭語的規則比較， utf8_spanish_ci 是以西班牙語的規則比較， utf8_general_ci 是一種通用的比較規則。字尾表示該比較規則是否區分語言中的重音、大小寫。具體如下：

常用操作：

#檢視GBK字元集的比較規則
SHOW COLLATION LIKE 'gbk%';
#檢視UTF-8字元集的比較規則
SHOW COLLATION LIKE 'utf8%';

#檢視伺服器的字元集和比較規則
SHOW VARIABLES LIKE '%_server';
#檢視資料庫的字元集和比較規則
SHOW VARIABLES LIKE '%_database';

#檢視具體資料庫的字元集
SHOW CREATE DATABASE dbtest1;
#修改具體資料庫的字元集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

#檢視表的字元集
show create table employees;
#檢視表的比較規則
show table status from atguigudb like 'employees';
#修改表的字元集和比較規則
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

請求到響應過程中字元集的變化

系統變數	描述
`character_set_client`	伺服器解碼請求時使用的字元集
`character_set_connection`	伺服器處理請求時會把請求字串從`character_set_client 轉為character_set_connection`
`character_set_results`	伺服器向用戶端返回資料時使用的字元集

這幾個系統變數在我的計算機上的預設值如下（不同作業系統的預設值可能不同，MySQL版本：8.0.30）:

字元集在請求處理過程中的變化：

SQL大小寫規範

Windows和Linux平臺區別

在 SQL 中，關鍵字和函數名是不用區分字母大小寫的，比如 SELECT、WHERE、ORDER、GROUP BY 等關鍵字，以及 ABS、MOD、ROUND、MAX 等函數名。

不過在 SQL 中，你還是要確定大小寫的規範，因為在 Linux 和 Windows 環境下，你可能會遇到不同的大小寫問題。windows系統預設大小寫不敏感，但是linux系統是大小寫敏感的。

通過如下命令檢視：

SHOW VARIABLES LIKE '%lower_case_table_names%';

Windows系統下：

Linux系統下：

lower_case_table_names 引數值的設定：

預設為0，大小寫敏感
設定1，大小寫不敏感。建立的表，資料庫都是以小寫形式存放在磁碟上，對於sql語句都是轉換為小寫對錶和資料庫進行查詢
設定2，建立的表和資料庫依據語句上格式存放，凡是查詢都是轉換為小寫進行

MySQL在Linux下資料庫名、表名、列名、別名大小寫規則是這樣的：

資料庫名、表名、表的別名、變數名是嚴格區分大小寫的

關鍵字、函數名稱在 SQL 中不區分大小寫

列名（或欄位名）與列的別名（或欄位別名）在所有的情況下均是忽略大小寫的

MySQL在Windows的環境下全部不區分大小寫

Linux下大小寫規則設定：

當想設定為大小寫不敏感時，要在my.cnf 這個組態檔 [mysqld] 中加入lower_case_table_names=1 ，然後重啟伺服器

但是要在重啟資料庫範例之前就需要將原來的資料庫和錶轉換為小寫，否則將找不到資料庫名
此引數適用於MySQL5.7 ，在MySQL8 下禁止在重新啟動 MySQL 服務時將lower_case_table_names 設定成不同於初始化 MySQL 服務時設定的lower_case_table_names 值

SQL編寫建議

如果你的變數名命名規範沒有統一，就可能產生錯誤。

這裡有一個有關命名規範的建議：

關鍵字和函數名稱全部大寫
資料庫名、表名、表別名、欄位名、欄位別名等全部小寫
SQL 語句必須以分號結尾

資料庫名、表名和欄位名在 Linux MySQL 環境下是區分大小寫的，因此建議你統一這些欄位的命名規則，比如全部採用小寫的方式

雖然關鍵字和函數名稱在 SQL 中不區分大小寫，也就是如果小寫的話同樣可以執行。但是同時將關鍵詞和函數名稱全部大寫，以便於區分資料庫名、表名、欄位名

sql_mode的合理設定

寬鬆模式

如果設定的是寬鬆模式，那麼我們在插入資料的時候，即便是給了一個錯誤的資料，也可能會被接受，並且不報錯。

舉例：我在建立一個表時，該表中有一個欄位為name，給name設定的欄位型別時char(10) ，如果我在插入資料的時候，其中name這個欄位對應的有一條資料的長度超過了10 ，例如'1234567890abc'，超過了設定的欄位長度10，那麼不會報錯，並且取前10個字元存上，也就是說你這個資料被存為了'1234567890'，而'abc'就沒有了。但是，我們給的這條資料是錯誤的，因為超過了欄位長度，但是並沒有報錯，並且mysql自行處理並接受了，這就是寬鬆模式的效果。

應用場景：通過設定sql mode為寬鬆模式，來保證大多數sql符合標準的sql語法，這樣應用在不同資料庫之間進行遷移時，則不需要對業務sql 進行較大的修改。