MySQL: adding current year as a default value for a field 'year'

Question

I have the following table in MySQL (version 5):

id      int(10)  UNSIGNED             No  auto_increment             
year    varchar(4)          latin1_swedish_ci           No                
title   varchar(250)        latin1_swedish_ci       Yes         NULL               
body    text            latin1_swedish_ci     Yes         NULL

And I want the db to auto add the current year on insert, I've tried the following SQL statement:

ALTER TABLE  `tips` CHANGE  `year`  `year` VARCHAR( 4 ) NOT NULL DEFAULT year(now())

But it gives the following error:

1067 - Invalid default value for 'year'

What can I do to get this functionality? Thanks in advance!

@gabriel1863: Thanks for the reply, but im still getting the same error. Even when I use backticks on the year field.

Thank you all for the help!

Answer 1

The following should work:

ALTER TABLE tips MODIFY COLUMN year YEAR(4) NOT NULL DEFAULT CURRENT_TIMESTAMP

Please see Year Data Type for further information.

So, I tested this once I got access and it doesn't work. As another poster pointed out CURRENT_TIMESTAMP only works on the TIMESTAMP data type.

Is there a specific problem with storing a complete time stamp and then only using the year in your code? If not, then I would recommend storing this value as a timestamp.

Your other option would be to create a trigger:

CREATE TRIGGER example_trigger AFTER INSERT ON tips
FOR EACH ROW BEGIN
UPDATE tips SET year = YEAR(NOW()) WHERE tip_id = NEW.tip_id
END;

Otherwise, assign this value to the INSERT statement from within your code.

The best solution in your case will depend entirely on the circumstances surrounding your particular application.

Answer 2

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.

-- MySQL Manual

You can, however, write a trigger that sets the value. I wish I could help, but I'm not really familiar with writing stored procedures in MySQL.

I think this would work:

CREATE TRIGGER ins_year
BEFORE INSERT ON tips
    FOR EACH ROW SET NEW.year = YEAR(NOW());