【随笔】《全国车牌与地区数据表设计分享：从车牌前缀到行政地区全覆盖》

框架师

发布于 2025-02-06 09:08:16

13300

代码可运行

文章被收录于专栏：墨白的Java基地墨白的Java基地

运行总次数：0

代码可运行

前言

在项目中，经常会涉及到全国行政区域和车牌号的存储与查询。本文分享三张优化过的表结构，分别是车牌表（sys_plate）、地区表（ sys_region）以及最详细的行政区域表（sys_cnarea），并提供一些快捷查询的 SQL 示例。

1. 车牌表 `sys_plate`

该表用于存储全国车牌信息，包括省份、城市、车牌前缀等。

CREATE TABLE IF NOT EXISTS `sys_plate` (
    `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
    `province_id` VARCHAR ( 11 ) NOT NULL COMMENT '省份唯一编码',
    `province_name` VARCHAR ( 11 ) NOT NULL COMMENT '所属省份',
    `province_code` VARCHAR ( 8 ) NOT NULL COMMENT '车牌第一个字',
    `city_id` VARCHAR ( 10 ) NOT NULL COMMENT '城市唯一编码',
    `city_name` VARCHAR ( 25 ) NOT NULL COMMENT '城市名称',
    `city_code` VARCHAR ( 23 ) NOT NULL COMMENT '城市车牌首字母',
    `prefix` VARCHAR ( 50 ) NOT NULL COMMENT '完整车牌前缀（如：京 A、沪 B）',
    `status` TINYINT ( 1 ) NOT NULL DEFAULT 0 COMMENT '状态（0 正常 1 停用）',
    `deleted` TINYINT ( 1 ) NOT NULL DEFAULT 0 COMMENT '删除标志（0 未删除，1 已删除）',
    PRIMARY KEY ( `id` ) USING BTREE,
    INDEX `idx_province_id` ( `province_id` ),
    INDEX `idx_city_id` ( `city_id` ),
aINDEX `idx_prefix` ( `prefix` ) 
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '全国车牌表' ROW_FORMAT = DYNAMIC;

结构优化点

增加 province_id 和 city_id，方便关联行政地区表。
增加 prefix 字段，直接存储完整的车牌前缀。
通过 status 和 deleted 字段，控制数据状态。

查询示例

查询某个省份下的所有车牌信息：

SELECT
  * 
FROM
  sys_plate 
WHERE
  province_id = '110000';

查询某个城市的车牌前缀：

SELECT
  prefix 
FROM
  sys_plate 
WHERE
  city_id = '130100';

2. 行政地区表 `sys_region`

该表用于存储省、市、区三级行政区划信息。

CREATE TABLE IF NOT EXISTS `sys_region` (
    `region_id` VARCHAR ( 10 ) NOT NULL COMMENT '地区唯一编码（国标）',
    `region_name` VARCHAR ( 50 ) NOT NULL COMMENT '地区全称',
    `region_short_name` VARCHAR ( 10 ) NULL DEFAULT NULL COMMENT '地区简称',
    `region_code` VARCHAR ( 20 ) NULL DEFAULT NULL COMMENT '行政区划代码（如 110101）',
    `region_parent_id` VARCHAR ( 10 ) NOT NULL DEFAULT '0' COMMENT '地区父级 ID（0 表示顶级）',
    `region_level` INT NOT NULL DEFAULT 0 COMMENT '地区级别 1- 省 / 自治区 / 直辖市 2- 地级市 / 自治州 3- 区 / 县',
    PRIMARY KEY ( `region_id` ) USING BTREE,
    INDEX `idx_region_parent` ( `region_parent_id` ),
    INDEX `idx_region_level` ( `region_level` ),
INDEX `idx_region_code` ( `region_code` ) 
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '全国行政地区表，存储省、市、区三级行政区划' ROW_FORMAT = Dynamic;

结构优化点

region_id 作为唯一主键，存储地区编码。
region_level 标识行政层级（1- 省级，2- 地级市，3- 区县）。
region_parent_id 便于层级查询。

查询示例

查询某个省份下的所有城市：

SELECT *
FROM sys_region
WHERE region_parent_id = '1000000'
  AND region_level = 2;

查询某个城市所在的省份：

SELECT *
FROM sys_region
WHERE region_id = (SELECT region_parent_id FROM sys_region WHERE region_id = '1000046');

递归查询某个地区的完整层级路径（省 -> 市 -> 区）：

WITH RECURSIVE region_hierarchy AS (
  SELECT
    region_id,
    region_name,
    region_parent_id 
  FROM
    sys_region 
  WHERE
    region_id = '1000046' UNION ALL
  SELECT
    r.region_id,
    r.region_name,
    r.region_parent_id 
  FROM
    sys_region r
    INNER JOIN region_hierarchy rh ON r.region_id = rh.region_parent_id 
  ) SELECT
  * 
FROM
  region_hierarchy;

3. 详细行政地区表 `sys_cnarea`

该表存储全国最详细的行政区划数据，包含邮编、区号、经纬度等信息，数据量较大（约 70 万条）。

CREATE TABLE IF NOT EXISTS `sys_cnarea` (
    `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '唯一主键 ID',
    `level` TINYINT UNSIGNED NOT NULL COMMENT '行政区划层级（1- 省级，2- 地级，3- 县级，4- 乡镇级）',
    `parent_code` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级行政区划代码',
    `area_code` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '行政区划代码',
    `zip_code` MEDIUMINT ( 6 ) UNSIGNED ZEROFILL NOT NULL DEFAULT 000000 COMMENT '邮政编码',
    `city_code` CHAR ( 6 ) NOT NULL DEFAULT '' COMMENT ' 电话区号 ',
    `name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT ' 行政区划名称 ',
    `short_name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT ' 简称 ',
    `merger_name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT ' 完整名称（省 / 市 / 区）',
    `pinyin` VARCHAR ( 30 ) NOT NULL DEFAULT '' COMMENT ' 名称拼音 ',
    `lng` DECIMAL ( 10, 6 ) NOT NULL DEFAULT 0.000000 COMMENT '经度',
    `lat` DECIMAL ( 10, 6 ) NOT NULL DEFAULT 0.000000 COMMENT '纬度',
    PRIMARY KEY ( `id` ) USING BTREE,
    UNIQUE INDEX `uk_code` ( `area_code` ) USING BTREE,
INDEX `idx_parent_code` ( `parent_code` ) USING BTREE 
) ENGINE = MyISAM AUTO_INCREMENT = 743299 DEFAULT CHARSET = utf8mb3 COMMENT = '全国行政区划表';

结构优化点

parent_code 用于存储父级行政代码，方便递归查询。
pinyin 存储地区拼音，支持拼音搜索。
lng、lat 存储经纬度，便于地理位置计算。

查询示例

查询某个地区的所有下级行政区：

SELECT
  * 
FROM
  sys_cnarea 
WHERE
  parent_code = '110000000000' 
  AND LEVEL = 2;

查询某个区县的完整层级路径：

SELECT
  merger_name 
FROM
  sys_cnarea 
WHERE
  area_code = 110100000000;

查询某个省份的所有市：

SELECT
  * 
FROM
  sys_cnarea 
WHERE
  parent_code = 110101001000 
  AND LEVEL = 5;

计算两个地区之间的直线距离（基于经纬度）：

SELECT
  a.NAME AS location1,
  b.NAME AS location2,
  6371 * ACOS(
    COS(
      RADIANS( a.lat )) * COS(
      RADIANS( b.lat )) * COS(
      RADIANS( b.lng ) - RADIANS( a.lng )) + SIN(
      RADIANS( a.lat )) * SIN(
    RADIANS( b.lat ))) AS distance_km 
FROM
  sys_cnarea a,
  sys_cnarea b 
WHERE
  a.area_code = 110101001007 
  AND b.area_code = 110101004004;