Linux下MySQL的安裝與使用

1. 安裝前說(shuō)明

1. Linux系統(tǒng)及工具的準(zhǔn)備

• 安裝并啟動(dòng)好兩臺(tái)虛擬機(jī)： CentOS 7

  • 掌握克隆虛擬機(jī)的操作

    • mac地址

      • 網(wǎng)絡(luò)是配置 -》高級(jí) -》 MAC 地址 -》 生成

        [圖片上傳失敗...(image-887755-1713884406725)]

    • 主機(jī)名

      vi /etc/hostname
      
      # 修改主機(jī)名后進(jìn)行保存退出
      onnoamysql57
      

      重啟虛擬機(jī)

      reboot
      

- ip地址

  ```shell
  vim /etc/sysconfig/network-scripts/ifcfg-ens33
  ```

  編輯后保存 - 修改 UUID 的值（隨便修改某個(gè)值即可），修改成靜態(tài)ip

  ```shell
  TYPE="Ethernet"
  PROXY_METHOD="none"
  BROWSER_ONLY="no"
  BOOTPROTO="dhcp"
  DEFROUTE="yes"
  IPV4_FAILURE_FATAL="no"
  IPV6INIT="yes"
  IPV6_AUTOCONF="yes"
  IPV6_DEFROUTE="yes"
  IPV6_FAILURE_FATAL="no"
  IPV6_ADDR_GEN_MODE="stable-privacy"
  NAME="ens33"
  UUID="9d5b196d-cafd-895d-a0a4-d50390c1b4d5"
  DEVICE="ens33"
  ONBOOT="yes"
  # 修改成靜態(tài)ip
  IPADDR=192.168.40.150
  NETMASK=255.255.255.0
  GATEWAY=192.168.40.2
  DNS1=192.168.40.2
  ```

  重啟網(wǎng)絡(luò)服務(wù)

  ```shell
  systemctl restart network
  ```

- UUID

  參考上一步

• 安裝有 Xshell 和 Xftp 等訪問(wèn)CentOS系統(tǒng)的工具

  • xftp 連接linux目錄顯示亂碼

    [圖片上傳失敗...(image-6d4083-1713884406725)]

    解決：

    [圖片上傳失敗...(image-5b9187-1713884406725)]

    刷新即可正常顯示

    [圖片上傳失敗...(image-19ba08-1713884406725)]

• CentOS6和CentOS7在MySQL的使用中的區(qū)別

1. 防火墻：6是iptables，7是firewalld

2. 啟動(dòng)服務(wù)的命令：6是service，7是systemctl

2. 查看是否安裝過(guò)MySQL

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

  rpm -qa | grep -i mysql # -i 忽略大小寫(xiě)
  

• 檢查mysql service：

  systemctl status mysqld.service
  

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

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

3. MySQL的卸載

1. 關(guān)閉 mysql 服務(wù)

   systemctl stop mysqld.service
   

2. 查看當(dāng)前 mysql安裝狀況

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

3. 卸載上述命令查詢(xún)出的已安裝程序

# 務(wù)必卸載干凈，反復(fù)執(zhí)行 rpm -qa | grep -i mysql 確認(rèn)是否有卸載殘留
yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

4. 刪除 mysql 相關(guān)文件

   • 查找相關(guān)文件

     find / -name mysql
     

   • 刪除上述命令查找出的相關(guān)文件

     rm -rf xxx
     

5. 刪除 my.cnf

   rm -rf /etc/my.cnf
   

2. MySQL的Linux版安裝

1. MySQL的4大版本

• MySQL Community Server 社區(qū)版本，開(kāi)源免費(fèi)，自由下載，但不提供官方技術(shù)支持，適用于大多數(shù)普通用戶(hù)。

• MySQL Enterprise Edition 企業(yè)版本，需付費(fèi)，不能在線下載，可以試用30天。提供了更多的功能和更完備的技術(shù)支持，更適合于對(duì)數(shù)據(jù)庫(kù)的功能和可靠性要求較高的企業(yè)客戶(hù)。

• MySQL Cluster 集群版，開(kāi)源免費(fèi)。用于架設(shè)集群服務(wù)器，可將幾個(gè)MySQL Server封裝成一個(gè)Server。需要在社區(qū)版或企業(yè)版的基礎(chǔ)上使用。

• MySQL Cluster CGE 高級(jí)集群版，需付費(fèi)。

此外，官方還提供了 MySQL Workbench （GUITOOL）一款專(zhuān)為MySQL設(shè)計(jì)的 ER/數(shù)據(jù)庫(kù)建模工具 。它是著名的數(shù)據(jù)庫(kù)設(shè)計(jì)工具DBDesigner4的繼任者。MySQLWorkbench又分為兩個(gè)版本，分別是 社區(qū)版（MySQL Workbench OSS）、 商用版 （MySQL WorkbenchSE）。

2. 下載MySQL指定版本

1.下載地址

官網(wǎng)：https://www.mysql.com

2. 打開(kāi)官網(wǎng)，點(diǎn)擊DOWNLOADS

然后，點(diǎn)擊 MySQL Community(GPL) Downloads

[圖片上傳失敗...(image-d70497-1713884406725)]

3. 點(diǎn)擊 MySQL Community Server

[圖片上傳失敗...(image-29cf43-1713884406725)]

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

• 如果安裝 Windows 系統(tǒng)下MySQL ，推薦下載 MSI安裝程序 ；點(diǎn)擊 Go to Download Page 進(jìn)行下載即可

  [圖片上傳失敗...(image-19fe3-1713884406725)]

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

  • mysql-installer-web-community-8.0.25.0.msi 下載程序大?。?.4M；安裝時(shí)需要聯(lián)網(wǎng)安裝組件。

  • mysql-installer-community-8.0.25.0.msi 下載程序大?。?35.7M；安裝時(shí)離線安裝即可。推薦。

5. Linux系統(tǒng)下安裝MySQL的幾種方式

5.1 Linux系統(tǒng)下安裝軟件的常用三種方式：

方式1：rpm命令

使用rpm命令安裝擴(kuò)展名為".rpm"的軟件包。

.rpm包的一般格式：

[圖片上傳失敗...(image-c7f1bd-1713884406725)]

方式2：yum命令

需聯(lián)網(wǎng)，從 互聯(lián)網(wǎng)獲取 的yum源，直接使用yum命令安裝。

方式3：編譯安裝源碼包

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

5.2 Linux系統(tǒng)下安裝MySQL，官方給出多種安裝方式

• 這里不能直接選擇CentOS 7系統(tǒng)的版本，所以選擇與之對(duì)應(yīng)的 Red Hat Enterprise Linux

• https://downloads.mysql.com/archives/community/ 直接點(diǎn)Download下載RPM Bundle全量包。包括了所有下面的組件。不需要一個(gè)一個(gè)下載了。

  [圖片上傳失敗...(image-b05202-1713884406725)]

6. 下載的tar包，用壓縮工具打開(kāi)

[圖片上傳失敗...(image-e5a101-1713884406725)]

• 解壓后rpm安裝包 （紅框?yàn)槌槿〕鰜?lái)的安裝包）

  [圖片上傳失敗...(image-c30971-1713884406725)]

3. CentOS7下檢查MySQL依賴(lài)

1. 檢查/tmp臨時(shí)目錄權(quán)限（必不可少）

由于mysql安裝過(guò)程中，會(huì)通過(guò)mysql用戶(hù)在/tmp目錄下新建tmp_db文件，所以請(qǐng)給/tmp較大的權(quán)限。執(zhí)行 ：

chmod -R 777 /tmp

[圖片上傳失敗...(image-c5724b-1713884406725)]

2. 安裝前，檢查依賴(lài)

rpm -qa|grep libaio

• 如果存在libaio包如下：

  [圖片上傳失敗...(image-deb1b9-1713884406725)]

rpm -qa|grep net-tools

• 如果存在net-tools包如下：

  [圖片上傳失敗...(image-206fc8-1713884406725)]

• 如果不存在需要到centos安裝盤(pán)里進(jìn)行rpm安裝。安裝linux如果帶圖形化界面，這些都是安裝好的。

4. CentOS7下MySQL安裝過(guò)程

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

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

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

• 注意: 如在檢查工作時(shí)，沒(méi)有檢查mysql依賴(lài)環(huán)境在安裝mysql-community-server會(huì)報(bào)錯(cuò)

• rpm 是Redhat Package Manage縮寫(xiě)，通過(guò)RPM的管理，用戶(hù)可以把源代碼包裝成以rpm為擴(kuò)展名的文件形式，易于安裝。

• -i , --install 安裝軟件包

• -v , --verbose 提供更多的詳細(xì)信息輸出

• -h , --hash 軟件包安裝的時(shí)候列出哈希標(biāo)記 (和 -v 一起使用效果更好)，展示進(jìn)度條

[圖片上傳失敗...(image-d36568-1713884406725)]

2. 安裝過(guò)程截圖

安裝過(guò)程中可能的報(bào)錯(cuò)信息：

[圖片上傳失敗...(image-9dd002-1713884406725)]

一個(gè)命令：yum remove mysql-libs 解決，清除之前安裝過(guò)的依賴(lài)即可,再重第三步 rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm 開(kāi)始安裝即可

3. 查看MySQL版本

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

mysql --version
#或
mysqladmin --version

[圖片上傳失敗...(image-2192ce-1713884406725)]

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

rpm -qa|grep -i mysql

[圖片上傳失敗...(image-2c9719-1713884406725)]

4. 服務(wù)的初始化

為了保證數(shù)據(jù)庫(kù)目錄與文件的所有者為 mysql 登錄用戶(hù)，如果你是以 root 身份運(yùn)行 mysql 服務(wù)，需要執(zhí)行下面的命令初始化：

mysqld --initialize --user=mysql

說(shuō)明： --initialize 選項(xiàng)默認(rèn)以“安全”模式來(lái)初始化，則會(huì)為 root 用戶(hù)生成一個(gè)密碼并將 該密碼標(biāo)記為過(guò)期 ，登錄后你需要設(shè)置一個(gè)新的密碼。生成的臨時(shí)密碼會(huì)往日志中記錄一份。

查看密碼：

cat /var/log/mysqld.log

[圖片上傳失敗...(image-f63727-1713884406725)]

root@localhost: s6>_eVLd=qkc 后面就是初始化的密碼

mysql5.7密碼：JZ<LU0eIoUrv

5. 啟動(dòng)MySQL，查看狀態(tài)

#加不加.service后綴都可以

啟動(dòng)：systemctl start mysqld.service

關(guān)閉：systemctl stop mysqld.service

重啟：systemctl restart mysqld.service

查看狀態(tài)：systemctl status mysqld.service

mysqld 這個(gè)可執(zhí)行文件就代表著 MySQL 服務(wù)器程序，運(yùn)行這個(gè)可執(zhí)行文件就可以直接啟動(dòng)一個(gè)服務(wù)器進(jìn)程。

[圖片上傳失敗...(image-f036e4-1713884406725)]

查看進(jìn)程：

ps -ef | grep -i mysql

[圖片上傳失敗...(image-4faaba-1713884406725)]

6. 查看MySQL服務(wù)是否自啟動(dòng)

systemctl list-unit-files|grep mysqld.service

[圖片上傳失敗...(image-188c9d-1713884406725)]

默認(rèn)是enabled。

• 如不是enabled可以運(yùn)行如下命令設(shè)置自啟動(dòng)

  systemctl enable mysqld.service
  

• 如果希望不進(jìn)行自啟動(dòng)，運(yùn)行如下命令設(shè)置

  systemctl disable mysqld.service
  

3. MySQL登錄

1. 首次登錄

通過(guò) mysql -hlocalhost -P3306 -uroot -p 進(jìn)行登錄，在Enter password：錄入初始化密碼

[圖片上傳失敗...(image-81152b-1713884406725)]

2 修改密碼

• 因?yàn)槌跏蓟艽a默認(rèn)是過(guò)期的，所以查看數(shù)據(jù)庫(kù)會(huì)報(bào)錯(cuò)

  [圖片上傳失敗...(image-230567-1713884406725)]

• 修改密碼：

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

• 5.7版本之后（不含5.7），mysql加入了全新的密碼安全機(jī)制。設(shè)置新密碼太簡(jiǎn)單會(huì)報(bào)錯(cuò)。

  [圖片上傳失敗...(image-446abd-1713884406725)]

• 改為更復(fù)雜的密碼規(guī)則之后，設(shè)置成功，可以正常使用數(shù)據(jù)庫(kù)了

  [圖片上傳失敗...(image-672b04-1713884406725)]

3 設(shè)置遠(yuǎn)程登錄

1. 當(dāng)前問(wèn)題

在用SQLyog或Navicat中配置遠(yuǎn)程連接Mysql數(shù)據(jù)庫(kù)時(shí)遇到如下報(bào)錯(cuò)信息，這是由于Mysql配置了不支持遠(yuǎn)程連接引起的。

[圖片上傳失敗...(image-70d318-1713884406725)]

2. 確認(rèn)網(wǎng)絡(luò)

1.在遠(yuǎn)程機(jī)器上使用ping ip地址保證網(wǎng)絡(luò)暢通

2.在遠(yuǎn)程機(jī)器上使用telnet命令保證端口號(hào)開(kāi)放訪問(wèn)

telnet ip地址 端口號(hào)

拓展： telnet命令開(kāi)啟 :

[圖片上傳失敗...(image-2ace38-1713884406725)]

[圖片上傳失敗...(image-e95223-1713884406726)]

[圖片上傳失敗...(image-53c945-1713884406726)]

按照提示重啟電腦即可。

3. 關(guān)閉防火墻或開(kāi)放端口

方式一：關(guān)閉防火墻

• CentOS6 ：

  service iptables stop
  

• CentOS7：

  systemctl start firewalld.service
  
  systemctl status firewalld.service
  
  systemctl stop firewalld.service
  
  #設(shè)置開(kāi)機(jī)啟用防火墻
  systemctl enable firewalld.service
  
  #設(shè)置開(kāi)機(jī)禁用防火墻
  systemctl disable firewalld.service
  

方式二：開(kāi)放端口

• 查看開(kāi)放的端口號(hào)

  firewall-cmd --list-all
  

• 設(shè)置開(kāi)放的端口號(hào)

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

• 重啟防火墻

  firewall-cmd --reload
  

4. Linux下修改配置

在Linux系統(tǒng)MySQL下測(cè)試：

use mysql;

select Host,User from user;

[圖片上傳失敗...(image-5ff5e6-1713884406726)]

可以看到root用戶(hù)的當(dāng)前主機(jī)配置信息為localhost。

• 修改Host為通配符%

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

  注意：在生產(chǎn)環(huán)境下不能為了省事將host設(shè)置為%，這樣做會(huì)存在安全問(wèn)題，具體的設(shè)置可以根據(jù)生產(chǎn)環(huán)境的IP進(jìn)行設(shè)置。

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

  Host設(shè)置了“%”后便可以允許遠(yuǎn)程訪問(wèn)。

  [圖片上傳失敗...(image-86ea57-1713884406726)]

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

  flush privileges;
  

5. 測(cè)試

• 如果是 MySQL5.7 版本，接下來(lái)就可以使用SQLyog或者Navicat成功連接至MySQL了。

• 如果是 MySQL8 版本，連接時(shí)還會(huì)出現(xiàn)如下問(wèn)題：

  [圖片上傳失敗...(image-8d6e0e-1713884406726)]

  配置新連接報(bào)錯(cuò)：錯(cuò)誤號(hào)碼 2058，分析是 mysql 密碼加密方法變了。

  解決方法：Linux下 mysql -u root -p 登錄你的 mysql 數(shù)據(jù)庫(kù)，然后 執(zhí)行這條SQL：

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

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



## 4. MySQL8的密碼強(qiáng)度評(píng)估（了解）

### 1. MySQL不同版本設(shè)置密碼(可能出現(xiàn))

- MySQL5.7中：成功

```mysql
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
  

2. MySQL8之前的安全策略

在MySQL 8.0之前，MySQL使用的是validate_password插件檢測(cè)、驗(yàn)證賬號(hào)密碼強(qiáng)度，保障賬號(hào)的安全

性。

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

[mysqld]

plugin-load-add=validate_password.so

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

validate-password=FORCE_PLUS_PERMANENT

說(shuō)明1： plugin library中的validate_password文件名的后綴名根據(jù)平臺(tái)不同有所差異。 對(duì)于Unix和Unix-like系統(tǒng)而言，它的文件后綴名是.so，對(duì)于Windows系統(tǒng)而言，它的文件后綴名是.dll。

說(shuō)明2： 修改參數(shù)后必須重啟MySQL服務(wù)才能生效。

說(shuō)明3： 參數(shù)FORCE_PLUS_PERMANENT是為了防止插件在MySQL運(yùn)行時(shí)的時(shí)候被卸載。當(dāng)你卸載插件時(shí)就會(huì)報(bào)錯(cuò)。如下所示。

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

安裝/啟用插件方式2：運(yùn)行時(shí)命令安裝（推薦）

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

此方法也會(huì)注冊(cè)到元數(shù)據(jù)，也就是mysql.plugin表中，所以不用擔(dān)心MySQL重啟后插件會(huì)失效。

3. MySQL8的安全策略

1. validate_password說(shuō)明

MySQL 8.0，引入了服務(wù)器組件（Components）這個(gè)特性，validate_password插件已用服務(wù)器組件重新實(shí)現(xiàn)。8.0.25版本的數(shù)據(jù)庫(kù)中，默認(rèn)自動(dòng)安裝validate_password組件。

未安裝插件前，執(zhí)行如下兩個(gè)指令 ，執(zhí)行效果：

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

安裝插件后，執(zhí)行如下兩個(gè)指令 ，執(zhí)行效果：

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)

關(guān)于 validate_password 組件對(duì)應(yīng)的系統(tǒng)變量說(shuō)明：

提示：

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

2. 修改安全策略

修改密碼驗(yàn)證安全強(qiáng)度

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

此外，還可以修改密碼中字符的長(zhǎng)度

set global validate_password_length=1;

3. 密碼強(qiáng)度測(cè)試

如果你創(chuàng)建密碼是遇到“Your password does not satisfy the current policy requirements”，可以通過(guò)函數(shù)組件去檢測(cè)密碼是否滿足條件： 0-100。當(dāng)評(píng)估在100時(shí)就是說(shuō)明使用上了最基本的規(guī)則：大寫(xiě)+小寫(xiě)+特殊字符+數(shù)字組成的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)

注意：如果沒(méi)有安裝validate_password組件或插件的話，那么這個(gè)函數(shù)永遠(yuǎn)都返回0。 關(guān)于密碼復(fù)雜度對(duì)應(yīng)的密碼復(fù)雜度策略。如下表格所示：

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. 字符集的相關(guān)操作

1 修改MySQL5.7字符集

1. 修改步驟

在MySQL 8.0版本之前，默認(rèn)字符集為 latin1 ，utf8字符集指向的是 utf8mb3 。網(wǎng)站開(kāi)發(fā)人員在數(shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候往往會(huì)將編碼修改為utf8字符集。如果遺忘修改默認(rèn)的編碼，就會(huì)出現(xiàn)亂碼的問(wèn)題。從MySQL8.0開(kāi)始，數(shù)據(jù)庫(kù)的默認(rèn)編碼將改為 utf8mb4 ，從而避免上述亂碼的問(wèn)題。

操作1：查看默認(rèn)使用的字符集

show variables like 'character%';

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

• MySQL8.0中執(zhí)行：

  [圖片上傳失敗...(image-9b7c75-1713884406726)]

• MySQL5.7中執(zhí)行：

  MySQL 5.7 默認(rèn)的客戶(hù)端和服務(wù)器都用了 latin1 ，不支持中文，保存中文會(huì)報(bào)錯(cuò)。MySQL5.7截圖如下：

  [圖片上傳失敗...(image-989c6d-1713884406726)]

  在MySQL5.7中添加中文數(shù)據(jù)時(shí)，報(bào)錯(cuò)：

  [圖片上傳失敗...(image-3c195a-1713884406726)]

  因?yàn)槟J(rèn)情況下，創(chuàng)建表使用的是 latin1 。如下：

  [圖片上傳失敗...(image-68f61b-1713884406726)]

操作2：修改字符集

vim /etc/my.cnf

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

character_set_server=utf8

[圖片上傳失敗...(image-eaecf7-1713884406726)]

操作3：重新啟動(dòng)MySQL服務(wù)

systemctl restart mysqld

此時(shí)，重新創(chuàng)建庫(kù)，再創(chuàng)建表后，可以插入中文數(shù)據(jù)。

查看字符集：

show variables like 'character%';

[圖片上傳失敗...(image-5d4d25-1713884406726)]

注意：但是原庫(kù)、原表的設(shè)定不會(huì)發(fā)生變化，參數(shù)修改只對(duì)新建的數(shù)據(jù)庫(kù)生效。

2. 已有庫(kù)&表字符集的變更

MySQL5.7版本中，以前創(chuàng)建的庫(kù)，創(chuàng)建的表字符集還是latin1。

[圖片上傳失敗...(image-84b4f8-1713884406726)]

• 修改已創(chuàng)建數(shù)據(jù)庫(kù)的字符集

  alter database dbtest1 character set 'utf8';
  

• 修改已創(chuàng)建數(shù)據(jù)表的字符集

  alter table emp convert to character set 'utf8';
  

[圖片上傳失敗...(image-f471d6-1713884406726)]

注意：但是原有的數(shù)據(jù)如果是用非'utf8'編碼的話，數(shù)據(jù)本身編碼不會(huì)發(fā)生改變。已有數(shù)據(jù)需要導(dǎo)出或刪除，然后重新插入。

2. 各級(jí)別的字符集

MySQL有4個(gè)級(jí)別的字符集和比較規(guī)則，分別是：

• 服務(wù)器級(jí)別
• 數(shù)據(jù)庫(kù)級(jí)別
• 表級(jí)別
• 列級(jí)別

執(zhí)行如下SQL語(yǔ)句：

show variables like 'character%';

[圖片上傳失敗...(image-6f3562-1713884406726)]

• character_set_server：服務(wù)器級(jí)別的字符集
• character_set_database：當(dāng)前數(shù)據(jù)庫(kù)的字符集
• character_set_client：服務(wù)器解碼請(qǐng)求時(shí)使用的字符集
• character_set_connection：服務(wù)器處理請(qǐng)求時(shí)會(huì)把請(qǐng)求字符串從character_set_client轉(zhuǎn)為character_set_connection
• character_set_results：服務(wù)器向客戶(hù)端返回?cái)?shù)據(jù)時(shí)使用的字符集

1. 服務(wù)器級(jí)別

• character_set_server ：服務(wù)器級(jí)別的字符集。

我們可以在啟動(dòng)服務(wù)器程序時(shí)通過(guò)啟動(dòng)選項(xiàng)或者在服務(wù)器程序運(yùn)行過(guò)程中使用 SET 語(yǔ)句修改這兩個(gè)變量的值。比如我們可以在配置文件中這樣寫(xiě)：

[server]
character_set_server=gbk # 默認(rèn)字符集
collation_server=gbk_chinese_ci #對(duì)應(yīng)的默認(rèn)的比較規(guī)則

當(dāng)服務(wù)器啟動(dòng)的時(shí)候讀取這個(gè)配置文件后這兩個(gè)系統(tǒng)變量的值便修改了。

2. 數(shù)據(jù)庫(kù)級(jí)別

• character_set_database ：當(dāng)前數(shù)據(jù)庫(kù)的字符集

我們?cè)趧?chuàng)建和修改數(shù)據(jù)庫(kù)的時(shí)候可以指定該數(shù)據(jù)庫(kù)的字符集和比較規(guī)則，具體語(yǔ)法如下：

CREATE DATABASE 數(shù)據(jù)庫(kù)名
    [[DEFAULT] CHARACTER SET 字符集名稱(chēng)]
    [[DEFAULT] COLLATE 比較規(guī)則名稱(chēng)];


ALTER DATABASE 數(shù)據(jù)庫(kù)名
    [[DEFAULT] CHARACTER SET 字符集名稱(chēng)]
    [[DEFAULT] COLLATE 比較規(guī)則名稱(chēng)];

其中DEFAULT可以省略，并不影響語(yǔ)句的語(yǔ)義。

CREATE DATABASE dbtest1 CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;

數(shù)據(jù)庫(kù)的創(chuàng)建語(yǔ)句中也可以不指定字符集和比較規(guī)則，如：

create database 數(shù)據(jù)庫(kù)名;

這樣的話將使用服務(wù)器級(jí)別的字符集和比較規(guī)則作為數(shù)據(jù)庫(kù)的字符集和比較規(guī)則。

3. 表級(jí)別

我們也可以在創(chuàng)建和修改表的時(shí)候指定表的字符集和比較規(guī)則，語(yǔ)法如下：

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


ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名稱(chēng)]
[COLLATE 比較規(guī)則名稱(chēng)]

如果創(chuàng)建和修改表的語(yǔ)句中沒(méi)有指明字符集和比較規(guī)則，將使用該表所在數(shù)據(jù)庫(kù)的字符集和比較規(guī)則作為該表的字符集和比較規(guī)則。

4. 列級(jí)別

對(duì)于存儲(chǔ)字符串的列，同一個(gè)表中的不同的列也可以有不同的字符集和比較規(guī)則。我們?cè)趧?chuàng)建和修改列定義的時(shí)候可以指定該列的字符集和比較規(guī)則，語(yǔ)法如下：

CREATE TABLE 表名(
列名 字符串類(lèi)型 [CHARACTER SET 字符集名稱(chēng)] [COLLATE 比較規(guī)則名稱(chēng)],
其他列...
);


ALTER TABLE 表名 MODIFY 列名 字符串類(lèi)型 [CHARACTER SET 字符集名稱(chēng)] [COLLATE 比較規(guī)則名稱(chēng)];

對(duì)于某個(gè)列來(lái)說(shuō)，如果在創(chuàng)建和修改的語(yǔ)句中沒(méi)有指明字符集和比較規(guī)則，將使用該列所在表的字符集和比較規(guī)則作為該列的字符集和比較規(guī)則。

提示:

在轉(zhuǎn)換列的字符集時(shí)需要注意，如果轉(zhuǎn)換前列中存儲(chǔ)的數(shù)據(jù)不能用轉(zhuǎn)換后的字符集進(jìn)行表示會(huì)發(fā)生錯(cuò)誤。比方說(shuō)原先列使用的字符集是utf8，列中存儲(chǔ)了一些漢字，現(xiàn)在把列的字符集轉(zhuǎn)換為ascii的話就會(huì)出錯(cuò)，因?yàn)閍scii字符集并不能表示漢字字符。

5. 小結(jié)

我們介紹的這4個(gè)級(jí)別字符集和比較規(guī)則的聯(lián)系如下：

• 如果 創(chuàng)建或修改列 時(shí)沒(méi)有顯式的指定字符集和比較規(guī)則，則該列 默認(rèn)用表的 字符集和比較規(guī)則
• 如果 創(chuàng)建表時(shí) 沒(méi)有顯式的指定字符集和比較規(guī)則，則該表 默認(rèn)用數(shù)據(jù)庫(kù)的 字符集和比較規(guī)則
• 如果 創(chuàng)建數(shù)據(jù)庫(kù)時(shí) 沒(méi)有顯式的指定字符集和比較規(guī)則，則該數(shù)據(jù)庫(kù) 默認(rèn)用服務(wù)器的 字符集和比較規(guī)則

知道了這些規(guī)則之后，對(duì)于給定的表，我們應(yīng)該知道它的各個(gè)列的字符集和比較規(guī)則是什么，從而根據(jù)這個(gè)列的類(lèi)型來(lái)確定存儲(chǔ)數(shù)據(jù)時(shí)每個(gè)列的實(shí)際數(shù)據(jù)占用的存儲(chǔ)空間大小了。比方說(shuō)我們向表 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 ，一個(gè)字符 '我' 在 gbk 中的編碼為 0xCED2 ，占用兩個(gè)字節(jié)，兩個(gè)字符的實(shí)際數(shù)據(jù)就占用4個(gè)字節(jié)。如果把該列的字符集修改為 utf8 的話，這兩個(gè)字符就實(shí)際占用6個(gè)字節(jié)

3. 字符集與比較規(guī)則(了解)

1. utf8 與 utf8mb4

utf8 字符集表示一個(gè)字符需要使用1～4個(gè)字節(jié)，但是我們常用的一些字符使用1～3個(gè)字節(jié)就可以表示了。而字符集表示一個(gè)字符所用的最大字節(jié)長(zhǎng)度，在某些方面會(huì)影響系統(tǒng)的存儲(chǔ)和性能，所以設(shè)計(jì)MySQL的設(shè)計(jì)者偷偷的定義了兩個(gè)概念：

• utf8mb3 ：閹割過(guò)的 utf8 字符集，只使用1～3個(gè)字節(jié)表示字符。
• utf8mb4 ：正宗的 utf8 字符集，使用1～4個(gè)字節(jié)表示字符。

在 MySQL 中 utf8 是utf8mb3 的別名，所以之后再M(fèi)ySQL中提到utf8就意味著用1-3個(gè)字節(jié)來(lái)表示一個(gè)字符。如果大家有使用4個(gè)子接編碼一個(gè)字符的情況，比如存儲(chǔ)一些emoji表情，那請(qǐng)使用utf8mb4。

此外通過(guò)如下指令可以查看MySQL支持的字符集：

show charset;

# 或者
show character set;

[圖片上傳失敗...(image-7fbeb1-1713884406726)]

2. 比較規(guī)則

上表中，MySQL版本一共支持41種字符集，其中的 Default collation 列表示這種字符集中一種默認(rèn)的比較規(guī)則，里面包含著該比較規(guī)則主要作用于哪種語(yǔ)言，比如 utf8_polish_ci 表示以波蘭語(yǔ)的規(guī)則比較， utf8_spanish_ci 是以西班牙語(yǔ)的規(guī)則比較， utf8_general_ci 是一種通用的比較規(guī)則。后綴表示該比較規(guī)則是否區(qū)分語(yǔ)言中的重音、大小寫(xiě)。具體如下：

最后一列 Maxlen ，它代表該種字符集表示一個(gè)字符最多需要幾個(gè)字節(jié)。

常見(jiàn)的字符集和對(duì)應(yīng)的Maxlen顯示如下：

常用操作1：

#查看GBK字符集的比較規(guī)則
SHOW COLLATION LIKE 'gbk%';

#查看UTF-8字符集的比較規(guī)則
SHOW COLLATION LIKE 'utf8%';

常用操作2：

#查看服務(wù)器的字符集和比較規(guī)則
SHOW VARIABLES LIKE '%_server';

#查看數(shù)據(jù)庫(kù)的字符集和比較規(guī)則
SHOW VARIABLES LIKE '%_database';

#查看具體數(shù)據(jù)庫(kù)的字符集
SHOW CREATE DATABASE dbtest1;

#修改具體數(shù)據(jù)庫(kù)的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

說(shuō)明:

utf8_unicode_ci 和 utf8_general_ci 對(duì)中、英文來(lái)說(shuō)沒(méi)有實(shí)質(zhì)的差別。

utf8_general_ci 相對(duì)速度快，但準(zhǔn)確度稍差。

utf8_unicode_ci 準(zhǔn)確度高，但相對(duì)速度稍慢。

一般情況，用 utf8_general_ci 就夠了，但如果你的應(yīng)用有德語(yǔ)、法語(yǔ)或者俄語(yǔ)，請(qǐng)一定使用 utf8_unicode_ci 。

修改了數(shù)據(jù)庫(kù)的默認(rèn)字符集和比較規(guī)則后，原來(lái)已經(jīng)創(chuàng)建的表格的字符集和比較規(guī)則并不會(huì)改變，如果需要，那么需要單獨(dú)修改。

常用操作3：

#查看表的字符集
show create table employees;

#查看表的比較規(guī)則
show table status from atguigudb like 'employees';

#修改表的字符集和比較規(guī)則
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

4. 請(qǐng)求到響應(yīng)過(guò)程中字符集的變化

我們知道從客戶(hù)端發(fā)往服務(wù)器的請(qǐng)求本質(zhì)上就是一個(gè)字符串，服務(wù)器向客戶(hù)端返回的結(jié)果本質(zhì)上也是一個(gè)字符串，而字符串其實(shí)就是使用某種字符集編碼的二進(jìn)制數(shù)據(jù)。這個(gè)字符串可不是使用一種字符集的編碼方式一條道走到黑的，從發(fā)送請(qǐng)求到返回結(jié)果這個(gè)過(guò)程中伴隨著多次字符集的轉(zhuǎn)換，在這個(gè)過(guò)程中會(huì)用到3個(gè)系統(tǒng)變量，如下：

這幾個(gè)系統(tǒng)變量在我的計(jì)算機(jī)上的默認(rèn)值如下（不同操作系統(tǒng)的默認(rèn)值可能不同）：

[圖片上傳失敗...(image-7c239a-1713884406726)]

為了體現(xiàn)出字符集在請(qǐng)求處理過(guò)程中的變化，我們這里特意修改一個(gè)系統(tǒng)變量的值：

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

現(xiàn)在假設(shè)我們客戶(hù)端發(fā)送的請(qǐng)求是下邊這個(gè)字符串：

SELECT * FROM t WHERE s = '我';

為了方便大家理解這個(gè)過(guò)程，我們只分析字符 '我' 在這個(gè)過(guò)程中字符集的轉(zhuǎn)換。

現(xiàn)在看一下在請(qǐng)求從發(fā)送到結(jié)果返回過(guò)程中字符集的變化：

1. 客戶(hù)端發(fā)送請(qǐng)求所使用的字符集

   一般情況下客戶(hù)端所使用的字符集和當(dāng)前操作系統(tǒng)一致，不同操作系統(tǒng)使用的字符集可能不一樣，如下：

   • 類(lèi) Unix 系統(tǒng)使用的是 utf8
   • Windows 使用的是 gbk

   當(dāng)客戶(hù)端使用的是 utf8 字符集，字符 '我' 在發(fā)送給服務(wù)器的請(qǐng)求中的字節(jié)形式就是：0xE68891

   提示

   如果你使用的是可視化工具，比如navicat之類(lèi)的，這些工具可能會(huì)使用自定義的字符集來(lái)編碼發(fā)送到服務(wù)器的字符串，而不采用操作系統(tǒng)默認(rèn)的字符集（所以在學(xué)習(xí)的時(shí)候還是盡量用命令行窗口）。

2. 服務(wù)器接收到客戶(hù)端發(fā)送來(lái)的請(qǐng)求其實(shí)是一串二進(jìn)制的字節(jié)，它會(huì)認(rèn)為這串字節(jié)采用的字符集是character_set_client ，然后把這串字節(jié)轉(zhuǎn)換為 character_set_connection 字符集編碼的字符。

   由于我的計(jì)算機(jī)上 character_set_client 的值是 utf8 ，首先會(huì)按照 utf8 字符集對(duì)字節(jié)串0xE68891 進(jìn)行解碼，得到的字符串就是 '我' ，然后按照 character_set_connection 代表的字符集，也就是 gbk 進(jìn)行編碼，得到的結(jié)果就是字節(jié)串 0xCED2 。

3. 因?yàn)楸?t 的列 col 采用的是 gbk 字符集，與 character_set_connection 一致，所以直接到列中找字節(jié)值為 0xCED2 的記錄，最后找到了一條記錄。

   提示

   如果某個(gè)列使用的字符集和character_set_connection代表的字符集不一致的話，還需要進(jìn)行一次字符集轉(zhuǎn)換。

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

5. 由于客戶(hù)端是用的字符集是 utf8 ，所以可以順利的將 0xE68891 解釋成字符 我 ，從而顯示到我們的顯示器上，所以我們?nèi)祟?lèi)也讀懂了返回的結(jié)果。

總結(jié)圖示如下：

[圖片上傳失敗...(image-595fa0-1713884406726)]

[圖片上傳失敗...(image-5e51e6-1713884406726)]

從這個(gè)分析中我們可以得出這么幾點(diǎn)需要注意的地方：

• 服務(wù)器認(rèn)為客戶(hù)端發(fā)送過(guò)來(lái)的請(qǐng)求是用 character_set_client 編碼的。

  假設(shè)你的客戶(hù)端采用的字符集和 character_set_client 不一樣的話，這就會(huì)出現(xiàn)識(shí)別不準(zhǔn)確的情況。比如我的客戶(hù)端使用的是 utf8字符集，如果把系統(tǒng)變量 character_set_client的值設(shè)置為 ascii 的話，服務(wù)器可能無(wú)法理解我們發(fā)送的請(qǐng)求，從而無(wú)法處理請(qǐng)求。

• 服務(wù)器將把得到的結(jié)果集使用 character_set_results 編碼后發(fā)送給客戶(hù)端

  假設(shè)你的客戶(hù)端采用的字符集和 character_set_results 不一樣的話，這就可能會(huì)出現(xiàn)客戶(hù)端無(wú)法解碼結(jié)果集的情況，結(jié)果就是在你的屏幕上出現(xiàn)亂碼。比如我的客戶(hù)端使用的是 utf8 字符集，如果把系統(tǒng)變量 character_set_results 的值設(shè)置為 ascii 的話，可能會(huì)產(chǎn)生亂碼。

• character_set _connection 只是在服務(wù)器再將請(qǐng)求的字符串從 character_set_client 轉(zhuǎn)換為 character_set_connection 時(shí)使用，一定要注意，該字符集包含的字符范圍一定涵蓋請(qǐng)求中的字符，要不然會(huì)導(dǎo)致有的字符無(wú)法使用 character_set_connection 代表的字符集進(jìn)行編碼。

經(jīng)驗(yàn)：

開(kāi)發(fā)中通常把 character_set_client 、character_set_connection、character_set_results 這三個(gè)系統(tǒng)變量設(shè)置成和客戶(hù)端使用的字符集一致的情況，這樣就減少了很多無(wú)畏的字符集轉(zhuǎn)換。為了方便我們?cè)O(shè)置，MySQL提供了一條非常簡(jiǎn)便的語(yǔ)句：

SET names 字符集名;

這一條語(yǔ)句產(chǎn)生的效果和我們執(zhí)行這三條語(yǔ)句的效果是一樣的：

set character_set_client = 字符集名;

set character_set_connection = 字符集名;

set character_set_results = 字符集名;

如果想在啟動(dòng)客戶(hù)端時(shí)就將 character_set_client 、character_set_connection、character_set_results 三個(gè)系統(tǒng)變量設(shè)置成一樣，可以通過(guò)修改配置文件 my.cnf：

[client]
default-character-set=utf8

它起到的效果和執(zhí)行一遍 SET names utf8 是一樣的，都會(huì)將那三個(gè)系統(tǒng)變量的值設(shè)置成utf8。

6. SQL大小寫(xiě)規(guī)范

1. Windows和Linux平臺(tái)區(qū)別

在 SQL 中，關(guān)鍵字和函數(shù)名是不用區(qū)分字母大小寫(xiě)的，比如 SELECT、WHERE、ORDER、GROUP BY 等關(guān)鍵字，以及 ABS、MOD、ROUND、MAX 等函數(shù)名。

不過(guò)在 SQL 中，你還是要確定大小寫(xiě)的規(guī)范，因?yàn)樵?Linux 和 Windows 環(huán)境下，你可能會(huì)遇到不同的大小寫(xiě)問(wèn)題。 windows系統(tǒng)默認(rèn)大小寫(xiě)不敏感 ，但是 linux系統(tǒng)是大小寫(xiě)敏感的 。

通過(guò)如下命令查看：

SHOW VARIABLES LIKE '%lower_case_table_names%';

• Windows系統(tǒng)下：

  [圖片上傳失敗...(image-cfe449-1713884406726)]

• Linux系統(tǒng)下：

  [圖片上傳失敗...(image-2778cf-1713884406726)]

• lower_case_table_names參數(shù)值的設(shè)置：
  • 默認(rèn)為0，大小寫(xiě)敏感 。
  • 設(shè)置1，大小寫(xiě)不敏感。創(chuàng)建的表，數(shù)據(jù)庫(kù)都是以小寫(xiě)形式存放在磁盤(pán)上，對(duì)于sql語(yǔ)句都是轉(zhuǎn)換為小寫(xiě)對(duì)表和數(shù)據(jù)庫(kù)進(jìn)行查找。
  • 設(shè)置2，創(chuàng)建的表和數(shù)據(jù)庫(kù)依據(jù)語(yǔ)句上格式存放，凡是查找都是轉(zhuǎn)換為小寫(xiě)進(jìn)行。
• 兩個(gè)平臺(tái)上SQL大小寫(xiě)的區(qū)別具體來(lái)說(shuō)：

MySQL在Linux下數(shù)據(jù)庫(kù)名、表名、列名、別名大小寫(xiě)規(guī)則是這樣的：

1、數(shù)據(jù)庫(kù)名、表名、表的別名、變量名是嚴(yán)格區(qū)分大小寫(xiě)的；

2、關(guān)鍵字、函數(shù)名稱(chēng)在 SQL 中不區(qū)分大小寫(xiě)；

3、列名（或字段名）與列的別名（或字段別名）在所有的情況下均是忽略大小寫(xiě)的；

MySQL在Windows的環(huán)境下全部不區(qū)分大小寫(xiě)

2. Linux 下大小寫(xiě)規(guī)則設(shè)置

當(dāng)想設(shè)置為大小寫(xiě)不敏感時(shí)，要在 my.cnf 這個(gè)配置文件 [mysqld] 中加入lower_case_table_names=1 ，然后重啟服務(wù)器。

• 但是要在重啟數(shù)據(jù)庫(kù)實(shí)例之前就需要將原來(lái)的數(shù)據(jù)庫(kù)和表轉(zhuǎn)換為小寫(xiě)，否則將找不到數(shù)據(jù)庫(kù)名。

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

  1、停止MySQL服務(wù)

  2、刪除數(shù)據(jù)目錄，即刪除 /var/lib/mysql 目錄

  3、在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1

  4、啟動(dòng)MySQL服務(wù)

注意：在進(jìn)行數(shù)據(jù)庫(kù)參數(shù)名設(shè)置之前，需要掌握這個(gè)參數(shù)帶來(lái)的影響，切不可盲目設(shè)置。

3. SQL編寫(xiě)建議

如果你的變量名命名規(guī)范沒(méi)有統(tǒng)一，就可能產(chǎn)生錯(cuò)誤。這里有一個(gè)有關(guān)命名規(guī)范的建議：

1. 關(guān)鍵字和函數(shù)名稱(chēng)全部大寫(xiě)；

2. 數(shù)據(jù)庫(kù)名、表名、表別名、字段名、字段別名等全部小寫(xiě)；

3. SQL 語(yǔ)句必須以分號(hào)結(jié)尾。

數(shù)據(jù)庫(kù)名、表名和字段名在 Linux MySQL 環(huán)境下是區(qū)分大小寫(xiě)的，因此建議你統(tǒng)一這些字段的命名規(guī)則，比如全部采用小寫(xiě)的方式。

雖然關(guān)鍵字和函數(shù)名稱(chēng)在 SQL 中不區(qū)分大小寫(xiě)，也就是如果小寫(xiě)的話同樣可以執(zhí)行。但是同時(shí)將關(guān)鍵詞和函數(shù)名稱(chēng)全部大寫(xiě)，以便于區(qū)分?jǐn)?shù)據(jù)庫(kù)名、表名、字段名。

7. sql_mode的合理設(shè)置

1.介紹

sql_mode 會(huì)影響 MySQL 支持的 SQL 語(yǔ)法以及它執(zhí)行的 數(shù)據(jù)驗(yàn)證檢查。通過(guò)設(shè)置 sql_mode ，可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗(yàn)，有效地保障數(shù)據(jù)準(zhǔn)確性。

MySQL 服務(wù)器可以在不同的 SQL 模式下運(yùn)行，并且可以針對(duì)不同的客戶(hù)端以不同的方式應(yīng)用這些模式，具體取決于 sql_mode 系統(tǒng)變量的值。

MySQL 5.6 和 MySQL5.7默認(rèn)的sql_mode模式參數(shù)是不一樣的：

• 5.6的mode默認(rèn)值為空（即：NO_ENGINE_SUBSTITUTION），其實(shí)表示的是一個(gè)空值，相當(dāng)于沒(méi)有什么模式設(shè)置，可以理解為寬松模式。在這種設(shè)置下是可以允許一些非法操作的，比如允許一些非法數(shù)據(jù)的插入。
• 5.7的mode是STRICT_RANS_TABLES，也就是嚴(yán)格模式。用于進(jìn)行數(shù)據(jù)的嚴(yán)格校驗(yàn)，錯(cuò)誤數(shù)據(jù)不能插入，報(bào)error（錯(cuò)誤），并且事務(wù)回滾。

2. 寬松模式 vs嚴(yán)格模式

寬松模式：

如果設(shè)置的是寬松模式，那么我們?cè)诓迦霐?shù)據(jù)的時(shí)候，即便是給了一個(gè)錯(cuò)誤的數(shù)據(jù)，也可能會(huì)被接受，并且不報(bào)錯(cuò)。

舉例 ：我在創(chuàng)建一個(gè)表時(shí)，該表中有一個(gè)字段為name，給name設(shè)置的字段類(lèi)型時(shí) char(10) ，如果我在插入數(shù)據(jù)的時(shí)候，其中name這個(gè)字段對(duì)應(yīng)的有一條數(shù)據(jù)的 長(zhǎng)度超過(guò)了10 ，例如'1234567890abc'，超過(guò)了設(shè)定的字段長(zhǎng)度10，那么不會(huì)報(bào)錯(cuò)，并且取前10個(gè)字符存上，也就是說(shuō)你這個(gè)數(shù)據(jù)被存為了'1234567890'，而'abc'就沒(méi)有了。但是，我們給的這條數(shù)據(jù)是錯(cuò)誤的，因?yàn)槌^(guò)了字段長(zhǎng)度，但是并沒(méi)有報(bào)錯(cuò)，并且mysql自行處理并接受了，這就是寬松模式的效果。

應(yīng)用場(chǎng)景 ：通過(guò)設(shè)置sql mode為寬松模式，來(lái)保證大多數(shù)sql符合標(biāo)準(zhǔn)的sql語(yǔ)法，這樣應(yīng)用在不同數(shù)據(jù)庫(kù)之間進(jìn)行 遷移 時(shí)，則不需要對(duì)業(yè)務(wù)sql 進(jìn)行較大的修改。

嚴(yán)格模式：

出現(xiàn)上面寬松模式的錯(cuò)誤，應(yīng)該報(bào)錯(cuò)才對(duì)，所以MySQL5.7版本就將sql_mode默認(rèn)值改為了嚴(yán)格模式。所以在 生產(chǎn)等環(huán)境 中，我們必須采用的是嚴(yán)格模式，進(jìn)而開(kāi)發(fā)、測(cè)試環(huán)境的數(shù)據(jù)庫(kù)也必須要設(shè)置，這樣在開(kāi)發(fā)測(cè)試階段就可以發(fā)現(xiàn)問(wèn)題。并且我們即便是用的MySQL5.6，也應(yīng)該自行將其改為嚴(yán)格模式。

開(kāi)發(fā)經(jīng)驗(yàn) ：MySQL等數(shù)據(jù)庫(kù)總想把關(guān)于數(shù)據(jù)的所有操作都自己包攬下來(lái)，包括數(shù)據(jù)的校驗(yàn)，其實(shí)開(kāi)發(fā)中，我們應(yīng)該在自己 開(kāi)發(fā)的項(xiàng)目程序級(jí)別將這些校驗(yàn)給做了 ，雖然寫(xiě)項(xiàng)目的時(shí)候麻煩了一些步驟，但是這樣做之后，我們?cè)谶M(jìn)行數(shù)據(jù)庫(kù)遷移或者在項(xiàng)目的遷移時(shí)，就會(huì)方便很多。

改為嚴(yán)格模式后可能會(huì)存在的問(wèn)題：

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

3. 寬松模式再舉例

寬松模式舉例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;

4. 模式查看和設(shè)置

• 查看當(dāng)前的sql_mode

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

  [圖片上傳失敗...(image-6e44a1-1713884406726)]

• 臨時(shí)設(shè)置方式：設(shè)置當(dāng)前窗口中設(shè)置sql_mode

  SET GLOBAL sql_mode = 'modes...'; #全局
  
  SET SESSION sql_mode = 'modes...'; #當(dāng)前會(huì)話
  

  舉例：

  #改為嚴(yán)格模式。此方法只在當(dāng)前會(huì)話中生效，關(guān)閉當(dāng)前會(huì)話就不生效了。
  set SESSION sql_mode='STRICT_TRANS_TABLES';
  
  
  #改為嚴(yán)格模式。此方法在當(dāng)前服務(wù)中生效，重啟MySQL服務(wù)后失效。
  set GLOBAL sql_mode='STRICT_TRANS_TABLES';
  

• 永久設(shè)置方式：在/etc/my.cnf中配置sql_mode

  在my.cnf文件(windows系統(tǒng)是my.ini文件)，新增：

  [mysqld]
  sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR
  _DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  

  然后 重啟MySQL 。

  當(dāng)然生產(chǎn)環(huán)境上是禁止重啟MySQL服務(wù)的，所以采用臨時(shí)設(shè)置方式 + 永久設(shè)置方式來(lái)解決線上的問(wèn)題，那么即便是有一天真的重啟了MySQL服務(wù)，也會(huì)永久生效了。

5. sql_mode 常用值