MySQL表中的常量列值 [英] Constant column value in MySQL table

问题描述

我在MySQL中有一张表.我想将表的列值设置为常量整数.我该怎么办?

I have a table in MySQL. I'd like to set a column value for a table to be a constant integer. How can I do this?

推荐答案

不幸的是，MySQL不支持SQL检查约束.你可以 出于兼容性原因，请在您的DDL查询中定义它们，但是它们是 只是忽略了.您可以创建BEFORE INSERT和BEFORE UPDATE触发器 这会导致错误或在以下情况下将该字段设置为其默认值 数据要求不满足.

Unfortunately MySQL does not support SQL check constraints. You can define them in your DDL query for compatibility reasons but they are just ignored. You can create BEFORE INSERT and BEFORE UPDATE triggers which either cause an error or set the field to its default value when the requirements of the data are not met.

因此，您可以在MYSQL TRIGGER中找到解决方法.

So here you can find a way around through MYSQL TRIGGER.

样品表:

DROP TABLE IF EXISTS `constantvaluetable`;
CREATE TABLE `constantvaluetable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `constValue` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

触发器:

DROP TRIGGER IF EXISTS trigger_const_check;
delimiter //
CREATE TRIGGER trigger_const_check BEFORE INSERT ON constantvaluetable 
    FOR EACH ROW 
  BEGIN 
        IF NEW.constValue <> 71 THEN 
        SIGNAL SQLSTATE '45000' SET message_text ='Only allowed value is 71';
        END IF; 
  END //
delimiter ;

测试:

INSERT INTO constantvaluetable(constValue) VALUES(71);

INSERT INTO constantvaluetable(constValue) VALUES(66);

结果:

第一个insert语句将成功.

The first insert statement will succeed.

第二个insert语句将失败.并且将显示以下错误消息:

The second insert statement will fail. And the following error message will be shown:

[Err] 1644-仅允许值为71

[Err] 1644 - Only allowed value is 71

注意:假设您的 CONSTANT 值为71.

这篇关于MySQL表中的常量列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！