MySQL 序列實現指南：AUTO_INCREMENT 完整用法、獲取值、重排序、自定義起始值

核心機制：AUTO_INCREMENT 列

CREATE TABLE insect (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  date DATE NOT NULL,
  origin VARCHAR(30) NOT NULL
);

INSERT INTO insect (id, name, date, origin) VALUES
(NULL, 'housefly', '2001-09-10', 'kitchen'),
(NULL, 'millipede', '2001-09-10', 'driveway');
-- id 自動生成 1, 2

獲取最後插入 ID

SQL 函數

SELECT LAST_INSERT_ID();

PHP

mysql_query("INSERT ...", $conn);
$seq = mysql_insert_id($conn);

Perl (DBI)

$dbh->do("INSERT ...");
my $seq = $dbh->{mysql_insertid};

進階操作

1. 重新排序現有序列（謹慎！影響外鍵）

ALTER TABLE insect DROP id;
ALTER TABLE insect ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

2. 指定起始值

CREATE TABLE t (id INT AUTO_INCREMENT = 100 PRIMARY KEY, ...);
-- 或修改現有表
ALTER TABLE t AUTO_INCREMENT = 100;

3. 手動指定值（跳過自動分配）

INSERT INTO insect (id, name, ...) VALUES (100, 'custom');

複雜場景：多主鍵表的序列

CREATE TABLE animals (
  grp ENUM('mammal','bird') NOT NULL,
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  PRIMARY KEY (grp, id)
) ENGINE=MyISAM;
-- 每個 grp 獨立序列：mammal 1,2... bird 1,2...

InnoDB 注意事項

• 自增計數器僅在內存，重啟後重新掃描 MAX(id)
• innodb_autoinc_lock_mode 控制鎖模式（0/1/2），影響並發插入效能
• 8.0+ 支援 AUTO_INCREMENT 持久化到 redo log