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

其他文章：

138 張圖帶你 MySQL 入門(mén)

47 張圖帶你 MySQL 進(jìn)階！！！

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

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

我們知道，MyISAM 和 MEMORY 存儲(chǔ)引擎支持表級(jí)鎖定(table-level locking)，InnoDB 存儲(chǔ)引擎支持行級(jí)鎖定(row-level locking)，BDB 存儲(chǔ)引擎支持頁(yè)級(jí)鎖定(page-level locking)。各個(gè)鎖定級(jí)別的特點(diǎn)如下

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

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

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

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

鎖定語(yǔ)句

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

lock table cxuan005 read;

我們鎖定了 cxuan005 的 read 鎖，然后這時(shí)我們?cè)龠M(jìn)行一次查詢，看看是否能夠執(zhí)行這條語(yǔ)句

select * from cxuan005 where id = 111;

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

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

select * from cxuan005;

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

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

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

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

解鎖語(yǔ)句

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

unlock tables;

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

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

select * from cxuan005 where id = 111;

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

事務(wù)控制

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

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

自動(dòng)提交

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

show variables like 'autocommit';

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

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

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

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

set autocommit = 0;

然后我們?cè)倏匆幌伦詣?dòng)提交是否關(guān)閉了，再次執(zhí)行一下 show variables like 'autocommit' 語(yǔ)句

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

set autocommit = 1;

會(huì)再次開(kāi)啟自動(dòng)提交。

這里注意一下特殊操作。

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

不過(guò)，常用的 select、insert、update 和 delete命令，都不會(huì)強(qiáng)制提交事務(wù)。

手動(dòng)提交

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

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

start transaction; ... # 一條或者多條語(yǔ)句 commit;

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

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

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

start transaction;

然后執(zhí)行

select * from cxuan005;

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

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

update cxuan005 set info='cxuan';

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

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

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

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

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

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

然后再次查詢

select * from cxuan005;

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

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

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

這里切忌一點(diǎn)：delete 刪除語(yǔ)句一定要加 where ，不加 where 語(yǔ)句的刪除就是耍流氓。

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

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

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

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

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

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

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

我們通過(guò)一個(gè)示例來(lái)進(jìn)行模擬不同的 SAVEPOINT

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

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

查詢之后的記錄如下

然后我們定義一個(gè) SAVEPOINT，如下所示

savepoint test;

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

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

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

select * from cxuan005 where id = 777;

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

rollback to savepoint test;

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

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

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

SQL 安全問(wèn)題

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

SQL 注入簡(jiǎn)介

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

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

SQL Mode

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

SQL Mode 解決問(wèn)題

SQL Mode 可以解決下面這幾種問(wèn)題

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

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

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

下面我們就通過(guò)示例來(lái)演示一下 SQL Mode 用法

我們可以通過(guò)

select @@sql_mode;

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

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

ONLY_FULL_GROUP_BY：這個(gè)模式會(huì)對(duì) GROUP BY 進(jìn)行合法性檢查，對(duì)于 GROUP BY 操作，如果在SELECT 中的列，沒(méi)有在 GROUP BY 中出現(xiàn)，那么將認(rèn)為這個(gè) SQL 是不合法的，因?yàn)榱胁辉?GROUP BY 從句中

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

select id,info from cxuan005;

這樣是可以運(yùn)行的

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

select id,info from cxuan005 group by info;

我們可以從錯(cuò)誤原因中看到，這條 SQL 語(yǔ)句是不符合 ONLY_FULL_GROUP_BY 的這條 SQL Mode 的。因?yàn)槲覀冎粚?duì) info 進(jìn)行分組了，沒(méi)有對(duì) id 進(jìn)行分組，我們把 SQL 語(yǔ)句改成如下形式

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',''));

來(lái)進(jìn)行刪除，刪除后我們使用分組語(yǔ)句就可以放飛自我了。

select id,info from cxuan005 group by info;

但是這種做法只是暫時(shí)的修改，我們可以修改配置文件 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)格模式，在這個(gè)模式下會(huì)對(duì)數(shù)據(jù)進(jìn)行嚴(yán)格的校驗(yàn)，錯(cuò)誤數(shù)據(jù)不能插入，報(bào)error 錯(cuò)誤。如果不能將給定的值插入到事務(wù)表中，則放棄該語(yǔ)句。對(duì)于非事務(wù)表，如果值出現(xiàn)在單行語(yǔ)句或多行語(yǔ)句的第1行，則放棄該語(yǔ)句。

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

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

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

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';

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

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

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

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

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

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

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

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

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

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

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

SQL Mode 三種作用域

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

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

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

全局域就是當(dāng)前會(huì)話關(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)該都用過(guò)，不過(guò)你在 MySQL 中用過(guò)正則表達(dá)式嗎？下面我們就來(lái)聊一聊 SQL 中的正則表達(dá)式。

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

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

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

select 'aaaabbbccc' regexp '^a';

同樣的，$ 會(huì)在末尾處進(jìn)行匹配，如下所示

select 'aaaabbbccc' regexp 'c$';

. 匹配單個(gè)任意字符

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

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

select 'whosyourdaddy' regexp '[abc]';

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

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

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

select 'aabbcc' regexp 'd*';

沒(méi)有 d 出現(xiàn)也可以返回 1 ，因?yàn)?* 表示 0 或者多個(gè)。

n+ 表示匹配 1 個(gè)或者 n 個(gè)字符串

select 'aabbcc' regexp 'd+';

n? 的用法和 n+ 類(lèi)似，只不過(guò) n? 可以匹配空串

常見(jiàn) SQL 技巧

RAND() 函數(shù)

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

在 MySQL 中，通常使用 RAND() 函數(shù)來(lái)產(chǎn)生隨機(jī)數(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ù)進(jìn)行隨機(jī)檢索數(shù)據(jù)行

select * from clerk_info order by rand();

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

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

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

GROUP BY + WITH ROLLUP

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

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

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

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

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

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

數(shù)據(jù)庫(kù)名、表名大小寫(xiě)問(wèn)題

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

在 MySQL 中，數(shù)據(jù)庫(kù)名和表名是由 lower_case_tables_name 系統(tǒng)變量決定的。可以在啟動(dòng) mysqld 時(shí)設(shè)置這個(gè)系統(tǒng)變量。下面是 lower_case_tables_name 的值。

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

外鍵問(wèn)題

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

MySQL 常用函數(shù)

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

字符串函數(shù)

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

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

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

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

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

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

INSERT(str,x,y,instr) ：將字符串 str 從指定 x 的位置開(kāi)始， 取 y 個(gè)長(zhǎng)度的字串替換為 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 個(gè)字符長(zhǎng)度的字符串

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

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ù)值運(yùn)算。下面我們一起來(lái)學(xué)習(xí)一下 MySQL 中的數(shù)值函數(shù)，下面是所有的數(shù)值函數(shù)

下面我們還是以實(shí)踐為主來(lái)聊一聊這些用法

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

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

MOD(x,y)，對(duì) x 和 y 進(jìn)行取模操作

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

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

TRUNCATE(x,y): 返回?cái)?shù)字 x 截?cái)酁?y 位小數(shù)的結(jié)果， TRUNCATE 知識(shí)截?cái)啵⒉皇撬纳嵛迦搿?/p>

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

日期和時(shí)間函數(shù)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

DATE_DIFF(date1, date2) 用來(lái)計(jì)算兩個(gè)日期之間相差的天數(shù)

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

流程函數(shù)

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

其他函數(shù)

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

下面來(lái)看一下具體的使用

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

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

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 相對(duì)

總結(jié)

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

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

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

責(zé)任編輯：haq