共計 5761 個字符，預計需要花費 15 分鐘才能閱讀完成。
這篇文章主要介紹了 MySQL 中實用的知識點有哪些的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇 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 user_id,user_name FROM `sys_user` WHERE user_name LIKE %JM%
ORDER BY CHAR_LENGTH(user_name) ASC LIMIT 5;執行效果如圖所示：
name 字段使用關鍵字模糊查詢之后，再使用 char_length 函數獲取 name 字段的字符長度，然后按長度升序。
3.locate
有時候我們在查找某個關鍵字，比如：JM，需要明確知道它在某個字符串中的位置時，該怎么辦呢？
答：使用 locate 函數。
使用 locate 函數改造之后 sql 如下：
SELECT user_id,user_name FROM `sys_user` WHERE user_name LIKE %JM%
ORDER BY CHAR_LENGTH(user_name) ASC, LOCATE(JM ,user_name) LIMIT 2,2;執行結果：
先按長度排序，小的排在前面。如果長度相同，則按關鍵字從左到右進行排序，越靠左的越排在前面。
除此之外，我們還可以使用：instr 和 position 函數，它們的功能跟 locate 函數類似，在這里我就不一一介紹了。
4.replace
我們經常會有替換字符串中部分內容的需求，比如：將字符串中的字符 A 替換成 B。
這種情況就能使用 replace 函數。
例如：
UPDATE sys_user set user_name=REPLACE(user_name, A , B) WHERE user_id=1;這樣就能輕松實現字符替換功能。
也能用該函數去掉前后空格：
UPDATE sys_user set user_name=REPLACE(user_name, ,) WHERE user_name LIKE %
UPDATE sys_user set user_name=REPLACE(user_name, ,) WHERE user_name LIKE % 使用該函數還能替換 json 格式的數據內容，真的非常有用。
5.now
時間是個好東西，用它可以快速縮小數據范圍，我們經常有獲取當前時間的需求。
在 MYSQL 中獲取當前時間，可以使用 now() 函數，例如：
SELECT now() FROM sys_user LIMIT 1;返回結果為下面這樣的：
它會包含年月日時分秒。
如果你還想返回毫秒，可以使用 now(3)，例如：
SELECT now(3) FROM sys_user LIMIT 1;返回結果為下面這樣的：
使用起來非常方便好記。
6.insert into … select
在工作中很多時候需要插入數據。
傳統的插入數據的 sql 是這樣的：
INSERT INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`)
VALUES (6, 103 , JM , now());它主要是用于插入少量并且已經確定的數據。但如果有大批量的數據需要插入，特別是是需要插入的數據來源于，另外一張表或者多張表的結果集中。
這種情況下，使用傳統的插入數據的方式，就有點束手無策了。
這時候就能使用 MYSQL 提供的：insert into … select 語法。
例如：
INSERT INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`)
SELECT null,dept_id,user_name,now(3) FROM `sys_user_backup` WHERE dept_id in (103 , 105這樣就能將用戶備份表中的部分數據，非常輕松插入到用戶表中。
7.insert into … ignore
不知道你有沒有遇到過這樣的場景：在插入 1000 個用戶之前，需要先根據 user_name，判斷一下是否存在。如果存在，則不插入數據。如果不存在，才需要插入數據。
如果直接這樣插入數據：
INSERT INTO `sys_user`(`id`, `dept_id`, `user_name`, `create_time`)
VALUES (6, 103 , JM , now());肯定不行，因為 sys_user 表的 user_name 字段創建了唯一索引，同時該表中已經有一條 user_name 等于 JM 的數據了。
執行之后直接報錯了：
這就需要在插入之前加一下判斷。
當然很多人通過在 sql 語句后面拼接 not exists 語句，也能達到防止出現重復數據的目的，比如：
INSERT INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`)
SELECT null,dept_id,user_name,now(3) FROM sys_user_backup
WHERE not exists (SELECT * FROM `sys_user` WHERE user_name = JM)這條 sql 確實能夠滿足要求，但是總覺得有些麻煩。那么，有沒有更簡單的做法呢？
答：可以使用 insert into … ignore 語法。
例如：
INSERT ignore INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`)
VALUES (123, 105 , JM , now(3));這樣改造之后，如果 sys_user 表中沒有 user_name 為 JM 的數據，則可以直接插入成功。
但如果 sys_user 表中已經存在 user_name 為 JM 的數據了，則該 sql 語句也能正常執行，并不會報錯。因為它會忽略異常，返回的執行結果影響行數為 0，它不會重復插入數據。
8.select … for update
MYSQL 數據庫自帶了悲觀鎖，它是一種排它鎖，根據鎖的粒度從大到小分為：表鎖、間隙鎖和行鎖。
在我們的實際業務場景中，有些情況并發量不太高，為了保證數據的正確性，使用悲觀鎖也可以。
比如：用戶扣減積分，用戶的操作并不集中。但也要考慮系統自動贈送積分的并發情況，所以有必要加悲觀鎖限制一下，防止出現積分加錯的情況發生。
這時候就可以使用 MYSQL 中的 select … for update 語法了。
例如：
BEGIN;
SELECT * FROM `sys_user` where user_id=1
FOR UPDATE;
// 業務邏輯處理
UPDATE `sys_user` SET score = score -1 WHERE user_id=1;
COMMIT;這樣在一個事務中使用 for update 鎖住一行記錄，其他事務就不能在該事務提交之前，去更新那一行的數據。
需要注意的是 for update 前的 id 條件，必須是表的主鍵或者唯一索引，不然行鎖可能會失效，有可能變成表鎖。
9.on duplicate key update
通常情況下，我們在插入數據之前，一般會先查詢一下，該數據是否存在。如果不存在，則插入數據。如果已存在，則不插入數據，而直接返回結果。
在沒啥并發量的場景中，這種做法是沒有什么問題的。但如果插入數據的請求，有一定的并發量，這種做法就可能會產生重復的數據。
當然防止重復數據的做法很多，比如：加唯一索引、加分布式鎖等。
但這些方案，都沒法做到讓第二次請求也更新數據，它們一般會判斷已經存在就直接返回了。
這種情況可以使用 on duplicate key update 語法。
該語法會在插入數據之前判斷，如果主鍵或唯一索引不存在，則插入數據。如果主鍵或唯一索引存在，則執行更新操作。
具體需要更新的字段可以指定，例如：
INSERT INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`)
VALUES (null, 103 , JM , now())
OM DUPLICATE KEY UPDATE user_name= JM ,create_time=now();這樣一條語句就能輕松搞定需求，既不會產生重復數據，也能更新最新的數據。
但需要注意的是，在高并發的場景下使用 on duplicate key update 語法，可能會存在死鎖的問題，所以要根據實際情況酌情使用。
10.show create table
有時候，我們想快速查看某張表的字段情況，通常會使用 desc 命令，比如：
DESC `sys_dept`;結果如圖所示：
確實能夠看到 sys_dept 表中的字段名稱、字段類型、字段長度、是否允許為空，是否主鍵、默認值等信息。
但看不到該表的索引信息，如果想看創建了哪些索引，該怎么辦呢？
答：使用 show index 命令。
比如：
SHOW INDEX FROM sys_dept;也能查出該表所有的索引：
但查看字段和索引數據呈現方式，總覺得有點怪怪的，有沒有一種更直觀的方式？
答：這就需要使用 show create table 命令了。
例如：
show create table `order`;執行結果如圖所示：
其中 Table 表示表名，Create Table 就是我們需要看的建表信息，將數據展開：我們能夠看到非常完整的建表語句，表名、字段名、字段類型、字段長度、字符集、主鍵、索引、執行引擎等都能看到。
非常直接明了。
11.create table … select
有時候，我們需要快速備份表。
通常情況下，可以分兩步走：
創建一張臨時表
將數據插入臨時表
創建臨時表可以使用命令：
CREATE TABLE user_20221219 LIKE `sys_user`;創建成功之后，就會生成一張名稱叫：user_20221219，表結構跟 sys_user 一模一樣的新表，只是該表的數據為空而已。
接下來使用命令：
INSERT INTO user_20221219 SELECT * FROM `sys_user`;執行之后就會將 order 表的數據插入到 user_20221219 表中，也就是實現數據備份的功能。
但有沒有命令，一個命令就能實現上面這兩步的功能呢？
答：用 create table … select 命令。
例如：
CREATE TABLE user_20221219SELECT * FROM `sys_user`;執行完之后，就會將 user_20221219 表創建好，并且將 sys_user 表中的數據自動插入到新創建的 user_20221219 中。
一個命令就能輕松搞定表備份。
12.explain
很多時候，我們優化一條 sql 語句的性能，需要查看索引執行情況。
答：可以使用 explain 命令，查看 mysql 的執行計劃，它會顯示索引的使用情況。
例如：
EXPLAIN SELECT * FROM `sys_user` WHERE dept_id=103;結果：
通過這幾列可以判斷索引使用情況，執行計劃包含列的含義如下圖所示：
說實話，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關于“MySQL 中實用的知識點有哪些”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“MySQL 中實用的知識點有哪些”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道。
