用PHP从MySQL单表检索分级数据并拆分至多表实现动态下拉

我来帮你一步步搞定这个问题，从适配现有表的PHP查询，到拆分数据到专用表，再到实现动态下拉，全流程给你安排明白～

一、PHP数据检索实现 #

你的表通过kode的格式（小数点数量/位数）区分层级，我们可以利用正则表达式精准匹配不同层级的数据，以下是常用场景的PDO查询示例（PDO比mysqli更安全，适配性更强）：

1. 查询所有省份（kode为2位纯数字，无小数点） #

<?php
$dsn = 'mysql:host=localhost;dbname=你的数据库名;charset=utf8mb4';
$username = '你的数据库账号';
$password = '你的数据库密码';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 匹配2位纯数字的省份kode
    $stmt = $pdo->prepare("SELECT kode, name FROM 你的表名 WHERE kode REGEXP '^[0-9]{2}$'");
    $stmt->execute();
    $provinces = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 可根据业务需求输出，比如转成JSON给前端
    print_r($provinces);
} catch(PDOException $e) {
    echo "查询失败: " . $e->getMessage();
}
?>

2. 查询指定省份下的所有城市 #

比如要查kode为61的陕西省下属城市，匹配61.xx格式的kode：

$targetProvinceKode = '61';
$stmt = $pdo->prepare("SELECT kode, name FROM 你的表名 WHERE kode REGEXP ?");
// 正则规则：以目标省份kode开头，后跟.和两位数字
$stmt->execute(["^$targetProvinceKode\.[0-9]{2}$"]);
$cities = $stmt->fetchAll(PDO::FETCH_ASSOC);

3. 通用层级查询（区县/村庄） #

只需调整正则表达式即可：

• 区县：^[0-9]{2}\.[0-9]{2}\.[0-9]{2}$（匹配xx.xx.xx格式）
• 村庄：^[0-9]{2}\.[0-9]{2}\.[0-9]{2}\.[0-9]{2}$（匹配xx.xx.xx.xx格式）

二、拆分数据到专用表（province/city/district/village） #

为了更高效实现动态下拉，建议把数据拆分到对应层级的专用表，步骤如下：

1. 创建专用表结构 #

-- 省份表
CREATE TABLE province (
    id VARCHAR(10) PRIMARY KEY COMMENT '对应原表kode',
    name VARCHAR(100) NOT NULL COMMENT '省份名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 城市表（关联省份）
CREATE TABLE city (
    id VARCHAR(10) PRIMARY KEY COMMENT '对应原表kode',
    name VARCHAR(100) NOT NULL COMMENT '城市名称',
    province_id VARCHAR(10) NOT NULL COMMENT '所属省份kode',
    FOREIGN KEY (province_id) REFERENCES province(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 区县表（关联城市）
CREATE TABLE district (
    id VARCHAR(10) PRIMARY KEY COMMENT '对应原表kode',
    name VARCHAR(100) NOT NULL COMMENT '区县名称',
    city_id VARCHAR(10) NOT NULL COMMENT '所属城市kode',
    FOREIGN KEY (city_id) REFERENCES city(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 村庄表（关联区县）
CREATE TABLE village (
    id VARCHAR(10) PRIMARY KEY COMMENT '对应原表kode',
    name VARCHAR(100) NOT NULL COMMENT '村庄名称',
    district_id VARCHAR(10) NOT NULL COMMENT '所属区县kode',
    FOREIGN KEY (district_id) REFERENCES district(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 从原表导入数据到专用表 #

-- 导入省份数据
INSERT INTO province (id, name)
SELECT kode, name FROM 你的表名 WHERE kode REGEXP '^[0-9]{2}$';

-- 导入城市数据，自动关联所属省份
INSERT INTO city (id, name, province_id)
SELECT kode, name, SUBSTRING_INDEX(kode, '.', 1)
FROM 你的表名 WHERE kode REGEXP '^[0-9]{2}\.[0-9]{2}$';

-- 导入区县数据，自动关联所属城市
INSERT INTO district (id, name, city_id)
SELECT kode, name, SUBSTRING_INDEX(kode, '.', 2)
FROM 你的表名 WHERE kode REGEXP '^[0-9]{2}\.[0-9]{2}\.[0-9]{2}$';

-- 导入村庄数据，自动关联所属区县
INSERT INTO village (id, name, district_id)
SELECT kode, name, SUBSTRING_INDEX(kode, '.', 3)
FROM 你的表名 WHERE kode REGEXP '^[0-9]{2}\.[0-9]{2}\.[0-9]{2}\.[0-9]{2}$';

三、导出各层级数据为SQL文件 #

1. 拆分后从专用表导出 #

用终端执行mysqldump命令：

-- 导出省份数据
mysqldump -u 你的账号 -p 你的数据库名 province > province.sql

-- 导出城市数据
mysqldump -u 你的账号 -p 你的数据库名 city > city.sql

执行后输入数据库密码，就能生成对应层级的SQL文件。

2. 未拆分时从原表导出指定层级 #

如果还没拆分，也可以直接从原表导出指定层级的数据：

-- 导出省份数据
mysqldump -u 你的账号 -p 你的数据库名 你的表名 --where="kode REGEXP '^[0-9]{2}$'" > province_data.sql

-- 导出城市数据
mysqldump -u 你的账号 -p 你的数据库名 你的表名 --where="kode REGEXP '^[0-9]{2}\.[0-9]{2}$'" > city_data.sql

四、动态下拉选择功能实现 #

基于拆分后的专用表，我们可以实现省-市-区县-村庄的联动下拉，这里给你一个基础实现示例：

1. 后端PHP接口（获取指定省份的城市） #

<?php
header('Content-Type: application/json');
$dsn = 'mysql:host=localhost;dbname=你的数据库名;charset=utf8mb4';
$username = '你的数据库账号';
$password = '你的数据库密码';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $provinceId = $_GET['province_id'] ?? '';
    if(empty($provinceId)) {
        echo json_encode([]);
        exit;
    }

    $stmt = $pdo->prepare("SELECT id, name FROM city WHERE province_id = ?");
    $stmt->execute([$provinceId]);
    $cities = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($cities);
} catch(PDOException $e) {
    echo json_encode([]);
}
?>

2. 前端JS联动（原生JS实现） #

<select id="provinceSelect">
    <option value="">请选择省份</option>
    <!-- 省份选项通过PHP循环生成 -->
    <?php foreach($provinces as $prov): ?>
    <option value="<?= $prov['id'] ?>"><?= $prov['name'] ?></option>
    <?php endforeach; ?>
</select>

<select id="citySelect" disabled>
    <option value="">请先选择省份</option>
</select>

<script>
const provinceSelect = document.getElementById('provinceSelect');
const citySelect = document.getElementById('citySelect');

provinceSelect.addEventListener('change', async function() {
    const provinceId = this.value;
    citySelect.disabled = true;
    citySelect.innerHTML = '<option value="">加载中...</option>';

    if(!provinceId) {
        citySelect.innerHTML = '<option value="">请先选择省份</option>';
        return;
    }

    try {
        const response = await fetch(`get_cities.php?province_id=${provinceId}`);
        const cities = await response.json();
        
        citySelect.innerHTML = '<option value="">请选择城市</option>';
        cities.forEach(city => {
            const option = document.createElement('option');
            option.value = city.id;
            option.textContent = city.name;
            citySelect.appendChild(option);
        });
        citySelect.disabled = false;
    } catch(e) {
        citySelect.innerHTML = '<option value="">加载失败，请重试</option>';
    }
});
</script>

内容的提问来源于stack exchange，提问作者Ade Guntoro