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;