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

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

自增主鍵當前值查詢與重置是分庫分表、數據遷移、主從同步修復的高頻操作。兩條標準 SQL 搞定。

查詢當前自增值

SELECT AUTO_INCREMENT 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_db' 
  AND TABLE_NAME = 'your_table';

• 返回下一條 INSERT 將使用的自增值。
• 需 SELECT 權限於 information_schema。

修改自增值

ALTER TABLE your_table AUTO_INCREMENT = 10000;

• 新值必須 > 當前表中最大主鍵值，否則無效（不報錯但不生效）。
• 不鎖表（MDL 意向鎖極短），業務無感。
• 分庫分表場景：各分片預留步長（如 10000、20000、30000）避免主鍵衝突。

常見場景

1. 數據遷移後重置：導入歷史數據後，將自增值設為 MAX(id) + 1。
2. 主從同步修復：Slave 自增值偏移，手動對齊。
3. 測試環境清理：TRUNCATE TABLE 自動歸 1，或手動 ALTER ... AUTO_INCREMENT = 1。
4. 預留 ID 空間：新表預設 AUTO_INCREMENT = 1000000 避免與舊系統 ID 衝突。

驗證腳本

#!/bin/bash
DB=test_db
TBL=orders
NEXT=$(mysql -N -e "SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DB' AND TABLE_NAME='$TBL';")
echo "Next auto_increment: $NEXT"
MAX_ID=$(mysql -N -e "SELECT MAX(id) FROM $DB.$TBL;")
echo "Current max id: $MAX_ID"
if [ "$NEXT" -le "$MAX_ID" ]; then
    echo "WARNING: auto_increment <= max_id, fixing..."
    mysql -e "ALTER TABLE $DB.$TBL AUTO_INCREMENT = $((MAX_ID + 1));"
fi

自增值管理是分布式架構下的基建技能。information_schema 查、ALTER TABLE 改，兩條命令走天下。