MySQL中如何使用插入和批量語句

這篇文章主要介紹了MySQL中如何使用插入和批量語句，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

前言

最常見的方式就是為字段設置主鍵或唯一索引，當插入重復數據時，拋出錯誤，程序終止，但這會給后續處理帶來麻煩，因此需要對插入語句做特殊處理，盡量避開或忽略異常，下面我簡單介紹一下，感興趣的朋友可以嘗試一下：

這里為了方便演示，我新建了一個user測試表，主要有id，username，sex，address這4個字段，其中主鍵為id（自增），同時對username字段設置了唯一索引（idx_username）

1.insert ignore into

即插入數據時，如果數據存在，則忽略此次插入，前提條件是插入的數據字段設置了主鍵或唯一索引，測試SQL語句如下，當插入本條數據時，MySQL數據庫會首先檢索已有數據（也就是idx_username索引），如果存在，則忽略本次插入，如果不存在，則正常插入數據：

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')

2.on duplicate key update

即插入數據時，如果數據存在，則執行更新操作，前提條件同上，也是插入的數據字段設置了主鍵或唯一索引，測試SQL語句如下，當插入本條記錄時，MySQL數據庫會首先檢索已有數據（idx_username索引），如果存在，則執行update更新操作，如果不存在，則直接插入：

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')
on duplicate key update
SEX='boy',address='HongKong'

3.replace into

即插入數據時，如果數據存在，則刪除再插入，前提條件同上，插入的數據字段需要設置主鍵或唯一索引，測試SQL語句如下，當插入本條記錄時，MySQL數據庫會首先檢索已有數據（idx_username索引），如果存在，則先刪除舊數據，然后再插入，如果不存在，則直接插入：

REPLACE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')

4.insert if not exists

即 insert into … select … where not exist ... ，這種方式適合于插入的數據字段沒有設置主鍵或唯一索引，當插入一條數據時，首先判斷MySQL數據庫中是否存在這條數據，如果不存在，則正常插入，如果存在，則忽略：

INSERT  INTO user (username,sex,address) 
SELECT 'hanpang','boy','HongKong' FROM user
WHERE NOT EXISTS (SELECT username FROM user WHERE username='hanpang')

5.批量插入數據

上述的插入語句，是可以使用批量插入語句，表數據結構：

CREATE TABLE example (
    example_id INT NOT NULL,
    name VARCHAR( 50 ) NOT NULL,
    value VARCHAR( 50 ) NOT NULL,
    other_value VARCHAR( 50 ) NOT NULL
)

個人習慣使用這種批量操作方式，進行SQL語句拼接，但是當你的字符串太長（百萬數據）的時候，需要你對mysql設置指令：

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

實際開發中，我們更加喜歡使用通過程序代碼進行批量添加操作（使用事務提交，批量插入數據庫），使用上述的方式在插入測試數據或者其他低要求時比較合適，速度確實快。

6.批量更新

（1）replace into 批量更新(記得要有主鍵或者索引)

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

（2）insert into ...on duplicate key update批量更新

使用INSERT的時候 有表T(id,A,B,C,D)

插入的時候希望通過A,B索引唯一記錄 ，有重復的時候更新C,D

INSERT INTO T(A,B,C,D) VALUES (a,b,c,d) ON DUPLICATE KEY UPDATE C=C+1，D=d

（3）使用mysql 自帶的語句構建批量更新

UPDATE yoiurtable
	SET dingdan = CASE id 
		WHEN 1 THEN 3 
		WHEN 2 THEN 4
		WHEN 3 THEN 5 
		END
WHERE id IN (1,2,3)

（4）創建臨時表，先更新臨時表，然后從臨時表中update

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL中如何使用插入和批量語句”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!