4.16. 手机归属地数据库表

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;
		

原文出处：Netkiller 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。