mysqlmysql-error-1067

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


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!


Solution

  • 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());