如何在mysql中创建计算字段? [英] how to create calculated field in mysql?

问题描述

我有一个如下表:

create table info (username varchar(30),otherinfo varchar(100));

现在我要更改此表以具有新字段，并且该字段必须具有默认值

now i want to alter this table to have new field and this field has to have default value as

md5(username)

类似以下内容:

alter table info add NewField varchar(100) default md5(username);

该怎么做?
谢谢您的帮助

how to do so?
Thanks for your help

推荐答案

每个MySQL docs (强调)，您不能使用默认值的表达式:

Per MySQL docs (emphasis added) you cannot have an expression in a default value:

10.1.4.数据类型默认值

10.1.4. Data Type Default Values

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

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.

我已经测试了以下触发器可满足您的意图:

I've tested that the following trigger works for your intent:

CREATE TRIGGER MyTriggerName
  BEFORE INSERT ON info
  FOR EACH ROW
    SET NEW.NewField = md5(NEW.username);

这篇关于如何在mysql中创建计算字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！