MYSQL数据库省市县三级数据表的设计-CSDN博客

本文链接：https://blog.csdn.net/qq_26890487/article/details/81949086

关于省市县的数据表的设计有两种方式

1 将其设计成一张表：如图所示：

CREATE TABLE `region` (
  `region_id` varchar(10) NOT NULL COMMENT '地区主键编号',
  `region_name` varchar(50) NOT NULL COMMENT '地区名称',
  `region_short_name` varchar(10) DEFAULT NULL COMMENT '地区缩写',
  `region_code` varchar(20) DEFAULT NULL COMMENT '行政地区编号',
  `region_parent_id` varchar(10) DEFAULT NULL COMMENT '地区父id',
  `region_level` int(2) DEFAULT NULL COMMENT '地区级别 1-省、自治区、直辖市 2-地级市、地区、自治州、盟 3-市辖区、县级市、县',
  PRIMARY KEY (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='地区表';

2 将其设计成三张表，如图所示：

省表：

DROP TABLE IF EXISTS `province`;
CREATE TABLE `province` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of province
-- ----------------------------
INSERT INTO `province` VALUES ('1', '110000', '北京市');
INSERT INTO `province` VALUES ('2', '120000', '天津市');
INSERT INTO `province` VALUES ('3', '130000', '河北省');
INSERT INTO `province` VALUES ('4', '140000', '山西省');
INSERT INTO `province` VALUES ('5', '150000', '内蒙古');
INSERT INTO `province` VALUES ('6', '210000', '辽宁省');
INSERT INTO `province` VALUES ('7', '220000', '吉林省');
INSERT INTO `province` VALUES ('8', '230000', '黑龙江');
INSERT INTO `province` VALUES ('9', '310000', '上海市');
INSERT INTO `province` VALUES ('10', '320000', '江苏省');

市表：

DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  `provincecode` varchar(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=343 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of city
-- ----------------------------
INSERT INTO `city` VALUES ('1', '110100', '北京市', '110000');
INSERT INTO `city` VALUES ('2', '130100', '石家庄市', '130000');
INSERT INTO `city` VALUES ('3', '130200', '唐山市', '130000');
INSERT INTO `city` VALUES ('4', '130300', '秦皇岛市', '130000');
INSERT INTO `city` VALUES ('5', '130400', '邯郸市', '130000');
INSERT INTO `city` VALUES ('6', '130500', '邢台市', '130000');
INSERT INTO `city` VALUES ('7', '130600', '保定市', '130000');
INSERT INTO `city` VALUES ('8', '130700', '张家口市', '130000');
INSERT INTO `city` VALUES ('9', '130800', '承德市', '130000');
INSERT INTO `city` VALUES ('10', '130900', '沧州市', '130000');
INSERT INTO `city` VALUES ('11', '131000', '廊坊市', '130000');
INSERT INTO `city` VALUES ('12', '131100', '衡水市', '130000');
INSERT INTO `city` VALUES ('13', '140100', '太原市', '140000');
INSERT INTO `city` VALUES ('14', '140200', '大同市', '140000');
INSERT INTO `city` VALUES ('15', '140300', '阳泉市', '140000');
INSERT INTO `city` VALUES ('16', '140400', '长治市', '140000');
INSERT INTO `city` VALUES ('17', '140500', '晋城市', '140000');
INSERT INTO `city` VALUES ('18', '140600', '朔州市', '140000');
INSERT INTO `city` VALUES ('19', '140700', '晋中市', '140000');
INSERT INTO `city` VALUES ('20', '140800', '运城市', '140000');
INSERT INTO `city` VALUES ('21', '140900', '忻州市', '140000');
INSERT INTO `city` VALUES ('22', '141000', '临汾市', '140000');
INSERT INTO `city` VALUES ('23', '141100', '吕梁市', '140000');

县表：

DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  `citycode` varchar(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3145 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of area
-- ----------------------------
INSERT INTO `area` VALUES ('1', '110101', '东城区', '110100');
INSERT INTO `area` VALUES ('2', '110102', '西城区', '110100');
INSERT INTO `area` VALUES ('3', '110103', '崇文区', '110100');
INSERT INTO `area` VALUES ('4', '110104', '宣武区', '110100');
INSERT INTO `area` VALUES ('5', '110105', '朝阳区', '110100');
INSERT INTO `area` VALUES ('6', '110106', '丰台区', '110100');
INSERT INTO `area` VALUES ('7', '110107', '石景山区', '110100');
INSERT INTO `area` VALUES ('8', '110108', '海淀区', '110100');
INSERT INTO `area` VALUES ('9', '110109', '门头沟区', '110100');
INSERT INTO `area` VALUES ('10', '110111', '房山区', '110100');
INSERT INTO `area` VALUES ('11', '110112', '通州区', '110100');
INSERT INTO `area` VALUES ('12', '110113', '顺义区', '110100');
INSERT INTO `area` VALUES ('13', '110114', '昌平区', '110100');
INSERT INTO `area` VALUES ('14', '110115', '大兴区', '110100');
INSERT INTO `area` VALUES ('15', '110116', '怀柔区', '110100');
INSERT INTO `area` VALUES ('16', '110117', '平谷区', '110100');
INSERT INTO `area` VALUES ('17', '110228', '密云县', '110200');
INSERT INTO `area` VALUES ('18', '110229', '延庆县', '110200');
INSERT INTO `area` VALUES ('19', '120101', '和平区', '120100');
INSERT INTO `area` VALUES ('20', '120102', '河东区', '120100');
INSERT INTO `area` VALUES ('21', '120103', '河西区', '120100');
INSERT INTO `area` VALUES ('22', '120104', '南开区', '120100');
INSERT INTO `area` VALUES ('23', '120105', '河北区', '120100');
INSERT INTO `area` VALUES ('24', '120106', '红桥区', '120100');
INSERT INTO `area` VALUES ('25', '120107', '塘沽区', '120100');
INSERT INTO `area` VALUES ('26', '120108', '汉沽区', '120100');
INSERT INTO `area` VALUES ('27', '120109', '大港区', '120100');
INSERT INTO `area` VALUES ('28', '120110', '东丽区', '120100');
INSERT INTO `area` VALUES ('29', '120111', '西青区', '120100');
INSERT INTO `area` VALUES ('30', '120112', '津南区', '120100');
INSERT INTO `area` VALUES ('31', '120113', '北辰区', '120100');
INSERT INTO `area` VALUES ('32', '120114', '武清区', '120100');
INSERT INTO `area` VALUES ('33', '120115', '宝坻区', '120100');
INSERT INTO `area` VALUES ('34', '120221', '宁河县', '120200');
INSERT INTO `area` VALUES ('35', '120223', '静海县', '120200');
INSERT INTO `area` VALUES ('36', '120225', '蓟　县', '120200')