mysql 常量_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?

解决方案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.

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

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.

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

[Err] 1644 - Only allowed value is 71

Note: Assuming your CONSTANT value is 71.