Linux下MySQL的安裝與使用

1 安裝前說明

1.1 Linux系統及工具的準備

安裝并啟動好兩臺虛擬機：CentOS 7

掌握克隆虛擬機的操作

- mac地址
- 主機名
- ip地址
- UUID

安裝有 Xshell 和 Xftp 等訪問 CentOS 系統的工具

CentOS6 和 CentOS7 在 MySQL 的使用中的區別

防火墻：6是iptables，7是firewalld
啟動服務的命令：6是service，7是systemctl

1.2 查看是否安裝過MySQL

如果你是用 rpm 安裝, 檢查一下 RPM PACKAGE：

rpm -qa | grep -i mysql  # -i 忽略大小寫

檢查 mysql service：

systemctl status mysqld.service

如果存在 mysql-libs 的舊版本包，顯示如下：

如果不存在 mysql-lib 的版本，顯示如下：

1.3 MySQL的卸載

1. 關閉 mysql 服務

systemctl stop mysqld.service

2. 查看當前 mysql 安裝狀況

rpm -qa | grep -i mysql
#或
yum list installed | grep mysql

3. 卸載上述命令查詢出的已安裝程序

yum remove mysql-xxx mysql-xxx mysql-xxx mysql-xxxx

務必卸載干凈，反復執行 rpm -qa | grep -i mysql 確認是否有卸載殘留

4. 刪除 mysql 相關文件

查找相關文件

find / -name mysql

刪除上述命令查找出的相關文件

rm -rf xxx

5. 刪除 my.cnf

rm -rf /etc/my.cnf

2 MySQL的Linux版安裝

2.1 MySQL的4大版本

MySQL Community Server 社區版本，開源免費，自由下載，但不提供官方技術支持，適用于大多數普通用戶。
MySQL Enterprise Edition 企業版本，需付費，不能在線下載，可以試用30天。提供了更多的功能和更完備的技術支持，更適合于對數據庫的功能和可靠性要求較高的企業客戶。
MySQL Cluster 集群版，開源免費。用于架設集群服務器，可將幾個MySQL Server封裝成一個 Server。需要在社區版或企業版的基礎上使用。
MySQL Cluster CGE 高級集群版，需付費。

截止目前，官方最新版本為 8.0.27 。此前，8.0.0 在 2016.9.12 日就發布了。

本課程中主要使用 8.0.25 版本。同時為了更好的說明 MySQL8.0 新特性，還會安裝 MySQL5.7 版本，作為對比。

此外，官方還提供了 MySQL Workbench（GUITOOL）一款專為 MySQL 設計的 ER/數據庫建模工具。它是著名的數據庫設計工具 DBDesigner4 的繼任者。MySQL Workbench又分為兩個版本，分別是（MySQL Workbench OSS）、商用版（MySQL WorkbenchSE）。

2.2 下載MySQL指定版本

1. 下載地址

官網: https://www.mysql.com

2. 打開官網，點擊DOWNLOADS

然后，點擊 MySQL Community(GPL) Downloads

3. 點擊 MySQL Community Server

4. 在General Availability(GA) Releases中選擇適合的版本

如果安裝Windows 系統下MySQL ，推薦下載 MSI安裝程序，點擊 Go to Download Page 進行下載

即可

Windows下的MySQL安裝有兩種安裝程序

- mysql-installer-web-community-8.0.25.0.msi，下載程序大小：2.4M，安裝時需要聯網安裝組件。
- mysql-installer-community-8.0.25.0.msi，下載程序大小：435.7M，安裝時離線安裝即可，推薦。

5. Linux系統下安裝MySQL的幾種方式

5.1 Linux系統下安裝軟件的常用三種方式：

方式1：rpm命令

使用rpm命令安裝擴展名為".rpm"的軟件包，.rpm包的一般格式：

方式2：yum命令

需聯網，從互聯網獲取的yum源，直接使用yum命令安裝。

方式3：編譯安裝源碼包

針對 tar.gz 這樣的壓縮格式，要用tar命令來解壓；如果是其它壓縮格式，就使用其它命令。

5.2 Linux系統下安裝MySQL，官方給出多種安裝方式

安裝方式	特點
rpm	安裝簡單，靈活性差，無法靈活選擇版本、升級
rpm repository	安裝包極小，版本安裝簡單靈活，升級方便，需要聯網安裝
通用二進制包	安裝比較復雜，靈活性高，平臺通用性好
源碼包	安裝最復雜，時間長，參數設置靈活，性能好

這里不能直接選擇CentOS7系統的版本，所以選擇與之對應的Red Hat Enterprise Linux 直接點Download下載RPM Bundle全量包。包括了下面所有的組件，不需要一一下載了。

https://downloads.mysql.com/archives/community/

6. 下載的tar包，用壓縮工具打開

解壓后rpm安裝包 (紅框為抽取出來的安裝包)

2.3 CentOS7下檢查MySQL依賴

1. 檢查/tmp臨時目錄權限（必不可少）

由于mysql安裝過程中，會通過mysql用戶在/tmp目錄下新建tmp_db文件，所以請給/tmp較大的權限。執行：

chmod -R 777 /tmp

2. 安裝前，檢查依賴

rpm -qa|grep libaio

如果存在libaio包如下：

rpm -qa|grep net-tools

如果存在net-tools包如下：

如果不存在需要到centos安裝盤里進行rpm安裝。安裝linux如果帶圖形化界面，這些都是安裝好

的。

2.4 CentOS7下MySQL安裝過程

1. 將安裝程序拷貝到/opt目錄下

在mysql的安裝文件目錄下執行：（必須按照順序執行）

rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

注意：如在檢查工作時，沒有檢查mysql依賴環境在安裝mysql-community-server會報錯
rpm 是RedhatPackageManage縮寫，通過RPM的管理，用戶可以把源代碼包裝成以rpm為擴展名的文件形式，易于安裝。
-i , --install 安裝軟件包
-v , --verbose 提供更多的詳細信息輸出
-h , --hash 軟件包安裝的時候列出哈希標記（和 -v 一起使用效果更好），展示進度條

2. 安裝過程截圖

安裝過程中可能的報錯信息：

一個命令：yum remove mysql-libs 解決，清除之前安裝過的依賴即可

mysql-community-server 依賴檢測失敗：加上 --nodeps --force 即可

[root@localhost opt]# rpm -ivh mysql-community-server-8.0.36-1.el7.x86_64.rpm
警告：mysql-community-server-8.0.36-1.el7.x86_64.rpm: 頭V4 RSA/SHA256 Signature, 密鑰 ID a8d3785c: NOKEY
錯誤：依賴檢測失敗：mysql-community-icu-data-files = 8.0.36-1.el7 被 mysql-community-server-8.0.36-1.el7.x86_64 需要[root@localhost opt]# rpm -ivh mysql-community-server-8.0.36-1.el7.x86_64.rpm --nodeps --force
警告：mysql-community-server-8.0.36-1.el7.x86_64.rpm: 頭V4 RSA/SHA256 Signature, 密鑰 ID a8d3785c: NOKEY
準備中...                          ################################# [100%]
正在升級/安裝...1:mysql-community-server-8.0.36-1.e################################# [100%]
[root@localhost opt]#

3. 查看MySQL版本

執行如下命令，如果成功表示安裝mysql成功。類似java -version如果打出版本等信息

mysql --version
#或
mysqladmin --version

執行如下命令，查看是否安裝成功。需要增加 -i 不用去區分大小寫，否則搜索不到。

rpm -qa|grep -i mysql

4. 服務的初始化

為了保證數據庫目錄與文件的所有者為 mysql 登錄用戶，如果你是以 root 身份運行 mysql 服務，需要執

行下面的命令初始化：

mysqld --initialize --user=mysql

說明: --initialize 選項默認以“安全”模式來初始化，則會為 root 用戶生成一個密碼并將該密碼標記為過期，登錄后你需要設置一個新的密碼。生成的臨時密碼會往日志中記錄一份。

查看密碼：

cat /var/log/mysqld.log

root@localhost：后面就是初始化的密碼

5. 啟動MySQL，查看狀態

#加不加.service后綴都可以

啟動：systemctl start mysqld.service

關閉：systemctl stop mysqld.service

重啟：systemctl restart mysqld.service

查看狀態：systemctl status mysqld.service

mysqld 這個可執行文件就代表著 MySQL 服務器程序，運行這個可執行文件就可以直接啟動一個服務器進程。

查看進程：

ps -ef | grep -i mysql

6. 查看MySQL服務是否自啟動?

systemctl list-unit-files|grep mysqld.service

默認是enabled。

如不是enabled可以運行如下命令設置自啟動

systemctl enable mysqld.service

如果希望不進行自啟動，運行如下命令設置

systemctl disable mysqld.service

3 MYSQL登錄

3.1 首次登錄

通過 mysql -hlocalhost -P3306 -uroot -p 進行登錄，在Enter password：錄入初始化密碼

3.2 修改密碼

因為初始化密碼默認是過期的，所以查看數據庫會報錯，修改密碼：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

5.7版本之后（不含5.7），mysql加入了全新的密碼安全機制。設置新密碼太簡單會報錯。

改為更復雜的密碼規則之后，設置成功，可以正常使用數據庫了。

3.3 設置遠程登錄

1. 當前問題

在用SQLyog或Navicat中配置遠程連接Mysql數據庫時遇到如下報錯信息，這是由于Mysql配置了不支持遠程連接引起的。

2. 確認網絡

1.在遠程機器上使用ping ip地址保證網絡暢通

2.在遠程機器上使用telnet命令保證端口號開放訪問

telnet ip地址端口號

拓展：telnet命令開啟 :

3. 關閉防火墻或開放端口

方式一：關閉防火墻

CentOS6：

service iptables stop

CentOS7：

systemctl start firewalld.service systemctl status firewalld.service systemctl stop firewalld.service #設置開機啟用防火墻 
systemctl enable firewalld.service #設置開機禁用防火墻 
systemctl disable firewalld.service

方式二：開放端口

查看開放的端口號

firewall-cmd --list-all

設置開放的端口號

firewall-cmd --add-service=http --permanent 
firewall-cmd --add-port=3306/tcp --permanent

重啟防火墻

firewall-cmd --reload

4. Linux下修改配置

在Linux系統MySQL下測試：

use mysql; select Host,User from user;

可以看到root用戶的當前主機配置信息為localhost。

修改Host為通配符%

Host列指定了允許用戶登錄所使用的IP，比如user=root Host=192.168.1.1。這里的意思就是說root用戶只能通過192.168.1.1的客戶端去訪問。 user=root Host=localhost，表示只能通過本機客戶端去訪問。而 % 是個通配符，如果Host=192.168.1.%，那么就表示只要是IP地址前綴為“192.168.1.”的客戶端都可以連接。如果Host=%，表示所有IP都有連接權限。

注意：在生產環境下不能為了省事將host設置為%，這樣做會存在安全問題，具體的設置可以根據生產環境的IP進行設置。

update user set host = '%' where user ='root';

Host設置了“%”后便可以允許遠程訪問。

Host修改完成后記得執行flush privileges使配置立即生效：

flush privileges;

5. 測試

如果是 MySQL5.7 版本，接下來就可以使用SQLyog或者Navicat成功連接至MySQL了。
如果是 MySQL8 版本，連接時還會出現如下問題：

配置新連接報錯：錯誤號碼 2058，分析是 mysql 密碼加密方法變了。

解決方法：Linux下 mysql -u root -p 登錄你的 mysql 數據庫，然后執行這條SQL：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

然后在重新配置SQLyog的連接，則可連接成功了，OK。

4 MySQL8的密碼強度評估(了解)

4.1 MySQL不同版本設置密碼(可能出現)

MySQL5.7中：成功

mysql> alter user 'root' identified by 'abcd1234'; 
Query OK, 0 rows affected (0.00 sec)

MySQL8.0中：失敗

mysql> alter user 'root' identified by 'abcd1234'; # HelloWorld_123
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

4.2 MySQL8之前的安全策略

在MySQL 8.0之前，MySQL使用的是validate_password插件檢測、驗證賬號密碼強度，保障賬號的安全性。

安裝/啟用插件方式1：在參數文件my.cnf中添加參數

[mysqld]

plugin-load-add=validate_password.so

\#ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 是否使用該插件(及強制/永久強制使用)

validate-password=FORCE_PLUS_PERMANENT

說明1：plugin library中的validate_password文件名的后綴名根據平臺不同有所差異。對于Unix和 Unix-like系統而言，它的文件后綴名是.so，對于Windows系統而言，它的文件后綴名是.dll。

說明2：修改參數后必須重啟MySQL服務才能生效。

說明3：參數FORCE_PLUS_PERMANENT是為了防止插件在MySQL運行時的時候被卸載。當你卸載插件時就會報錯。如下所示。

mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION -> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME = 'validate_password';
+-------------------+----------------------+---------------+----------------------+
| PLUGIN_NAME | PLUGIN_LIBRARY | PLUGIN_STATUS | LOAD_OPTION | 
+-------------------+----------------------+---------------+----------------------+
| validate_password | validate_password.so | ACTIVE | FORCE_PLUS_PERMANENT | 
+-------------------+----------------------+---------------+----------------------+ 
1 row in set (0.00 sec)mysql> UNINSTALL PLUGIN validate_password;
ERROR 1702 (HY000): Plugin 'validate_password' is force_plus_permanent and can not be unloaded
mysql>

安裝/啟用插件方式2：運行時命令安裝 (推薦)

 mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so'; Query OK, 0 rows affected, 1 warning (0.11 sec)

此方法也會注冊到元數據，也就是mysql.plugin表中，所以不用擔心MySQL重啟后插件會失效。

4.3 MySQL8的安全策略

1. validate_password說明

MySQL 8.0，引入了服務器組件（Components）這個特性，validate_password插件已用服務器組件重新實現。8.0.25版本的數據庫中，默認自動安裝validate_password組件。

未安裝插件前，執行如下兩個指令，執行效果：

mysql> show variables like 'validate_password%'; Empty set (0.04 sec)
mysql> SELECT * FROM mysql.component;
ERROR 1146 (42S02): Table 'mysql.component' doesn't exist

安裝插件后，執行如下兩個指令，執行效果：

mysql> SELECT * FROM mysql.component; 
+--------------+--------------------+------------------------------------+ 
|component_id | component_group_id | component_urn | 
+--------------+--------------------+------------------------------------+ 
|1 | 1 | file://component_validate_password | 
+--------------+--------------------+------------------------------------+ 
1 row in set (0.00 sec)mysql> show variables like 'validate_password%'; 
+--------------------------------------+--------+ 
| Variable_name | Value | 
+--------------------------------------+--------+ 
| validate_password.check_user_name | ON | 
| validate_password.dictionary_file | | 
| validate_password.length | 8 | 
| validate_password.mixed_case_count | 1 | 
| validate_password.number_count | 1 | 
| validate_password.policy | MEDIUM | 
| validate_password.special_char_count | 1 | 
+--------------------------------------+--------+ 
7 rows in set (0.01 sec)

關于 validate_password 組件對應的系統變量說明：

選項	默認值	參數描述
validate_password_check_user_name	ON	設置為ON的時候表示能將密碼設置成當前用戶名。
validate_password_dictionary_file		用于檢查密碼的字典文件的路徑名，默認為空
validate_password_length	8	密碼的最小長度，也就是說密碼長度必須大于或等于8
validate_password_mixed_case_count	1	如果密碼策略是中等或更強的， validate_password要求密碼具有的小寫和大寫字符的最小數量。對于給定的這個值密碼必須有那么多小寫字符和那么多大寫字符。
validate_password_number_count	1	密碼必須包含的數字個數
validate_password_policy	MEDIUM	密碼強度檢驗等級，可以使用數值0、1、2 或相應的符號值LOW、MEDIUM、STRONG來指定。0/LOW：只檢查長度。1/MEDIUM：檢查長度、數字、大小寫、特殊字符。 2/STRONG：檢查長度、數字、大小寫、特殊字符、字典文件。
validate_password_special_char_count	1	密碼必須包含的特殊字符個數

提示：

組件和插件的默認值可能有所不同。例如，MySQL 5.7. validate_password_check_user_name的默認值為OFF。

2. 修改安全策略

修改密碼驗證安全強度

SET GLOBAL validate_password_policy=LOW;
SET GLOBAL validate_password_policy=MEDIUM;
SET GLOBAL validate_password_policy=STRONG;
SET GLOBAL validate_password_policy=0; # For LOW
SET GLOBAL validate_password_policy=1; # For MEDIUM
SET GLOBAL validate_password_policy=2; # For HIGH 
#注意，如果是插件的話,SQL為set global validate_password_policy=LOW

此外，還可以修改密碼中字符的長度

set global validate_password_length=1;

3. 密碼強度測試

如果你創建密碼是遇到“Your password does not satisfy the current policy requirements”，可以通過函數組件去檢測密碼是否滿足條件: 0-100。當評估在100時就是說明使用上了最基本的規則：大寫+小寫+特殊字符+數字組成的8位以上密碼。

mysql> SELECT VALIDATE_PASSWORD_STRENGTH('medium'); 
+--------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('medium') | 
+--------------------------------------+
| 25 | 
+--------------------------------------+ 
1 row in set (0.00 sec)mysql> SELECT VALIDATE_PASSWORD_STRENGTH('K354*45jKd5'); 
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('K354*45jKd5') | 
+-------------------------------------------+
| 100 | 
+-------------------------------------------+ 
1 row in set (0.00 sec)

注意：如果沒有安裝validate_password組件或插件的話，那么這個函數永遠都返回0。關于密碼復雜度對應的密碼復雜度策略。如下表格所示：

Password Test	Return Value
Length < 4	0
Length ≥ 4 and < validate_password.length	25
Satisfies policy 1 (LOW)	50
Satisfies policy 2 (MEDIUM)	75
Satisfies policy 3 (STRONG)	100

4.4 卸載插件、組件（了解）

卸載插件

 mysql> UNINSTALL PLUGIN validate_password; Query OK, 0 rows affected, 1 warning (0.01 sec)

卸載組件

 mysql> UNINSTALL COMPONENT 'file://component_validate_password'; Query OK, 0 rows affected (0.02 sec)

5 字符集的操作

5.1 修改MySQL5.7字符集

1 修改步驟

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

操作1：查看默認使用的字符集

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

MySQL8.0中執行：

MySQL5.7 中執行：

MySQL5.7 默認的客戶端和服務器都用了 latin1 ，不支持中文，保存中文會報錯。MySQL5.7 截圖如下：

在 MySQL5.7 中添加中文數據時，報錯:

因為默認情況下，創建表使用的是 latin1 。如下:

操作2：修改字符集

vim /etc/my.cnf

在 MySQL5.7 或之前的版本中，在文件最后加上中文字符集配置：

character_set_server=utf8

操作3：重新啟動MySQL服務

systemctl restart mysqld

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

2. 已有庫&表字符集的變更

MySQL5.7 版本中，以前創建的庫，創建的表字符集還是latin1。

修改已創建數據庫的字符集

alter database dbtest1 character set 'utf8';

修改已創建數據表的字符集

alter table t_emp convert to character set 'utf8';

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

5.2 各級別的字符集

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：服務器向客戶端返回數據時使用的字符集

1.服務器級別

character_set_server：服務器級別的字符集。

我們可以在啟動服務器程序時通過啟動選項或者在服務器程序運行過程中使用 SET 語句修改這兩個變量的值。比如我們可以在配置文件中這樣寫：

[server]

character_set_server=gbk # 默認字符集

collation_server=gbk_chinese_ci #對應的默認的比較規則

當服務器啟動的時候讀取這個配置文件后這兩個系統變量的值便修改了。

2. 數據庫級別

character_set_database：當前數據庫的字符集

我們在創建和修改數據庫的時候可以指定該數據庫的字符集和比較規則，具體語法如下：

CREATE DATABASE 數據庫名
[[DEFAULT] CHARACTER SET 字符集名稱] [[DEFAULT] COLLATE 比較規則名稱];ALTER DATABASE 數據庫名
[[DEFAULT] CHARACTER SET 字符集名稱] [[DEFAULT] COLLATE 比較規則名稱];

3. 表級別

我們也可以在創建和修改表的時候指定表的字符集和比較規則，語法如下：

CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名稱] [COLLATE 比較規則名稱]]ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名稱] [COLLATE 比較規則名稱]

如果創建和修改表的語句中沒有指明字符集和比較規則，將使用該表所在數據庫的字符集和比較規則作為該表的字符集和比較規則。

4. 列級別

對于存儲字符串的列，同一個表中的不同的列也可以有不同的字符集和比較規則。我們在創建和修改列定義的時候可以指定該列的字符集和比較規則，語法如下：

CREATE TABLE 表名(列名 字符串類型 [CHARACTER SET 字符集名稱] [COLLATE 比較規則名稱], 其他列...
);ALTER TABLE 表名 MODIFY 列名 字符串類型 [CHARACTER SET 字符集名稱] [COLLATE 比較規則名稱];

對于某個列來說，如果在創建和修改的語句中沒有指明字符集和比較規則，將使用該列所在表的字符集和比較規則作為該列的字符集和比較規則。

提示

在轉換列的字符集時需要注意，如果轉換前列中存儲的數據不能用轉換后的字符集進行表示會發生錯誤。比方說原先列使用的字符集是 utf8，列中存儲了一些漢字，現在把列的字符集轉換為 ascii 的話就會出錯，因為ascii 字符集并不能表示漢字字符。

5. 小結

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

如果創建或修改列時沒有顯式的指定字符集和比較規則，則該列默認用表字符集和比較規則
如果創建表時沒有顯式的指定字符集和比較規則，則該表默認用數據庫的字符集和比較規則
如果創建數據庫時沒有顯式的指定字符集和比較規則，則該數據庫默認用服務器的字符集和比較規則

知道了這些規則之后，對于給定的表，我們應該知道它的各個列的字符集和比較規則是什么，從而根據這個列的類型來確定存儲數據時每個列的實際數據占用的存儲空間大小了。比方說我們向表 t 中插入一條記錄：

mysql> INSERT INTO t(col) VALUES('我們'); 
Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM t; 
+--------+
|s|
+--------+
|我們 |
+--------+
1 row in set (0.00 sec)

首先列 col 使用的字符集是 gbk ，一個字符 '我' 在 gbk 中的編碼為 0xCED2，占用兩個字節，兩個字符的實際數據就占用4個字節。如果把該列的字符集修改為 utf8 的話，這兩個字符就實際占用6個字節。

5.3 字符集與比較規則（了解）

1. utf8 與 utf8mb4

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

utf8mb3：閹割過的 utf8 字符集，只使用1~3個字節表示字符。
utf8mb4：正宗的 utf8 字符集，使用1~4個字節表示字符。

2. 比較規則

上表中，MySQL 版本一共支持 41種字符集，其中 Default collation 列表示這種字符集中一種默認的比較規則，里面包含著該比較規則主要作用于哪種語言，比如 utf8_polish_ci 表示波蘭語的規則比較，utf8_spanish_ci 是以西班牙語的規則比較，utf8_general_ci 是一種通用的比較規則。

后綴表示該比較規則是否區分語言中的重音、大小寫。具體如下：

后綴	英文釋義	描述
_ai	accent insensitive	不區分重音
_as	accent sensitive	區分重音
_ci	case insensitive	不區分大小寫
_cs	cas sensitive	區分大小寫
_bin	binary	以二進制方式比較

最后一列 Maxlen ，它代表該種字符集表示一個字符最多需要幾個字節。

常用操作1：

#查看GBK字符集的比較規則
SHOW COLLATION LIKE 'gbk%';#查看UTF-8字符集的比較規則
SHOW COLLATION LIKE 'utf8%';

常用操作2：

#查看服務器的字符集和比較規則
SHOW VARIABLES LIKE '%_server';#查看數據庫的字符集和比較規則
SHOW VARIABLES LIKE '%_database';#查看具體數據庫的字符集
SHOW CREATE DATABASE dbtest1;#修改具體數據庫的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

常用操作3：

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

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

系統變量	描述
character_set_client	服務器解碼請求時使用的字符集
character_set_connection	服務器處理請求時會把請求字符串從 character_set_client 轉為 character_set_connection
character_set_results	服務器向客戶端返回數據時使用的字符集

這幾個系統變量在我的計算機上的默認值如下（不同操作系統的默認值可能不同）：

為了體現出字符集在請求處理過程中的變化，我們這里特意修改一個系統變量的值：

 mysql> set character_set_connection = gbk; Query OK, 0 rows affected (0.00 sec)

現在假設我們客戶端發送的請求是下邊這個字符串：

SELECT * FROM t WHERE s = '我';

為了方便大家理解這個過程，我們只分析字符 '我' 在這個過程中字符集的轉換。現在看一下在請求從發送到結果返回過程中字符集的變化：

1. 客戶端發送請求所使用的字符集

一般情況下客戶端所使用的字符集和當前操作系統一致，不同操作系統使用的字符集可能不一樣，如下：

類 Unix 系統使用的是 utf8
Windows 使用的是 gbk

當客戶端使用的是 utf8 字符集，字符 '我' 在發送給服務器的請求中的字節形式就是：0xE68891

提示

如果你使用的是可視化工具，比如navicat之類的，這些工具可能會使用自定義的字符集來編碼發送到服務器的字符串，而不采用操作系統默認的字符集(所以在學習的時候還是盡量用命令行窗口)。

2. 服務器接收到客戶端發送來的請求其實是一串二進制的字節，它會認為這串字節采用的字符集是 character_set_client ，然后把這串字節轉換為 character_set_connection 字符集編碼的字符。

由于我的計算機上 character_set_client 的值是 utf8 ，首先會按照 utf8 字符集對字節串 0xE68891 進行解碼，得到的字符串就是 '我' ，然后按照代表的字符集，也就是 gbk 進行編碼，得到的結果就是字節串。

3. 因為表 t 的列 col 采用的是 gbk 字符集，與 character_set_connection 一致，所以直接到列

中找字節值為 0xCED2 的記錄，最后找到了一條記錄。

提示

如果某個列使用的字符集和 character_set_connection 代表的字符集不一致的話，還需要進行一次字符集轉換。

4. 上一步驟找到的記錄中的 col 列其實是一個字節串 0xCED2 ， col 列是采用 gbk 進行編碼的，所以首先會將這個字節串使用 gbk 進行解碼，得到字符串 '我' ，然后再把這個字符串使用 character_set_results 代表的字符集，也就是 utf8 進行編碼，得到了新的字節串： 0xE68891，然后發送給客戶端。

5. 由于客戶端是用的字符集是 utf8 ，所以可以順利的將 0xE68891 解釋成字符我，從而顯示到我們的顯示器上，所以我們人類也讀懂了返回的結果。

總結圖示如下：

6 SQL大小寫規范

6.1 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，創建的表和數據庫依據語句上格式存放，凡是查找都是轉換為小寫進行。
兩個平臺上SQL大小寫的區別具體來說：

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

數據庫名、表名、表的別名、變量名是嚴格區分大小寫的;
關鍵字、函數名稱在 SQL 中不區分大小寫;
列名（或字段名）與列的別名（或字段別名）在所有的情況下均是忽略大小寫的;

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

6.2 Linux下大小寫規則設置

當想設置為大小寫不敏感時，要在 my.cnf 這個配置文件 [mysqld] 中加入 lower_case_table_names=1 ，然后重啟服務器。但是要在重啟數據庫實例之前就需要將原來的數據庫和表轉換為小寫，否則將找不到數據庫名。

此參數適用于 MySQL5.7。在 MySQL 8 下禁止在重新啟動 MySQL 服務時將 lower_case_table_names 設置成不同于初始化 MySQL 服務時設置的值。如果非要將 MySQL8 設置為大小寫不敏感，具體步驟為：

停止MySQL服務
刪除數據目錄，即刪除 /var/lib/mysql 目錄
在MySQL配置文件（ /etc/my.cnf）中添加 lower_case_table_names=1
啟動MySQL服務

6.3 SQL編寫建議

如果你的變量名命名規范沒有統一，就可能產生錯誤。這里有一個有關命名規范的建議：

1. 關鍵字和函數名稱全部大寫;

2. 數據庫名、表名、表別名、字段名、字段別名等全部小寫;

3. SQL 語句必須以分號結尾。

數據庫名、表名和字段名在 Linux MySQL 環境下是區分大小寫的，因此建議你統一這些字段的命名規則，比如全部采用小寫的方式。

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

7 sql_mode的合理設置

7.1 寬松模式 vs 嚴格模式

寬松模式：

如果設置的是寬松模式，那么我們在插入數據的時候，即便是給了一個錯誤的數據，也可能會被接受，并且不報錯。

舉例：我在創建一個表時，該表中有一個字段為 name，給 name 設置的字段類型為 char(10) ，如果我在插入數據的時候，其中 name 這個字段對應的有一條數據的長度超過了10 ，例如'1234567890abc'，超過了設定的字段長度10，那么不會報錯，并且取前10個字符存上，也就是說這個數據被存為了'1234567890'，而'abc'就沒有了。但是，我們給的這條數據是錯誤的，因為超過了字段長度，但是并沒有報錯，并且mysql自行處理并接受了，這就是寬松模式的效果。

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

嚴格模式：

出現上面寬松模式的錯誤，應該報錯才對，所以 MySQL5.7 版本就將 sql_mode 默認值改為了嚴格模式。所以在生產等環境中，我們必須采用的是嚴格模式，進而開發、測試環境的數據庫也必須要設置，這樣在開發測試階段就可以發現問題。并且我們即便是用的 MySQL5.6，也應該自行將其改為嚴格模式。

開發經驗：MySQL 等數據庫總想把關于數據的所有操作都自己包攬下來，包括數據的校驗，其實開發中，我們應該在自己開發的項目程序級別將這些校驗給做了，雖然寫項目的時候麻煩了一些步驟，但是這樣做之后，我們在進行數據庫遷移或者在項目的遷移時，就會方便很多。

改為嚴格模式后可能會存在的問題：

若設置模式中包含了 NO_ZERO_DATE，那么 MySQL 數據庫不允許插入零日期，插入零日期會拋出錯誤而不是警告。例如，表中含字段 TIMESTAMP 列（如果未聲明為 NULL 或顯示 DEFAULT 子句）將自動分配 DEFAULT '0000-00-00 00:00:00'（零時間戳），這顯然是不滿足 sql_mode 中的 NO_ZERO_DATE 而報錯。

7.2 寬松模式再舉例

寬松模式舉例1：

select * from employees group by department_id limit 10; set sql_mode = ONLY_FULL_GROUP_BY;select * from employees group by department_id limit 10;

寬松模式舉例2：

設置 sql_mode 模式為 STRICT_TRANS_TABLES ，然后插入數據：

7.3 模式查看和設置

查看當前的 sql_mode

select @@session.sql_mode select @@global.sql_mode#或者show variables like 'sql_mode';

臨時設置方式：設置當前窗口中設置sql_mode

SET GLOBAL sql_mode = 'modes...'; #全局SET SESSION sql_mode = 'modes...'; #當前會話

舉例：

 #改為嚴格模式。此方法只在當前會話中生效，關閉當前會話就不生效了。 set SESSION sql_mode='STRICT_TRANS_TABLES';#改為嚴格模式。此方法在當前服務中生效，重啟MySQL服務后失效。 set GLOBAL sql_mode='STRICT_TRANS_TABLES';