Variable SQL column Default Value

Question

I would like to have a column's default value be a specified on a per session basis. The below script doesn't work, but describes how I would like to use it. I am currently using MySQL 5.5.28, but can upgrade if necessary.

CREATE TABLE my_tbl (
 id INT NOT NULL AUTO_INCREMENT,
 data VARCHAR(45),
 date_created  TIMESTAMP DEFAULT   CURRENT_TIMESTAMP, 
 date_modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 created_by    INT DEFAULT         CUSTOM_USER_ID, 
 modified_by   INT ON UPDATE       CUSTOM_USER_ID
 PRIMARY KEY (id)
) ENGINE = InnoDB;

-- Will set created_id and modified_id=111
SET @CUSTOM_USER_ID=111;
INSERT INTO my_tbl(data) VALUES('hello');

-- Will set modified_id=222
SET @CUSTOM_USER_ID=222;
UPDATE my_tbl SET data='goodby' WHERE id=1;

-- Will keep modified_id=222
SET @CUSTOM_USER_ID=333;
UPDATE my_tbl SET data='hello again',modified_id=modified_id WHERE id=1;

this is impossible. Instead, you have to use an applicative layer to implement the default value. A trigger, although costly, sounds like a good candidate — Sebas
– Sebas, Commented Apr 16, 2013 at 0:33
@Sebas. Thanks! I was also thinking of a trigger. Wasn't sure if the updating of the record within the trigger would trigger another trigger. Thoughts? — user1032531
– user1032531, Commented Apr 16, 2013 at 0:37
yes it could happen. But I think in your case you would just have to change the :NEW value to the one you want (say, BEFORE INSERT happens) See this example: java2s.com/Tutorial/MySQL/0260__Trigger/Beforeinserttrigger.htm — Sebas
– Sebas, Commented Apr 16, 2013 at 0:38
@Sebas. Sounds like a good idea. You think it is very costly? There is no insert, but only setting a variable. — user1032531
– user1032531, Commented Apr 16, 2013 at 0:42
yes, but every single time you'll have an insert or update on that table the trigger will fire (to not do anything if not necessary of course, but just firing it could cost a lot if you suddenly decide to insert 100000 records) — Sebas
– Sebas, Commented Apr 16, 2013 at 0:43

Sebas · Accepted Answer · 2013-04-16 00:41:56Z

3

So I'm posting an alternative:

delimiter $$

SET @CUSTOM_USER_ID=111;

CREATE TRIGGER myTrigger_INS BEFORE INSERT ON my_tbl FOR EACH ROW
BEGIN
    IF NEW.created_by IS NULL OR NEW.created_by = '' THEN
        SET NEW.created_by = @CUSTOM_USER_ID;
    END IF;
END$$

CREATE TRIGGER myTrigger_UPD BEFORE UPDATE ON my_tbl FOR EACH ROW
BEGIN
    IF NEW.modified_by IS NULL OR NEW.modified_by = '' THEN
        SET NEW.modified_by = @CUSTOM_USER_ID;
    END IF;
END$$

delimiter ;

CREDITS

answered Apr 16, 2013 at 0:41

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Brant · Accepted Answer · 2013-04-16 00:33:48Z

1

This is not possible. Read the following from MySQL documentation:

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.

You can't use an expression like this to define a default value.

answered Apr 16, 2013 at 0:33

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

1 Comment

user1032531 Over a year ago

Bummer! Any suggested other ways to implement?

Collectives™ on Stack Overflow

Variable SQL column Default Value

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related