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中有一个表。我想将表的列值设置为一个常量。我该怎么做呢?

4 个解决方案

#1

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不支持SQL检查约束。出于兼容性的原因，您可以在DDL查询中定义它们，但它们会被忽略。可以在INSERT之前和UPDATE之前创建触发器，这些触发器可以导致错误，也可以在不满足数据需求时将字段设置为默认值。

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

这里你可以通过MYSQL触发器找到一条路。

Sample Table:

示例表:

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;

Trigger:

触发:

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 ;

Test:

测试:

INSERT INTO constantvaluetable(constValue) VALUES(71);

INSERT INTO constantvaluetable(constValue) VALUES(66);

Result:

结果:

The first insert statement will succeed.

第一个insert语句将成功。

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

第二个插入语句将失败。将显示以下错误消息:

[Err] 1644 - Only allowed value is 71

[Err] 1644 -仅允许值为71

Note: Assuming your CONSTANT value is 71.

注意:假设常数值是71。

#2

Do you really want to do this?

你真的想这么做吗?

Would the following not suffice

难道以下这些还不够吗

Select Field1, field2, field3 , 5 as `ConstantField` from myTable

#3

You would just set up a CHECK constraint in your table when you set it up. Something like this is all you need in most DBMSs

在设置表时，您只需在表中设置一个检查约束。在大多数dbms中，您只需要这样的东西

CREATE someTable 
(
someValue int(4) CHECK (someValue = 4) 
)

However, with MySQL, CHECK constraints don't behave the same as they do in other DBMSs. The situation is a little more tricky. The answer seems to be here: https://*.com/a/14248038/5386243

但是，对于MySQL，检查约束的行为与其他dbms不同。情况有点棘手。答案似乎在这里:https://*.com/a/14248038/5386243

#4

Although 71's trigger solution is the general purpose approach, since it can be used for more complicated conditions, in your case where you just want to check for a constant value, you can stay closer to database logic and add a foreign key to a table that just contains that one allowed value in it, e.g.

尽管71年的触发的解决方案是通用的方法,因为它可以用于更复杂的条件下,在你的情况中,你只是想检查一个恒定的值,您可以保持接近数据库逻辑和添加一个外键的表,包含一个允许的值,例如:

create table tbl_checkconst (constraintvalue int primary key);
insert into tbl_checkconst values (71);

alter table yourtable 
add constraint fk_yourtable_constcheck 
foreign key (column1)
references tbl_chechconst (constraintvalue);

It will actually add some overhead (since it will need to add an index), but would express your constraint in database logic, and your constant usually has a meaning that is in this way designed into the database model (although it is just 1 value now), and you (and any user with the correct permissions) can easily add more allowed values by adding it to the tbl_checkconst-table without modifying your trigger code.

它会增加一些开销(因为它将需要添加一个索引),但将在数据库逻辑,表达你的约束和常数通常有一个意义,是这样设计到数据库模型(尽管现在是1值),你(和任何用户使用正确的权限)可以很容易地添加更多的允许的值添加到tbl_checkconst-table无需修改触发代码。

And another reason I added it is that I guess you are really actually looking for a foreign key: In one of your comments you said you are trying to create a "double foreign key to a reference table". If I understand that correctly, you might want to use a composite foreign key, since you are able to combine columns for a foreign key:

我补充的另一个原因是，我猜您实际上是在寻找一个外键:在您的评论中，您说您正在尝试创建一个“引用表的双外键”。如果我理解正确，您可能希望使用复合外键，因为您能够将列合并为外键:

alter table yourtable 
add constraint fk_yourtable_col1col2 
foreign key (column1, column2)
references your_reference_table (refcolumn1, refcolumn2);

#1