MySQL:将当前年份添加为字段"year"的默认值 [英] MySQL: adding current year as a default value for a field 'year'

问题描述

我在MySQL(版本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

我希望数据库在插入时自动添加当前年份，我尝试了以下SQL语句:

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

但是会出现以下错误:

1067 - Invalid default value for 'year'

我该怎么做才能获得此功能?预先感谢！

解决方案

数据中的DEFAULT值子句 类型说明指示默认值 列的值.与一个 例外，默认值必须是 持续的;它不能是函数或 一种表达.这意味着，对于 例如，您无法设置 默认情况下，日期列为 函数的值，例如NOW()或 当前日期.唯一的例外是 您可以将CURRENT_TIMESTAMP指定为 TIMESTAMP列的默认值.

-MySQL手册

但是，您可以编写触发价值.我希望可以提供帮助，但是我不太熟悉如何在MySQL中编写存储过程.

我认为这会起作用:

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

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!

解决方案

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

这篇关于MySQL:将当前年份添加为字段"year"的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！