The AUTO_INCREMENT field attribute has some attributes that are true in some cases, and not in others:
- The value is unique
- The value in incremented in sequence
- The value is incremented without "gaps" in the sequence
The two last of these attributes are no longer always true. In addition, to keep the value unique one has to resort to using auto_increment_increment and auto_increment_offset, which will break the last of the assumptions above and when using a distributed system, classic Replication or Galera, then even the second one breaks. But these settings are still necessary to ensure uniqueness in a distributed system.
So the idea then is to, in addition to AUTO_INCREMENT I suggest we add a UUID column attribute. As for an INSERT, this would be the same as using UUID() for the column in the insert statement, but having this as a column attribute has several advantages. Compared to AUTO_INCREMENT there are also many advantages, such as avoiding at AUTO_INCREMENT mutex locks. In addition, having this as a column attribute would be a declarative, so the schema documents the column attribute properly. To make this work, we need to add a mysql_last_uuid() function and a LAST_UUID SQL functioon added.
I could see this having some additional features, such as:
- Having this attribute assume a specific datatype, so that the type need not be explicitly specified..
- Not allowing the value in a column with this attribute to be updated or inserted with anything but NULL, which will generate a new UUID.
- Allowing a UUID_SHORT in addition to a UUID column attribute.
- Automatic compacting of the UUID value (remove the dashes etc).
A drawback of this schema compared to AUTO_INCREMENT is that this will consume some more space in the specified column, but 16 bytes (8 bytes of we use UUID_SHORT) when the dashes are removed isn't so bad. The advantages not having to deal with the auto_increment_xxx settings, in particular when new nodes are added to the cluster, should outweigh this,