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

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.

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

I know this is a fairly old post, but for others still finding this via search engines. As of MySQL 8.0.13, you can use expressions as column defaults. So, your original alter statement is valid. You just need to wrap it in parentheses like so. I would also suggest to use the YEAR data type instead of varchar, it contains validation and built-in conversions; like 10 will be converted to 2010 etc...

ALTER TABLE `tips`
    CHANGE `year` `year` YEAR NOT NULL DEFAULT (YEAR(CURDATE()));