Add a UUID to a new row in MySQL automatically

Posted by robbiebow on 1 February, 2010 under Dunno | Be the First to Comment

Unfortunately MySQL only supports the CURRENT_TIMESTAMP function for setting default values on a column. If you maintain a CMS type system you’ll more than likely want UUIDs for each article or page, and also likely want to keep that data in your MySQL database. UUIDs are commonly used as unique identifiers in ATOM or RSS news feeds.

To automatically add a UUID when you insert a row, use a trigger:

DELIMITER $$
CREATE
 /*[DEFINER = { user | CURRENT_USER }]*/
 TRIGGER `mydatabase`.`Add UUID to mytable` BEFORE INSERT
 ON `mydatabase`.`mytable`
 FOR EACH ROW BEGIN
 SET NEW.`uuid` = UUID();
 END$$
DELIMITER;

UUIDs are 36 characters wide, so a column type of CHAR(36) is suitable.