本篇文章給大家?guī)?lái)了關(guān)于mysql的相關(guān)知識(shí)，其中主要介紹了關(guān)于增刪改查與常見(jiàn)陷阱的相關(guān)內(nèi)容，下面一起來(lái)看一下，希望對(duì)大家有幫助。

程序員必備接口測(cè)試調(diào)試工具：立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api設(shè)計(jì)、調(diào)試、文檔、自動(dòng)化測(cè)試工具
后端、前端、測(cè)試，同時(shí)在線協(xié)作，內(nèi)容實(shí)時(shí)同步

推薦學(xué)習(xí)：mysql視頻教程

一、MySQL的增刪改查

MySQL 中我們最常用的增刪改查，對(duì)應(yīng)SQL語(yǔ)句就是 insert 、delete、update、select，這種操作數(shù)據(jù)的語(yǔ)句，又叫Data Manipulation Statements（數(shù)據(jù)操作語(yǔ)句）。

一共是15種，分別是CALL、DELETE、DO、HANDLER、IMPORT TABLE、INSERT、LOAD DATA、LOAD XML、REPL ACE、SELECT、Subqueries、TABLE、UPDATE、VALUES、WITH。

1、insert語(yǔ)句

1.1 insert語(yǔ)句原理

insert 插入，下面給出插入數(shù)據(jù)行的通用語(yǔ)句，如果列表和 VALUES 列表都為空，則INSERT創(chuàng)建一行，每列設(shè)置為其默認(rèn)值；

還可以使用 VALUES ROW() 語(yǔ)法的語(yǔ)句也可以插入多行。在這種情況下，每個(gè)值列表必須包含在ROW()（行構(gòu)造函數(shù)）中，如下所示：

-- 插入語(yǔ)句模板 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);

我們建表的時(shí)候經(jīng)常會(huì)使用主鍵，當(dāng)我們的系統(tǒng)執(zhí)行并發(fā)落庫(kù)的時(shí)候，為了避免主鍵沖突，經(jīng)常會(huì)使用 ON DUPLICATE KEY UPDATE。

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

如果使用了 ON DUPLICATE KEY UPDATE 子句，并且重復(fù)的鍵導(dǎo)致執(zhí)行UPDATE，則該語(yǔ)句需要更新列的UPDATE權(quán)限。對(duì)于已讀取但未修改的列，您只需要SELECT權(quán)限（因?yàn)闊o(wú)需更新，很好理解）。

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

1.2 MySQL插入陷阱

如果未啟用嚴(yán)格模式（嚴(yán)格 SQL 模式），MySQL 對(duì)任何沒(méi)有顯式定義默認(rèn)值的列使用隱式默認(rèn)值。如果啟用了嚴(yán)格模式，如果任何列沒(méi)有默認(rèn)值，則會(huì)發(fā)生錯(cuò)誤。（嚴(yán)格模式會(huì)在后續(xù)的文章中講到） 。

2、delete語(yǔ)句

2.1 delete語(yǔ)句原理

delete顧名思義是刪除，該DELETE語(yǔ)句從中刪除行 tbl_name并返回已刪除的行數(shù)。要檢查刪除的行數(shù)我們一般寫(xiě)代碼的時(shí)候使用 int 類(lèi)型返回：

-- 刪除語(yǔ)法 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name     [PARTITION (partition_name [, partition_name] ...)]     [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]  -- WHERE 中的條件確定要?jiǎng)h除哪些行，如果沒(méi)有WHERE 子句則刪除所有行 -- 如果指定了ORDER BY子句，則按指定的順序刪除行 -- LIMIT子句對(duì)可以刪除的行數(shù)進(jìn)行了限制  -- 如果指定LOW_PRIORITY修飾符，服務(wù)器會(huì)延遲刪除，DELETE直到?jīng)]有其他客戶端從表中讀取 -- QUICK是否合并索引進(jìn)行刪除操作，可能會(huì)導(dǎo)致索引中未回收的空間浪費(fèi) -- IGNORE，MySQL在刪除行的過(guò)程中忽略可忽略的錯(cuò)誤

如果指定LOW_PRIORITY修飾符，服務(wù)器會(huì)延遲刪除，DELETE直到?jīng)]有其他客戶端從表中讀取。QUICK是否合并索引進(jìn)行刪除操作，可能會(huì)導(dǎo)致索引中未回收的空間浪費(fèi)。IGNORE，MySQL在刪除行的過(guò)程中忽略可忽略的錯(cuò)誤。

WHERE 中的條件確定要?jiǎng)h除哪些行，如果沒(méi)有WHERE 子句則刪除所有行，如果指定了ORDER BY子句，則按指定的順序刪除行，LIMIT子句對(duì)可以刪除的行數(shù)進(jìn)行了限制

2.2 MySQL刪除陷阱

1、大批量刪除

如果要從大表中刪除許多行，則可能會(huì)超過(guò)InnoDB表的鎖表大小。為了避免這個(gè)問(wèn)題，或者僅僅為了最小化表保持鎖定的時(shí)間，以下策略可能會(huì)有所幫助：

1、使用存儲(chǔ)過(guò)程進(jìn)行不影響業(yè)務(wù)的小批量、長(zhǎng)時(shí)間刪除，刪除完畢后將存儲(chǔ)過(guò)程從生產(chǎn)環(huán)境下線。

2、選擇不刪除的行，同步與原表結(jié)構(gòu)相同的空表中：INSERT INTO t_copy SELECT * FROM t WHERE … ;

3、用于 RENAMETABLE 以原子方式將原始表移開(kāi)并將副本重命名為原始名稱：RENAME TABLE t TO t_old, t_copy TO t;

2、多表刪除

1、根據(jù)WHERE子句中的條件，可以在DELETE語(yǔ)句中指定多個(gè)表以從一個(gè)或多個(gè)表中刪除行，但是不能在多表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語(yǔ)句原理

UPDATE是修改表中行的語(yǔ)句，返回實(shí)際更改的行數(shù)，要檢查刪除的行數(shù)我們一般寫(xiě)代碼的時(shí)候使用 int 類(lèi)型返回，對(duì)于單表語(yǔ)法，UPDATE語(yǔ)句使用新值更新命名表中現(xiàn)有行的列。

SET 要修改的列以及應(yīng)該給出的值，每個(gè)值都可以作為表達(dá)式或關(guān)鍵字DEFAULT給出，以將列顯式設(shè)置為其默認(rèn)值。

WHERE 指定標(biāo)識(shí)要更新哪些行的條件。如果沒(méi)有WHERE子句，將更新所有行。如果指定了ORDER BY子句，則將按指定的順序更新行。LIMIT子句限制了可以更新的行數(shù)。

-- 更新單表語(yǔ)法 UPDATE [LOW_PRIORITY] [IGNORE] table_reference     SET assignment_list     [WHERE where_condition]     [ORDER BY ...]     [LIMIT row_count]  -- 使用LOW_PRIORITY修飾符，UPDATE延遲執(zhí)行，直到?jīng)]有其他客戶端從表中讀取 -- 使用IGNORE修飾符，即使更新期間發(fā)生錯(cuò)誤，更新語(yǔ)句也不會(huì)中止  UPDATE item_id, discounted SET items_info WHERE id = "";

4、select

SELECT用于檢索從一個(gè)或多個(gè)表中選擇的行，并且可以包括UNION操作和子查詢。從MySQL 8.0.31開(kāi)始，還支持INTERSECT和EXCEPT操作。后面筆者會(huì)單獨(dú)拿出一篇文章講解子查詢、左連接、查詢優(yōu)化、查詢?cè)淼鹊取?/p>

后面更新后會(huì)附上連接

二、15種MySQL數(shù)據(jù)操作語(yǔ)句

類(lèi)似于增刪改查的語(yǔ)句我們?cè)诘谝还?jié)已經(jīng)學(xué)習(xí)，本小節(jié)主要講解 CALL、DO、HANDLER、IMPORT TABLE、LOAD DATA、LOAD XML、REPL ACE、Subqueries、TABLE、VALUES、WITH，這11個(gè)語(yǔ)句的使用，后續(xù)會(huì)詳細(xì)的進(jìn)行詳細(xì)分析，關(guān)注本專(zhuān)欄。

1、REPLACE語(yǔ)句

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

2、CALL語(yǔ)句

CALL語(yǔ)句調(diào)用先前使用CREATE procedure定義的存儲(chǔ)過(guò)程。當(dāng)過(guò)程返回時(shí)，客戶端程序還可以獲得例程內(nèi)執(zhí)行的最終語(yǔ)句所影響的行數(shù)。

3、TABLE語(yǔ)句

TABLE是MySQL 8.0.19中引入的DML語(yǔ)句，返回命名表的行和列。

4、WITH語(yǔ)句

WITH每個(gè)子子句提供一個(gè)子查詢，該子查詢生成一個(gè)結(jié)果集，并將名稱與子查詢相關(guān)聯(lián)。

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

兩個(gè)值進(jìn)行查詢，運(yùn)算或者比較，首先要求數(shù)據(jù)類(lèi)型必須一致。如果發(fā)現(xiàn)兩個(gè)數(shù)據(jù)類(lèi)型不一致時(shí)就會(huì)發(fā)生隱式類(lèi)型轉(zhuǎn)換。

問(wèn)題描述：

分享一個(gè)筆者同事曾經(jīng)發(fā)生的產(chǎn)線問(wèn)題：在一次MySQL查詢中，某字段為 varchar 字符串類(lèi)型，傳入?yún)?shù)值為 long 數(shù)字類(lèi)型，發(fā)現(xiàn)查詢的結(jié)果和預(yù)期的不一致。

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

但是由于測(cè)試環(huán)境的數(shù)據(jù)量較少，并沒(méi)有發(fā)現(xiàn)，只到上了生產(chǎn)環(huán)境，在進(jìn)行大數(shù)據(jù)查詢時(shí)，由于數(shù)據(jù)庫(kù)的odr_id是 varchar 類(lèi)型，查詢條件是 long類(lèi)型，所有每條查詢出來(lái)的數(shù)據(jù)都會(huì)進(jìn)行隱式類(lèi)型轉(zhuǎn)換的比較，直接導(dǎo)致long sql，處理辦法是緊急版本上線。

隱式類(lèi)型轉(zhuǎn)換原理：

如果一個(gè)或兩個(gè)參數(shù)均為NULL，則比較的結(jié)果為NULL，除了 相等比較運(yùn)算符。對(duì)于NULL NULL，結(jié)果為true；如果比較操作中的兩個(gè)參數(shù)都是字符串，則將它們作為字符串進(jìn)行比較；如果兩個(gè)參數(shù)都是整數(shù)，則將它們作為整數(shù)進(jìn)行比較。

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

如果另一個(gè)參數(shù)是十進(jìn)制或整數(shù)值，則將參數(shù)作為十進(jìn)制值進(jìn)行比較（這里如果生產(chǎn)環(huán)境是varchar后果將是災(zāi)難級(jí)的）；

如果另一個(gè)參數(shù)是浮點(diǎn)值，則將參數(shù)作為浮點(diǎn)值進(jìn)行比較。；在所有其他情況下，將參數(shù)作為浮點(diǎn)數(shù)（實(shí)數(shù)）進(jìn)行比較。例如，將字符串和數(shù)字操作數(shù)進(jìn)行比較，將其作為浮點(diǎn)數(shù)的比較。

通過(guò)隱式類(lèi)型轉(zhuǎn)換可以得出上述示例的結(jié)果：當(dāng)查詢中有數(shù)字時(shí)那么會(huì)將字符串轉(zhuǎn)化成數(shù)字進(jìn)行比較。所以當(dāng)你的列為字符串時(shí)那么需要將列中字符串進(jìn)行類(lèi)型格式轉(zhuǎn)換而進(jìn)行字符格式轉(zhuǎn)換之后則與索引不一致；當(dāng)你的列為數(shù)字時(shí)查詢等式為字符串時(shí)只是把查詢的常量轉(zhuǎn)成數(shù)字并不影響列的類(lèi)型所以依然可以使用索引并沒(méi)有破壞索引的類(lèi)型。

推薦學(xué)習(xí)：mysql視頻教程