MySQL 查詢與修改 AUTO_INCREMENT 值：information_schema 與 ALTER TABLE

MySQL 查詢與修改 AUTO_INCREMENT 值：information_schema 與 ALTER TABLE

自增主鍵達上限、測試需重置、遷移合併需對齊 ID 等場景，需查詢當前值與手動調整。標準方法僅需兩條 SQL。

1. 查詢當前 AUTO_INCREMENT 值

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name'
  AND TABLE_NAME = 'table_name';

回傳下一個將分配的值 (即當前最大 ID + 1，或手動設定後的值)。

2. 修改 AUTO_INCREMENT 起始值

ALTER TABLE table_name AUTO_INCREMENT = 10000;

規則：

• 新值必須 > 當前表中最大主鍵值，否則無效 (MySQL 會忽略並保持原值)。
• 不支援減小 AUTO_INCREMENT (需重建表或 TRUNCATE)。
• InnoDB 重啟後會重新計算 (Max PK + 1)，持久化修改需 8.0+ innodb_autoinc_lock_mode=2 配合。

3. 實戰場景範例

場景 A：分庫分表 ID 不衝突預留範圍

-- 分片 1
ALTER TABLE shard_1.users AUTO_INCREMENT = 1000000000;
-- 分片 2
ALTER TABLE shard_2.users AUTO_INCREMENT = 2000000000;

場景 B：測試環境重置 ID 從 1 開始

TRUNCATE TABLE test_table;  -- 重置自增計數器為 1 (同時清空資料)
-- 或保留資料但重置 (需無資料或最大 ID < 新值)
DELETE FROM test_table;
ALTER TABLE test_table AUTO_INCREMENT = 1;

場景 C：主從/雙主複寫避免 ID 衝突

-- Master 1 (奇數)
SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 1;
-- Master 2 (偶數)
SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 2;

4. 監控自增 ID 接近上限告警

SELECT
  TABLE_SCHEMA, TABLE_NAME,
  AUTO_INCREMENT,
  CASE
    WHEN DATA_TYPE LIKE 'tinyint%' THEN 255
    WHEN DATA_TYPE LIKE 'smallint%' THEN 65535
    WHEN DATA_TYPE LIKE 'mediumint%' THEN 16777215
    WHEN DATA_TYPE LIKE 'int%' THEN 4294967295
    WHEN DATA_TYPE LIKE 'bigint%' THEN 18446744073709551615
  END AS MAX_VALUE,
  ROUND(AUTO_INCREMENT / 
    CASE
      WHEN DATA_TYPE LIKE 'tinyint%' THEN 255
      WHEN DATA_TYPE LIKE 'smallint%' THEN 65535
      WHEN DATA_TYPE LIKE 'mediumint%' THEN 16777215
      WHEN DATA_TYPE LIKE 'int%' THEN 4294967295
      WHEN DATA_TYPE LIKE 'bigint%' THEN 18446744073709551615
    END * 100, 2) AS USAGE_PCT
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
  AND t.TABLE_NAME = c.TABLE_NAME
  AND c.COLUMN_KEY = 'PRI'
  AND c.EXTRA = 'auto_increment'
WHERE t.AUTO_INCREMENT IS NOT NULL
  AND t.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
HAVING USAGE_PCT > 80
ORDER BY USAGE_PCT DESC;

5. 相關變數