MySQL autoincrement column jumps by 10 - why?

2024-04-07

MySQLのAUTO_INCREMENT列が10ずつ増加する理由

原因

10ずつ増加する主な原因は次の3つです。

auto_increment_increment 設定

MySQLには、auto_increment_increment という設定があり、これはAUTO_INCREMENT列の増加量を指定します。デフォルトは1ですが、10などに設定することも可能です。

SET auto_increment_increment = 10;

レプリケーション

複数のMySQLサーバー間でデータを同期するレプリケーションを使用している場合、AUTO_INCREMENT列の値が衝突しないように、auto_increment_increment が1より大きい値に設定されることがあります。

ロールバック

INSERT文を実行中にエラーが発生し、ロールバックされた場合、AUTO_INCREMENT列の値がすでに割り当てられていたとしても解放されないことがあります。その結果、次回のINSERT時に10ずつ増加する可能性があります。

解決方法

10ずつ増加するのを防ぐためには、以下の方法があります。

auto_increment_increment 設定が10になっている場合は、1に変更します。

SET auto_increment_increment = 1;

レプリケーション設定を確認する

レプリケーションを使用している場合は、auto_increment_increment 設定が適切に設定されていることを確認します。

ロールバック時の処理を検討する

ロールバックが発生した場合でも、AUTO_INCREMENT列の値を解放するようにアプリケーション側で処理を検討します。

AUTO_INCREMENT列は、主キーとして使用されることが多いですが、必ずしも必要ではありません。
AUTO_INCREMENT列の値は、手動で変更することも可能です。
AUTO_INCREMENT列に関する詳細は、MySQLの公式ドキュメントを参照してください。

auto_increment_increment 設定の確認

SHOW VARIABLES LIKE 'auto_increment_increment';

auto_increment_increment 設定の変更

SET auto_increment_increment = 1;

レプリケーション設定の確認

SHOW VARIABLES LIKE 'slave_auto_increment_increment';

ロールバック時の処理

def insert_data(data):
    try:
        # データ挿入
        connection.cursor().execute(
            "INSERT INTO table (name, age) VALUES (?, ?)",
            (data["name"], data["age"])
        )
        connection.commit()
    except Exception as e:
        # ロールバック時の処理
        connection.rollback()
        raise e

# データ挿入
insert_data({"name": "John Doe", "age": 30})

主キーとして使用しない場合

CREATE TABLE table (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (name)
);

手動での変更

UPDATE table SET id = 100 WHERE name = 'John Doe';

SEQUENCE オブジェクトの使用

CREATE SEQUENCE seq_id START WITH 1 INCREMENT BY 10;

CREATE TABLE table (
    id INT NOT NULL DEFAULT NEXT VALUE FOR seq_id,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL
);

トリガーを使用して、INSERT文が実行されるたびに、AUTO_INCREMENT列の値を10ずつ増加させることもできます。

CREATE TRIGGER before_insert
BEFORE INSERT ON table
FOR EACH ROW
SET NEW.id = NEW.id + 10;

アプリケーション側で、AUTO_INCREMENT列の値を10ずつ増加させる処理を実装することもできます。

def insert_data(data):
    # 現在の最大値を取得
    max_id = connection.cursor().execute("SELECT MAX(id) FROM table")

    # 10ずつ増加
    data["id"] = max_id + 10

    # データ挿入
    connection.cursor().execute(
        "INSERT INTO table (id, name, age) VALUES (?, ?, ?)",
        (data["id"], data["name"], data["age"])
    )
    connection.commit()

# データ挿入
insert_data({"name": "John Doe", "age": 30})