MySQL实现INSERT或UPDATE去重及XML分类数据同步问题

你现在的需求是：

• 把包含**分类（category）和子分类（subcategory）**的XML数据同步到MySQL数据库
• 每日自动检测XML文件是否有变更，有变更时更新数据库
• 现有PHP导入代码会重复插入所有数据，需要改成「不存在则插入、存在则更新」的逻辑

现有代码示例（你提供的片段）：

<?php 
// URL为示例，非真实地址 
$url = 'http://xml.com/cate...';
// 后续XML解析和插入逻辑...
?>

要解决这个问题，需要从三个关键点入手：

1. 定义数据的唯一标识（避免重复的依据） #

首先要给你的分类和子分类表设置唯一键约束，这是实现「插新更旧」的基础：

• 分类表：可以用XML中自带的唯一category_id作为唯一键，或者如果分类名称在全局唯一，也可以用name字段
• 子分类表：通常用subcategory_id作为唯一键，或者用name + parent_category_id的联合唯一键（确保同一父分类下子分类名称不重复）

比如创建分类表的SQL：

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id VARCHAR(50) NOT NULL UNIQUE, -- 来自XML的唯一ID
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

子分类表：

CREATE TABLE subcategories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subcategory_id VARCHAR(50) NOT NULL UNIQUE, -- 来自XML的唯一ID
    category_id VARCHAR(50) NOT NULL, -- 关联父分类
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

2. 使用MySQL的INSERT ... ON DUPLICATE KEY UPDATE语法 #

这是实现「不存在则插入、存在则更新」的核心语法，当插入的数据触发唯一键冲突时，会自动执行指定的更新操作。

3. 先检测XML是否变更，避免无效操作 #

每次运行脚本时，先判断XML文件（或远程XML内容）是否有变化，只有变化时才执行同步逻辑，节省服务器资源。可以通过以下两种方式判断：

• 比对远程XML的Last-Modified响应头（如果是远程URL）
• 计算XML内容的哈希值（比如MD5），和上次存储的哈希值比对

下面是整合了以上逻辑的示例代码：

<?php
// 数据库配置
$dbHost = 'localhost';
$dbUser = 'your_username';
$dbPass = 'your_password';
$dbName = 'your_database';

// XML源地址
$xmlUrl = 'http://xml.com/cate...';

// 存储上次XML哈希值的文件路径（可根据实际调整）
$hashFile = '/tmp/xml_last_hash.txt';

// 1. 检测XML是否有变更
function isXmlUpdated($xmlUrl, $hashFile) {
    // 获取XML内容
    $xmlContent = file_get_contents($xmlUrl);
    if (!$xmlContent) {
        return false;
    }
    // 计算当前哈希值
    $currentHash = md5($xmlContent);
    
    // 读取上次的哈希值
    $lastHash = file_exists($hashFile) ? file_get_contents($hashFile) : '';
    
    if ($currentHash !== $lastHash) {
        // 更新哈希值文件
        file_put_contents($hashFile, $currentHash);
        return true;
    }
    return false;
}

// 2. 连接数据库
$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($conn->connect_error) {
    die("数据库连接失败: " . $conn->connect_error);
}

// 3. 如果XML有变更，执行同步逻辑
if (isXmlUpdated($xmlUrl, $hashFile)) {
    // 解析XML
    $xml = simplexml_load_file($xmlUrl);
    if (!$xml) {
        die("XML解析失败");
    }
    
    // 处理分类数据
    foreach ($xml->category as $categoryNode) {
        $categoryId = (string)$categoryNode->category_id;
        $name = (string)$categoryNode->name;
        $description = (string)$categoryNode->description;
        
        // 使用INSERT ... ON DUPLICATE KEY UPDATE
        $sql = "INSERT INTO categories (category_id, name, description)
                VALUES (?, ?, ?)
                ON DUPLICATE KEY UPDATE 
                    name = VALUES(name), 
                    description = VALUES(description)";
        
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sss", $categoryId, $name, $description);
        $stmt->execute();
        $stmt->close();
    }
    
    // 处理子分类数据
    foreach ($xml->subcategory as $subcategoryNode) {
        $subcategoryId = (string)$subcategoryNode->subcategory_id;
        $categoryId = (string)$subcategoryNode->category_id;
        $name = (string)$subcategoryNode->name;
        $description = (string)$subcategoryNode->description;
        
        $sql = "INSERT INTO subcategories (subcategory_id, category_id, name, description)
                VALUES (?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE 
                    category_id = VALUES(category_id), 
                    name = VALUES(name), 
                    description = VALUES(description)";
        
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ssss", $subcategoryId, $categoryId, $name, $description);
        $stmt->execute();
        $stmt->close();
    }
    
    echo "数据同步完成";
} else {
    echo "XML未变更，无需同步";
}

// 关闭数据库连接
$conn->close();
?>

• 如果你的XML结构和示例不同（比如分类嵌套在子节点里），需要调整XML解析的循环逻辑，确保正确获取每个分类和子分类的字段
• 对于大型XML文件，建议使用XMLReader代替simplexml_load_file，避免内存溢出
• 可以把脚本加入服务器的定时任务（比如Linux的Crontab），实现每日自动运行：

  # 每天凌晨2点运行脚本
  0 2 * * * /usr/bin/php /path/to/your/xml_sync.php
  

内容的提问来源于stack exchange，提问作者Iva Kobalava