简介：在本教程中，您将学习如何使用触发器或视图在MySQL中使用仿真CHECK约束。

MySQL 8.0.16完全实现了SQL CHECK约束。
如果使用MySQL 8.0.16或更高版本，请查看CHECK约束教程。

使用触发器模拟CHECK约束

要在MySQL中模拟CHECK约束，可以使用两个触发器：BEFORE INSERT和BEFORE UPDATE。

首先，为演示创建一个名为parts的新表：

CREATE TABLE IF NOT EXISTS parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10 , 2 ) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

接下来，创建一个存储过程以检查成本和价格列中的值。

DELIMITER $

CREATE PROCEDURE `check_parts`(IN cost DECIMAL(10,2), IN price DECIMAL(10,2))
BEGIN
    IF cost < 0 THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'check constraint on parts.cost failed';
    END IF;
    
    IF price < 0 THEN
	SIGNAL SQLSTATE '45001'
	   SET MESSAGE_TEXT = 'check constraint on parts.price failed';
    END IF;
    
    IF price < cost THEN
	SIGNAL SQLSTATE '45002'
           SET MESSAGE_TEXT = 'check constraint on parts.price & parts.cost failed';
    END IF;
END$
DELIMITER ;

然后，创建插入前''和更新前''触发器。
在触发器内部，调用check_parts()存储过程。

-- before insert
DELIMITER $
CREATE TRIGGER `parts_before_insert` BEFORE INSERT ON `parts`
FOR EACH ROW
BEGIN
    CALL check_parts(new.cost,new.price);
END$   
DELIMITER ; 
-- before update
DELIMITER $
CREATE TRIGGER `parts_before_update` BEFORE UPDATE ON `parts`
FOR EACH ROW
BEGIN
    CALL check_parts(new.cost,new.price);
END$   
DELIMITER ;

之后，插入满足以下所有条件的新行：

• 费用> 0
• 价格> 0
• 并且价格> =成本

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);

1 row(s) affected

INSERT语句调用BEFORE INSERT触发器并接受值。

以下INSERT语句失败，因为它违反了以下条件：cost> 0。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);

Error Code: 1644. check constraint on parts.cost failed

以下INSERT语句失败，因为它违反了以下条件：price> 0。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',100,-120);

Error Code: 1644. check constraint on parts.price failed

以下INSERT语句失败，因为它违反了以下条件：price> cost。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-003','wiper',120,100);

让我们看看零件表中现在有什么。

SELECT * FROM parts;

以下语句尝试更新成本以使其低于价格：

UPDATE parts
SET price = 10
WHERE part_no = 'A-001';

Error Code: 1644. check constraint on parts.price & parts.cost failed

声明被拒绝。

因此，通过使用两个触发器：BEFORE INSERT和BEFORE UPDATE，您可以在MySQL中模拟CHECK约束。

使用视图模拟CHECK约束

这个想法是基于基础表创建一个WITH CHECK OPTION的视图。
在视图定义的SELECT语句中，我们仅选择满足CHECK条件的有效行。
如果对该视图进行的任何插入或更新操作都导致新行不出现在视图中，则将被拒绝。

首先，删除部件表以删除所有关联的触发器，并创建一个新的表(如部件表)，但是名称不同：parts_data：

DROP TABLE IF EXISTS parts;

CREATE TABLE IF NOT EXISTS parts_data (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10,2) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

接下来，基于parts_data表创建一个名为parts的视图。
通过这样做，我们可以保持使用部件表的应用程序的代码保持不变。
此外，旧零件表的所有特权均保持不变。

CREATE VIEW parts AS
    SELECT 
        part_no, description, cost, price
    FROM
        parts_data
    WHERE
        cost > 0 AND price > 0 AND price >= cost 
WITH CHECK OPTION;

然后，通过部件视图将新行插入parts_data表中：

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);

之所以被接受，是因为该新行在视图中有效。

之后，尝试插入将不会出现在视图中的新行。

INSERT INTO parts_checked(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);

Error Code: 1369. CHECK OPTION failed 'classicmodels.parts_checked'

在本教程中，您学习了如何使用触发器或视图来模拟MySQL中的CHECK约束。