Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

default value of GUID in for a column in mysql

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?

like image 280
Blankman Avatar asked Oct 30 '10 02:10

Blankman


People also ask

What is the default value of GUID?

default Guid is {00000000-0000-0000-0000-000000000000} . It's basically binary zeroes.

How do I find the default value of a column in MySQL?

The DEFAULT() function returns the default value for table column. DEFAULT value of a column is a value used in the case, there is no value specified by user. In order, to use this function there should be a DEFAULT value assign to the column.

How do I add a default value to a column in MySQL?

A column's default value is part of its definition, but can be modified separately from other aspects of the definition. To change a default value, use ALTER col_name SET DEFAULT : ALTER TABLE mytbl ALTER j SET DEFAULT 1000; Default values must be constants.

What is the default value of column?

Default values can be NULL, or they can be a value that matches the data type of the column (number, text, date, for example).


1 Answers

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.

like image 180
pospi Avatar answered Sep 24 '22 15:09

pospi