default value of GUID in for a column in mysql

Question

I want a column to default to a GUID, so if I am doing an insert and I don't explicitly set the value, I want it to default to a new GUID value.

how can I do this?

Answer 1

Being that UUID() isn't accepted as a DEFAULT constraint, you need to use a trigger.

This one sets the value for the NEW_TABLE.uuid column:

delimiter $$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `example`.`newid`
BEFORE INSERT ON `example`.`new_table`
FOR EACH ROW
BEGIN
  SET NEW.`uuid` = UUID();
END
$$

Answer 2

Previous answer is not quite right - you've got to be careful with triggers... they will actually overwrite any default value you pass in if used as in that example. Everything will work fine when the primary key is not set, but if you pass one in with the INSERT it will get wiped with a new random one by the trigger.

For it to work properly you must check whether the field already has a value before assigning a new one, as follows:

DELIMITER ;;
CREATE TRIGGER `sometrigger` 
BEFORE INSERT ON `sometable` 
FOR EACH ROW 
BEGIN 
    IF ASCII(NEW.uuid) = 0 THEN 
        SET NEW.uuid = UNHEX(REPLACE(UUID(),'-','')); 
    END IF; 
    SET @last_uuid = NEW.uuid; 
END
;;

I use ASCII() to check the new field value, as ASCII() will return 0 for an empty string whether the data is in textual or binary format (and an empty string is the default value for fields with no default set). I also use binary(16) to store my UUID's for most efficient storage space and query speed... if you don't want to deal with the complexities of binary fields then you can simply use UUID() in place of UNHEX(REPLACE(UUID(),'-','')) with a char(36) field.