共計 4380 個字符，預計需要花費 11 分鐘才能閱讀完成。
本文丸趣 TV 小編為大家詳細介紹“MySQL 增刪改查方法與常見陷阱有哪些”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MySQL 增刪改查方法與常見陷阱有哪些”文章能幫助大家解決疑惑，下面跟著丸趣 TV 小編的思路慢慢深入，一起來學習新知識吧。
一、MySQL 的增刪改查
MySQL 中我們最常用的增刪改查，對應 SQL 語句就是 insert、delete、update、select，這種操作數據的語句，又叫 Data Manipulation Statements（數據操作語句）。
一共是 15 種，分別是 CALL、DELETE、DO、HANDLER、IMPORT TABLE、INSERT、LOAD DATA、LOAD XML、REPL ACE、SELECT、Subqueries、TABLE、UPDATE、VALUES、WITH。
1、insert 語句 1.1 insert 語句原理
insert 插入，下面給出插入數據行的通用語句，如果列表和 VALUES 列表都為空，則 INSERT 創建一行，每列設置為其默認值；
還可以使用 VALUES ROW() 語法的語句也可以插入多行。在這種情況下，每個值列表必須包含在 ROW()（行構造函數）中，如下所示：
-- 插入語句模板
INSERT INTO tbl_name () VALUES();
-- 插入多行
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);
INSERT INTO tbl_name (a,b,c) VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);我們建表的時候經常會使用主鍵，當我們的系統執行并發落庫的時候，為了避免主鍵沖突，經常會使用 ON DUPLICATE KEY UPDATE。
注意：ON DUPLICATE KEY UPDATE 是 Mysql 特有的語法，僅 Mysql 有效。作用：當執行 insert 操作時，有已經存在的記錄，執行 update 操作。
如果使用了 ON DUPLICATE KEY UPDATE 子句，并且重復的鍵導致執行 UPDATE，則該語句需要更新列的 UPDATE 權限。對于已讀取但未修改的列，您只需要 SELECT 權限（因為無需更新，很好理解）。
INSERT INTO test ( id, NAME, age ) VALUES( 1, 張三 , 13 )
ON DUPLICATE KEY UPDATE age = 13,1.2 MySQL 插入陷阱
如果未啟用嚴格模式（嚴格 SQL 模式），MySQL 對任何沒有顯式定義默認值的列使用隱式默認值。如果啟用了嚴格模式，如果任何列沒有默認值，則會發生錯誤。（嚴格模式會在后續的文章中講到）。
2、delete 語句 2.1 delete 語句原理
delete 顧名思義是刪除，該 DELETE 語句從中刪除行 tbl_name 并返回已刪除的行數。要檢查刪除的行數我們一般寫代碼的時候使用 int 類型返回：
-- 刪除語法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
-- WHERE 中的條件確定要刪除哪些行，如果沒有 WHERE 子句則刪除所有行
-- 如果指定了 ORDER BY 子句，則按指定的順序刪除行
-- LIMIT 子句對可以刪除的行數進行了限制
-- 如果指定 LOW_PRIORITY 修飾符，服務器會延遲刪除，DELETE 直到沒有其他客戶端從表中讀取
-- QUICK 是否合并索引進行刪除操作，可能會導致索引中未回收的空間浪費
-- IGNORE，MySQL 在刪除行的過程中忽略可忽略的錯誤 如果指定 LOW_PRIORITY 修飾符，服務器會延遲刪除，DELETE 直到沒有其他客戶端從表中讀取。QUICK 是否合并索引進行刪除操作，可能會導致索引中未回收的空間浪費。IGNORE，MySQL 在刪除行的過程中忽略可忽略的錯誤。
WHERE 中的條件確定要刪除哪些行，如果沒有 WHERE 子句則刪除所有行，如果指定了 ORDER BY 子句，則按指定的順序刪除行，LIMIT 子句對可以刪除的行數進行了限制
2.2 MySQL 刪除陷阱
1、大批量刪除
如果要從大表中刪除許多行，則可能會超過 InnoDB 表的鎖表大小。為了避免這個問題，或者僅僅為了最小化表保持鎖定的時間，以下策略可能會有所幫助：
1、使用存儲過程進行不影響業務的小批量、長時間刪除，刪除完畢后將存儲過程從生產環境下線。
2、選擇不刪除的行，同步與原表結構相同的空表中：INSERT INTO t_copy SELECT * FROM t WHERE … ;
3、用于 RENAMETABLE 以原子方式將原始表移開并將副本重命名為原始名稱：RENAME TABLE t TO t_old, t_copy TO t;
2、多表刪除
1、根據 WHERE 子句中的條件，可以在 DELETE 語句中指定多個表以從一個或多個表中刪除行，但是不能在多表 DELETE 中使用 ORDER BY 或 LIMIT。
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;3、update 語句原理
UPDATE 是修改表中行的語句，返回實際更改的行數，要檢查刪除的行數我們一般寫代碼的時候使用 int 類型返回，對于單表語法，UPDATE 語句使用新值更新命名表中現有行的列。
SET 要修改的列以及應該給出的值，每個值都可以作為表達式或關鍵字 DEFAULT 給出，以將列顯式設置為其默認值。
WHERE 指定標識要更新哪些行的條件。如果沒有 WHERE 子句，將更新所有行。如果指定了 ORDER BY 子句，則將按指定的順序更新行。LIMIT 子句限制了可以更新的行數。
-- 更新單表語法
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
-- 使用 LOW_PRIORITY 修飾符，UPDATE 延遲執行，直到沒有其他客戶端從表中讀取
-- 使用 IGNORE 修飾符，即使更新期間發生錯誤，更新語句也不會中止
UPDATE item_id, discounted SET items_info WHERE id = 4、select
SELECT 用于檢索從一個或多個表中選擇的行，并且可以包括 UNION 操作和子查詢。從 MySQL 8.0.31 開始，還支持 INTERSECT 和 EXCEPT 操作。后面筆者會單獨拿出一篇文章講解子查詢、左連接、查詢優化、查詢原理等等。
二、15 種 MySQL 數據操作語句
類似于增刪改查的語句我們在第一節已經學習，本小節主要講解 CALL、DO、HANDLER、IMPORT TABLE、LOAD DATA、LOAD XML、REPL ACE、Subqueries、TABLE、VALUES、WITH，這 11 個語句的使用，后續會詳細的進行詳細分析，關注本專欄。
1、REPLACE 語句
REPLACE 的工作方式與 INSERT 完全相同，只是如果表中的一個舊行與 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，則在插入新行之前會刪除舊行。在 MySQL 8.0 中已不支持 DELAYED。
2、CALL 語句
CALL 語句調用先前使用 CREATE procedure 定義的存儲過程。當過程返回時，客戶端程序還可以獲得例程內執行的最終語句所影響的行數。
3、TABLE 語句
TABLE 是 MySQL 8.0.19 中引入的 DML 語句，返回命名表的行和列。
4、WITH 語句
WITH 每個子子句提供一個子查詢，該子查詢生成一個結果集，并將名稱與子查詢相關聯。
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;三、MySQL 查詢陷阱
兩個值進行查詢，運算或者比較，首先要求數據類型必須一致。如果發現兩個數據類型不一致時就會發生隱式類型轉換。
問題描述：
分享一個筆者同事曾經發生的產線問題：在一次 MySQL 查詢中，某字段為 varchar 字符串類型，傳入參數值為 long 數字類型，發現查詢的結果和預期的不一致。
select * from 表 where odr_id =
select * from 表 where odr_id = long;但是由于測試環境的數據量較少，并沒有發現，只到上了生產環境，在進行大數據查詢時，由于數據庫的 odr_id 是 varchar 類型，查詢條件是 long 類型，所有每條查詢出來的數據都會進行隱式類型轉換的比較，直接導致 long sql，處理辦法是緊急版本上線。
隱式類型轉換原理：
如果一個或兩個參數均為 NULL，則比較的結果為 NULL，除了 相等比較運算符。對于 NULL NULL，結果為 true；如果比較操作中的兩個參數都是字符串，則將它們作為字符串進行比較；如果兩個參數都是整數，則將它們作為整數進行比較。
如果不與數字比較，則將十六進制值視為二進制字符串；如果參數之一是 timestamp 或 datatime column，而另一個參數是常量，則在執行比較之前，該常量將轉換為時間戳；如果參數之一是十進制值，則比較取決于另一個參數。
如果另一個參數是十進制或整數值，則將參數作為十進制值進行比較（這里如果生產環境是 varchar 后果將是災難級的）；
如果另一個參數是浮點值，則將參數作為浮點值進行比較。；在所有其他情況下，將參數作為浮點數（實數）進行比較。例如，將字符串和數字操作數進行比較，將其作為浮點數的比較。
通過隱式類型轉換可以得出上述示例的結果：當查詢中有數字時那么會將字符串轉化成數字進行比較。所以當你的列為字符串時那么需要將列中字符串進行類型格式轉換而進行字符格式轉換之后則與索引不一致；當你的列為數字時查詢等式為字符串時只是把查詢的常量轉成數字并不影響列的類型所以依然可以使用索引并沒有破壞索引的類型。
讀到這里，這篇“MySQL 增刪改查方法與常見陷阱有哪些”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注丸趣 TV 行業資訊頻道。
