知乎专栏 | 多维度架构 | 微信号 netkiller-ebook | QQ群:128659835 请注明“读者” |
members_location 表与 members 表是一对一关系,该表只负责存储归属地信息
DROP TABLE IF EXISTS `members_location`; CREATE TABLE IF NOT EXISTS `members_location` ( `id` int(10) unsigned NOT NULL, `province` varchar(50) NOT NULL, `city` varchar(50) NOT NULL, `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `province` (`province`), KEY `city` (`city`), CONSTRAINT `FK_members_location_members` FOREIGN KEY (`id`) REFERENCES `members` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
当某些用户符合条件需要查询归属地是,只要将其插入到 members_mobile 表即可。该表使用黑洞引擎并不会存储手机号码,所以明文手机号码安全得到了保障。
DROP TABLE IF EXISTS `members_mobile`; CREATE TABLE IF NOT EXISTS `members_mobile` ( `id` int(10) NOT NULL, `number` varchar(11) NOT NULL ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;
当有数据进入到 members_mobile 时出发器 members_mobile_insert 会工作,去 mobile_location 表中查询归属地后保存在 members_location 表中
DROP TRIGGER IF EXISTS `members_mobile_insert`; SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE=''; DELIMITER // CREATE TRIGGER `members_mobile_insert` BEFORE INSERT ON `members_mobile` FOR EACH ROW BEGIN insert into members_location(id,province,city) select NEW.id,mobile_location.province,mobile_location.city from mobile_location where mobile_location.id = md5(LEFT(NEW.number, 7)); END// DELIMITER ; SET SQL_MODE=@OLDTMP_SQL_MODE;
mobile_location 是存储手机号段与归属地信息的数据库
DROP TABLE IF EXISTS `mobile_location`; CREATE TABLE IF NOT EXISTS `mobile_location` ( `id` varchar(50) NOT NULL, `province` varchar(50) DEFAULT NULL, `city` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;