MySQL中這14個牛逼的功能，驚豔到我了！！！
前言
我最近幾年用MYSQL資料庫挺多的，發現了一些非常有用的小玩意，今天拿出來分享到大家，希望對你會有所幫助。
1.group_concat
在我們平常的工作中，使用group by進行分組的場景，是非常多的。
比如想統計出使用者表中，名稱不同的使用者的具體名稱有哪些？
具體sql如下：
select name from `user`
group by name;
但如果想把name相同的code拼接在一起，放到另外一列中該怎麼辦呢？
答：使用group_concat函數。
例如：
select name,group_concat(code) from `user`
group by name;
執行結果：
使用group_concat函數，可以輕鬆的把分組後，name相同的資料拼接到一起，組成一個字串，用逗號分隔。
2.char_length
有時候我們需要獲取字元的長度，然後根據字元的長度進行排序。
MYSQL給我們提供了一些有用的函數，比如：char_length。
通過該函數就能獲取字元長度。
獲取字元長度並且排序的sql如下：
select * from brand where name like '%蘇三%'
order by char_length(name) asc limit 5;
執行效果如圖所示：
name欄位使用關鍵字模糊查詢之後，再使用char_length函數獲取name欄位的字元長度，然後按長度升序。
3.locate
有時候我們在查詢某個關鍵字，比如：蘇三，需要明確知道它在某個字串中的位置時，該怎麼辦呢？
答：使用locate函數。
使用locate函數改造之後sql如下：
select * from brand where name like '%蘇三%'
order by char_length(name) asc, locate('蘇三',name) asc limit 5,5;
執行結果：
先按長度排序，小的排在前面。如果長度相同，則按關鍵字從左到右進行排序，越靠左的越排在前面。
除此之外，我們還可以使用：instr和position函數，它們的功能跟locate函數類似，在這裡我就不一一介紹了，感興趣的小夥伴可以找我私聊。
4.replace
我們經常會有替換字串中部分內容的需求，比如：將字串中的字元A替換成B。
這種情況就能使用replace函數。
例如：
update brand set name=REPLACE(name,'A','B')
where id=1;
這樣就能輕鬆實現字元替換功能。
也能用該函數去掉前後空格：
update brand set name=REPLACE(name,' ','') where name like ' %';
update brand set name=REPLACE(name,' ','') where name like '% ';
使用該函數還能替換json格式的資料內容，真的非常有用。
5.now
時間是個好東西，用它可以快速縮小資料範圍，我們經常有獲取當前時間的需求。
在MYSQL中獲取當前時間，可以使用now()函數，例如：
select now() from brand limit 1;
返回結果為下面這樣的：
它會包含年月日時分秒。
如果你還想返回毫秒，可以使用now(3)，例如：
select now(3) from brand limit 1;
返回結果為下面這樣的：
使用起來非常方便好記。
6.insert into ... select
在工作中很多時候需要插入資料。
傳統的插入資料的sql是這樣的：
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (5, '108', '蘇三', '2022-09-02 19:42:21');
它主要是用於插入少量並且已經確定的資料。但如果有大批次的資料需要插入，特別是是需要插入的資料來源於，另外一張表或者多張表的結果集中。
這種情況下，使用傳統的插入資料的方式，就有點束手無策了。
這時候就能使用MYSQL提供的：insert into ... select語法。
例如：
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
select null,code,name,now(3) from `order` where code in ('004','005');
這樣就能將order表中的部分資料，非常輕鬆插入到brand表中。
7.insert into ... ignore
不知道你有沒有遇到過這樣的場景：在插入1000個品牌之前，需要先根據name，判斷一下是否存在。如果存在，則不插入資料。如果不存在，才需要插入資料。
如果直接這樣插入資料：
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (123, '108', '蘇三', now(3));
肯定不行，因為brand表的name欄位建立了唯一索引，同時該表中已經有一條name等於蘇三的資料了。
執行之後直接報錯了：
這就需要在插入之前加一下判斷。
當然很多人通過在sql語句後面拼接not exists語句，也能達到防止出現重複資料的目的，比如：
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
select null,'108', '蘇三',now(3)
from dual where not exists (select * from `brand` where name='蘇三');
這條sql確實能夠滿足要求，但是總覺得有些麻煩。那麼，有沒有更簡單的做法呢？
答：可以使用insert into ... ignore語法。
例如：
INSERT ignore INTO `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` where id=1
for update;
//業務邏輯處理
update `user` set score=score-1 where id=1;
commit;
這樣在一個事務中使用for update鎖住一行記錄，其他事務就不能在該事務提交之前，去更新那一行的資料。
需要注意的是for update前的id條件，必須是表的主鍵或者唯一索引，不然行鎖可能會失效，有可能變成表鎖。
9.on duplicate key update
通常情況下，我們在插入資料之前，一般會先查詢一下，該資料是否存在。如果不存在，則插入資料。如果已存在，則不插入資料，而直接返回結果。
在沒啥並行量的場景中，這種做法是沒有什麼問題的。但如果插入資料的請求，有一定的並行量，這種做法就可能會產生重複的資料。
當然防止重複資料的做法很多，比如：加唯一索引、加分散式鎖等。
但這些方案，都沒法做到讓第二次請求也更新資料，它們一般會判斷已經存在就直接返回了。
這種情況可以使用on duplicate key update語法。
該語法會在插入資料之前判斷，如果主鍵或唯一索引不存在，則插入資料。如果主鍵或唯一索引存在，則執行更新操作。
具體需要更新的欄位可以指定，例如：
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (123, '108', '蘇三', now(3))
on duplicate key update name='蘇三',edit_date=now(3);
這樣一條語句就能輕鬆搞定需求，既不會產生重複資料，也能更新最新的資料。
但需要注意的是，在高並行的場景下使用on duplicate key update語法，可能會存在死鎖的問題，所以要根據實際情況酌情使用。
10.show create table
有時候，我們想快速檢視某張表的欄位情況，通常會使用desc命令，比如：
desc `order`;
結果如圖所示：
確實能夠看到order表中的欄位名稱、欄位型別、欄位長度、是否允許為空，是否主鍵、預設值等資訊。
但看不到該表的索引資訊，如果想看建立了哪些索引，該怎麼辦呢？
答：使用show index命令。
比如：
show index from `order`;
也能查出該表所有的索引：
但檢視欄位和索引資料呈現方式，總覺得有點怪怪的，有沒有一種更直觀的方式？
答：這就需要使用show create table命令了。
例如：
show create table `order`;
執行結果如圖所示：
其中Table表示表名，Create Table就是我們需要看的建表資訊，將資料展開：
我們能夠看到非常完整的建表語句，表名、欄位名、欄位型別、欄位長度、字元集、主鍵、索引、執行引擎等都能看到。
非常直接明瞭。
11.create table ... select
有時候，我們需要快速備份表。
通常情況下，可以分兩步走：
- 建立一張臨時表
- 將資料插入臨時表
建立臨時表可以使用命令：
create table order_2022121819 like `order`;
建立成功之後，就會生成一張名稱叫：order_2022121819，表結構跟order一模一樣的新表，只是該表的資料為空而已。
接下來使用命令：
insert into order_2022121819 select * from `order`;
執行之後就會將order表的資料插入到order_2022121819表中，也就是實現資料備份的功能。
但有沒有命令，一個命令就能實現上面這兩步的功能呢？
答：用create table ... select命令。
例如：
create table order_2022121820
select * from `order`;
執行完之後，就會將order_2022121820表建立好，並且將order表中的資料自動插入到新建立的order_2022121820中。
一個命令就能輕鬆搞定表備份。
12.explain
很多時候，我們優化一條sql語句的效能，需要檢視索引執行情況。
答：可以使用explain命令，檢視mysql的執行計劃，它會顯示索引的使用情況。
例如：
explain select * from `order` where code='002';
結果：
通過這幾列可以判斷索引使用情況，執行計劃包含列的含義如下圖所示：
如果你想進一步瞭解explain的詳細用法，可以看看我的另一篇文章《explain | 索引優化的這把絕世好劍，你真的會用嗎？》
說實話，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
最後說一句(求關注，別白嫖我)
如果這篇文章對您有所幫助，或者有所啟發的話，幫忙掃描下發二維條碼關注一下，您的支援是我堅持寫作最大的動力。
求一鍵三連：點贊、轉發、在看。
關注公眾號：【蘇三說技術】，在公眾號中回覆：面試、程式碼神器、開發手冊、時間管理有超讚的粉絲福利，另外回覆：加群，可以跟很多BAT大廠的前輩交流和學習。