MySQL - Can you retrieve the default value of a co

问题:

I was looking at creating a TRIGGER that will set the value of a column to its DEFAULT if the INSERT value happens to be an empty string.

My TRIGGER looks like this:

CREATE TRIGGER column_a_to_default 
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
IF NEW.a = '' THEN
SET NEW.a = 'some value';
END IF;
END

I would like to know if I can replace 'some value' with a way to set it to the DEFAULT value of the column. i.e.

SET NEW.a = a.DEFAULT

Thanks

回答1:

This should work for you

SET NEW.a = DEFAULT(NEW.a)

EDIT: It looks like that doesn't work.

Use this workaround

IF NEW.a = '' THEN
   SELECT COLUMN_DEFAULT INTO @def
   FROM information_schema.COLUMNS
   WHERE
     table_schema = 'database_name'
     AND table_name = 'your_table'
     AND column_name = 'a';
   SET NEW.a = @def;
END IF;

You can also try

SET NEW.a = DEFAULT(table_name.a)