CREATE TABLE `contact` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '唯一ID', `name` VARCHAR(50) NOT NULL COMMENT '姓名', `mobile` VARBINARY(32) NULL DEFAULT NULL COMMENT '手机号码', `email` VARBINARY(50) NULL DEFAULT NULL COMMENT '电子邮件', `mobile_digest` VARCHAR(32) NULL DEFAULT NULL COMMENT '摘要', `email_digest` VARCHAR(32) NULL DEFAULT NULL COMMENT '邮件摘要', `birthday` DATE NULL DEFAULT NULL COMMENT '生日', `description` VARCHAR(255) NULL DEFAULT NULL COMMENT '备注描述', `status` ENUM('Subscription','Unsubscribe') NOT NULL DEFAULT 'Subscription' COMMENT '订阅状态', `ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (`id`), UNIQUE INDEX `digest` (`mobile_digest`, `email_digest`) ) COMMENT='会员手机短信与电子邮件映射表' COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=43642; CREATE TABLE `group` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `description` VARCHAR(512) NOT NULL, `ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE INDEX `name` (`name`) ) COMMENT='短信分组' COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=8; CREATE TABLE `group_has_contact` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `group_id` INT(10) UNSIGNED NOT NULL, `contact_id` INT(10) UNSIGNED NOT NULL, `ctime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE INDEX `group_contact` (`group_id`, `contact_id`), INDEX `FK_group_has_contact_contact` (`contact_id`), CONSTRAINT `FK_group_has_contact_contact` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT `FK_group_has_contact_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON UPDATE CASCADE ON DELETE CASCADE ) COMMENT='N:M' COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=55764;
原文出处:Netkiller 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。