如何在 PHP + MySQL 中正确实现用户国家偏好存储（推荐关系型设计）

本文详解如何避免将国家 ID 数组直接存为字符串，而是采用符合数据库范式的多对多关系设计，提升查询性能、数据完整性和可维护性。

本文详解如何避免将国家 ID 数组直接存为字符串，而是采用符合数据库范式的多对多关系设计，提升查询性能、数据完整性和可维护性。

在构建新闻类应用时，让用户按国家筛选内容是常见需求。但若将用户选择的国家 ID（如 ['1', '5', '12']）直接用 implode() 拼接后存入单个字段（如 country_selection VARCHAR(2000)），虽能快速实现，却违背第一范式（1NF），带来严重隐患：无法高效查询（如“哪些用户订阅了日本？”）、难以维护外键约束、无法利用索引加速 JOIN、且更新逻辑脆弱（如取消某国需字符串解析+重组）。

✅ 正确做法：采用规范化的关系表结构
应建立三张核心表：

-- 已存在的国家主表（确保有主键）
CREATE TABLE countries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    code CHAR(2) UNIQUE
);

-- 新增：用户-国家关联表（无业务意义的纯关系表）
CREATE TABLE user_country_preferences (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    country_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_country (user_id, country_id), -- 防重复
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
);

? 关键优势：

• ✅ 支持原子化增删：勾选/取消一个国家只需一条 INSERT 或 DELETE；
• ✅ 查询极致高效：获取用户订阅的新闻，只需标准 JOIN：

  SELECT n.* FROM news n
  INNER JOIN user_country_preferences ucp ON n.country_id = ucp.country_id
  WHERE ucp.user_id = ?;

• ✅ 天然支持统计分析：如 SELECT country_id, COUNT(*) FROM user_country_preferences GROUP BY country_id ORDER BY COUNT(*) DESC;；
• ✅ 完整性保障：外键自动拒绝非法 country_id，ON DELETE CASCADE 确保国家删除时清理关联记录。

? PHP 实现示例（安全、健壮）：

public static function saveCountryPreferences($selectedCountryIds) {
    $database = DatabaseFactory::getFactory()->getConnection();

    // 1. 获取当前用户ID（建议使用已验证的Session）
    $userId = Session::get('user_id');
    if (!$userId) {
        throw new InvalidArgumentException("User not logged in");
    }

    // 2. 清理旧偏好（原子性：先删后插，或用 REPLACE/INSERT ... ON DUPLICATE KEY UPDATE）
    $deleteSql = "DELETE FROM user_country_preferences WHERE user_id = :user_id";
    $deleteStmt = $database->prepare($deleteSql);
    $deleteStmt->execute([':user_id' => $userId]);

    // 3. 批量插入新偏好（防御空数组）
    if (!empty($selectedCountryIds)) {
        // 过滤并校验ID为正整数（防注入）
        $cleanIds = array_filter(array_map('intval', $selectedCountryIds), function($id) {
            return $id > 0;
        });

        if (!empty($cleanIds)) {
            $placeholders = str_repeat('(:user_id, ?),', count($cleanIds) - 1) . '(:user_id, ?)';
            $insertSql = "INSERT INTO user_country_preferences (user_id, country_id) VALUES $placeholders";
            $insertStmt = $database->prepare($insertSql);

            // 绑定用户ID一次，再绑定所有country_id
            $params = array_fill(0, count($cleanIds), $userId);
            $params = array_merge($params, $cleanIds);
            $insertStmt->execute($params);
        }
    }

    return true;
}

⚠️ 注意事项：

• 永远不要拼接 SQL 字符串（如 "IN (".$user_countries.")"），必须使用预处理语句防止 SQL 注入；
• 若需高频读取（如每次新闻列表渲染），可考虑缓存用户国家ID数组（如 Redis），但底层存储仍应坚持关系表；
• 前端渲染复选框时，务必从 countries 表动态生成，确保与数据库一致：

  $stmt = $pdo->query("SELECT id, name FROM countries ORDER BY name");
  while ($country = $stmt->fetch()) {
      $checked = in_array((string)$country['id'], $currentUserCountries) ? 'checked' : '';
      echo "<label><input type='checkbox' name='countries[]' value='{$country['id']}' $checked> {$country['name']}</label>";
  }

总结：看似多建一张表增加了复杂度，实则换来长期可扩展性与数据可靠性。用 implode() 存数组是技术债，而规范化设计是面向未来的工程实践——它让“用户订阅管理”从一个易出错的手动操作，转变为可审计、可优化、可组合的系统能力。