How to default a column as a UUID/GUID in MySQL ~ Tech Blog

MySQL does not yet support setting a column's default value using a function (at least not yet as of version 5.6) but you can use a trigger instead. This post shows how to set a column to a UUID/GUID by default in MySQL using a trigger.

A warning about replication before we start

If you are using statement based replication between a master & slave (or master & master), the examples on this page will not replicate the UUID across to the slave server(s) and different UUID(s) will be created on the slave(s). I cover another method which is statement based replication safe here.

Example table

This is a very basic example table; replace 'tablename' with your actual tablename and add appropriate fields, primary key, change the uuid fieldname, etc.

CREATE TABLE `tablename` (
    `uuid` char(36) NULL,
    `reference` varchar(100) NOT NULL
);

The trigger

The following trigger will set the uuid field to a UUID if it hasn't already been set by your insert query whenever a new record is inserted:

DELIMITER ;;
CREATE TRIGGER before_insert_tablename
BEFORE INSERT ON tablename
FOR EACH ROW
BEGIN
  IF new.uuid IS NULL THEN
    SET new.uuid = uuid();
  END IF;
END
;;

Note that the SET and END IF lines need to be terminated with ; otherwise you'll get a SQL error. However, these would define the end of the query, hence the DELIMITER ;; statement at the very start of the trigger creation and then the ;; at the end to finish it.

Remember to reset you delimiter back to ; if you are running this from the MySQL CLI:

DELIMITER ;

Example insert and resulting data

We'll insert a value into the reference column only, and the trigger will automatically set the uuid field to a UUID:

INSERT INTO tablename (reference) VALUES ('example');

Now we'll select the data to see the automatically populated UUID:

SELECT * FROM tablename;

And the resulting data:

+--------------------------------------+-----------+
| uuid                                 | reference |
+--------------------------------------+-----------+
| 79c9e1ac-96f1-11e5-85a6-000c29f1f6c4 | example   |
+--------------------------------------+-----------+

This method can be used for any functions you want to automatically populate a column with in MySQL, by using triggers. Note that as mentioned at the start, this won't work as expected when using statement based replication, because the UUID generated will be different on each server.

Check the Advantages and Disadvantages of Statement-Based and Row-Based Replication section of the MySQL manual for more information about the functions which won't be replicated correctly, and I cover another method which is statement based replication safe here.

Tech Blog

Monday, 10 September 2018