AUTO_INCREMENT自定义起始值不生效，设置为2却从1开始

Hey there, let's figure out why your auto-increment isn't starting at 2 as expected.

First, let's start with checking the actual current auto-increment value of your table—sometimes the CREATE TABLE setting doesn't stick as we think. Run this query:

SHOW TABLE STATUS LIKE 'object' FROM db;

Look at the Auto_increment column in the result. If it shows 1 instead of 2, that's the root of the problem. Here are the most common reasons and fixes:

Common Causes & Solutions #

• Old table data lingering: If you had a previous db.object table that you dropped, InnoDB might have left some residual metadata that messed up the new table's auto-increment start. To fix this, make sure you fully drop the old table before creating the new one:

  DROP TABLE IF EXISTS db.object;
  CREATE TABLE db.object ( 
      `objid` bigint(20) NOT NULL AUTO_INCREMENT, 
      `object_type` varchar(32) NOT NULL, 
      PRIMARY KEY (`objid`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
  

  Right after creating it, run the SHOW TABLE STATUS query again to confirm the auto-increment is set to 2, then insert your data.

• InnoDB's in-memory auto-increment counter: InnoDB stores the auto-increment value in memory. If your MySQL server restarted after creating the table (and the table was empty), the counter resets to 1. In this case, manually set the auto-increment value after restart:

  ALTER TABLE db.object AUTO_INCREMENT = 2;
  

• Hidden writes to the table: If there's a trigger, stored procedure, or even an accidental insert (followed by a delete) that ran right after table creation, it could have used up the first auto-increment value. If you don't need any existing data, truncate the table and reset the counter:

  TRUNCATE TABLE db.object;
  ALTER TABLE db.object AUTO_INCREMENT = 2;
  

  Note that TRUNCATE fully clears the table and resets the auto-increment counter, whereas DELETE FROM won't reset it.

• MySQL version quirks: Older MySQL versions (pre-5.1, for example) had some inconsistencies with how AUTO_INCREMENT was set during table creation. If you're on an old version, upgrading might help, but the manual ALTER TABLE fix will work regardless.

Once you've applied one of these fixes, your next insert (without specifying objid) should start at 2 as expected.

内容的提问来源于stack exchange，提问作者user2133404