省市区三级联动mysql

基础概念

省市区三级联动是指在一个系统中，用户可以通过选择省份来动态加载对应的城市列表，再通过选择城市来动态加载对应的区县列表。这种功能通常用于地址选择、物流配送、地理位置定位等场景。

类型

静态数据：将所有省市区的数据预先存储在前端，通过JavaScript实现联动效果。
动态数据：通过后端API接口动态获取省市区数据，前端根据用户选择调用相应的API。

应用场景

电商平台的地址选择：用户在下单时选择收货地址。
地图应用：用户选择特定区域进行地图展示。
物流系统：根据用户地址选择最优配送路径。

数据库设计

假设我们有一个名为region的表，结构如下：

CREATE TABLE region (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES region(id)
);

其中，id是区域的唯一标识，name是区域的名称，parent_id是父区域的ID。

示例代码

后端（Python + Flask）

from flask import Flask, jsonify, request
import mysql.connector

app = Flask(__name__)

def get_regions(parent_id):
    conn = mysql.connector.connect(user='user', password='password', host='host', database='database')
    cursor = conn.cursor(dictionary=True)
    query = "SELECT id, name FROM region WHERE parent_id = %s"
    cursor.execute(query, (parent_id,))
    regions = cursor.fetchall()
    cursor.close()
    conn.close()
    return regions

@app.route('/regions', methods=['GET'])
def regions():
    parent_id = request.args.get('parent_id', type=int)
    regions = get_regions(parent_id)
    return jsonify(regions)

if __name__ == '__main__':
    app.run(debug=True)

前端（HTML + JavaScript）

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>省市区三级联动</title>
</head>
<body>
    <select id="province" onchange="loadCities()">
        <option value="">请选择省份</option>
    </select>
    <select id="city" onchange="loadDistricts()">
        <option value="">请选择城市</option>
    </select>
    <select id="district">
        <option value="">请选择区县</option>
    </select>

    <script>
        async function loadRegions(parent_id) {
            const response = await fetch(`/regions?parent_id=${parent_id}`);
            const regions = await response.json();
            return regions;
        }

        async function loadProvinces() {
            const provinces = await loadRegions(null);
            const provinceSelect = document.getElementById('province');
            provinces.forEach(province => {
                const option = document.createElement('option');
                option.value = province.id;
                option.textContent = province.name;
                provinceSelect.appendChild(option);
            });
        }

        async function loadCities() {
            const provinceSelect = document.getElementById('province');
            const citySelect = document.getElementById('city');
            citySelect.innerHTML = '<option value="">请选择城市</option>';
            const districtSelect = document.getElementById('district');
            districtSelect.innerHTML = '<option value="">请选择区县</option>';
            const provinceId = provinceSelect.value;
            const cities = await loadRegions(provinceId);
            cities.forEach(city => {
                const option = document.createElement('option');
                option.value = city.id;
                option.textContent = city.name;
                citySelect.appendChild(option);
            });
        }

        async function loadDistricts() {
            const citySelect = document.getElementById('city');
            const districtSelect = document.getElementById('district');
            districtSelect.innerHTML = '<option value="">请选择区县</option>';
            const cityId = citySelect.value;
            const districts = await loadRegions(cityId);
            districts.forEach(district => {
                const option = document.createElement('option');
                option.value = district.id;
                option.textContent = district.name;
                districtSelect.appendChild(option);
            });
        }

        loadProvinces();
    </script>
</body>
</html>