圖翻拍自網路
我們都知道mysql中批量插入非常簡單,那麼批量更新呢?
IN 語句有比較大的局限性,更新後的結果必須一致。比如下面是將所有滿足條件的行的狀態(status)設置為1。
如果想部分設置為1,部分設置為2等,則無法實現,或者通過寫多條SQL語句實現。
Update users Set status=1 Where account IN ('xx1', 'xx2');
藉助 For 循環 + Update 語句,即逐一更新,優點是清晰直觀,適用於大部分情況,不易出錯。缺點是性能較差,容易造成堵塞。
如果是在MYSQL客戶端執行,這種方法很不方便。一般需要生成多條Update語句,或者可以用存儲過程實現。
利用主鍵(或唯一鍵)的唯一性進行更新的好處是支持批量更新,更新結果不需要保持一致。缺點是一般第三方庫不支持這種語法,需要寫原生SQL,所有欄位必須有默認值(包括NULL)。
create table users
(
id int(11) PRIMARY KEY AUTO_INCREMENT,
name varchar(255) NOT NUll DEFAULT '',
age smallint,
job varchar(255)
);
INSERT INTO go_business.users (id, name, age, job) VALUES (1, 'name1', 1, 'job1');
INSERT INTO go_business.users (id, name, age, job) VALUES (2, 'namw2', 2, 'job2');
INSERT INTO go_business.users (id, name, age, job) VALUES (3, 'name3', 3, 'job3');
INSERT INTO go_business.users (id, name, age, job) VALUES (4, 'name4', 4, 'job4');
INSERT INTO go_business.users (id, name, age, job) VALUES (5, 'name5', 5, 'job5');
mysql> insert into users (id, job, age) values (1, 'job11', 11),(2, 'job22', 22)
on duplicate key update job=values(job), age=values(age);
mysql> select * from users where id in (1, 2);
+----+-------+------+-------+
| id | name | age | job |
+----+-------+------+-------+
| 1 | name1 | 11 | job11 |
| 2 | namw2 | 22 | job22 |
+----+-------+------+-------+
眾所周知,它是一個替換,相當於一個 update。語法類似於第三種方法,但比第三種方法更危險,因為更新時如果欄位不完整,未覆蓋的欄位將被設置為默認值。
replace into users(id, job, age) VALUES (1, 'job111', 111),(2, 'job222', 222);
mysql> select * from users where id in (1, 2);
+----+------+------+--------+
| id | name | age | job |
+----+------+------+--------+
| 1 | | 111 | job111 |
| 2 | | 222 | job222 |
+----+------+------+--------+
2 rows in set (0.00 sec)
原因是 replace into 操作的本質是先刪除重複記錄再插入,所以如果更新的欄位不完整,缺失的欄位會被設置為默認值,而 insert into 只是更新重複記錄,不會改變其他欄位。
通常它是通過主鍵或唯一鍵更新的。
update users
set job = case id
when 1 then 'job11'
when 2 then 'job12'
end,
age = case id
when 1 then 11
when 2 then 12
end
where id IN (1, 2);
mysql> select * from users where id in (1, 2);
+----+-------+------+-------+
| id | name | age | job |
+----+-------+------+-------+
| 1 | name1 | 11 | job11 |
| 2 | name2 | 12 | job12 |
+----+-------+------+-------+
一般這種方式也比較容易出錯,主要有兩種:
update users
set job = case id
when 1 then 'job11'
when 3 then 'job13'
end,
age = case id
when 1 then 11
when 2 then 12
end
where id IN (1, 2);
select * from users where id in (1, 2);
+----+-------+------+-------+
| id | name | age | job |
+----+-------+------+-------+
| 1 | name1 | 11 | job11 |
| 2 | name2 | 12 | NULL |
+----+-------+------+-------+
update users
set job = case id
when 1 then 'job11'
when 2 then 'job12'
end,
age = case id
when 1 then 11
when 2 then 12
end;
select * from users;
+----+-------+------+-------+
| id | name | age | job |
+----+-------+------+-------+
| 1 | name1 | 11 | job11 |
| 2 | name2 | 12 | job12 |
| 3 | name3 | NULL | NULL |
| 4 | name4 | NULL | NULL |
| 5 | name5 | NULL | NULL |
+----+-------+------+-------+
通過上面的測試,我們可以看出這種操作方式是相當危險的。一不小心,欄位就會更新為默認值,所以使用的時候一定要非常小心,一定不能漏掉Where子句。
臨時表的方式是替換另一個表的數據,但是一般情況下我們是沒有創建表的權限的,所以這個想法可能不太現實。
create temporary table users_tmp
(
id int(11) PRIMARY KEY AUTO_INCREMENT,
age smallint,
job varchar(255)
);
insert into users_tmp(id, job, age) values (1, 'job11', 11), (2, 'job22', 22);
update users, users_tmp set users.job=users_tmp.job, users.age=users_tmp.age where users.id=users_tmp.id;
如果你有更多方法,請留言。
到訪人數:(167)