Geek Answers Handbook

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

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 db to automatically add the current year to insert, I tried the following SQL statement:

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

But this leads to the following error:

 1067 - Invalid default value for 'year'

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

@ gabriel1863: Thanks for the answer, but I still get the same error. Even when I use backticks in the year field.

Thank you all for your help!

+6
mysql mysql-error-1067
source share
2 answers

Suggesting the DEFAULT value in the data type indicates the default value for the column. With one exception, the default value must be constant; it cannot be a function or expression. This means that for example, you cannot set the default value for a date column to be a function value, such as NOW () or CURRENT DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for the TIMESTAMP column.

- MySQL Guide

However, you can write trigger that sets the value. I would like to help me, but I'm not very good at writing stored procedures in MySQL.

I think this will work:

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

The following should work:

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

See Year Data Type for more information.

So, I checked this as soon as I got access and it does not work. As another poster pointed out, CURRENT_TIMESTAMP only works with the TIMESTAMP data type .

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

Another option is 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 your code.

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

+7
source share

More articles:


All Articles