MySQL INSERT的4種形態(tài)

INSERT語句是最常見的SQL語句之一，MySQL中INSERT有其他形態(tài)的插入數(shù)據(jù)方式。下面了解一下MySQL中常用的四種插入數(shù)據(jù)的語句:

INSERT INTO

1. insert into表示插入數(shù)據(jù)，數(shù)據(jù)庫會檢查主鍵（PrimaryKey），如果出現(xiàn)重復會報錯；除了這個之外還有一些配合的參數(shù)。

語法如下：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name    [PARTITION (partition_name [, partition_name] ...)]    [(col_name [, col_name] ...)]    {VALUES | VALUE} (value_list) [, (value_list)] ...    [ON DUPLICATE KEY UPDATE assignment_list]

• DELAYED：
  是立刻返回一個標識，告訴上層程序，數(shù)據(jù)已經(jīng)插入了，當表沒有被其它線程使用時，此行被插入，真實插入時間就不可控了。所以這樣的寫法對數(shù)據(jù)的安全性是沒有保障的。
  延遲插入和替換在MySQL 5.6中是不推薦的。在MySQL 5.7，MySQL 8.0中，不支持延遲。服務(wù)器識別但忽略DELAYED關(guān)鍵字，將插入處理為非延遲插入，并生成er_warn_legacy_syntax_convert警告(“不再支持延遲插入”)。語句被轉(zhuǎn)換為INSERT”)。DELAYED關(guān)鍵字計劃在未來的版本中刪除。

• LOW_PRIORITY：
  插入的執(zhí)行將被延遲，直到?jīng)]有其他客戶端從表中讀取數(shù)據(jù)。這包括在現(xiàn)有客戶端正在讀取時以及INSERT LOW_PRIORITY語句正在等待時開始讀取的其他客戶端。因此，對于發(fā)出INSERT LOW_PRIORITY語句的客戶機，可能要等待很長時間。
  LOW_PRIORITY只影響只使用表級鎖的存儲引擎(如MyISAM、內(nèi)存和合并)。
  LOW_PRIORITY通常不應(yīng)該用于MyISAM表，因為這樣做會禁用并發(fā)插入

• HIGH_PRIORITY:
  如果指定了HIGH_PRIORITY，同時服務(wù)器采用–low-priority-updates選項啟動，則HIGH_PRIORITY將覆蓋–low-priority-updates選項。這么做還會導 ? ? ? ? ? ? ? ? ? ? ? ? 致同時進行的插入被取消。
  【 low_priority_updates：如果設(shè)置為1，所有插入、更新、刪除和鎖表寫語句都將等待，直到受影響的表上沒有未決的選擇或鎖表讀取。使用{INSERT | REPLACE | DELETE | UPDATE} LOW_PRIORITY…僅降低一個查詢的優(yōu)先級。這個變量只影響只使用表級鎖的存儲引擎(如MyISAM、MEMORY和MERGE)MySQL的merge引擎類型允許把許多結(jié)構(gòu)相同的表合并為一個表,Merge表有點類似于視圖。】

• IGNORE：
  insert ignore表示，如果中已經(jīng)存在相同的記錄，則忽略當前新數(shù)據(jù)，主鍵和唯一鍵為基準；

mysql> insert ignore  tinsert(id,name) values(3,'D');Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> show warnings;+---------+------+---------------------------------------+| Level   | Code | Message                               |+---------+------+---------------------------------------+| Warning | 1062 | Duplicate entry '3' for key 'PRIMARY' |+---------+------+---------------------------------------+

INSERT INTO SELECT

把一張表的字段數(shù)據(jù)導入到另一張表中，執(zhí)行語句會把整個數(shù)據(jù)會打包成一個事務(wù)執(zhí)行。

注意：當從同一個表中選擇和插入時，MySQL創(chuàng)建一個內(nèi)部臨時表來保存SELECT中的行，然后將這些行插入到目標表中。但是，不能使用INSERT INTO t…選擇……當t是臨時表時，從t開始，因為臨時表不能在同一語句中被引用兩次。

REPLACE INTO

replace into 跟 insert 功能類似，不同點在于：replace into 首先嘗試插入數(shù)據(jù)到表中，
1. 如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)（根據(jù)主鍵或者唯一索引判斷）則先刪除此行數(shù)據(jù)，然后插入新的數(shù)據(jù)。
2. 否則，直接插入新數(shù)據(jù)。
3. REPLACE，您必須同時擁有表的INSERT，UPDATE，DELETE權(quán)限。

語法：

REPLACE [LOW_PRIORITY | DELAYED]    [INTO] tbl_name    [PARTITION (partition_name [, partition_name] ...)]    [(col_name [, col_name] ...)]    {VALUES | VALUE} (value_list) [, (value_list)] ...

注意：插入數(shù)據(jù)的表必須有主鍵或者是唯一索引！否則的話，replace into 會直接插入數(shù)據(jù)，這將導致表中出現(xiàn)重復的數(shù)據(jù)。

1. 下面看看binlog 解析：主鍵和唯一鍵同事存在的時候語句不一樣。
主鍵：是進行update操作。
主鍵+唯一鍵：delete+insert操作。

2. 但對于同一個數(shù)據(jù)所有行都一樣的時候replace into就不會進行更新操作。

INSERT INTO ON DUMPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE語句，并且要插入的行將導致惟一索引或主鍵中出現(xiàn)重復值，則會對舊行進行更新。但主鍵和唯一鍵同事存在的時候，選擇主鍵。
實際驗證流程如下：

總結(jié)

從基本Insert延伸到另外3種方式，理解了其用法，非常重要的。通過了解，可以在實際環(huán)境中有效的利用起來，要是盲目的投入使用，就會存在性能問題。

1. 在實際sysbench壓測中，硬件配置比較好的情況，混合&插入模式下 MySQL的單臺TPS能到 1w~6w的性能。insert也是有極限的，超過這個范圍的時候，會存在延遲等性能瓶頸。
2. REPLACE INTO性能中 delete insert索引頁分裂可能非常嚴重。需要注意
3. INSERT ON DUPLICATE KEY UPDATE如果一個表定義有多個唯一鍵或 主鍵同時存在時，是不安全的，這會引發(fā)操作錯誤，導致數(shù)據(jù)處理錯誤。
4. INSERT SELECT是 表之間遷移數(shù)據(jù)的很好的方式，但需要用帶索引的字段進行條件和排序限制。除此之外數(shù)據(jù)量多的時候，可以理解成一個大事務(wù)。

你點的每個贊，我都認真當成了喜歡