2

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?

Yanick Rochon
  • 51,409
  • 25
  • 133
  • 214
  • 1
    The assertions made above are simply untrue – Strawberry Mar 20 '20 at 13:18
  • 1
    Does this answer your question? [When I INSERT multiple rows into a MySQL table, will the ids be increment by 1 everytime?](https://stackoverflow.com/questions/34200805/when-i-insert-multiple-rows-into-a-mysql-table-will-the-ids-be-increment-by-1-e) – P.Salmon Mar 20 '20 at 13:19
  • 1
    While mysql does some optimizing, also a CPU can make optimizations, so that if you have a high load the order and id could not be consecutive. a transqaction could help with this, but you lock the table and this would also be not helpful. Your table is so not correct id must be autoimcrement or else that would not help – nbk Mar 20 '20 at 13:20

1 Answers1

1

...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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • When writing an API, I was looking at ways to optimize bulk inserts, but since `RETURNING` is not supported with MySQL, and only the last inserted id is provided (set to the actual first inserted row) with a number of rows inserted, I was looking at updating the data from processing that limited result instead of individually insert rows. I really do not care if the ids are sequential or not, I just want to get their values. – Yanick Rochon Mar 20 '20 at 13:37
  • I may be wrong, and I don't remember clearly off the top of my head. When I used MySQL from JDBC (java) the `getKeys()` method would return a *ResultSet* with all the newly created keys. But I will need to check that. What language/driver are you using to call the query? – The Impaler Mar 20 '20 at 13:41
  • I'm using Node.js with the `mysql2` module. – Yanick Rochon Mar 20 '20 at 13:48