萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 自增主鍵初始值設定、歸零、獲取四大法(JDBC/LAST_INSERT_ID/@@IDENTITY)詳解

設定自增起始值

建表時指定

CREATE TABLE t (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
) AUTO_INCREMENT = 1001;

建表後修改

ALTER TABLE t AUTO_INCREMENT = 1001;

限制:新值必須 > 當前最大 ID,否則無效。查看當前值:SHOW TABLE STATUS LIKE 't'\G (看 Auto_increment 欄位)。

自增歸零/重置

方法一:TRUNCATE TABLE(清空數據 + 重置自增)

TRUNCATE TABLE t;
  • DDL 操作,不可回滾,極快,釋放空間。
  • 觸發器不會觸發,外鍵約束會阻礙(需先刪外鍵)。

方法二:DELETE + ALTER(保留表結構/權限/觸發器)

DELETE FROM t;
ALTER TABLE t AUTO_INCREMENT = 1;
  • DML 可回滾,保留表元資料。
  • 大表 DELETE 慢且產生大量 Binlog/Undo Log,慎用。

方法三:DBCC CHECKIDENT(SQL Server 語法,MySQL 不支援)

原文提到此法屬誤植,MySQL 無此命令,請忽略。

獲取剛插入的自增 ID(四大法)

1. JDBC 3.0 getGeneratedKeys()(標準、推薦)

try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement(
         "INSERT INTO t(name) VALUES(?)", Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, "test");
    ps.executeUpdate();
    try (ResultSet rs = ps.getGeneratedKeys()) {
        if (rs.next()) {
            long id = rs.getLong(1);
            System.out.println("Generated ID: " + id);
        }
    }
}

2. JDBC 2.0 insertRow()(游標操作,罕用)

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT id, name FROM t");
rs.moveToInsertRow();
rs.updateString("name", "test");
rs.insertRow();
rs.last();  // 移到剛插入行
long id = rs.getLong("id");

3. SQL 函數 LAST_INSERT_ID()(會話級、連線池安全)

INSERT INTO t(name) VALUES('test');
SELECT LAST_INSERT_ID();  -- 返回當前會話最後一次 INSERT 產生的自增值
  • **連線池安全**:每個連線獨立維護,互不干擾。
  • 批次插入 INSERT ... VALUES(...),(...),(...) 只返回**第一行**自增值。
  • INSERT ... ON DUPLICATE KEY UPDATE 觸發更新時,LAST_INSERT_ID() 返回受影響行的自增值(可配合 LAST_INSERT_ID(expr) 自定義)。

4. 會話變數 @@IDENTITY(同 LAST_INSERT_ID,別名)

SELECT @@IDENTITY;

自增主鍵設計鐵律

  1. 型別用 BIGINT UNSIGNED(42 億/1800 兆),防 ID 耗盡。
  2. 務必 NOT NULL + PRIMARY KEY(隱含唯一索引)。
  3. 業務主鍵(如訂單號)勿用自增,用雪花演算法/區段號+序列,避免洩露業務量、便於分庫分表。
  4. 監控 Auto_increment 接近上限告警,提前擴容/分表。
copyright © 萬盛學電腦網 all rights reserved