實用技巧
1. group_concat
在我們平常的工作中,使用group by
進行分組的場景,是非常多的。
比如想統計出使用者表中,名稱不同的使用者的具體名稱有哪些?
具體 sql 如下:
select name from `user` group by name;
但如果想把 name 相同的 code 拼接在一起,放到另外一列中該怎麼辦呢?
使用group_concat
函式,可以輕鬆的把分組後,name 相同的資料拼接到一起,組成一個字串,用逗號
分隔。
2. 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格式
的資料內容,真的非常有用。
3. now
時間是個好東西,用它可以快速縮小資料範圍,我們經常有獲取當前時間的需求。
在 MYSQL 中獲取當前時間
,可以使用now()
函式,例如:
select now() from brand limit 1;
它會包含年月日時分秒
。
如果你還想返回毫秒
,可以使用now(3)
,例如:
select now(3) from brand limit 1;
4. insert into ... ignore
不知道你有沒有遇到過這樣的場景:在插入 1000 個品牌之前,需要先根據 name,判斷一下是否存在。如果存在,則不插入資料。如果不存在,才需要插入資料。
如果直接這樣插入資料:
INSERT IGNORE INTO `brand`(`code`, `name`, `edit_date`) VALUES ('108', '蘇三', now(3));
肯定不行,因為 brand 表的 name 欄位建立了唯一索引,同時該表中已經有一條 name 等於蘇三的資料了。
使用insert ignore into
語法可以解決此問題。
例如:
INSERT IGNORE INTO `brand`(`code`, `name`, `edit_date`) VALUES ('108', '蘇三', now(3));
這樣改造之後,如果 brand 表中沒有 name 為蘇三的資料,則可以直接插入成功。
但如果 brand 表中已經存在 name 為蘇三的資料了,則該 sql 語句也能正常執行,並不會報錯。因為它會忽略異常,返回的執行結果影響行數為 0。
5. 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
語法,可能會存在死鎖
的問題,所以要根據實際情況酌情使用。
6. show create table
同時看欄位跟索引的資訊
show create table `order`;
7. create table ... select
一個命令完成表備份
。
create table order_2022121820 select * from `order`;
8. show processlist
有些時候我們線上 sql 或者資料庫出現了問題。比如出現了資料庫連線過多問題,或者發現有一條 sql 語句的執行時間特別長。
這時候該怎麼辦呢?
答:我們可以使用show processlist
命令檢視當前執行緒執行情況
。
如圖所示: 從執行結果中,我們可以檢視當前的連線狀態,幫助識別出有問題的查詢語句。
- id 執行緒 id
- User 執行 sql 的賬號
- Host 執行 sql 的資料庫的 ip 和端號
- db 資料庫名稱
- Command 執行命令,包括:Daemon、Query、Sleep 等。
- Time 執行 sql 所消耗的時間
- State 執行狀態
- info 執行資訊,裡面可能包含 sql 資訊。