insert語句(MySQL中insert語句的使用方法)

一、insert的幾種語法

1-1.普通插入語句

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

這里不再贅述,注意順序即可,不建議小伙伴們去掉前面括號的內容,別問為什么,容易被同事罵。

1-2.插入或更新

如果我們希望插入一條新記錄(insert),但如果記錄已經存在,就更新該記錄,此時,可以使用"insert INTO … ON DUPLICATE KEY update …"語句:

情景示例:這張表存了用戶歷史充值金額,如果第一次充值就新增一條數據,如果該用戶充值過就累加歷史充值金額,需要保證單個用戶數據不重復錄入。

這時可以使用"insert INTO … ON DUPLICATE KEY update …"語句。

注意事項:"insert INTO … ON DUPLICATE KEY update …"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的。如下SQL所示,需要在username字段上建立唯一索引(Unique),transId設置自增即可。

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

若username='chenhaha'的記錄不存在,insert語句將插入新記錄,否則,當前username='chenhaha'的記錄將被更新,更新的字段由update指定。

對了,ON DUPLICATE KEY update為MySQL特有語法,比如在MySQL遷移Oracle或其他DB時,類似的語句要改為MERGE INTO語法,兼容性讓人想罵街。但沒辦法,就像用WPS寫的xlsx用Office無法打開一樣。

1-3.插入或替換

如果我們想插入一條新記錄(insert),但如果記錄已經存在,就先刪除原記錄,再插入新記錄。

情景示例:這張表存的每個客戶最近一次交易訂單信息,要求保證單個用戶數據不重復錄入,且執行效率最高,與數據庫交互最少,支撐數據庫的高可用。

此時,可以使用"replace INTO"語句,這樣就不必先查詢,再決定是否先刪除再插入。

"replace INTO"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的。
"replace INTO"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的。
"replace INTO"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的。

注意事項:如下SQL所示,需要在username字段上建立唯一索引(Unique),transId設置自增即可。

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

若username='chenhaha'的記錄不存在,replace語句將插入新記錄(首次充值),否則,當前username='chenhaha'的記錄將被刪除,然后再插入新記錄。

id不要給具體值,不然會影響SQL執行,業務有特殊需求除外。

小tips:
ON DUPLICATE KEY update:如果插入行出現唯一索引或者主鍵重復時,則執行舊的update;如果不會導致唯一索引或者主鍵重復時,就直接添加新行。
replace INTO:如果插入行出現唯一索引或者主鍵重復時,則delete老記錄,而錄入新的記錄;如果不會導致唯一索引或者主鍵重復時,就直接添加新行。

replace into 與 insert on deplicate udpate 比較:

1、在沒有主鍵或者唯一索引重復時,replace into 與 insert on deplicate udpate 相同。

2、在主鍵或者唯一索引重復時,replace是delete老記錄,而錄入新的記錄,所以原有的所有記錄會被清除,這個時候,如果replace語句的字段不全的話,有些原有的比如c字段的值會被自動填充為默認值(如Null)。

3、細心地朋友們會發現,insert on deplicate udpate只是影響一行,而replace INTO可能影響多行,為什么呢?寫在文章最后一節咯~

1-4.插入或忽略

如果我們希望插入一條新記錄(insert),但如果記錄已經存在,就啥事也不干直接忽略,此時,可以使用insert IGNORE INTO …語句:情景很多,不再舉例贅述。

注意事項:同上,"insert IGNORE INTO …"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的,需要在username字段上建立唯一索引(Unique),transId設置自增即可。

-- 用戶首次添加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');

二、大量數據插入

2-1、三種處理方式

2-1-1、單條循環插入

我們取10w條數據進行了一些測試,如果插入方式為程序遍歷循環逐條插入。在mysql上檢測插入一條的速度在0.01s到0.03s之間。

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

下面代碼是測試例子:

1普通循環插入100000條數據的時間測試

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

執行速度是30分鐘也就是0.018*100000的速度?？梢哉f是很慢了

發現逐條插入優化成本太高。然后去查詢優化方式。發現用批量插入的方法可以顯著提高速度。

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

2-1-2、修改SQL語句批量插入

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

用批量插入插入100000條數據,測試代碼如下:

 @Test public void insertUsers2() {     List<User> list= new ArrayList<User>();  User user = new User();  user.setPassword("正在送命");  user.setPrice(3150);  user.setHobby("種蘑菇");  for (int i = 0; i < 100000; i++) {   user.setUserName("提莫隊長" + i);   // 將單個對象放入參數list中   list.add(user);     }  userMapper.insertListUser(list); }

批量插入使用了0.046s 這相當于插入一兩條數據的速度,所以用批量插入會大大提升數據插入速度,當有較大數據插入操作是用批量插入優化

批量插入的寫法:

dao定義層方法:

Integer insertListUser(List<User> 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>

這樣就能進行批量插入操作:

注:但是當批量操作數據量很大的時候。例如我插入10w條數據的SQL語句要操作的數據包超過了1M,MySQL會報如下錯:

報錯信息:

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.

解釋:

用于查詢的數據包太大(6832997> 1048576)。 您可以通過設置max_allowed_packet的變量來更改服務器上的這個值。

通過解釋可以看到用于操作的包太大。這里要插入的SQL內容數據大小為6M 所以報錯。

解決方法:

數據庫是MySQL57,查了一下資料是MySQL的一個系統參數問題:

max_allowed_packet,其默認值為1048576(1M),

查詢:

show VARIABLES like '%max_allowed_packet%';

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

max_allowed_packet = 1M,如更改為20M(或更大,如果沒有這行內容,增加這一行),如下圖

保存,重啟MySQL服務?，F在可以執行size大于1M小于20M的SQL語句了。

但是如果20M也不夠呢?

2-1-3、分批量多次循環插入

如果不方便修改數據庫配置或需要插入的內容太多時,也可以通過后端代碼控制,比如插入10w條數據,分100批次每次插入1000條即可,也就是幾秒鐘而已;當然,如果每條的內容很多的話,另說。。

2-2、插入速度慢的其他幾種優化途徑

A、通過show processlist;命令,查詢是否有其他長進程或大量短進程搶占線程池資源 ?看能否通過把部分進程分配到備庫從而減輕主庫壓力;或者,先把沒用的進程kill掉一些?(手動撓頭o_O)

B、大批量導數據,也可以先關閉索引,數據導入完后再打開索引

關閉:ALTER TABLE user_info DISABLE KEYS;
開啟:ALTER TABLE user_info ENABLE KEYS;

三、replace INTO語法的“坑”

上面曾提到replace可能影響3條以上的記錄,這是因為在表中有超過一個的唯一索引。在這種情況下,replace將考慮每一個唯一索引,并對每一個索引對應的重復記錄都刪除,然后插入這條新記錄。假設有一個table1表,有3個字段a, b, c。它們都有一個唯一索引,會怎么樣呢?我們早一些數據測試一下。

-- 測試表創建,a,b,c三個字段均有唯一索引CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);-- 插入三條測試數據insert into table1 VALUES(1,1,1);insert into table1 VALUES(2,2,2);insert into table1 VALUES(3,3,3);

此時table1中已經有了3條記錄,a,b,c三個字段都是唯一(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語句向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)

此時查詢table1中的記錄如下,只剩一條數據了~

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

replace INTO語法回顧:如果插入行出現唯一索引或者主鍵重復時,則delete老記錄,而錄入新的記錄;如果不會導致唯一索引或者主鍵重復時,就直接添加新行。

我們可以看到,在用replace INTO時每個唯一索引都會有影響的,可能會造成誤刪數據的情況,因此建議不要在多唯一索引的表中使用replace INTO;

以上就是MySQL中insert語句的使用方法,小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注本站行業資訊頻道。