MySQL實(shí)戰(zhàn)之Insert語(yǔ)句的使用心得

一、Insert的幾種語(yǔ)法

1-1.普通插入語(yǔ)句

INSERT INTO table (`a`, `b`, `c`, ……) VALUES ('a', 'b', 'c', ……);

這里不再贅述，注意順序即可，不建議小伙伴們?nèi)サ羟懊胬ㄌ?hào)的內(nèi)容，別問(wèn)為什么，容易被同事罵。

1-2.插入或更新

如果我們希望插入一條新記錄（INSERT），但如果記錄已經(jīng)存在，就更新該記錄，此時(shí)，可以使用"INSERT INTO … ON DUPLICATE KEY UPDATE …"語(yǔ)句：

情景示例：這張表存了用戶歷史充值金額，如果第一次充值就新增一條數(shù)據(jù)，如果該用戶充值過(guò)就累加歷史充值金額，需要保證單個(gè)用戶數(shù)據(jù)不重復(fù)錄入。

這時(shí)可以使用"INSERT INTO … ON DUPLICATE KEY UPDATE …"語(yǔ)句。

注意事項(xiàng)："INSERT INTO … ON DUPLICATE KEY UPDATE …"語(yǔ)句是基于唯一索引或主鍵來(lái)判斷唯一（是否存在）的。如下SQL所示，需要在username字段上建立唯一索引（Unique），transId設(shè)置自增即可。

-- 用戶陳哈哈充值了30元買會(huì)員
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) 
 VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '充會(huì)員') 
 ON DUPLICATE KEY UPDATE total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='充會(huì)員';
 
-- 用戶陳哈哈充值了100元買瞎子至高之拳皮膚
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) 
 VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', '購(gòu)買盲僧至高之拳皮膚') 
 ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='購(gòu)買盲僧至高之拳皮膚';

若username='chenhaha'的記錄不存在，INSERT語(yǔ)句將插入新記錄，否則，當(dāng)前username='chenhaha'的記錄將被更新，更新的字段由UPDATE指定。

對(duì)了，ON DUPLICATE KEY UPDATE為MySQL特有語(yǔ)法，比如在MySQL遷移Oracle或其他DB時(shí)，類似的語(yǔ)句要改為MERGE INTO語(yǔ)法，兼容性讓人想罵街。但沒(méi)辦法，就像用WPS寫的xlsx用Office無(wú)法打開(kāi)一樣。

1-3.插入或替換

如果我們想插入一條新記錄（INSERT），但如果記錄已經(jīng)存在，就先刪除原記錄，再插入新記錄。

情景示例：這張表存的每個(gè)客戶最近一次交易訂單信息，要求保證單個(gè)用戶數(shù)據(jù)不重復(fù)錄入，且執(zhí)行效率最高，與數(shù)據(jù)庫(kù)交互最少，支撐數(shù)據(jù)庫(kù)的高可用。

此時(shí)，可以使用"REPLACE INTO"語(yǔ)句，這樣就不必先查詢，再?zèng)Q定是否先刪除再插入。

"REPLACE INTO"語(yǔ)句是基于唯一索引或主鍵來(lái)判斷唯一（是否存在）的。
"REPLACE INTO"語(yǔ)句是基于唯一索引或主鍵來(lái)判斷唯一（是否存在）的。
"REPLACE INTO"語(yǔ)句是基于唯一索引或主鍵來(lái)判斷唯一（是否存在）的。

注意事項(xiàng)：如下SQL所示，需要在username字段上建立唯一索引（Unique），transId設(shè)置自增即可。

-- 20點(diǎn)充值
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
 VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '會(huì)員充值');
 
-- 21點(diǎn)買皮膚
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
 VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', '購(gòu)買盲僧至高之拳皮膚');

若username='chenhaha'的記錄不存在，REPLACE語(yǔ)句將插入新記錄（首次充值），否則，當(dāng)前username='chenhaha'的記錄將被刪除，然后再插入新記錄。

id不要給具體值，不然會(huì)影響SQL執(zhí)行，業(yè)務(wù)有特殊需求除外。

小tips：
ON DUPLICATE KEY UPDATE：如果插入行出現(xiàn)唯一索引或者主鍵重復(fù)時(shí)，則執(zhí)行舊的update；如果不會(huì)導(dǎo)致唯一索引或者主鍵重復(fù)時(shí)，就直接添加新行。
REPLACE INTO：如果插入行出現(xiàn)唯一索引或者主鍵重復(fù)時(shí)，則delete老記錄，而錄入新的記錄；如果不會(huì)導(dǎo)致唯一索引或者主鍵重復(fù)時(shí)，就直接添加新行。

replace into 與 insert on deplicate udpate 比較：

1、在沒(méi)有主鍵或者唯一索引重復(fù)時(shí)，replace into 與 insert on deplicate udpate 相同。

2、在主鍵或者唯一索引重復(fù)時(shí)，replace是delete老記錄，而錄入新的記錄，所以原有的所有記錄會(huì)被清除，這個(gè)時(shí)候，如果replace語(yǔ)句的字段不全的話，有些原有的比如c字段的值會(huì)被自動(dòng)填充為默認(rèn)值（如Null）。

3、細(xì)心地朋友們會(huì)發(fā)現(xiàn)，insert on deplicate udpate只是影響一行，而REPLACE INTO可能影響多行，為什么呢？寫在文章最后一節(jié)咯~

1-4.插入或忽略

如果我們希望插入一條新記錄（INSERT），但如果記錄已經(jīng)存在，就啥事也不干直接忽略，此時(shí)，可以使用INSERT IGNORE INTO …語(yǔ)句：情景很多，不再舉例贅述。

注意事項(xiàng)：同上，"INSERT IGNORE INTO …"語(yǔ)句是基于唯一索引或主鍵來(lái)判斷唯一（是否存在）的，需要在username字段上建立唯一索引（Unique），transId設(shè)置自增即可。

-- 用戶首次添加
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
 VALUES (null, 'chenhaha', '男', 26, 0, '2020-06-11 20:00:20');
 
-- 二次添加，直接忽略
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
 VALUES (null, 'chenhaha', '男', 26, 0, '2020-06-11 21:00:20');

二、大量數(shù)據(jù)插入

2-1、三種處理方式

2-1-1、單條循環(huán)插入

我們?nèi)?0w條數(shù)據(jù)進(jìn)行了一些測(cè)試，如果插入方式為程序遍歷循環(huán)逐條插入。在mysql上檢測(cè)插入一條的速度在0.01s到0.03s之間。

逐條插入的平均速度是0.02*100000，也就是33分鐘左右。

下面代碼是測(cè)試?yán)樱?/p>

1普通循環(huán)插入100000條數(shù)據(jù)的時(shí)間測(cè)試

 @Test
 public void insertUsers1() {
  
  User user = new User();
  
  user.setUserName("提莫隊(duì)長(zhǎng)");
  user.setPassword("正在送命");
  user.setPrice(3150);
  user.setHobby("種蘑菇");
  
  for (int i = 0; i  100000; i++) {
   user.setUserName("提莫隊(duì)長(zhǎng)" + i);
   // 調(diào)用插入方法
   userMapper.insertUser(user);
  }
 }

執(zhí)行速度是30分鐘也就是0.018*100000的速度?？梢哉f(shuō)是很慢了

發(fā)現(xiàn)逐條插入優(yōu)化成本太高。然后去查詢優(yōu)化方式。發(fā)現(xiàn)用批量插入的方法可以顯著提高速度。

將100000條數(shù)據(jù)的插入速度提升到1-2分鐘左右↓

2-1-2、修改SQL語(yǔ)句批量插入

insert into user_info （user_id,username,password,price,hobby）
 values （null,'提莫隊(duì)長(zhǎng)1','123456',3150,'種蘑菇'）,(null,'蓋倫','123456',450,'踩蘑菇');

用批量插入插入100000條數(shù)據(jù)，測(cè)試代碼如下：

 @Test
 public void insertUsers2() {
   
  ListUser> list= new ArrayListUser>();
		
  User user = new User();
  user.setPassword("正在送命");
  user.setPrice(3150);
  user.setHobby("種蘑菇");
		
  for (int i = 0; i  100000; i++) {
   user.setUserName("提莫隊(duì)長(zhǎng)" + i);
   // 將單個(gè)對(duì)象放入?yún)?shù)list中
   list.add(user);
   
  }
  userMapper.insertListUser(list);
 }

批量插入使用了0.046s 這相當(dāng)于插入一兩條數(shù)據(jù)的速度，所以用批量插入會(huì)大大提升數(shù)據(jù)插入速度，當(dāng)有較大數(shù)據(jù)插入操作是用批量插入優(yōu)化

批量插入的寫法：

dao定義層方法:

Integer insertListUser(ListUser> user);

mybatis Mapper中的sql寫法：

insert id="insertListUser" parameterType="java.util.List">
  INSERT INTO `db`.`user_info`
   ( `id`,
    `username`,
    `password`,
    `price`,
    `hobby`) 
   values
  foreach collection="list" item="item" separator="," index="index">
   (null,
   #{item.userName},
   #{item.password},
   #{item.price},
   #{item.hobby})
  /foreach>
 
 /insert>

這樣就能進(jìn)行批量插入操作：

注：但是當(dāng)批量操作數(shù)據(jù)量很大的時(shí)候。例如我插入10w條數(shù)據(jù)的SQL語(yǔ)句要操作的數(shù)據(jù)包超過(guò)了1M，MySQL會(huì)報(bào)如下錯(cuò):

報(bào)錯(cuò)信息：

Mysql You can change this value on the server by setting the max_allowed_packet' variable. Packet for query is too large (6832997 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

解釋：

用于查詢的數(shù)據(jù)包太大（6832997> 1048576）。 您可以通過(guò)設(shè)置max_allowed_packet的變量來(lái)更改服務(wù)器上的這個(gè)值。

通過(guò)解釋可以看到用于操作的包太大。這里要插入的SQL內(nèi)容數(shù)據(jù)大小為6M 所以報(bào)錯(cuò)。

解決方法：

數(shù)據(jù)庫(kù)是MySQL57，查了一下資料是MySQL的一個(gè)系統(tǒng)參數(shù)問(wèn)題:

max_allowed_packet，其默認(rèn)值為1048576(1M)，

查詢：

show VARIABLES like '%max_allowed_packet%';

修改此變量的值：MySQL安裝目錄下的my.ini(windows)或/etc/mysql.cnf(linux) 文件中的[mysqld]段中的

max_allowed_packet = 1M，如更改為20M(或更大，如果沒(méi)有這行內(nèi)容，增加這一行)，如下圖

保存，重啟MySQL服務(wù)。現(xiàn)在可以執(zhí)行size大于1M小于20M的SQL語(yǔ)句了。

但是如果20M也不夠呢？

2-1-3、分批量多次循環(huán)插入

如果不方便修改數(shù)據(jù)庫(kù)配置或需要插入的內(nèi)容太多時(shí)，也可以通過(guò)后端代碼控制，比如插入10w條數(shù)據(jù)，分100批次每次插入1000條即可，也就是幾秒鐘而已；當(dāng)然，如果每條的內(nèi)容很多的話，另說(shuō)。。

2-2、插入速度慢的其他幾種優(yōu)化途徑

A、通過(guò)show processlist;命令，查詢是否有其他長(zhǎng)進(jìn)程或大量短進(jìn)程搶占線程池資源 ？看能否通過(guò)把部分進(jìn)程分配到備庫(kù)從而減輕主庫(kù)壓力；或者，先把沒(méi)用的進(jìn)程kill掉一些？（手動(dòng)撓頭o_O）

B、大批量導(dǎo)數(shù)據(jù)，也可以先關(guān)閉索引，數(shù)據(jù)導(dǎo)入完后再打開(kāi)索引

關(guān)閉：ALTER TABLE user_info DISABLE KEYS;
開(kāi)啟：ALTER TABLE user_info ENABLE KEYS;

三、REPLACE INTO語(yǔ)法的“坑”

上面曾提到REPLACE可能影響3條以上的記錄，這是因?yàn)樵诒碇杏谐^(guò)一個(gè)的唯一索引。在這種情況下，REPLACE將考慮每一個(gè)唯一索引，并對(duì)每一個(gè)索引對(duì)應(yīng)的重復(fù)記錄都刪除，然后插入這條新記錄。假設(shè)有一個(gè)table1表，有3個(gè)字段a, b, c。它們都有一個(gè)唯一索引，會(huì)怎么樣呢？我們?cè)缫恍?shù)據(jù)測(cè)試一下。

-- 測(cè)試表創(chuàng)建，a,b,c三個(gè)字段均有唯一索引
CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
-- 插入三條測(cè)試數(shù)據(jù)
INSERT into table1 VALUES(1,1,1);
INSERT into table1 VALUES(2,2,2);
INSERT into table1 VALUES(3,3,3);

此時(shí)table1中已經(jīng)有了3條記錄，a,b,c三個(gè)字段都是唯一（UNIQUE）索引

mysql> select * from table1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+---+---+---+
3 rows in set (0.00 sec)

下面我們使用REPLACE語(yǔ)句向table1中插入一條記錄。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

mysql> REPLACE INTO table1(a, b, c) VALUES(1,2,3);
Query OK, 4 rows affected (0.04 sec)

此時(shí)查詢table1中的記錄如下，只剩一條數(shù)據(jù)了~

mysql> select * from table1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
1 row in set (0.00 sec)

（老板：插入前10w數(shù)據(jù)，插入5w數(shù)據(jù)后還剩8w數(shù)據(jù)？？，咱們家數(shù)據(jù)讓你喂狗了嗎?。。?/p>

REPLACE INTO語(yǔ)法回顧：如果插入行出現(xiàn)唯一索引或者主鍵重復(fù)時(shí)，則delete老記錄，而錄入新的記錄；如果不會(huì)導(dǎo)致唯一索引或者主鍵重復(fù)時(shí)，就直接添加新行。

我們可以看到，在用REPLACE INTO時(shí)每個(gè)唯一索引都會(huì)有影響的，可能會(huì)造成誤刪數(shù)據(jù)的情況，因此建議不要在多唯一索引的表中使用REPLACE INTO；

總結(jié)

到此這篇關(guān)于MySQL實(shí)戰(zhàn)之Insert語(yǔ)句的使用心得的文章就介紹到這了,更多相關(guān)MySQL Insert語(yǔ)句使用心得內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！