您好,登錄后才能下訂單哦!
本篇文章給大家分享的是有關(guān)MySQL中Insert語句的使用方法,小編覺得挺實(shí)用的,因此分享給大家學(xué)習(xí),希望大家閱讀完這篇文章后可以有所收獲,話不多說,跟著小編一起來看看吧。
INSERT INTO table (`a`, `b`, `c`, ……) VALUES ('a', 'b', 'c', ……);
這里不再贅述,注意順序即可,不建議小伙伴們?nèi)サ羟懊胬ㄌ?hào)的內(nèi)容,別問為什么,容易被同事罵。
如果我們希望插入一條新記錄(INSERT),但如果記錄已經(jīng)存在,就更新該記錄,此時(shí),可以使用"INSERT INTO … ON DUPLICATE KEY UPDATE …"語句:
情景示例:這張表存了用戶歷史充值金額,如果第一次充值就新增一條數(shù)據(jù),如果該用戶充值過就累加歷史充值金額,需要保證單個(gè)用戶數(shù)據(jù)不重復(fù)錄入。
這時(shí)可以使用"INSERT INTO … ON DUPLICATE KEY UPDATE …"語句。
注意事項(xiàng):"INSERT INTO … ON DUPLICATE KEY UPDATE …"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的。如下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語句將插入新記錄,否則,當(dāng)前username='chenhaha'的記錄將被更新,更新的字段由UPDATE指定。
對(duì)了,ON DUPLICATE KEY UPDATE為MySQL特有語法,比如在MySQL遷移Oracle或其他DB時(shí),類似的語句要改為MERGE INTO語法,兼容性讓人想罵街。但沒辦法,就像用WPS寫的xlsx用Office無法打開一樣。
如果我們想插入一條新記錄(INSERT),但如果記錄已經(jīng)存在,就先刪除原記錄,再插入新記錄。
情景示例:這張表存的每個(gè)客戶最近一次交易訂單信息,要求保證單個(gè)用戶數(shù)據(jù)不重復(fù)錄入,且執(zhí)行效率最高,與數(shù)據(jù)庫交互最少,支撐數(shù)據(jù)庫的高可用。
此時(shí),可以使用"REPLACE INTO"語句,這樣就不必先查詢,再?zèng)Q定是否先刪除再插入。
"REPLACE INTO"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的。
"REPLACE INTO"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的。
"REPLACE INTO"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的。
注意事項(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語句將插入新記錄(首次充值),否則,當(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、在沒有主鍵或者唯一索引重復(fù)時(shí),replace into 與 insert on deplicate udpate 相同。
2、在主鍵或者唯一索引重復(fù)時(shí),replace是delete老記錄,而錄入新的記錄,所以原有的所有記錄會(huì)被清除,這個(gè)時(shí)候,如果replace語句的字段不全的話,有些原有的比如c字段的值會(huì)被自動(dòng)填充為默認(rèn)值(如Null)。
3、細(xì)心地朋友們會(huì)發(fā)現(xiàn),insert on deplicate udpate只是影響一行,而REPLACE INTO可能影響多行,為什么呢?寫在文章最后一節(jié)咯~
如果我們希望插入一條新記錄(INSERT),但如果記錄已經(jīng)存在,就啥事也不干直接忽略,此時(shí),可以使用INSERT IGNORE INTO …語句:情景很多,不再舉例贅述。
注意事項(xiàng):同上,"INSERT IGNORE INTO …"語句是基于唯一索引或主鍵來判斷唯一(是否存在)的,需要在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');
我們?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ā)現(xiàn)逐條插入優(yōu)化成本太高。然后去查詢優(yōu)化方式。發(fā)現(xiàn)用批量插入的方法可以顯著提高速度。
將100000條數(shù)據(jù)的插入速度提升到1-2分鐘左右↓
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() { 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("提莫隊(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(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>
這樣就能進(jìn)行批量插入操作:
注:但是當(dāng)批量操作數(shù)據(jù)量很大的時(shí)候。例如我插入10w條數(shù)據(jù)的SQL語句要操作的數(shù)據(jù)包超過了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)。 您可以通過設(shè)置max_allowed_packet的變量來更改服務(wù)器上的這個(gè)值。
通過解釋可以看到用于操作的包太大。這里要插入的SQL內(nèi)容數(shù)據(jù)大小為6M 所以報(bào)錯(cuò)。
解決方法:
數(shù)據(jù)庫是MySQL57,查了一下資料是MySQL的一個(gè)系統(tǒng)參數(shù)問題:
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(或更大,如果沒有這行內(nèi)容,增加這一行),如下圖
保存,重啟MySQL服務(wù)?,F(xiàn)在可以執(zhí)行size大于1M小于20M的SQL語句了。
但是如果20M也不夠呢?
如果不方便修改數(shù)據(jù)庫配置或需要插入的內(nèi)容太多時(shí),也可以通過后端代碼控制,比如插入10w條數(shù)據(jù),分100批次每次插入1000條即可,也就是幾秒鐘而已;當(dāng)然,如果每條的內(nèi)容很多的話,另說。。
A、通過show processlist;命令,查詢是否有其他長(zhǎng)進(jìn)程或大量短進(jìn)程搶占線程池資源 ?看能否通過把部分進(jìn)程分配到備庫從而減輕主庫壓力;或者,先把沒用的進(jìn)程kill掉一些?(手動(dòng)撓頭o_O)
B、大批量導(dǎo)數(shù)據(jù),也可以先關(guān)閉索引,數(shù)據(jù)導(dǎo)入完后再打開索引
關(guān)閉:ALTER TABLE user_info DISABLE KEYS;
開啟:ALTER TABLE user_info ENABLE KEYS;
上面曾提到REPLACE可能影響3條以上的記錄,這是因?yàn)樵诒碇杏谐^一個(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語句向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)
REPLACE INTO語法回顧:如果插入行出現(xiàn)唯一索引或者主鍵重復(fù)時(shí),則delete老記錄,而錄入新的記錄;如果不會(huì)導(dǎo)致唯一索引或者主鍵重復(fù)時(shí),就直接添加新行。
我們可以看到,在用REPLACE INTO時(shí)每個(gè)唯一索引都會(huì)有影響的,可能會(huì)造成誤刪數(shù)據(jù)的情況,因此建議不要在多唯一索引的表中使用REPLACE INTO;
以上就是MySQL中Insert語句的使用方法,小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘9ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。