Home | 简体中文 | 繁体中文 | 杂文 | 知乎专栏 | Github | OSChina 博客 | 云社区 | 云栖社区 | Facebook | Linkedin | 视频教程 | 打赏(Donations) | About
知乎专栏多维度架构 微信号 netkiller-ebook | QQ群:128659835 请注明“读者”

9.13. 状态保护

表中有一个 Status 字段,是一个状态机,你可以理解为工作流,工作流是有任务流向的,不能随意修改其状态。

		
CREATE TABLE `card` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`uuid` VARCHAR(36) NOT NULL COMMENT 'UUID' COLLATE 'utf8mb4_unicode_ci',
	`number` VARCHAR(36) NOT NULL COMMENT '充值卡号码' COLLATE 'utf8mb4_unicode_ci',
	`price` MEDIUMINT(8) UNSIGNED NOT NULL COMMENT '面值',
	`status` ENUM('New','Activated','Recharged','Discard') NOT NULL DEFAULT 'New' COMMENT '充值卡状态' COLLATE 'utf8mb4_unicode_ci',
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `number` (`number`),
	UNIQUE INDEX `uuid` (`uuid`)
)
COMMENT='充值卡表'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
		
		

状态流向

		
	+----------+    +-----------+    +-----------+    
	| New      | -> | Activated | -> | Recharged |
	+----------+    +-----------+    +-----------+    
		 |                |
		 V                |
	+----------+          |
    | Discard  | <--------+
	+----------+
		
		

为此我们创建触发器保护状态正确走向。

		
CREATE DEFINER=`root`@`%` TRIGGER `card_before_update` BEFORE UPDATE ON `card` FOR EACH ROW BEGIN
	set new.uuid 		= old.uuid;
	set new.number		= old.number;
	set new.price		= old.price;
	set new.ctime		= old.ctime;
	
	IF old.status = "New" THEN
		IF new.status NOT IN ("Activated","Discard") THEN
			SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Status denied', MYSQL_ERRNO = 1001;
		END IF;
	END IF;
	IF old.status = "Activated" THEN
		IF new.status NOT IN ("Recharged") THEN
			SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Status denied', MYSQL_ERRNO = 1001;
		END IF;
	END IF;
	IF old.status = "Recharged" THEN
		set new.status	= old.status;
	END IF;
END
		
		

保护记录不被删除

		
CREATE DEFINER=`root`@`%` TRIGGER `card_before_delete` BEFORE DELETE ON `card` FOR EACH ROW BEGIN
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Permission denied', MYSQL_ERRNO = 1001;
END
		
		

这个方案很容易移植到其他场景中,例如购物,发货,收货等等