用 CHECK 约束挡住错误订单

CREATE DATABASE cookbook_check;
CREATE TABLE cookbook_check.orders(
  id INT PRIMARY KEY, amount INT,
  CONSTRAINT positive_amount CHECK(amount > 0)
);
INSERT INTO cookbook_check.orders VALUES(1,10);
INSERT IGNORE INTO cookbook_check.orders VALUES(2,-5);
SELECT COUNT(*) AS accepted_rows FROM cookbook_check.orders;
SHOW CREATE TABLE cookbook_check.orders;
DROP DATABASE cookbook_check;

accepted_rows=1,建表定义中应有 positive_amount。普通 INSERT 违反约束会报错;INSERT IGNORE 跳过违反行。对 NULL 的业务要求应另加 NOT NULL,因为 CHECK 对未知条件不按失败处理。参见源码 约束测试。