Document MySQL, PostgreSQL, SQLite differences regarding auto increment and multi-insert

[AnyhowStep]

trafficstars

Schema (MySQL v5.7)

---

Query #1

CREATE TABLE myTable (
  	myId INT PRIMARY KEY AUTO_INCREMENT,
	myColumn INT
);

There are no results to be displayed.

---

Query #2

INSERT INTO
	myTable(myColumn)
VALUES
	(4),
    (5);

There are no results to be displayed.

---

Query #3

SELECT LAST_INSERT_ID();

LAST_INSERT_ID()
1

---

View on DB Fiddle

---

Schema (PostgreSQL v10.0)

---

Query #1

CREATE TABLE myTable (
  	myId SERIAL,
	myColumn INT
);

There are no results to be displayed.

---

Query #2

INSERT INTO
	myTable(myColumn)
VALUES
	(4),
    (5);

There are no results to be displayed.

---

Query #3

SELECT LASTVAL();

lastval
2

---

View on DB Fiddle

---

Schema (SQLite v3.26)

---

Query #1

CREATE TABLE myTable (
  	myId INTEGER PRIMARY KEY AUTOINCREMENT,
	myColumn INT
);

There are no results to be displayed.

---

Query #2

INSERT INTO
	myTable(myColumn)
VALUES
	(4),
    (5);

There are no results to be displayed.

---

Query #3

SELECT LAST_INSERT_ROWID();

LAST_INSERT_ROWID()
2

---

View on DB Fiddle

[AnyhowStep]

Basically, for multi inserts,

Database	Result
MySQL	FIrst insert id
PostgreSQL	Last insert id
SQLite	Last insert id

As usual, MySQL fucks everything up.

---

How the hell is it LAST_INSERT_ID() if you don't give the... last insert id?

[AnyhowStep]

Potential workaround: start a transaction, perform multi-insert/replace, get MAX(autoIncrementId). But you have to know that you did not insert/replace zero rows. If it's one row, you can use LAST_INSERT_ID() just fine.

This workaround is only feasible if the isolation level is REPEATABLE READ or SERIALIZABLE.

READ COMMITTED and READ UNCOMMITTED are not strong enough.

---

Have to be careful to not use MAX(autoIncrementId) if an explicit value was specified for it, however. Just use the specified value.

---

Another workaround for MySQL is LAST_INSERT_ID() + insert/replace row count.

The insert/replace row count is not the same as MySQL's affectedRowCount. Particularly for REPLACE statements.

When 2 rows are deleted and 1 are inserted for a REPLACE statement, MySQL's affectedRowCount is 3. But the insert/replace row count is just 1.

[EDIT]

The LAST_INSERT_ID() + insert/replace row count will not work, https://stackoverflow.com/questions/6895679/mysqls-auto-increment-behavior-in-a-multiple-row-insert

One scenario where ID's would not be sequential is in replicated multi-master setup. If for example two servers exist in such setup, one will only generate even auto IDs, and the other only odd IDs (keep in mind it's just an example).

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_innodb_autoinc_lock_mode

If you have innodb_autoinc_lock_mode=2, which allows higher concurrency for insert operations, use row-based replication rather than statement-based replication. This setting is known as interleaved lock mode, because multiple multi-row insert statements running at the same time can receive auto-increment values that are interleaved.

---

Another workaround is to just start a transaction and run many INSERT-one statements, instead of one INSERT-multi statement.

[AnyhowStep]

Unrelated to multi-insert. This is setting an explicit value for the auto-increment column.

MySQL,

INSERT INTO
	myTable(myId, myColumn)
VALUES
	(999, 6);
SELECT LAST_INSERT_ID();

PostgreSQL,

INSERT INTO
	myTable(myId, myColumn)
VALUES
	(999, 6);
SELECT LASTVAL();

SQLite,

INSERT INTO
	myTable(myId, myColumn)
VALUES
	(999, 6);
SELECT LAST_INSERT_ROWID();

Database	Result
MySQL	No Change
PostgreSQL	No Change
SQLite	999

[AnyhowStep]

We have the following possibilities,

---

• insertOne()
• insertMany() - Inserts zero rows
• insertMany() - Inserts one row
• insertMany() - Inserts many rows
• insertSelect() - inserts zero rows
• insertSelect() - inserts one row
• insertSelect() - inserts many rows

---

• Table has autoIncrement, autoIncrement is specified
• Table has autoIncrement, autoIncrement is not specified
• Table does not have autoIncrement

---

The trouble here seems to be,

• insertMany() - Inserts many rows
• insertSelect() - inserts many rows
• Table has autoIncrement, autoIncrement is specified

[AnyhowStep]

• insertMany() - Inserts many rows
• Table has autoIncrement, autoIncrement is specified

How to get lastInsertId,

• Use ??? on MySQL
• Use ??? on PostgreSQL; or RETURNING clause (might cause large result set)
• Use LAST_INSERT_ROWID() on SQLite

Note that some rows may have autoIncrement specified. Others may not.

The last row may or may not have autoIncrement specified.

---

• insertMany() - Inserts many rows
• Table has autoIncrement, autoIncrement is not specified

How to get lastInsertId,

• Use the MAX(autoIncrementId) trick on MySQL
• Use LASTVAL() on PostgreSQL; or RETURNING clause; or RETURNING MAX(autoIncrementId) trick
• Use LAST_INSERT_ROWID() on SQLite

---

• insertSelect() - Inserts many rows
• Table has autoIncrement, autoIncrement is specified

How to get lastInsertId,

• Use ??? on MySQL
• Use ??? on PostgreSQL; or RETURNING clause (might cause large result set)
• Use LAST_INSERT_ROWID() on SQLite

---

• insertSelect() - Inserts many rows
• Table has autoIncrement, autoIncrement is not specified

How to get lastInsertId,

• Use the MAX(autoIncrementId) trick on MySQL
• Use LASTVAL() on PostgreSQL; or RETURNING clause; or RETURNING MAX(autoIncrementId) trick
• Use LAST_INSERT_ROWID() on SQLite

---

LASTVAL() breaks once you start having triggers that perform INSERTs to the same table.

https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

RETURNING clause is bad if your INSERTs start inserting thousands of rows. Because you'll end up with a large number of rows in your result set.

The RETURNING MAX(autoIncrementId) trick is,

WITH x AS (
INSERT INTO
	myTable(myColumn)
VALUES
	(4),
	(5)
RETURNING myId
)
SELECT MAX(myId) FROM x;

We cannot use the MAX(autoIncrementId) trick on PostgreSQL because of the way it handles autoIncrement generation.

[AnyhowStep]

Given the following INSERTs,

1. implicit autoincrement
2. implicit autoincrement
3. explicit autoincrement to 999
4. implicit autoincrement
5. implicit autoincrement

The following IDs get generated,

• MySQL: 1,2,999,1000,1001
• PostgreSQL: 1,2,999,3,4
• SQLite: 1,2,999,1000,1001

---

Given the following INSERTs,

1. implicit autoincrement
2. implicit autoincrement
3. explicit autoincrement to 4
4. implicit autoincrement
5. implicit autoincrement

The following IDs get generated,

• MySQL: 1,2,4,5,6
• PostgreSQL: 1,2,4,3,error: duplicate key value violates unique constraint
• SQLite: 1,2,4,5,6

PostgreSQL's SERIAL PRIMARY KEY gets messed up if you try to specify explicit values for autoincrement.

[AnyhowStep]

Actually, fuck it.

The moment an INSERT or REPLACE statement inserts more than one row, we shouldn't bother with setting lastAutoIncrementId. It's just too complicated and too hacky.

---

Or... Just ditch the idea completely for now.

[keithlayne]

Stop stealing my links. I own the internet.

🚎

[AnyhowStep]

I decided to make it an entirely new issue, even. https://github.com/AnyhowStep/tsql/issues/93