Categories: 生活

資料庫MYSQL大量更新、修改數據的語法有哪些？

圖翻拍自網路

我們都知道mysql中批量插入非常簡單，那麼批量更新呢？

1.IN

IN 語句有比較大的局限性，更新後的結果必須一致。比如下面是將所有滿足條件的行的狀態（status）設置為1。

如果想部分設置為1，部分設置為2等，則無法實現，或者通過寫多條SQL語句實現。

Update users Set status=1 Where account IN ('xx1', 'xx2');

2. For + Update

藉助 For 循環 + Update 語句，即逐一更新，優點是清晰直觀，適用於大部分情況，不易出錯。缺點是性能較差，容易造成堵塞。

如果是在MYSQL客戶端執行，這種方法很不方便。一般需要生成多條Update語句，或者可以用存儲過程實現。

3. Insert into…on duplicate key 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 |
+----+-------+------+-------+

4. Replace into

眾所周知，它是一個替換，相當於一個 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 只是更新重複記錄，不會改變其他欄位。

5. Set…case…when…where

優點：可以批量更新，也支持更新多個欄位，更新多個結果。
缺點：語句較長，實現起來比較麻煩，也比較容易出錯。

通常它是通過主鍵或唯一鍵更新的。

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子句。

6.創建臨時表

臨時表的方式是替換另一個表的數據，但是一般情況下我們是沒有創建表的權限的，所以這個想法可能不太現實。

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;

如果你有更多方法，請留言。

到訪人數：(178)