0

I chose to update my application and database to use UUIDs as primary key. As I'm trying to achieve this with as few changes to my application as possible, I thought about using mysql triggers.

This thread

Creating trigger in mysql for generation UUID

explains how to generate UUIDs on insert. But using it as a key attribute, I need to handle the unlikely case of collision. Is it possible to react to constraint violations by retrying the insert with a new UUID using mysql triggers?

NPat
  • 1
  • 1
  • 1
    You may not need to handle collisions. See https://stackoverflow.com/questions/24876188/how-big-is-the-chance-to-get-a-java-uuid-randomuuid-collision – Stephen Crosby Jan 02 '18 at 19:31
  • Do not use the UUID value as a primary key, [the performance is horrible](http://kccoder.com/mysql/uuid-vs-int-insert-performance/). Use a normal `AUTO_INCREMENT` primary key instead. However you are totally fine to store the UUID in a non-primary-key field. – Progman Jan 02 '18 at 20:04
  • @Progman, a couple of things about that horrible performance. It appears that the UUID is being stored raw in a CHAR(36). There is no indication of what charset is being used, but if it is UTF-8, that's 108 bytes. A UUID is better stored in a BINARY(16), which is 4 times larger than an INT rather than 9-27 times larger. That in itself should reduce runtime. Also, MySQL 5.0.45? I imagine that MySQL is much more performant by 5.6 or greater. But as always, one should benchmark for one's specific use case. The fastest algorithm can also be the slowest depending on your data set. – Duncan Jan 10 '19 at 08:53

1 Answers1

1

To answer the question that was asked ...

Q: Is it possible to react to constraint violations by retrying the insert with a new UUID using mysql triggers?

A: No, it's not possible to "retry" a MySQL INSERT from within a BEFORE INSERT or an AFTER INSERT trigger. Not within a trigger. (Note that this restriction is independent of storing UUID values in a column, or using that column as a primary key.)

If the INSERT operation throws a duplicate key exception, there's no way to catch that in an AFTER INSERT trigger.

spencer7593
  • 106,611
  • 15
  • 112
  • 140