前言

我最近幾年用MYSQL數(shù)據(jù)庫(kù)挺多的，發(fā)現(xiàn)了一些非常有用的小玩意，今天拿出來(lái)分享到大家，希望對(duì)你會(huì)有所幫助。

1.group_concat

在我們平常的工作中，使用group by進(jìn)行分組的場(chǎng)景，是非常多的。

比如想統(tǒng)計(jì)出用戶表中，名稱不同的用戶的具體名稱有哪些？

具體sql如下：

selectnamefrom`user`
groupbyname;

但如果想把name相同的code拼接在一起，放到另外一列中該怎么辦呢？

答：使用group_concat函數(shù)。

例如：

selectname,group_concat(code)from`user`
groupbyname;

執(zhí)行結(jié)果：



使用group_concat函數(shù)，可以輕松的把分組后，name相同的數(shù)據(jù)拼接到一起，組成一個(gè)字符串，用逗號(hào)分隔。

2.char_length

有時(shí)候我們需要獲取字符的長(zhǎng)度，然后根據(jù)字符的長(zhǎng)度進(jìn)行排序。

MYSQL給我們提供了一些有用的函數(shù)，比如：char_length。

通過(guò)該函數(shù)就能獲取字符長(zhǎng)度。

獲取字符長(zhǎng)度并且排序的sql如下：

select*frombrandwherenamelike'%蘇三%'
orderbychar_length(name)asclimit5;

執(zhí)行效果如圖所示：



name字段使用關(guān)鍵字模糊查詢之后，再使用char_length函數(shù)獲取name字段的字符長(zhǎng)度，然后按長(zhǎng)度升序。

3.locate

有時(shí)候我們?cè)诓檎夷硞€(gè)關(guān)鍵字，比如：蘇三，需要明確知道它在某個(gè)字符串中的位置時(shí)，該怎么辦呢？

答：使用locate函數(shù)。

使用locate函數(shù)改造之后sql如下：

select*frombrandwherenamelike'%蘇三%'
orderbychar_length(name)asc,locate('蘇三',name)asclimit5,5;

執(zhí)行結(jié)果：



先按長(zhǎng)度排序，小的排在前面。如果長(zhǎng)度相同，則按關(guān)鍵字從左到右進(jìn)行排序，越靠左的越排在前面。

除此之外，我們還可以使用：instr和position函數(shù)，它們的功能跟locate函數(shù)類似。

4.replace

我們經(jīng)常會(huì)有替換字符串中部分內(nèi)容的需求，比如：將字符串中的字符A替換成B。

這種情況就能使用replace函數(shù)。

例如：

updatebrandsetname=REPLACE(name,'A','B')
whereid=1;

這樣就能輕松實(shí)現(xiàn)字符替換功能。

也能用該函數(shù)去掉前后空格：

updatebrandsetname=REPLACE(name,'','')wherenamelike'%';
updatebrandsetname=REPLACE(name,'','')wherenamelike'%';

使用該函數(shù)還能替換json格式的數(shù)據(jù)內(nèi)容，真的非常有用。

5.now

時(shí)間是個(gè)好東西，用它可以快速縮小數(shù)據(jù)范圍，我們經(jīng)常有獲取當(dāng)前時(shí)間的需求。

在MYSQL中獲取當(dāng)前時(shí)間，可以使用now()函數(shù)，例如：

selectnow()frombrandlimit1;

返回結(jié)果為下面這樣的：



它會(huì)包含年月日時(shí)分秒。

如果你還想返回毫秒，可以使用now(3)，例如：

selectnow(3)frombrandlimit1;

返回結(jié)果為下面這樣的：



使用起來(lái)非常方便好記。

6.insert into ... select

在工作中很多時(shí)候需要插入數(shù)據(jù)。

傳統(tǒng)的插入數(shù)據(jù)的sql是這樣的：

INSERTINTO`brand`(`id`,`code`,`name`,`edit_date`)
VALUES(5,'108','蘇三','2022-09-021921');

它主要是用于插入少量并且已經(jīng)確定的數(shù)據(jù)。但如果有大批量的數(shù)據(jù)需要插入，特別是是需要插入的數(shù)據(jù)來(lái)源于，另外一張表或者多張表的結(jié)果集中。

這種情況下，使用傳統(tǒng)的插入數(shù)據(jù)的方式，就有點(diǎn)束手無(wú)策了。

這時(shí)候就能使用MYSQL提供的：insert into ... select語(yǔ)法。

例如：

INSERTINTO`brand`(`id`,`code`,`name`,`edit_date`)
selectnull,code,name,now(3)from`order`wherecodein('004','005');

這樣就能將order表中的部分?jǐn)?shù)據(jù)，非常輕松插入到brand表中。

7.insert into ... ignore

不知道你有沒(méi)有遇到過(guò)這樣的場(chǎng)景：在插入1000個(gè)品牌之前，需要先根據(jù)name，判斷一下是否存在。如果存在，則不插入數(shù)據(jù)。如果不存在，才需要插入數(shù)據(jù)。

如果直接這樣插入數(shù)據(jù)：

INSERTINTO`brand`(`id`,`code`,`name`,`edit_date`)
VALUES(123,'108','蘇三',now(3));

肯定不行，因?yàn)閎rand表的name字段創(chuàng)建了唯一索引，同時(shí)該表中已經(jīng)有一條name等于蘇三的數(shù)據(jù)了。

執(zhí)行之后直接報(bào)錯(cuò)了：



這就需要在插入之前加一下判斷。

當(dāng)然很多人通過(guò)在sql語(yǔ)句后面拼接not exists語(yǔ)句，也能達(dá)到防止出現(xiàn)重復(fù)數(shù)據(jù)的目的，比如：

INSERTINTO`brand`(`id`,`code`,`name`,`edit_date`)
selectnull,'108','蘇三',now(3)
fromdualwherenotexists(select*from`brand`wherename='蘇三');

這條sql確實(shí)能夠滿足要求，但是總覺(jué)得有些麻煩。那么，有沒(méi)有更簡(jiǎn)單的做法呢？

答：可以使用insert into ... ignore語(yǔ)法。

例如：

INSERTignoreINTO`brand`(`id`,`code`,`name`,`edit_date`)
VALUES(123,'108','蘇三',now(3));

這樣改造之后，如果brand表中沒(méi)有name為蘇三的數(shù)據(jù)，則可以直接插入成功。

但如果brand表中已經(jīng)存在name為蘇三的數(shù)據(jù)了，則該sql語(yǔ)句也能正常執(zhí)行，并不會(huì)報(bào)錯(cuò)。因?yàn)樗鼤?huì)忽略異常，返回的執(zhí)行結(jié)果影響行數(shù)為0，它不會(huì)重復(fù)插入數(shù)據(jù)。

8.select ... for update

MYSQL數(shù)據(jù)庫(kù)自帶了悲觀鎖，它是一種排它鎖，根據(jù)鎖的粒度從大到小分為：表鎖、間隙鎖和行鎖。

在我們的實(shí)際業(yè)務(wù)場(chǎng)景中，有些情況并發(fā)量不太高，為了保證數(shù)據(jù)的正確性，使用悲觀鎖也可以。

比如：用戶扣減積分，用戶的操作并不集中。但也要考慮系統(tǒng)自動(dòng)贈(zèng)送積分的并發(fā)情況，所以有必要加悲觀鎖限制一下，防止出現(xiàn)積分加錯(cuò)的情況發(fā)生。

這時(shí)候就可以使用MYSQL中的select ... for update語(yǔ)法了。

例如：

begin;
select*from`user`whereid=1
forupdate;

//業(yè)務(wù)邏輯處理

update`user`setscore=score-1whereid=1;
commit;

這樣在一個(gè)事務(wù)中使用for update鎖住一行記錄，其他事務(wù)就不能在該事務(wù)提交之前，去更新那一行的數(shù)據(jù)。

需要注意的是for update前的id條件，必須是表的主鍵或者唯一索引，不然行鎖可能會(huì)失效，有可能變成表鎖。

9.on duplicate key update

通常情況下，我們?cè)诓迦霐?shù)據(jù)之前，一般會(huì)先查詢一下，該數(shù)據(jù)是否存在。如果不存在，則插入數(shù)據(jù)。如果已存在，則不插入數(shù)據(jù)，而直接返回結(jié)果。

在沒(méi)啥并發(fā)量的場(chǎng)景中，這種做法是沒(méi)有什么問(wèn)題的。但如果插入數(shù)據(jù)的請(qǐng)求，有一定的并發(fā)量，這種做法就可能會(huì)產(chǎn)生重復(fù)的數(shù)據(jù)。

當(dāng)然防止重復(fù)數(shù)據(jù)的做法很多，比如：加唯一索引、加分布式鎖等。

但這些方案，都沒(méi)法做到讓第二次請(qǐng)求也更新數(shù)據(jù)，它們一般會(huì)判斷已經(jīng)存在就直接返回了。

這種情況可以使用on duplicate key update語(yǔ)法。

該語(yǔ)法會(huì)在插入數(shù)據(jù)之前判斷，如果主鍵或唯一索引不存在，則插入數(shù)據(jù)。如果主鍵或唯一索引存在，則執(zhí)行更新操作。

具體需要更新的字段可以指定，例如：

INSERTINTO`brand`(`id`,`code`,`name`,`edit_date`)
VALUES(123,'108','蘇三',now(3))
onduplicatekeyupdatename='蘇三',edit_date=now(3);

這樣一條語(yǔ)句就能輕松搞定需求，既不會(huì)產(chǎn)生重復(fù)數(shù)據(jù)，也能更新最新的數(shù)據(jù)。

但需要注意的是，在高并發(fā)的場(chǎng)景下使用on duplicate key update語(yǔ)法，可能會(huì)存在死鎖的問(wèn)題，所以要根據(jù)實(shí)際情況酌情使用。

10.show create table

有時(shí)候，我們想快速查看某張表的字段情況，通常會(huì)使用desc命令，比如：

desc`order`;

結(jié)果如圖所示：



確實(shí)能夠看到order表中的字段名稱、字段類型、字段長(zhǎng)度、是否允許為空，是否主鍵、默認(rèn)值等信息。

但看不到該表的索引信息，如果想看創(chuàng)建了哪些索引，該怎么辦呢？

答：使用show index命令。

比如：

showindexfrom`order`;

也能查出該表所有的索引：



但查看字段和索引數(shù)據(jù)呈現(xiàn)方式，總覺(jué)得有點(diǎn)怪怪的，有沒(méi)有一種更直觀的方式？

答：這就需要使用show create table命令了。

例如：

showcreatetable`order`;

執(zhí)行結(jié)果如圖所示：



其中Table表示表名，Create Table就是我們需要看的建表信息，將數(shù)據(jù)展開(kāi)：



我們能夠看到非常完整的建表語(yǔ)句，表名、字段名、字段類型、字段長(zhǎng)度、字符集、主鍵、索引、執(zhí)行引擎等都能看到。

非常直接明了。

11.create table ... select

有時(shí)候，我們需要快速備份表。

通常情況下，可以分兩步走：

創(chuàng)建一張臨時(shí)表

將數(shù)據(jù)插入臨時(shí)表

創(chuàng)建臨時(shí)表可以使用命令：

createtableorder_2022121819like`order`;

創(chuàng)建成功之后，就會(huì)生成一張名稱叫：order_2022121819，表結(jié)構(gòu)跟order一模一樣的新表，只是該表的數(shù)據(jù)為空而已。

接下來(lái)使用命令：

insertintoorder_2022121819select*from`order`;

執(zhí)行之后就會(huì)將order表的數(shù)據(jù)插入到order_2022121819表中，也就是實(shí)現(xiàn)數(shù)據(jù)備份的功能。

但有沒(méi)有命令，一個(gè)命令就能實(shí)現(xiàn)上面這兩步的功能呢？

答：用create table ... select命令。

例如：

createtableorder_2022121820
select*from`order`;

執(zhí)行完之后，就會(huì)將order_2022121820表創(chuàng)建好，并且將order表中的數(shù)據(jù)自動(dòng)插入到新創(chuàng)建的order_2022121820中。

一個(gè)命令就能輕松搞定表備份。

12.explain

很多時(shí)候，我們優(yōu)化一條sql語(yǔ)句的性能，需要查看索引執(zhí)行情況。

答：可以使用explain命令，查看mysql的執(zhí)行計(jì)劃，它會(huì)顯示索引的使用情況。

例如：

explainselect*from`order`wherecode='002';

結(jié)果：

通過(guò)這幾列可以判斷索引使用情況，執(zhí)行計(jì)劃包含列的含義如下圖所示：

說(shuō)實(shí)話，sql語(yǔ)句沒(méi)有走索引，排除沒(méi)有建索引之外，最大的可能性是索引失效了。

下面說(shuō)說(shuō)索引失效的常見(jiàn)原因：



如果不是上面的這些原因，則需要再進(jìn)一步排查一下其他原因。

13.show processlist

有些時(shí)候我們線上sql或者數(shù)據(jù)庫(kù)出現(xiàn)了問(wèn)題。比如出現(xiàn)了數(shù)據(jù)庫(kù)連接過(guò)多問(wèn)題，或者發(fā)現(xiàn)有一條sql語(yǔ)句的執(zhí)行時(shí)間特別長(zhǎng)。

這時(shí)候該怎么辦呢？

答：我們可以使用show processlist命令查看當(dāng)前線程執(zhí)行情況。

如圖所示：



從執(zhí)行結(jié)果中，我們可以查看當(dāng)前的連接狀態(tài)，幫助識(shí)別出有問(wèn)題的查詢語(yǔ)句。

id 線程id

User 執(zhí)行sql的賬號(hào)

Host 執(zhí)行sql的數(shù)據(jù)庫(kù)的ip和端號(hào)

db 數(shù)據(jù)庫(kù)名稱

Command 執(zhí)行命令，包括：Daemon、Query、Sleep等。

Time 執(zhí)行sql所消耗的時(shí)間

State 執(zhí)行狀態(tài)

info 執(zhí)行信息，里面可能包含sql信息。

如果發(fā)現(xiàn)了異常的sql語(yǔ)句，可以直接kill掉，確保數(shù)據(jù)庫(kù)不會(huì)出現(xiàn)嚴(yán)重的問(wèn)題。

14.mysqldump

有時(shí)候我們需要導(dǎo)出MYSQL表中的數(shù)據(jù)。

這種情況就可以使用mysqldump工具，該工具會(huì)將數(shù)據(jù)查出來(lái)，轉(zhuǎn)換成insert語(yǔ)句，寫(xiě)入到某個(gè)文件中，相當(dāng)于數(shù)據(jù)備份。

我們獲取到該文件，然后執(zhí)行相應(yīng)的insert語(yǔ)句，就能創(chuàng)建相關(guān)的表，并且寫(xiě)入數(shù)據(jù)了，這就相當(dāng)于數(shù)據(jù)還原。

mysqldump命令的語(yǔ)法為：mysqldump -h主機(jī)名 -P端口 -u用戶名 -p密碼 參數(shù)1,參數(shù)2.... > 文件名稱.sql

備份遠(yuǎn)程數(shù)據(jù)庫(kù)中的數(shù)據(jù)庫(kù)：

mysqldump-h192.22.25.226-uroot-p123456dbname>backup.sql

審核編輯：劉清