摘要：在MySQL中，我們在執(zhí)行update或者insert語句時，可以借助select語句更新一個字段，使其值更新為該字段的最大值加1。

  在《MySQL中自增長序列(@i:=@i+1)的用處及用法》中，介紹了如何在select語句中生成遞增序列，《MySQL 把查詢結(jié)果更新或者插入到新表》介紹了把查詢到的多條記錄復(fù)制到另一張表中，均沒有介紹如何更新數(shù)據(jù)庫字段的值為當(dāng)前最大值加指定步長。所以在本文中，樓蘭胡楊將帶著大家了解如何更新某個字段的值為當(dāng)前最大值加指定步長1，步長也可以是其它滿足訴求的值。

創(chuàng)建測試表

  使用以下 MySQL 語句創(chuàng)建測試表test并寫入三條測試數(shù)據(jù)：

CREATE TABLE test (
  `id` bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID',
  incr_id INT NOT NULL COMMENT '遞增序列',
  creator varchar(30) NOT NULL COMMENT '創(chuàng)建者' 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '測試表';
INSERT INTO test(incr_id,creator) VALUES (FLOOR(1 + RAND() * 100),"張三");
INSERT INTO test(incr_id,creator) VALUES (FLOOR(1 + RAND() * 100),"張三豐");
INSERT INTO test(incr_id,creator) VALUES (FLOOR(1 + RAND() * 100),"樓蘭胡楊");

  表中incr_id用于演示如何更新它的值為其當(dāng)前最大值加1。當(dāng)然，令其如主鍵ID一樣支持AUTO_INCREMENT時，可以實現(xiàn)自增長，實現(xiàn)方案非常簡單，本文不再贅述。這里主要講述如何通過update select和insert select實現(xiàn)字段值遞增的策略。

update select實現(xiàn)字段遞增賦值

  首先使用聚合函數(shù)max來計算最大值，然后將其加1。以下SQL將返回test表中incr_id字段的最大值并且加1：

SELECT MAX(incr_id)+1 FROM test;

  運行此命令后，將返回一個包含最大值加1的單獨的列。接下來，樓蘭胡楊將此用作update語句的源值，以下實現(xiàn)方案基于嵌套子查詢完成：

UPDATE test 
SET incr_id = (
    SELECT max_incr FROM (
        SELECT MAX(incr_id) + 1 AS max_incr FROM test
    ) AS tmp
)
WHERE id = 3;

  在這個更新DML中，我們在子查詢語句中使用聚合函數(shù)max計算字段最大值，然后對其最大值加1。與前面的例子一樣，WHERE子句用于指定要更新的記錄。這個方案因為耗內(nèi)存、效率低而不適用于批量更新場景，但變量模擬自增方案適用于批量更新或復(fù)雜邏輯控制，實現(xiàn)腳本如下：

-- 自定義變量
SET @max_incr = (SELECT MAX(incr_id) + 1 FROM test);
-- 使用變量更新字段
UPDATE test SET incr_id = @max_incr WHERE id = 3;

insert select實現(xiàn)字段遞增賦值

  在insert操作中實現(xiàn)字段遞增賦值時，也是首先使用聚合函數(shù)max來計算最大值，然后將其加1。先介紹一下insert select語法糖：

INSERT INTO target_table (column1, column2, ...)
SELECT value1, value2, ...
FROM source_table_a a JOIN source_table_b b ON a.id = b.a_id
WHERE condition_clause;

• target_table：用于插入數(shù)據(jù)的目標(biāo)表。
• source_table_a 和 source_table_b：源表，從中選擇數(shù)據(jù)用于插入目標(biāo)表?？梢詮亩鄠€表中選擇數(shù)據(jù)并插入到目標(biāo)表中，可以一個表。
• value1, value2, …：被插入到目標(biāo)表的值，既可以是從源表中選擇的列，也可以是返回常量的表達(dá)式，更可以是一個常量。在編寫SELECT語句的時候，可以使用MySQL支持的全部語法。

  下面使用上述語法糖新增一條記錄，并且令incr_id字段的新值為其最大值加1：

INSERT INTO test ( `incr_id`, `creator`) 
SELECT IFNULL(max(incr_id), 0) + 1, '遞增序列' FROM test;

  IFNULL是專門處理 NULL 值的。若目標(biāo)表的某些列不允許為 NULL，而假設(shè) SELECT 查詢返回 NULL 值，會導(dǎo)致數(shù)據(jù)插入操作失敗。這種書寫格式與如下常見VALUES寫法區(qū)別很大：

INSERT INTO test(incr_id) VALUES (FLOOR(1 + RAND() * 100),"普通insert操作");

小結(jié)

  本篇文章的內(nèi)容基本上就是這些，我們來復(fù)盤一下。在本文中，我們提供了一些使用MySQL select語句更新字段的示例，這些DML可以使用聚合函數(shù)或者嵌套子查詢來計算要設(shè)置的新值。各位老鐵無論選擇哪種方式，都需要確保更新操作僅更新需要更新的記錄，否則您可能會無意中、錯誤地?更改了整張表的記錄。樓蘭胡楊還提到，如果您的表包含大量記錄，使用SELECT子查詢操作可能會比較慢，而借助自定義變量計算最大值的方案可能更輕快。

到此這篇關(guān)于MySQL 更新字段的值為當(dāng)前最大值加1的文章就介紹到這了,更多相關(guān)MySQL 更新字段的值為當(dāng)前最大值加1內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論