之前兩篇文章帶你了解了 MySQL 的基礎(chǔ)語法和 MySQL 的進階內(nèi)容，那么這篇文章我們來了解一下 MySQL 中的高級內(nèi)容。

其他文章：

138 張圖帶你 MySQL 入門

47 張圖帶你 MySQL 進階?。?！

本文思維導(dǎo)圖如下。

事務(wù)控制和鎖定語句

我們知道，MyISAM 和 MEMORY 存儲引擎支持表級鎖定(table-level locking)，InnoDB 存儲引擎支持行級鎖定(row-level locking)，BDB 存儲引擎支持頁級鎖定(page-level locking)。各個鎖定級別的特點如下

頁級鎖：銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般

表級鎖：表級鎖是對整張表進行加鎖，MyISAM 和 MEMORY 主要支持表級鎖，表級鎖加鎖快，不會出現(xiàn)死鎖，鎖的粒度比較粗，并發(fā)度最低

行級鎖：行級鎖可以說是 MySQL 中粒度最細(xì)的一種鎖了，InnoDB 支持行級鎖，行級鎖容易發(fā)生死鎖，并發(fā)度比較好，同時鎖的開銷也比較大。

MySQL 默認(rèn)情況下支持表級鎖定和行級鎖定。但是在某些情況下需要手動控制事務(wù)以確保整個事務(wù)的完整性，下面我們就來探討一下事務(wù)控制。但是在探討事務(wù)控制之前我們先來認(rèn)識一下兩個鎖定語句

鎖定語句

MySQL 的鎖定語句主要有兩個 Lock 和 unLock，Lock Tables 可用于鎖定當(dāng)前線程的表，就跟 Java 語法中的 Lock 鎖的用法是一樣的，如果表鎖定，意味著其他線程不能再操作表，直到鎖定被釋放為止。如下圖所示

lock table cxuan005 read;

我們鎖定了 cxuan005 的 read 鎖，然后這時我們再進行一次查詢，看看是否能夠執(zhí)行這條語句

select * from cxuan005 where id = 111;

可以看到，在進行 read 鎖定了，我們?nèi)耘f能夠執(zhí)行查詢語句。

現(xiàn)在我們另外起一個窗口，相當(dāng)于另起了一個線程來進行查詢操作。

select * from cxuan005;

這是第二個窗口執(zhí)行查詢的結(jié)果，可以看到，在一個線程執(zhí)行 read 鎖定后，其他線程仍然可以進行表的查詢操作。

那么第二個線程能否執(zhí)行更新操作呢？我們來看一下

update cxuan005 set info='cxuan' where id = 111;

發(fā)生了什么？怎么沒有提示結(jié)果呢？其實這個情況下表示 cxuan005 已經(jīng)被加上了 read 鎖，由于當(dāng)前線程不是持有鎖的線程，所以當(dāng)前線程無法執(zhí)行更新。

解鎖語句

現(xiàn)在我們把窗口切換成持有 read 鎖的線程，來進行 read 鎖的解鎖

unlock tables;

在解鎖完成前，進行更新的線程會一直等待，直到解鎖完成后，才會進行更新。我們可以看一下更新線程的結(jié)果。

可以看到，線程已經(jīng)更新完畢，我們看一下更新的結(jié)果

select * from cxuan005 where id = 111;

如上圖所示，id = 111 的值已經(jīng)被更新成了 cxuan。

事務(wù)控制

事務(wù)(Transaction) 是訪問和更新數(shù)據(jù)庫的基本執(zhí)行單元，一個事務(wù)中可能會包含多個 SQL 語句，事務(wù)中的這些 SQL 語句要么都執(zhí)行，要么都不執(zhí)行，而 MySQL 它是一個關(guān)系型數(shù)據(jù)庫，它自然也是支持事務(wù)的。事務(wù)同時也是區(qū)分關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫的一個重要的方面。

在 MySQL 事務(wù)中，主要涉及的語法包含SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK等。

自動提交

在 MySQL 中，事務(wù)默認(rèn)是自動提交(Autocommit)的，如下所示

show variables like 'autocommit';

在自動提交的模式下，每個 SQL 語句都會當(dāng)作一個事務(wù)執(zhí)行提交操作，例如我們上面使用的更新語句

update cxuan005 set info='cxuan' where id = 111;

如果想要關(guān)閉數(shù)據(jù)庫的自動提交應(yīng)該怎么做呢？

其實，MySQL 是可以關(guān)閉自動提交的，你可以執(zhí)行

set autocommit = 0;

然后我們再看一下自動提交是否關(guān)閉了，再次執(zhí)行一下 show variables like 'autocommit' 語句

可以看到，自動提交已經(jīng)關(guān)閉了，再次執(zhí)行

set autocommit = 1;

會再次開啟自動提交。

這里注意一下特殊操作。

在 MySQL 中，存在一些特殊的命令，如果在事務(wù)中執(zhí)行了這些命令，會馬上強制執(zhí)行 commit 提交事務(wù)；比如 DDL 語句(create table/drop table/alter/table)、lock tables 語句等等。

不過，常用的 select、insert、update 和 delete命令，都不會強制提交事務(wù)。

手動提交

如果需要手動 commit 和 rollback 的話，就需要明確的事務(wù)控制語句了。

典型的 MySQL 事務(wù)操作如下

start transaction; ... # 一條或者多條語句 commit;

上面代碼中的 start transaction 就是事務(wù)的開始語句，編寫 SQL 后會調(diào)用 commit 提交事務(wù)，然后將事務(wù)統(tǒng)一執(zhí)行，如果 SQL 語句出現(xiàn)錯誤會自動調(diào)用 Rollback 進行回滾。

下面我們就通過示例來演示一下 MySQL 的事務(wù)，同樣的，我們需要啟動兩個窗口來演示，為了便于區(qū)分，我們使用 mysql01 和 mysql02 來命名。

我們用 start transaction 命令啟動一個事務(wù)，然后再 cxuan005 表中插入一條數(shù)據(jù)，此時 mysql02 不做任何操作。涉及的 SQL 語句如下。

start transaction;

然后執(zhí)行

select * from cxuan005;

查詢一下 cxuan005 中的數(shù)據(jù)

嗯。。。很多長度太長了，現(xiàn)在我們把所有的 info 數(shù)據(jù)都更新為 cxuan 。

update cxuan005 set info='cxuan';

更新完畢后，我們先不提交事務(wù)，分別在 mysql01 和 mysql02 中進行查詢，發(fā)現(xiàn)只有 mysql01 窗口中的查詢已經(jīng)生效，而 mysql02 中還是更新前的數(shù)據(jù)

現(xiàn)在我們在 mysql01 中 commit 當(dāng)前事務(wù)，然后在 mysql02 中查詢，發(fā)現(xiàn)數(shù)據(jù)已經(jīng)被修改了。

除了 commit 之外，MySQL 中還有 commit and chain 命令，這個命令會提交當(dāng)前事務(wù)并且重新開啟一個新的事務(wù)。如下代碼所示

start transaction; # 開啟一個新的事務(wù) insert into cxuan005(id,info) values (555,'cxuan005'); # 插入一條數(shù)據(jù) commit and chain; # 提交當(dāng)前事務(wù)并重新開啟一個事務(wù)

上面是一個事務(wù)操作，在 commit and chain 鍵入后，我們可以再次執(zhí)行 SQL 語句

update cxuan005 set info = 'cxuan' where id = 555; commit;

然后再次查詢

select * from cxuan005;

執(zhí)行后，可以發(fā)現(xiàn)，我們僅僅使用了一個 start transaction 命令就執(zhí)行了兩次事務(wù)操作。

如果在手動提交的事務(wù)中，你發(fā)現(xiàn)有一條 SQL 語句寫的不正確或者有其他原因需要回滾，那么此時你就會用到 rollback 語句，它會回滾當(dāng)前事務(wù)，相當(dāng)于什么也沒發(fā)生。如下代碼所示。

start transaction; delete from cxuan005 where id = 555; rollback;

這里切忌一點：delete 刪除語句一定要加 where ，不加 where 語句的刪除就是耍流氓。

在同一個事務(wù)操作中，最好使用相同存儲引擎的表，如果使用不同存儲引擎的表后，rollback 語句會對非事務(wù)類型的表進行特別處理，因此 commit 、rollback 只能對事務(wù)類型的表進行提交和回滾。

我們提交的事務(wù)一般都會被記錄到二進制的日志中，但是如果一個事務(wù)中包含非事務(wù)類型的表，那么回滾操作也會被記錄到二進制日志中，以確保非事務(wù)類型的表可以被復(fù)制到從數(shù)據(jù)庫中。

這里解釋一下什么是事務(wù)表和非事務(wù)表

事務(wù)表和非事務(wù)表

事務(wù)表故名思義就是支持事務(wù)的表，支不支持事務(wù)和 MySQL 的存儲類型有關(guān)，一般情況下，InnoDB 存儲引擎的表是支持事務(wù)的，關(guān)于 InnoDB 的知識，我們會在后面詳細(xì)介紹。

非事務(wù)表相應(yīng)的就是不支持事務(wù)的表，在 MySQL 中，存儲引擎 MyISAM 是不支持事務(wù)的，非事務(wù)表的特點是不支持回滾。

對于回滾的話，還要講一點就是 SAVEPOINT，它能指定事務(wù)回滾的一部分，但是不能指定事務(wù)提交的一部分。SAVEPOINT 可以指定多個，在滿足不同條件的同時，回滾不同的 SAVEPOINT。需要注意的是，如果定義了兩個相同名稱的 SAVEPOINT，則后面定義的 SAVEPOINT 會覆蓋之前的定義。如果 SAVEPOINT 不再需要的話，可以通過 RELEASE SAVEPOINT 來進行刪除。刪除后的 SAVEPOINT 不能再執(zhí)行 ROLLBACK TO SAVEPOINT 命令。

我們通過一個示例來進行模擬不同的 SAVEPOINT

首先先啟動一個事務(wù) ，向 cxuan005 中插入一條數(shù)據(jù)，然后進行查詢，那么是可以查詢到這條記錄的

start transaction; insert into cxuan005(id,info) values(666,'cxuan666'); select * from cxuan005 where id = 666;

查詢之后的記錄如下

然后我們定義一個 SAVEPOINT，如下所示

savepoint test;

然后繼續(xù)插入一條記錄

insert into cxuan005(id,info) values(777,'cxuan777');

此時就可以查詢到兩條新增記錄了，id 是 666 和 777 的記錄。

select * from cxuan005 where id = 777;

那么我們可以回滾到剛剛定義的 SAVEPOINT

rollback to savepoint test;

再次查詢 cxuan005 這個表，可以看到，只有 id=666 的這條記錄插入進來了，說明 id=777 這條記錄已經(jīng)被回滾了。

此時我們看到的都是 mysql01 中事務(wù)還沒有提交前的狀態(tài)，所以這時候 mysql02 中執(zhí)行查詢操作是看不到 666 這條記錄的。

然后我們在 mysql01 中執(zhí)行 commit 操作，那么此時在 mysql02 中就可以查詢到這條記錄了。

SQL 安全問題

SQL 安全問題應(yīng)該是我們程序員比較忽視的一個地方了。日常開發(fā)中，我們一般只會關(guān)心 SQL 能不能解決我們的業(yè)務(wù)問題，能不能把數(shù)據(jù)查出來，而對于 SQL 問題，我們一般都認(rèn)為這是 DBA 的活，其實我們 CRUD 程序員也應(yīng)該了解一下 SQL 的安全問題。

SQL 注入簡介

SQL 注入就是利用某些數(shù)據(jù)庫的外部接口將用戶數(shù)據(jù)插入到實際的 SQL 中，從而達(dá)到入侵?jǐn)?shù)據(jù)庫的目的。SQL 注入是一種常見的網(wǎng)絡(luò)攻擊的方式，它不是利用操作系統(tǒng)的 BUG 來實現(xiàn)攻擊的。SQL 主要是針對程序員編寫時的疏忽來入侵的。

SQL 注入攻擊有很大的危害，攻擊者可以利用它讀取、修改或者刪除數(shù)據(jù)庫內(nèi)的數(shù)據(jù)，獲取數(shù)據(jù)庫中的用戶名和密碼，甚至獲得數(shù)據(jù)庫管理員的權(quán)限。并且 SQL 注入一般比較難以防范。

SQL Mode

MySQL 可以運行在不同的 SQL Mode 模式下，不同的 SQL Mode 定義了不同的 SQL 語法，數(shù)據(jù)校驗規(guī)則，這樣就能夠在不同的環(huán)境中使用 MySQL ，下面我們就來介紹一下 SQL Mode。

SQL Mode 解決問題

SQL Mode 可以解決下面這幾種問題

通過設(shè)置 SQL Mode，可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗，有效保障數(shù)據(jù)的準(zhǔn)確性。

設(shè)置 SQL Mode 為 ANSI 模式，來保證大多數(shù) SQL 符合標(biāo)準(zhǔn)的 SQL 語法，這樣應(yīng)用在不同數(shù)據(jù)庫的遷移中，不需要對 SQL 進行較大的改變

數(shù)據(jù)在不同數(shù)據(jù)庫的遷移中，通過改變 SQL Mode 能夠更方便的進行遷移。

下面我們就通過示例來演示一下 SQL Mode 用法

我們可以通過

select @@sql_mode;

來查看默認(rèn)的 SQL Mode，如下是我的數(shù)據(jù)庫所支持的 SQL Mode

涉及到很多 SQL Mode，下面是這些 SQL Mode 的解釋

ONLY_FULL_GROUP_BY：這個模式會對 GROUP BY 進行合法性檢查，對于 GROUP BY 操作，如果在SELECT 中的列，沒有在 GROUP BY 中出現(xiàn)，那么將認(rèn)為這個 SQL 是不合法的，因為列不在 GROUP BY 從句中

同樣舉個例子，我們現(xiàn)在查詢一下 cxuan005 的 id 和 info 字段。

select id,info from cxuan005;

這樣是可以運行的

然后我們使用 GROUP BY 字句進行分組，這里只對 info 進行分組，我們看一下會出現(xiàn)什么情況

select id,info from cxuan005 group by info;

我們可以從錯誤原因中看到，這條 SQL 語句是不符合 ONLY_FULL_GROUP_BY 的這條 SQL Mode 的。因為我們只對 info 進行分組了，沒有對 id 進行分組，我們把 SQL 語句改成如下形式

select id,info from cxuan005 group by id,info;

這樣 SQL 就能正確執(zhí)行了。

當(dāng)然，我們也可以刪除 sql_mode = ONLY_FULL_GROUP_BY 的這條 Mode，可以使用

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

來進行刪除，刪除后我們使用分組語句就可以放飛自我了。

select id,info from cxuan005 group by info;

但是這種做法只是暫時的修改，我們可以修改配置文件 my.ini 中的 sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES：這就是嚴(yán)格模式，在這個模式下會對數(shù)據(jù)進行嚴(yán)格的校驗，錯誤數(shù)據(jù)不能插入，報error 錯誤。如果不能將給定的值插入到事務(wù)表中，則放棄該語句。對于非事務(wù)表，如果值出現(xiàn)在單行語句或多行語句的第1行，則放棄該語句。

當(dāng)使用 innodb 存儲引擎表時，考慮使用 innodb_strict_mode 模式的 sql_mode，它能增量額外的錯誤檢測功能。

NO_ZERO_IN_DATE：這個模式影響著日期中的月份和天數(shù)是否可以為 0（注意年份是非 0 的），這個模式也取決于嚴(yán)格模式是否被啟用。如果這個模式未啟用，那么日期中的零部分被允許并且插入沒有警告。如果這個模式啟用，那么日期中的零部分插入被作為 0000-00-00 并且產(chǎn)生一個警告。

這個模式需要注意下，如果啟用的話，需要 STRICT_TRANS_TABLES 和 NO_ZERO_IN_DATE 同時啟用，否則不起作用，也就是

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';

然后我們換表了，使用 cxuan003 這張表，表結(jié)構(gòu)如下

我們主要測試日期的使用，在 cxuan003 中插入一條日期為 0000-00-00 的數(shù)據(jù)

insert into cxuan003 values(111,'study','0000-00-00');

發(fā)現(xiàn)能夠執(zhí)行成功，但是把年月日各自變?yōu)?0 之后再進行插入，則會插入失敗。

insert into cxuan003 values(111,'study','2021-00-00');

insert into cxuan003 values(111,'study','2021-01-00');

這些組合有很多，我這里就不再細(xì)致演示了，讀者可以自行測試。

如果要插入 0000-00-00 這樣的數(shù)據(jù)，必須設(shè)置 NO_ZERO_IN_DATE 和 NO_ZERO_DATE。

ERROR_FOR_DIVISION_BY_ZERO：如果這個模式未啟用，那么零除操作將會插入空值并且不會產(chǎn)生警告；如果這個模式啟用，零除操作插入空值并產(chǎn)生警告；如果這個模式和嚴(yán)格模式都啟用，零除從操作將會產(chǎn)生一個錯誤。

NO_AUTO_CREATE_USER：禁止使用 grant 語句自動創(chuàng)建用戶，除非認(rèn)證信息被指定。

NO_ENGINE_SUBSTITUTION：此模式指定當(dāng)執(zhí)行 create 語句或者 alter 語句指定的存儲引擎沒有啟用或者沒有編譯時，控制默認(rèn)存儲引擎的自動切換。默認(rèn)是啟用狀態(tài)的。

SQL Mode 三種作用域

SQL Mode 按作用區(qū)域和時間可分為 3。個級別，分別是會話級別，全局級別，配置（永久生效）級別。

我們上面使用的 SQL Mode 都是 會話級別，會話級別就是當(dāng)前窗口域有效。它的設(shè)置方式是

set @@session.sql_mode='xx_mode' set session sql_mode='xx_mode'

全局域就是當(dāng)前會話關(guān)閉不失效，但是在 MySQL 重啟后失效。它的設(shè)置方式是

set global sql_mode='xx_mode'; set @@global.sql_mode='xx_mode';

配置域就是在 vi /etc/my.cnf 里面添加

[mysqld] sql-mode = "xx_mode"

配置域在保存退出后，重啟服務(wù)器，即可永久生效。

SQL 正則表達(dá)式

正則表達(dá)式相信大家應(yīng)該都用過，不過你在 MySQL 中用過正則表達(dá)式嗎？下面我們就來聊一聊 SQL 中的正則表達(dá)式。

正則表達(dá)式(Regular Expression) 是指一個用來描述或者匹配字符串的句法規(guī)則。正則表達(dá)式通常用來檢索和替換某個文本中的文本內(nèi)容。很多語言都支持正則表達(dá)式，MySQL 同樣也不例外，MySQL 利用 REGEXP 命令提供給用戶擴展的正則表達(dá)式功能。下面是 MySQL 中正則表達(dá)式的一些規(guī)則。

下面來演示一下正則表達(dá)式的用法

^ 在字符串的開始進行匹配，根據(jù)返回的結(jié)果來判斷是否匹配，1 = 匹配，0 = 不匹配。下面嘗試匹配字符串 aaaabbbccc 是否以字符串 a 為開始

select 'aaaabbbccc' regexp '^a';

同樣的，$ 會在末尾處進行匹配，如下所示

select 'aaaabbbccc' regexp 'c$';

. 匹配單個任意字符

select 'berska' regexp '.s', 'zara' regexp '.a';

[...] 表示匹配括號內(nèi)的任意字符，示例如下

select 'whosyourdaddy' regexp '[abc]';

[^...] 匹配括號內(nèi)不包含的任意字符，和 [...] 是相反的，如果有任何匹配不上，返回 0 ，全部匹配上返回 1。

select 'x' regexp '[^xyz]';

n* 表示匹配零個或者多個 n 字符串，如下

select 'aabbcc' regexp 'd*';

沒有 d 出現(xiàn)也可以返回 1 ，因為 * 表示 0 或者多個。

n+ 表示匹配 1 個或者 n 個字符串

select 'aabbcc' regexp 'd+';

n? 的用法和 n+ 類似，只不過 n? 可以匹配空串

常見 SQL 技巧

RAND() 函數(shù)

大多數(shù)數(shù)據(jù)庫都會提供產(chǎn)生隨機數(shù)的函數(shù)，通過這些函數(shù)可以產(chǎn)生隨機數(shù)，也可以使用從數(shù)據(jù)庫表中抽取隨機產(chǎn)生的記錄，這對統(tǒng)計分析來說很有用。

在 MySQL 中，通常使用 RAND() 函數(shù)來產(chǎn)生隨機數(shù)。RAND() 和 ORDER BY 組合完成數(shù)據(jù)抽取功能，如下所示。

我們新建一張表用于數(shù)據(jù)檢索。

CREATE TABLE `clerk_Info` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `salary` decimal(10,2) DEFAULT NULL, `companyId` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

然后插入一些數(shù)據(jù)，插入完成后的數(shù)據(jù)如下。

然后我們可以使用 RAND() 函數(shù)進行隨機檢索數(shù)據(jù)行

select * from clerk_info order by rand();

檢索完成后的數(shù)據(jù)如下

多次查詢后發(fā)現(xiàn)每次檢索的數(shù)據(jù)順序都是隨機的。

這個函數(shù)多用于隨機抽樣，比如選取一定數(shù)量的樣本在進行隨機排序，需要用到 limit 關(guān)鍵字。

GROUP BY + WITH ROLLUP

我們經(jīng)常使用 GROUP BY 語句，但是你用過 GROUP BY 和 WITH ROLLUP 一起使用的嗎？使用 GROUP BY 和 WITH ROLLUP 字句可以檢索出更多的分組集合信息。

我們?nèi)耘f對 clerk_info 表進行操作，我們對 name 和 salary 進行分組統(tǒng)計工資總數(shù)。

select name,sum(salary) from clerk_info group by name with rollup;

可以看到上面的表按照 name 進行分組，然后再對 money 進行統(tǒng)計。

也就是說 GROUP BY 語句執(zhí)行完成后可以滿足用戶想要的任何一個分組以及分組組合的聚合信息值。

這里需要注意一點，不能同時使用 ORDER BY 字句對結(jié)果進行排序，ROLLUP 和 ORDER BY 是互斥的。

數(shù)據(jù)庫名、表名大小寫問題

在 MySQL 中，數(shù)據(jù)庫中的每個表至少對應(yīng)數(shù)據(jù)庫目錄中的一個文件，當(dāng)然這取決于存儲引擎的實現(xiàn)了。不同的操作系統(tǒng)對大小寫的敏感性決定了數(shù)據(jù)庫和表名的大小寫的敏感性。在 UNIX 操作系統(tǒng)中是對大小寫敏感的，因此數(shù)據(jù)庫名和表名也是具有敏感性的，而到了 Windows 則不存在敏感性問題，因為 Windows 操作系統(tǒng)本身對大小寫不敏感。列、索引、觸發(fā)器在任何平臺上都對大小寫不敏感。

在 MySQL 中，數(shù)據(jù)庫名和表名是由 lower_case_tables_name 系統(tǒng)變量決定的?？梢栽趩?mysqld 時設(shè)置這個系統(tǒng)變量。下面是 lower_case_tables_name 的值。

如果只在一個平臺上使用 MySQL 的話，通常不需要修改 lower_case_tables_name 變量。如果想要在不同系統(tǒng)系統(tǒng)之間遷移表就會涉及到大小寫問題，因為 UNIX 中 clerk_info 和 CLERK_INFO 被認(rèn)為是兩個不同的表，而 Windows 中則認(rèn)為是一個。在 UNIX 中使用 lower_case_tables_name=0， 而在 Windows 中使用lower_case_tables_name=2，這樣可以保留數(shù)據(jù)庫名和表名的大小寫，但是不能保證所有的 SQL 查詢中使用的表名和數(shù)據(jù)庫名的大小寫相同。如果 SQL 語句中沒有正確引用數(shù)據(jù)庫名和表名的大小寫，那么雖然在 Windows 中能正確執(zhí)行，但是如果將查詢轉(zhuǎn)移到 UNIX 中，大小寫不正確，將會導(dǎo)致查詢失敗。

外鍵問題

這里需要注意一個問題，InnoDB 存儲引擎是支持外鍵的，而 MyISAM 存儲引擎是不支持外鍵的，因此在 MyISAM 中設(shè)置外鍵會不起作用。

MySQL 常用函數(shù)

下面我們來了解一下 MySQL 函數(shù)，MySQL 函數(shù)也是我們?nèi)粘ｉ_發(fā)過程中經(jīng)常使用的，選用合適的函數(shù)能夠提高我們的開發(fā)效率，下面我們就來一起認(rèn)識一下這些函數(shù)

字符串函數(shù)

字符串函數(shù)是最常用的一種函數(shù)了，MySQL 也是支持很多種字符串函數(shù)，下面是 MySQL 支持的字符串函數(shù)表

下面通過具體的示例演示一下每個函數(shù)的用法

LOWER(str) 和 UPPER(str) 函數(shù)：用于轉(zhuǎn)換大小寫

CONCAT(s1,s2 ... sn) ：把傳入的參數(shù)拼接成一個字符串

上面把 c xu an 拼接成為了一個字符串，另外需要注意一點，任何和 NULL 進行字符串拼接的結(jié)果都是 NULL。

LEFT(str,x) 和 RIGHT(str,x) 函數(shù)：分別返回字符串最左邊的 x 個字符和最右邊的 x 個字符。如果第二個參數(shù)是 NULL，那么將不會返回任何字符串

INSERT(str,x,y,instr) ：將字符串 str 從指定 x 的位置開始， 取 y 個長度的字串替換為 instr。

LTRIM(str) 和 RTRIM(str) 分別表示去掉字符串 str 左側(cè)和右側(cè)的空格

REPEAT(str,x) 函數(shù)：返回 str 重復(fù) x 次的結(jié)果

TRIM(str) 函數(shù)：用于去掉目標(biāo)字符串的空格

SUBSTRING(str,x,y) 函數(shù)：返回從字符串 str 中第 x 位置起 y 個字符長度的字符串

LPAD(str,n,pad) 和 RPAD(str,n,pad) 函數(shù)：用字符串 pad 對 str 左邊和右邊進行填充，直到長度為 n 個字符長度

STRCMP(s1,s2) 用于比較字符串 s1 和 s2 的 ASCII 值大小。如果 s1 < s2，則返回 -1；如果 s1 = s2 ，返回 0 ；如果 s1 > s2 ，返回 1。

REPLACE(str,a,b) : 用字符串 b 替換字符串 str 種所有出現(xiàn)的字符串 a

數(shù)值函數(shù)

MySQL 支持?jǐn)?shù)值函數(shù)，這些函數(shù)能夠處理很多數(shù)值運算。下面我們一起來學(xué)習(xí)一下 MySQL 中的數(shù)值函數(shù)，下面是所有的數(shù)值函數(shù)

下面我們還是以實踐為主來聊一聊這些用法

ABS(x) 函數(shù)：返回 x 的絕對值

CEIL(x) 函數(shù)：返回大于 x 的整數(shù)

MOD(x,y)，對 x 和 y 進行取模操作

ROUND(x,y) 返回 x 四舍五入后保留 y 位小數(shù)的值；如果是整數(shù)，那么 y 位就是 0 ；如果不指定 y ，那么 y 默認(rèn)也是 0 。

FLOOR(x) : 返回小于 x 的最大整數(shù)，用法與 CEIL 相反

TRUNCATE(x,y): 返回數(shù)字 x 截斷為 y 位小數(shù)的結(jié)果， TRUNCATE 知識截斷，并不是四舍五入。

RAND() ：返回 0 到 1 的隨機值

日期和時間函數(shù)

日期和時間函數(shù)也是 MySQL 中非常重要的一部分，下面我們就來一起認(rèn)識一下這些函數(shù)

下面結(jié)合示例來講解一下每個函數(shù)的使用

NOW(): 返回當(dāng)前的日期和時間

WEEK(DATE) 和 YEAR(DATE) ：前者返回的是一年中的第幾周，后者返回的是給定日期的哪一年

HOUR(time) 和 MINUTE(time) : 返回給定時間的小時，后者返回給定時間的分鐘

MONTHNAME(date) 函數(shù)：返回 date 的英文月份

CURDATE() 函數(shù)：返回當(dāng)前日期，只包含年月日

CURTIME() 函數(shù)：返回當(dāng)前時間，只包含時分秒

UNIX_TIMESTAMP(date) : 返回 UNIX 的時間戳

FROM_UNIXTIME(date) : 返回 UNIXTIME 時間戳的日期值，和 UNIX_TIMESTAMP 相反

DATE_FORMAT(date,fmt) 函數(shù)：按照字符串 fmt 對 date 進行格式化，格式化后按照指定日期格式顯示

具體的日期格式可以參考這篇文章 blog.csdn.net/weixin_3870…

我們演示一下將當(dāng)前日期顯示為年月日的這種形式，使用的日期格式是%M %D %Y。

DATE_ADD(date, interval, expr type) 函數(shù)：返回與所給日期 date 相差 interval 時間段的日期

interval 表示間隔類型的關(guān)鍵字，expr 是表達(dá)式，這個表達(dá)式對應(yīng)后面的類型，type 是間隔類型，MySQL 提供了 13 種時間間隔類型

DATE_DIFF(date1, date2) 用來計算兩個日期之間相差的天數(shù)

查看離 2021 - 01 - 01 還有多少天

流程函數(shù)

流程函數(shù)也是很常用的一類函數(shù)，用戶可以使用這類函數(shù)在 SQL 中實現(xiàn)條件選擇。這樣做能夠提高查詢效率。下表列出了這些流程函數(shù)

其他函數(shù)

除了我們介紹過的字符串函數(shù)、日期和時間函數(shù)、流程函數(shù)，還有一些函數(shù)并不屬于上面三類函數(shù)，它們是

下面來看一下具體的使用

VERSION: 返回當(dāng)前數(shù)據(jù)庫版本

DATABASE: 返回當(dāng)前的數(shù)據(jù)庫名

USER : 返回當(dāng)前登錄用戶名

PASSWORD(str) : 返回字符串的加密版本，例如

MD5(str) 函數(shù)：返回字符串 str 的 MD5 值

INET_ATON(IP): 返回 IP 的網(wǎng)絡(luò)字節(jié)序列

INET_NTOA(num)函數(shù)：返回網(wǎng)絡(luò)字節(jié)序列代表的 IP 地址，與 INET_ATON 相對

總結(jié)

這篇文章我?guī)闶职咽謹(jǐn)]了一波 MySQL 的高級內(nèi)容，其實說高級也不一定真的高級或者說難，其實就是區(qū)分不同梯度的東西。

原文標(biāo)題：炸裂！MySQL 82 張圖帶你飛

文章出處：【微信公眾號：Linux愛好者】歡迎添加關(guān)注！文章轉(zhuǎn)載請注明出處。

責(zé)任編輯：haq