本文導讀

本文作為MySQL系列第二篇文章，詳細講解了MySQL的增刪改查的語句、語義和一些我們經常在開發(fā)工作中暴露的問題，MySQL的增刪改查又叫數據操作語句，本文有講些了一些常用的數據操作語句，select語句后續(xù)將作為一篇完整的文章進行學習它的查詢復雜場景語句、優(yōu)化以及原理，最后通過一個生產問題介紹了mysql隱式類型的陷阱。

一、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創(chuàng)建一行，每列設置為其默認值；

還可以使用 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);

我們建表的時候經常會使用主鍵，當我們的系統執(zhí)行并發(fā)落庫的時候，為了避免主鍵沖突，經常會使用 ON DUPLICATE KEY UPDATE。

注意：ON DUPLICATE KEY UPDATE 是Mysql特有的語法，僅Mysql有效。作用： 當執(zhí)行insert操作時，有已經存在的記錄，執(zhí)行update操作。

如果使用了 ON DUPLICATE KEY UPDATE 子句，并且重復的鍵導致執(zhí)行UPDATE，則該語句需要更新列的UPDATE權限。對于已讀取但未修改的列，您只需要SELECT權限（因為無需更新，很好理解）。

INSERT INTO test ( id, NAME, age ) VALUES( 1, '張三', 13 ) 
	ON DUPLICATE KEY UPDATE age = 13,

1.2 MySQL插入陷阱

如果未啟用嚴格模式（嚴格 SQL 模式），MySQL 對任何沒有顯式定義默認值的列使用隱式默認值。如果啟用了嚴格模式，如果任何列沒有默認值，則會發(fā)生錯誤。（嚴格模式會在后續(xù)的文章中講到） 。

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、使用存儲過程進行不影響業(yè)務的小批量、長時間刪除，刪除完畢后將存儲過程從生產環(huán)境下線。

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延遲執(zhí)行，直到沒有其他客戶端從表中讀取
-- 使用IGNORE修飾符，即使更新期間發(fā)生錯誤，更新語句也不會中止

UPDATE item_id, discounted SET items_info WHERE id = "";

4、select

SELECT用于檢索從一個或多個表中選擇的行，并且可以包括UNION操作和子查詢。從MySQL 8.0.31開始，還支持INTERSECT和EXCEPT操作。后面筆者會單獨拿出一篇文章講解子查詢、左連接、查詢優(yōu)化、查詢原理等等。

后面更新后會附上連接

二、15種MySQL數據操作語句

類似于增刪改查的語句我們在第一節(jié)已經學習，本小節(jié)主要講解 CALL、DO、HANDLER、IMPORT TABLE、LOAD DATA、LOAD XML、REPL ACE、Subqueries、TABLE、VALUES、WITH，這11個語句的使用，后續(xù)會詳細的進行詳細分析，關注本專欄。

1、REPLACE語句

REPLACE的工作方式與INSERT完全相同，只是如果表中的一個舊行與PRIMARY KEY或UNIQUE索引的新行具有相同的值，則在插入新行之前會刪除舊行。在MySQL 8.0中已不支持DELAYED。

2、CALL語句

CALL語句調用先前使用CREATE procedure定義的存儲過程。當過程返回時，客戶端程序還可以獲得例程內執(zhí)行的最終語句所影響的行數。

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查詢陷阱

兩個值進行查詢，運算或者比較，首先要求數據類型必須一致。如果發(fā)現兩個數據類型不一致時就會發(fā)生隱式類型轉換。

問題描述：

分享一個筆者同事曾經發(fā)生的產線問題：在一次MySQL查詢中，某字段為 varchar 字符串類型，傳入參數值為 long 數字類型，發(fā)現查詢的結果和預期的不一致。

select * from 表 where odr_id = "";
select * from 表 where odr_id = long;

但是由于測試環(huán)境的數據量較少，并沒有發(fā)現，只到上了生產環(huán)境，在進行大數據查詢時，由于數據庫的odr_id是 varchar 類型，查詢條件是 long類型，所有每條查詢出來的數據都會進行隱式類型轉換的比較，直接導致long sql，處理辦法是緊急版本上線。

隱式類型轉換原理：

如果一個或兩個參數均為NULL，則比較的結果為NULL，除了  相等比較運算符。對于NULL NULL，結果為true；如果比較操作中的兩個參數都是字符串，則將它們作為字符串進行比較；如果兩個參數都是整數，則將它們作為整數進行比較。

如果不與數字比較，則將十六進制值視為二進制字符串；如果參數之一是  timestamp 或 datatime column，而另一個參數是常量，則在執(zhí)行比較之前，該常量將轉換為時間戳；如果參數之一是十進制值，則比較取決于另一個參數。

如果另一個參數是十進制或整數值，則將參數作為十進制值進行比較（這里如果生產環(huán)境是varchar后果將是災難級的） ；

如果另一個參數是浮點值，則將參數作為浮點值進行比較。；在所有其他情況下，將參數作為浮點數（實數）進行比較。例如，將字符串和數字操作數進行比較，將其作為浮點數的比較。

通過隱式類型轉換可以得出上述示例的結果：當查詢中有數字時那么會將字符串轉化成數字進行比較。所以當你的列為字符串時那么需要將列中字符串進行類型格式轉換而進行字符格式轉換之后則與索引不一致；當你的列為數字時查詢等式為字符串時只是把查詢的常量轉成數字并不影響列的類型所以依然可以使用索引并沒有破壞索引的類型。

總結

本文作為MySQL系列第二篇文章，詳細講解了MySQL的增刪改查的語句、語義和一些我們經常在開發(fā)工作中暴露的問題，MySQL的增刪改查又叫數據操作語句，本文有講些了一些常用的數據操作語句，select語句后續(xù)將作為一篇完整的文章進行學習它的查詢復雜場景語句、優(yōu)化以及原理，最后通過一個生產問題介紹了mysql隱式類型的陷阱。

到此這篇關于MySQL中增刪改查操作與常見陷阱詳解的文章就介紹到這了,更多相關MySQL增刪改查內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論