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;

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

到訪人數:(167)

文章部分內容及圖片來源於網絡,如果侵犯到您的隱私、權益、請留言檢舉,並告知是哪一篇,本站將在第一時間進行處理,謝謝合作!留言版
宅男生活