上述的问题实现的方式有很多,但是最好的实现方式应该如下:
1、新建省份表
CREATE TABLE `provincial` (
`pid` int(11) NOT NULL DEFAULT '0',
`provincial` varchar(50) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、填充数据到省份表
INSERT INTO `provincial` VALUES (1, '北京市');
INSERT INTO `provincial` VALUES (2, '天津市');
INSERT INTO `provincial` VALUES (3, '上海市');
INSERT INTO `provincial` VALUES (4, '重庆市');
INSERT INTO `provincial` VALUES (5, '河北省');
INSERT INTO `provincial` VALUES (6, '山西省');
INSERT INTO `provincial` VALUES (7, '台湾省');
INSERT INTO `provincial` VALUES (8, '辽宁省');
INSERT INTO `provincial` VALUES (9, '吉林省');
INSERT INTO `provincial` VALUES (10, '黑龙江省');
INSERT INTO `provincial` VALUES (11, '江苏省');
INSERT INTO `provincial` VALUES (12, '浙江省');
INSERT INTO `provincial` VALUES (13, '安徽省');
INSERT INTO `provincial` VALUES (14, '福建省');
INSERT INTO `provincial` VALUES (15, '江西省');
INSERT INTO `provincial` VALUES (16, '山东省');
INSERT INTO `provincial` VALUES (17, '河南省');
INSERT INTO `provincial` VALUES (18, '湖北省');
INSERT INTO `provincial` VALUES (19, '湖南省');
INSERT INTO `provincial` VALUES (20, '广东省');
INSERT INTO `provincial` VALUES (21, '甘肃省');
INSERT INTO `provincial` VALUES (22, '四川省');
INSERT INTO `provincial` VALUES (23, '贵州省');
INSERT INTO `provincial` VALUES (24, '海南省');
INSERT INTO `provincial` VALUES (25, '云南省');
INSERT INTO `provincial` VALUES (26, '青海省');
INSERT INTO `provincial` VALUES (27, '陕西省');
INSERT INTO `provincial` VALUES (28, '广西壮族自治区');
INSERT INTO `provincial` VALUES (29, '西藏自治区');
INSERT INTO `provincial` VALUES (30, '宁夏回族自治区');
INSERT INTO `provincial` VALUES (31, '新疆维吾尔自治区');
INSERT INTO `provincial` VALUES (32, '内蒙古自治区');
INSERT INTO `provincial` VALUES (33, '澳门特别行政区');
INSERT INTO `provincial` VALUES (34, '香港特别行政区')
3、新建城市与省份的对应关系表
CREATE TABLE `city` (
`cid` int(10) NOT NULL,
`city` varchar(50) NOT NULL,
`pid` int(10) NOT NULL,
PRIMARY KEY (`city`),
KEY `t_province_pid` (`pid`),
CONSTRAINT `t_province_pid` FOREIGN KEY (`pid`) REFERENCES `provincial` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4、插入数据到上述表
INSERT INTO `city` VALUES (10, '七台河市', 10);
INSERT INTO `city` VALUES (7, '万宁市', 24);
INSERT INTO `city` VALUES (2, '三亚市', 24);
INSERT INTO `city` VALUES (4, '三明市', 14);
INSERT INTO `city` VALUES (12, '三门峡市', 17);
INSERT INTO `city` VALUES (1, '上海市', 3);
INSERT INTO `city` VALUES (11, '上饶市', 15);
INSERT INTO `city` VALUES (8, '东方市', 24);
INSERT INTO `city` VALUES (17, '东莞市', 20);
INSERT INTO `city` VALUES (5, '东营市', 16);
INSERT INTO `city` VALUES (5, '中卫市', 30);
INSERT INTO `city` VALUES (18, '中山市', 20);
INSERT INTO `city` VALUES (13, '临夏回族自治州', 21);
INSERT INTO `city` VALUES (10, '临汾市', 6);
INSERT INTO `city` VALUES (13, '临沂市', 16);
INSERT INTO `city` VALUES (8, '临沧市', 25);
INSERT INTO `city` VALUES (12, '临高县', 24);
INSERT INTO `city` VALUES (6, '丹东市', 8);
INSERT INTO `city` VALUES (11, '丽水市', 12);
INSERT INTO `city` VALUES (6, '丽江市', 25);
INSERT INTO `city` VALUES (9, '乌兰察布市', 32);
INSERT INTO `city` VALUES (3, '乌海市', 32);
INSERT INTO `city` VALUES (21, '乌苏市', 31);
INSERT INTO `city` VALUES (1, '乌鲁木齐市', 31);
INSERT INTO `city` VALUES (15, '乐东黎族自治县', 24);
INSERT INTO `city` VALUES (10, '乐山市', 22);
INSERT INTO `city` VALUES (4, '九江市', 15);
INSERT INTO `city` VALUES (16, '云林县', 7);
INSERT INTO `city` VALUES (21, '云浮市', 20);
INSERT INTO `city` VALUES (6, '五家渠市', 31);
INSERT INTO `city` VALUES (3, '五指山市', 24);
INSERT INTO `city` VALUES (15, '亳州市', 13);
INSERT INTO `city` VALUES (13, '仙桃市', 18);
INSERT INTO `city` VALUES (18, '伊宁市', 31);
INSERT INTO `city` VALUES (7, '伊春市', 10);
INSERT INTO `city` VALUES (6, '佛山市', 20);
INSERT INTO `city` VALUES (9, '佳木斯市', 10);
INSERT INTO `cit