👉 這是一個或許對你有用的社群
《專案實戰（影片）》：從書中學，往事上“練” 《網際網路高頻面試題》：面朝簡歷學習，春暖花開 《架構 x 系統設計》：摧枯拉朽，掌控面試高頻場景題 《精進 Java 學習指南》：系統學習，網際網路主流技術棧 《必讀 Java 原始碼專欄》：知其然，知其所以然
👉這是一個或許對你有用的開源專案國產 Star 破 10w+ 的開源專案，前端包括管理後臺 + 微信小程式，後端支援單體和微服務架構。功能涵蓋 RBAC 許可權、SaaS 多租戶、資料許可權、商城、支付、工作流、大屏報表、微信公眾號、ERP、CRM、AI 大模型等等功能：
Boot 多模組架構：https://gitee.com/zhijiantianya/ruoyi-vue-pro Cloud 微服務架構：https://gitee.com/zhijiantianya/yudao-cloud 影片教程：https://doc.iocoder.cn 【國內首批】支援 JDK 17/21 + SpringBoot 3.3、JDK 8/11 + Spring Boot 2.7 雙版本
-
前言 -
1.group_concat -
2.char_length -
3.locate -
4.replace -
5.now -
6.insert into … select -
7.insert into … ignore -
8.select … for update -
9.on duplicate key update -
10.show create table -
11.create table … select -
12.explain -
13.show processlist -
14.mysqldump
前言
我最近幾年用
MYSQL資料庫挺多的，發現了一些非常有用的小玩意，今天拿出來分享到大家，希望對你會有所幫助。基於 Spring Boot + MyBatis Plus + Vue & Element 實現的後臺管理系統 + 使用者小程式，支援 RBAC 動態許可權、多租戶、資料許可權、工作流、三方登入、支付、簡訊、商城等功能
專案地址：https://github.com/YunaiV/ruoyi-vue-pro 影片教程：https://doc.iocoder.cn/video/
1.group_concat
在我們平常的工作中，使用
group by進行分組的場景，是非常多的。比如想統計出使用者表中，名稱不同的使用者的具體名稱有哪些？
具體sql如下：
selectnamefrom`user`
groupbyname
;
但如果想把name相同的code拼接在一起，放到另外一列中該怎麼辦呢？
答：使用
group_concat函式。例如：
selectname
,
group_concat
(code)
from`user`
groupbyname
;
執行結果：
使用
group_concat函式，可以輕鬆的把分組後，name相同的資料拼接到一起，組成一個字串，用逗號分隔。基於 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實現的後臺管理系統 + 使用者小程式，支援 RBAC 動態許可權、多租戶、資料許可權、工作流、三方登入、支付、簡訊、商城等功能
專案地址：https://github.com/YunaiV/yudao-cloud 影片教程：https://doc.iocoder.cn/video/
2.char_length
有時候我們需要獲取字元的
長度，然後根據字元的長度進行排序。MYSQL給我們提供了一些有用的函式，比如：
char_length。透過該函式就能獲取字元長度。
獲取字元長度並且排序的sql如下：
select
*
from
brand
wherenamelike'%蘇三%'
orderbychar_length
(
name
)
asclimit5
;
執行效果如圖所示：
name欄位使用關鍵字
模糊查詢之後，再使用char_length函式獲取name欄位的字元長度，然後按長度升序。3.locate
有時候我們在查詢某個關鍵字，比如：
蘇三，需要明確知道它在某個字串中的位置時，該怎麼辦呢？答：使用
locate函式。使用locate函式改造之後sql如下：
select
*
from
brand
wherenamelike'%蘇三%'
orderbychar_length
(
name
)
asc
,
locate
(
'蘇三'
,
name
)
asclimit5
,
5
;
執行結果：
先按長度排序，小的排在前面。如果長度相同，則按關鍵字從左到右進行排序，越靠左的越排在前面。
除此之外，我們還可以使用：
instr和position函式，它們的功能跟locate函式類似，在這裡我就不一一介紹了，感興趣的小夥伴可以找我私聊。4.replace
我們經常會有替換字串中部分內容的需求，比如：將字串中的字元A替換成B。
這種情況就能使用
replace函式。例如：
update
brand
setname
=
REPLACE
(
name
,
'A'
,
'B'
)
whereid
=
1
;
這樣就能輕鬆實現字元替換功能。
也能用該函式去掉
前後空格：update
brand
setname
=
REPLACE
(
name
,
' '
,
''
)
wherenamelike' %'
;
update
brand
setname
=
REPLACE
(
name
,
' '
,
''
)
wherenamelike'% '
;
使用該函式還能替換
json格式的資料內容，真的非常有用。5.now
時間是個好東西，用它可以快速縮小資料範圍，我們經常有獲取當前時間的需求。
在MYSQL中獲取
當前時間，可以使用now()函式，例如：selectnow
()
from
brand
limit1
;
返回結果為下面這樣的：
它會包含
年月日時分秒。如果你還想返回
毫秒，可以使用now(3)，例如：selectnow
(
3
)
from
brand
limit1
;
返回結果為下面這樣的：
使用起來非常方便好記。
6.insert into … select
在工作中很多時候需要
插入資料。傳統的插入資料的sql是這樣的：
INSERTINTO`brand`
(
`id`
,
`code`
,
`name`
,
`edit_date`
)
VALUES
(
5
,
'108'
,
'蘇三'
,
'2022-09-02 19:42:21'
);
它主要是用於插入少量並且已經確定的資料。但如果有大批次的資料需要插入，特別是是需要插入的資料來源於，另外一張表或者多張表的結果集中。
這種情況下，使用傳統的插入資料的方式，就有點束手無策了。
這時候就能使用MYSQL提供的：
insert into ... select語法。例如：
INSERTINTO`brand`
(
`id`
,
`code`
,
`name`
,
`edit_date`
)
selectnull
,code,
name
,
now
(
3
)
from`order`where
code
in
(
'004'
,
'005'
);
這樣就能將order表中的部分資料，非常輕鬆插入到brand表中。
7.insert into … ignore
不知道你有沒有遇到過這樣的場景：在插入1000個品牌之前，需要先根據name，判斷一下是否存在。如果存在，則不插入資料。如果不存在，才需要插入資料。
如果直接這樣插入資料：
INSERTINTO`brand`
(
`id`
,
`code`
,
`name`
,
`edit_date`
)
VALUES
(
123
,
'108'
,
'蘇三'
,
now
(
3
));
肯定不行，因為brand表的name欄位建立了唯一索引，同時該表中已經有一條name等於蘇三的資料了。
執行之後直接報錯了：
這就需要在插入之前加一下判斷。
當然很多人透過在sql語句後面拼接
not exists語句，也能達到防止出現重複資料的目的，比如：INSERTINTO`brand`
(
`id`
,
`code`
,
`name`
,
`edit_date`
)
selectnull
,
'108'
,
'蘇三'
,
now
(
3
)
from
dual
wherenotexists
(
select
*
from`brand`wherename
=
'蘇三'
);
這條sql確實能夠滿足要求，但是總覺得有些麻煩。那麼，有沒有更簡單的做法呢？
答：可以使用
insert into ... ignore語法。例如：
INSERTignoreINTO`brand`
(
`id`
,
`code`
,
`name`
,
`edit_date`
)
VALUES
(
123
,
'108'
,
'蘇三'
,
now
(
3
));
這樣改造之後，如果brand表中沒有name為蘇三的資料，則可以直接插入成功。
但如果brand表中已經存在name為蘇三的資料了，則該sql語句也能正常執行，並不會報錯。因為它會忽略異常，返回的執行結果影響行數為0，它不會重複插入資料。
8.select … for update
MYSQL資料庫自帶了
悲觀鎖，它是一種排它鎖，根據鎖的粒度從大到小分為：表鎖、間隙鎖和行鎖。在我們的實際業務場景中，有些情況併發量不太高，為了保證資料的正確性，使用悲觀鎖也可以。
比如：使用者扣減積分，使用者的操作並不集中。但也要考慮系統自動贈送積分的併發情況，所以有必要加悲觀鎖限制一下，防止出現積分加錯的情況發生。
這時候就可以使用MYSQL中的
select ... for update語法了。例如：
begin
;
select
*
from`user`whereid
=
1
forupdate
;
//業務邏輯處理
update`user`set
score=score
-1whereid
=
1
;
commit
;
這樣在一個事務中使用
for update鎖住一行記錄，其他事務就不能在該事務提交之前，去更新那一行的資料。需要注意的是for update前的id條件，必須是表的
主鍵或者唯一索引，不然行鎖可能會失效，有可能變成表鎖。9.on duplicate key update
通常情況下，我們在插入資料之前，一般會先查詢一下，該資料是否存在。如果不存在，則插入資料。如果已存在，則不插入資料，而直接返回結果。
在沒啥併發量的場景中，這種做法是沒有什麼問題的。但如果插入資料的請求，有一定的併發量，這種做法就可能會產生重複的資料。
當然防止重複資料的做法很多，比如：
加唯一索引、加分散式鎖等。但這些方案，都沒法做到讓第二次請求也更新資料，它們一般會判斷已經存在就直接返回了。
這種情況可以使用
on duplicate key update語法。該語法會在插入資料之前判斷，如果主鍵或唯一索引不存在，則插入資料。如果主鍵或唯一索引存在，則執行更新操作。
具體需要更新的欄位可以指定，例如：
INSERTINTO`brand`
(
`id`
,
`code`
,
`name`
,
`edit_date`
)
VALUES
(
123
,
'108'
,
'蘇三'
,
now
(
3
))
onduplicatekeyupdatename
=
'蘇三'
,edit_date=
now
(
3
);
這樣一條語句就能輕鬆搞定需求，既不會產生重複資料，也能更新最新的資料。
但需要注意的是，在高併發的場景下使用
on duplicate key update語法，可能會存在死鎖的問題，所以要根據實際情況酌情使用。10.show create table
有時候，我們想快速檢視某張表的欄位情況，通常會使用
desc命令，比如：
desc `order`;
結果如圖所示：
確實能夠看到order表中的欄位名稱、欄位型別、欄位長度、是否允許為空，是否主鍵、預設值等資訊。
但看不到該表的索引資訊，如果想看建立了哪些索引，該怎麼辦呢？
答：使用
show index命令。比如：
showindexfrom`order`
;
也能查出該表所有的索引：
但檢視欄位和索引資料呈現方式，總覺得有點怪怪的，有沒有一種更直觀的方式？
答：這就需要使用
show create table命令了。例如：
showcreatetable`order`
;
執行結果如圖所示：
其中
Table表示表名，Create Table就是我們需要看的建表資訊，將資料展開：
我們能夠看到非常完整的建表語句，表名、欄位名、欄位型別、欄位長度、字元集、主鍵、索引、執行引擎等都能看到。
非常直接明瞭。
11.create table … select
有時候，我們需要快速備份表。
通常情況下，可以分兩步走：
-
建立一張臨時表 -
將資料插入臨時表
建立臨時表可以使用命令：
createtable
order_2022121819
like`order`
;
建立成功之後，就會生成一張名稱叫：order_2022121819，表結構跟order一模一樣的
新表，只是該表的資料為空而已。接下來使用命令：
insertinto
order_2022121819
select
*
from`order`
;
執行之後就會將order表的資料插入到order_2022121819表中，也就是實現資料備份的功能。
但有沒有命令，一個命令就能實現上面這兩步的功能呢？
答：用
create table ... select命令。例如：
createtable
order_2022121820
select
*
from`order`
;
執行完之後，就會將order_2022121820表建立好，並且將order表中的資料自動插入到新建立的order_2022121820中。
一個命令就能輕鬆搞定
表備份。12.explain
很多時候，我們最佳化一條sql語句的效能，需要檢視
索引執行情況。答：可以使用
explain命令，檢視mysql的執行計劃，它會顯示索引的使用情況。例如：
explainselect
*
from`order`where
code=
'002'
;
結果：
透過這幾列可以判斷索引使用情況，執行計劃包含列的含義如下圖所示：
說實話，sql語句沒有走索引，排除沒有建索引之外，最大的可能性是索引失效了。
下面說說索引失效的常見原因：
如果不是上面的這些原因，則需要再進一步排查一下其他原因。
13.show processlist
有些時候我們線上sql或者資料庫出現了問題。比如出現了資料庫連線過多問題，或者發現有一條sql語句的執行時間特別長。
這時候該怎麼辦呢？
答：我們可以使用
show processlist命令檢視當前執行緒執行情況。如圖所示：
從執行結果中，我們可以檢視當前的連線狀態，幫助識別出有問題的查詢語句。
-
id 執行緒id -
User 執行sql的賬號 -
Host 執行sql的資料庫的ip和端號 -
db 資料庫名稱 -
Command 執行命令，包括：Daemon、Query、Sleep等。 -
Time 執行sql所消耗的時間 -
State 執行狀態 -
info 執行資訊，裡面可能包含sql資訊。
如果發現了異常的sql語句，可以直接kill掉，確保資料庫不會出現嚴重的問題。
14.mysqldump
有時候我們需要匯出MYSQL表中的資料。
這種情況就可以使用
mysqldump工具，該工具會將資料查出來，轉換成insert語句，寫入到某個檔案中，相當於資料備份。我們獲取到該檔案，然後執行相應的insert語句，就能建立相關的表，並且寫入資料了，這就相當於
資料還原。mysqldump命令的語法為：
mysqldump -h主機名 -P埠 -u使用者名稱 -p密碼 引數1,引數2.... > 檔名稱.sql備份遠端資料庫中的資料庫：
mysqldump -h 192.22.25.226 -u root -p123456 dbname > backup.sql
歡迎加入我的知識星球，全面提升技術能力。
👉 加入方式，“長按”或“掃描”下方二維碼噢：
星球的內容包括：專案實戰、面試招聘、原始碼解析、學習路線。
文章有幫助的話，在看，轉發吧。
謝謝支援喲 (*^__^*）