CODEWITHSUNDEEP
mysqlrdbms
Yanick Rochon
Yanick Rochon

Reputation: 53536

Will inserting multiple rows within a single INSERT query guarantee sequential auto-increment primary keys?

Let's say I have this simple table:

CREATE TABLE some_table (
  id INT(11) PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(20)
);

with this INSERT statement :

INSERT INTO some_table (name) VALUES ("Foo"), ("Bar"), ("Buz");

The result gives me (for example) :

fieldCount = 0
affectedRows = 3
insertId = 18
info = 'Records: 3  Duplicates: 0  Warnings: 0',
serverStatus = 2
warningStatus = 0

Upon executing SELECT statement, I see that the first inserted row has the insertId = 18; is it guaranteed that the subsequent inserted document have their id in sequence?

In other words, is it guaranteed that the follow SELECT would yield the following rows?

SELECT * FROM some_table WHERE id >= 18 LIMIT 3

id    | name
------+--------------
18    | Foo
19    | Bar
20    | Buz

And if this result is not guaranteed, will a transaction guarentee it then?

And if a transaction does not guarantee it, is it possible to retrieve the individual id values in sequence?

Upvotes: 2

Views: 596

Answers (1)

The Impaler
The Impaler

Reputation: 48769

...will a single query guarantee sequential auto-increment primary keys?

No.

The database engine will ensure -- however -- they are DIFFERENT. Why do you want them to be sequential, in the first place?

Primary keys are not supposed to sexy or nice looking. Primary keys are technical-level unique row identifiers, typically hidden from the users. They should not be displayed into a UI or be shown to the end user on any reports.

If you need sequential numbers, it means that you probably want to expose them somewhere. Don't use the primary key for this. Maybe you should create a secondary UNIQUE column for that specific purpose, with nice formatting, cute numbering, and so on.

Upvotes: 1

Related Questions