MySQL-01.Linux下MySQL安裝和使用

长名06發表於2024-03-07
MySqlLinux

C-01.Linux下MySQL的安裝和使用

前置說明，MySQL高階部分，需要前置知識，為Linux作業系統。如果沒有該部分知識，這裡推薦韓順平老師的Linux作業系統的教程。韓老師Linux教程

1.安裝前準備

1.1 Linux系統及工具的準備

  • 安裝並啟動好兩臺虛擬機器: CentOS7版本
    • 掌握克隆虛擬機器的操作
      • mac地址
      • 主機名
      • ip地址
      • UUID
  • 安裝有Xshell和Xftp等遠端訪問CentOS系統的工具
  • CentOS6和CentOS7在MySQL的使用區別
#1. 防火牆：6是iptables，7是firewalld

#2. 啟動服務的命令：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-libs的版本，則沒有內容

1.3 MySQL的解除安裝

1.3.1 關閉mysqld服務
systemctl stop mysqld #.service字尾可省略
1.3.2 檢視當前mysql安裝狀況
rpm -qa | grep -i mysql

#或

yum list installed | grep mysql
1.3.3 解除安裝上述命令查詢出的已安裝程式
yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

必須解除安裝乾淨，反覆執行rpm -qa | grep -i mysql確認是否有解除安裝殘留

1.3.4 刪除mysql相關檔案
  • 查詢相關檔案
find / -name mysql
#備註 rpm安裝的mysql的目錄一般在/var/lib/mysql,/var/lib/mysql/mysql
  • 刪除上述命令查詢出的相關檔案
rm -rf xxx
1.3.5 刪除my.cnf檔案
rm -rf /etc/my.cnf #類似windows下的my.ini 是linux系統下的mysql的配置檔案

2.MySQL的Linux版安裝

2.1 MySQL的4大版本

2.2 下載MySQL指定版本

2.2.1 下載地址

MySQL社群版下載地址

2.2.2 選擇合適的MySQL版本

課程中，老師選擇的是8.0.25版本的進行安裝。

沒有CentOS系統的版本，選擇與之對應的Red Hat Enterprise Linux。選擇RPM Bundle全量包。包括了所有的元件。

2.2.3 從全量包中，抽出mysql的安裝檔案

2.3 CentOS7下檢查MySQL依賴

2.3.1 檢查/tmp臨時目錄許可權(必須)

由於mysql安裝過程中，會透過mysql使用者在/tmp目錄下新建tmp_db檔案，所以請給/tmp較大的許可權。

chmod -R 777 /tmp #修改目錄的許可權 -R 目錄中的所有子目錄
2.3.2 檢查依賴
rpm -qa | grep libaio

rpm -qa | grep net-tools

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

2.4 CentOS7下MySQL安裝過程

2.4.1 將抽出的mysql安裝檔案，上傳到/opt目錄下

使用Xftp或其他連線軟體，進行上傳。

2.4.2 安裝

下面命令，必須順序執行，需要再/opt目錄下執行，因為安裝檔案在該目錄下，cd /opt。

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

安裝過程中可能的報錯資訊

yum remove mariadb-libs#解決，刪除依賴 重新執行命令即可
2.4.3 檢視mysql版本
mysql --version
#或
msyqladmin --version

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

rpm -qa | grep -i mysql
2.3.4 服務的初始化

為了保證資料庫目錄與檔案的所有者為 mysql 登入使用者，如果你是以 root 身份執行 mysql 服務，需要執行下面的命令初始化：

mysqld --initialize --user=mysql

說明： --initialize 選項預設以“安全”模式來初始化，則會為 root 使用者生成一個密碼並將該密碼標記為過期，登入後你需要設定一個新的密碼。生成的臨時密碼會往日誌中記錄一份。

檢視密碼：

cat /var/log/mysqld.log

root@localhost: 後面就是初始化的密碼

2.3.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
2.3.6 檢視服務是否自啟動
systemctl list-unit-files | grep mysqld.service #預設是enabled自啟動的

systemctl enable mysqld.service #將服務設定為開機自啟動 disable不自啟動

3.MySQL登入

3.1 首次登入

mysql -uroot -p #不用 -hlocalhost -P3306 連線本機時，會自動補全 輸入初始化密碼

3.2 修改密碼

  • 因為安全策略啟動mysql服務，初始話密碼是過期的，所以需要重置密碼
  • 修改密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; #new_password替換為密碼

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

  • 密碼過簡單保錯，修改為複雜的密碼規則之後，設定成功，可以正常使用資料庫了

3.3 設定遠端連線

3.3.1 當前問題

在用SQLyog或Navicat中配置遠端連線Mysql資料庫時遇到如下錯誤號碼為1130的資訊，這是由於Mysql配置了不支援遠端連線引起的。

3.3.2 保證網路暢通

ping mysql服務ip地址 #保證能ping通

3.3.3 Linux開發3306埠，或者關閉防火牆(不推薦)
#centOS6
service iptables stop

#關閉防火牆
systemctl stop firewalld.service

#啟動防火牆
systemctl start firewalld.service

#檢視防火牆狀態
systemctl status firewalld.service

#設定開機啟用防火牆
systemctl enable firewalld.service

#設定開機禁用防火牆
systemctl disable firewalld.service

#檢視已開放的埠號
firewall-cmd --list-all

#開放埠號
firewall-cmd --add-port=3306/tcp --permanent #若需要Xshell遠端連線，需要開放22埠

#重啟防火牆
firewall-cmd --reload
3.3.4 修改User表
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;
3.3.5 測試
  • 如果是 MySQL5.7 版本，接下來就可以使用SQLyog或者Navicat成功連線至MySQL了。
  • 如果是 MySQL8 版本，連線時還可能會出現錯誤號碼為2058的錯誤資訊，配置新連線報錯：錯誤號碼 2058，分析是 mysql 密碼加密方法變了
    解決方法：Linux下 mysql -u root -p 登入你的 mysql 資料庫，然後 執行這條SQL：
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';#abc123更換為自己密碼

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執行時的時候被解除安裝。當你解除安裝外掛時就會報錯。如下所示。

安裝/啟用外掛方式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的安全策略

4.3.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> 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元件對應的系統變數說明：

提示：元件和外掛的預設值可能有所不同。例如，MySQL 5.7. validate_password_check_user_name的預設值為OFF。

4.3.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方式修改

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字符集

5.1.1 修改步驟

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

操作1：檢視預設使用的字符集

show variables like 'character%';
# 或者
show variables like '%char%';
  • MySQL8.0執行
mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
  • MySQL5.7 執行
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latinl                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latinl                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

在MySQL5.7中新增中文資料時，報錯，因為預設情況下，建立表使用的是 latin1 。

操作2：修改字符集

vim /etc/my.cnf

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

character_set_server=utf8

操作3：重新啟動MySQL服務

systemctl restart mysqld

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

5.1.2 已有庫&表字符集的修改
#修改已建立資料庫的字符集
alter database db_name character set 'utf8';

#修改已建立資料表的字符集
alter table table_name convert to character set 'utf8';

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

5.2 各級別的字符集

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

  • 伺服器級別
  • 資料庫級別
  • 表級別
  • 列級別
mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
  • character_set_server：伺服器級別的字符集
  • character_set_database：資料庫預設的的字符集
  • character_set_client：伺服器解碼請求時使用的字符集
  • character_set_connection：伺服器處理請求時會把請求字串從character_set_client轉為character_set_connection
  • character_set_results：伺服器向客戶端返回資料時使用的字符集
5.2.1 伺服器級別
  • character_set_server：伺服器級別的字符集。

我們可以在啟動伺服器程式時透過啟動選項或者在伺服器程式執行過程中使用SET語句修改這兩個變數的值。比如我們可以在配置檔案中這樣寫：

[server]
character_set_server=gbk # 預設字符集
collation_server=gbk_chinese_ci #對應的預設的比較規則

當伺服器啟動的時候讀取這個配置檔案後這兩個系統變數的值便修改了。

5.2.2 資料庫級別
  • character_set_database：當前資料庫的字符集

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

CREATE DATABASE 資料庫名
    [[DEFAULT] CHARACTER SET 字符集名稱]
    [[DEFAULT] COLLATE 比較規則名稱];
    
ALTER DATABASE 資料庫名
    [[DEFAULT] CHARACTER SET 字符集名稱]
    [[DEFAULT] COLLATE 比較規則名稱];
5.2.3 表級別

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

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

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

5.2.4 列級別

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

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

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

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

5.2.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 字符集與比較規則(瞭解)

5.3.1 utf8 與 utf8mb4

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

  • utf8mb3 ：閹割過的 utf8 字符集，只使用1～3個位元組表示字元。
  • utf8mb4 ：正宗的 utf8 字符集，使用1～4個位元組表示字元。
5.3.2 比較規則
mysql> show charset; #或者show character set

上述指令看到，MySQL版本一共支援41種字符集，其中的 Default collation 列表示這種字符集中一種預設的比較規則，裡面包含著該比較規則主要作用於哪種語言，比如utf8_polish_ci表示以波蘭語的規則比較，utf8_spanish_ci是以西班牙語的規則比較，utf8_general_ci是一種通用的比較規則。

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

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

最後一列Maxlen，它代表該種字符集表示一個字元最多需要幾個位元組。

#檢視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';

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

系統變數 描述
character_set_client 伺服器解碼請求時使用的字符集
character_set_connection 伺服器處理請求時會把請求字串從character_set_client 轉為 character_set_connection
character_set_results 伺服器向客戶端返回資料時使用的字符集 
mysql> show variables like 'character_%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

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

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 進行解碼，得到的字串就是 '我' ，然後按照character_set_connection代表的字符集，也就是gbk進行編碼，得到的結果就是位元組串 0xCED2 。

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系統是大小寫敏感的 。

  • Linux系統
mysql> show variables like '%lower_case_table_names%';#linux下
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)
  • Windows系統
mysql> show variables like '%lower_case_table_names%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set, 1 warning (0.01 sec)
  • lower_case_table_names引數值的設定：
    • 預設為0，大小寫敏感 。
    • 設定1，大小寫不敏感。建立的表，資料庫都是以小寫形式存放在磁碟上，對於sql語句都是轉
    • 換為小寫對錶和資料庫進行查詢。
    • 設定2，建立的表和資料庫依據語句上格式存放，凡是查詢都是轉換為小寫進行。
  • 兩個平臺上SQL大小寫的區別具體來說：

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

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

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

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

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

6.2 Linux下大小寫規則設定

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

  • 但是要在重啟資料庫例項之前就需要將原來的資料庫和錶轉換為小寫，否則將找不到資料庫名。

  • 此引數適用於MySQL5.7。在MySQL 8下禁止在重新啟動 MySQL 服務時將(不推薦改變)

    • lower_case_table_names設定成不同於初始化 MySQL 服務時設定的

    • lower_case_table_names值。如果非要將MySQL8設定為大小寫不敏感，具體步驟為：

/*
    1、停止MySQL服務
    2、刪除資料目錄，即刪除 /var/lib/mysql 目錄
    3、在MySQL配置檔案（ /etc/my.cnf ）中新增 lower_case_table_names=1
    4、啟動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 模式檢視和設定

  • 檢視當前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...'; #當前會話
  • 永久設定方式：在/etc/my.cnf中配置sql_mode

在my.cnf檔案(windows系統是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。

當然生產環境上是禁止重啟MySQL服務的，所以採用臨時設定方式 + 永久設定方式來解決線上的問題，那麼即便是有一天真的重啟了MySQL服務，也會永久生效了。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。

相關文章