mysql三级联动一张表还是三张表好用

原创

mob649e8154b5bf 2024-11-12 04:10:37 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e8154b5bf的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 三级联动实现：一张表还是三张表好用？

在现代的Web开发中，数据的展示和交互是非常重要的一部分。在很多情况下，我们需要实现三级联动的功能，比如在选择一个国家时，自动更新对应的省份和城市信息。在实现这一功能时，我们常常面临一个选择：使用一张表（通过数据字段进行关联）还是三张表（分别存储国家、省份、城市）。下面，我们将详细探讨这两种方法的优缺点，并提供具体的实现步骤。

1. 流程概述

在实现三级联动的过程中，我们通常遵循以下步骤：

步骤	操作
1	设计数据库结构
2	创建数据库表
3	插入数据
4	编写后端接口
5	编写前端页面及逻辑
6	测试并优化

接下来，我们将依次讲解每个步骤。

2. 设计数据库结构

我们可以选择三张表，分别存储国家、省份和城市。此时的关系图如下：

erDiagram
    COUNTRIES {
        INT id PK
        STRING name
    }
    PROVINCES {
        INT id PK
        STRING name
        INT country_id FK
    }
    CITIES {
        INT id PK
        STRING name
        INT province_id FK
    }
    
    COUNTRIES ||--o| PROVINCES : has
    PROVINCES ||--o| CITIES : has

每个表的字段说明：

COUNTRIES 表：存储国家信息，包含 id 和 name 字段。
PROVINCES 表：存储省份信息，包含 id、name 和 country_id（外键，关联COUNTRIES表）。
CITIES 表：存储城市信息，包含 id、name 和 province_id（外键，关联PROVINCES表）。

3. 创建数据库表

接下来，我们依次创建三张表，可以使用以下SQL语句：

-- 创建国家表
CREATE TABLE Countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 创建省份表
CREATE TABLE Provinces (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES Countries(id)
);

-- 创建城市表
CREATE TABLE Cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    province_id INT,
    FOREIGN KEY (province_id) REFERENCES Provinces(id)
);

解释：

AUTO_INCREMENT：表示此字段会自动增加。
FOREIGN KEY：用于设定外键约束，确保数据的完整性。

4. 插入数据

在创建完表后，我们需要插入一些模拟数据。可以使用以下SQL语句：

-- 插入国家数据
INSERT INTO Countries (name) VALUES ('中国'), ('美国'), ('加拿大');

-- 插入省份数据
INSERT INTO Provinces (name, country_id) VALUES 
('广东省', 1), 
('江苏省', 1),
('加利福尼亚州', 2),
('安大略省', 3);

-- 插入城市数据
INSERT INTO Cities (name, province_id) VALUES 
('广州', 1), 
('深圳', 1), 
('南京', 2), 
('洛杉矶', 3),
('多伦多', 4);

解释：

VALUES 中的每一项都是一行记录的数据。

5. 编写后端接口

我们可以使用Node.js和Express来编写后端接口，以下是示例代码：

const express = require('express');
const mysql = require('mysql');
const app = express();
const port = 3000;

// 创建数据库连接
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'yourpassword',
    database: 'yourdatabase'
});

// 连接数据库
connection.connect();

// 获取国家数据
app.get('/api/countries', (req, res) => {
    connection.query('SELECT * FROM Countries', (err, results) => {
        if (err) throw err;
        res.json(results);
    });
});

// 根据国家ID获取省份数据
app.get('/api/provinces/:countryId', (req, res) => {
    connection.query('SELECT * FROM Provinces WHERE country_id = ?', [req.params.countryId], (err, results) => {
        if (err) throw err;
        res.json(results);
    });
});

// 根据省份ID获取城市数据
app.get('/api/cities/:provinceId', (req, res) => {
    connection.query('SELECT * FROM Cities WHERE province_id = ?', [req.params.provinceId], (err, results) => {
        if (err) throw err;
        res.json(results);
    });
});

// 启动服务器
app.listen(port, () => {
    console.log(`Server running at http://localhost:${port}`);
});

解释：

通过设置接口，前端可以通过调用这些API获取相应的国家、省份和城市数据。
connection.query 用于执行SQL查询。

6. 编写前端页面及逻辑

基本的前端页面可以使用HTML和JavaScript来实现：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>三级联动</title>
</head>
<body>
    <select id="country"></select>
    <select id="province"></select>
    <select id="city"></select>

    <script>
        // 获取国家数据
        fetch('/api/countries')
            .then(response => response.json())
            .then(data => {
                const countrySelect = document.getElementById('country');
                data.forEach(country => {
                    const option = document.createElement('option');
                    option.value = country.id;
                    option.textContent = country.name;
                    countrySelect.appendChild(option);
                });
            });

        // 选择国家后加载省份
        document.getElementById('country').addEventListener('change', function() {
            const countryId = this.value;
            fetch(`/api/provinces/${countryId}`)
                .then(response => response.json())
                .then(data => {
                    const provinceSelect = document.getElementById('province');
                    provinceSelect.innerHTML = ''; // 清空省份选择
                    data.forEach(province => {
                        const option = document.createElement('option');
                        option.value = province.id;
                        option.textContent = province.name;
                        provinceSelect.appendChild(option);
                    });
                });
        });

        // 选择省份后加载城市
        document.getElementById('province').addEventListener('change', function() {
            const provinceId = this.value;
            fetch(`/api/cities/${provinceId}`)
                .then(response => response.json())
                .then(data => {
                    const citySelect = document.getElementById('city');
                    citySelect.innerHTML = ''; // 清空城市选择
                    data.forEach(city => {
                        const option = document.createElement('option');
                        option.value = city.id;
                        option.textContent = city.name;
                        citySelect.appendChild(option);
                    });
                });
        });
    </script>
</body>
</html>

解释：