Mysql – How to insert a record with generated identifiers using UUID()

mariadbMySQLstored-procedurestrigger

The Problem

Overview

So I'm trying to create records that have:

  1. id (primary key, auto-increment)

  2. 4 columns that are each a different UUID generated using the MySQL
    functions: REPLACE(UUID(), '-', ''). These 'obfuscated keys' are
    used for external lookup in other tables. REPLACE() is used to
    strip the dashes (-) from the UUID generated by MySQL, which is
    required to later transform the UUID into a binary format. Please
    don't worry about that!!!

The Table structure is as follows:
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| info | varchar(32) | YES | UNI | NULL | |
| note | varchar(32) | YES | UNI | NULL | |
| auth | varchar(32) | YES | UNI | NULL | |
| test | varchar(32) | YES | UNI | NULL | |
+-----------------+-------------+------+-----+---------+-------+

Environment

OS         : CentOS 7
Database   : MariaDB/MySQL
DB Version : 5.5

Partial Solutions Found

I have found a few articles that seem to point "a direction", however I have been unable to solve this.

  • Trigger before insert : Partially works, but only on the first column. The MySQL error is: Error Code: 1235. This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
     Also requires the columns to allow null defaults. Seems like too much of a band-aid hack.

    CREATE TRIGGER before_insert_infoid 
  BEFORE INSERT ON user 
  FOR EACH ROW 
      SET new.info = replace(uuid(),'-',''); 
CREATE TRIGGER before_insert_noteid 
  BEFORE INSERT ON user 
  FOR EACH ROW 
      SET new.note = replace(uuid(),'-',''); 
CREATE TRIGGER before_insert_authid 
  BEFORE INSERT ON user 
  FOR EACH ROW 
      SET new.auth = replace(uuid(),'-',''); 
CREATE TRIGGER before_insert_testid 
  BEFORE INSERT ON user 
  FOR EACH ROW 
      SET new.test = replace(uuid(),'-','');

  • Direct using insert : Works, but means that the logic is not contained in the database. Also, I get a MySQL warning warning(s): 1364 Field 'id' doesn't have a default value.

    INSERT INTO `meanlearn`.`students` 
(`info`, 
`note`, 
`auth`, 
`test`) 
VALUES 
(REPLACE(UUID(),'-',''), 
REPLACE(UUID(),'-',''), 
REPLACE(UUID(),'-',''), 
REPLACE(UUID(),'-',''));

Preliminary Conclusion / Request for Solutions

I'm sure I'm missing something. Any and all suggestions are greatly appreciated.

Best Answer

One way to vercome the multiple trigger error would be ot use one trigger - and not 4 - to set the new UUID values:

DELIMITER //
CREATE TRIGGER before_insert_on_user_set_uuids
  BEFORE INSERT ON user 
  FOR EACH ROW 
    BEGIN
      SET new.info = replace(uuid(),'-',''); 
      SET new.note = replace(uuid(),'-',''); 
      SET new.auth = replace(uuid(),'-',''); 
      SET new.test = replace(uuid(),'-',''); 
    END ;  //
DELIMITER ;

It would also be good idea to store the UUIDs in BINARY(16) columns instead of VARCHAR(32). Saves 16x4 bytes per row (plus 16 per row multiplied by the number of simple indexes on the columns) and some speed for not considering collations and character sets. And I assume you had the varchar column as ASCII and not UTF-8 which would be a real waste.

Related Question