CODEWITHSUNDEEP
mysql
cheppsn
cheppsn

Reputation: 212

AUTO_INCREMENT on UPDATE in MySql

I have a table with entries that have a version field.

I would like to set the version field to some global unique counter when ever I update or insert a row, like a combined AUTO_INCREMENT on insert or CURRENT_TIMESTAMP on update. Is it possible to do that within an SQL statement for MySql?

Example:

+---+------+-----------+
|id | name |version    |
+---+------+-----------+
|1  |a     |1          |
|2  |b     |2          |
|3  |c     |3          |
+---+------+-----------+

UPDATE my_tbl SET name=foo WHERE id=1

+---+------+-----------+
|id | name |version    |
+---+------+-----------+
|1  |foo   |4          |
|2  |b     |2          |
|3  |c     |3          |
+---+------+-----------+

INSERT INTO my_tbl SET name=bar

+---+------+-----------+
|id | name |version    |
+---+------+-----------+
|1  |foo   |4          |
|2  |b     |2          |
|3  |c     |3          |
|4  |bar   |5          |
+---+------+-----------+

I can not use AUTO_INCREMENT as it is only applied when inserting rows, but not when updating. CURRENT_TIMESTAMP is neither a solution, since a timestamp might not be unique in a concurrent environment. Also user variables do not help, as I desire a global unique and incremented sequence number, not a counter within a single session.

Upvotes: 2

Views: 1471

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270617

Although a timestamp updated via ON UPDATE CURRENT_TIMESTAMP may not be unique by itself, when coupled with the id column (already auto_increment) you guarantee a unique value between them.

So, you may calculate the version in your query by simply concatenating the two values in any way that makes sense. For example: id_timestamp or id_unix_timestamp(timestamp).

If you have a great need to query that value with exceptional performance, consider creating a composite index across the two columns. 

 ALTER TABLE yourtable ADD INDEX `idx_id_timestamp` (`id`, `timestamp`)

You can add a UNIQUE but it isn't absolutely necessary since the id guarantees that already as a primary key.

If absolutely necessary, an update trigger could be used to cache the concatenated value with each row into its own column, but that adds quite a lot of complexity and some duplication when you could normally produce it at query time and have the composite index available to query it otherwise.

Upvotes: 1

Related Questions