MySQL 默认值 - YEAR(CURRENT_DATE)

Question

我正在尝试创建包含类似列的表

model_year smallint(4) not null default YEAR(CURRENT_DATE)

但是有一个错误(无效的默认值)。我可以做些什么来设置 model_year 当年的默认值？

Answer 1

来自MySQL doc pages (我的粗体):

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.

我怀疑您要么必须在插入行时指定实际值。

---

如果您不想这样做，您可以允许 NULL，然后使用另一个进程定期清理数据库:

update table mytable set model_year = year(current_date) where model_year is null

但这是一种困惑，启动时相当危险，因为在一段时间内列中会存在 NULL。

这是你所有的查询都必须考虑的事情，导致像这样的丑陋:

select a, b, model_year from mytable where model_year is not null
union all
select a, b, year(current_date) from mytable where model_year is null

我自己会走非默认路线。