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 t;
DELETE FROM t;
ALTER TABLE t AUTO_INCREMENT = 1;
DELETE 慢且產生大量 Binlog/Undo Log,慎用。原文提到此法屬誤植,MySQL 無此命令,請忽略。
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);
}
}
}
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");
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) 自定義)。SELECT @@IDENTITY;
BIGINT UNSIGNED(42 億/1800 兆),防 ID 耗盡。NOT NULL + PRIMARY KEY(隱含唯一索引)。Auto_increment 接近上限告警,提前擴容/分表。