如何将XML数据同步至MySQL的两个关联表并每日自动更新

没问题！完全可以按照你说的思路来实现——用两个关联表存储分类和子分类，再加上每日自动检测XML变更同步的逻辑。下面我给你详细拆解整个方案：

首先创建两个关联表，确保子分类和主分类的关联关系清晰：

1. 主分类表 (categories) #

这个表存储所有一级分类，保证分类名称唯一：

CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL UNIQUE,
    `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 子分类表 (subcategories) #

子分类表通过category_id外键关联主分类表的id，实现多对一的关联：

CREATE TABLE IF NOT EXISTS `subcategories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_subcategory_per_category` (`category_id`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注：添加unique_subcategory_per_category唯一键是为了避免同一分类下出现重复子分类

下面是完整的同步脚本，包含XML变更检测、XML解析和数据库同步三个核心部分：

<?php
// 数据库配置
$dbHost = 'localhost';
$dbUser = 'your_db_user';
$dbPass = 'your_db_password';
$dbName = 'your_db_name';

// XML文件路径（本地或远程，远程可先下载到本地）
$xmlFilePath = '/path/to/your/categories.xml';
// 存储上次同步的文件哈希值（用文件存储示例，也可存在数据库）
$hashStorePath = '/path/to/last_sync_hash.txt';

// 1. 检测XML文件是否有变更
function isXmlUpdated($xmlPath, $hashStorePath) {
    // 计算当前XML文件的MD5哈希
    $currentHash = md5_file($xmlPath);
    // 获取上次同步的哈希值
    $lastHash = file_exists($hashStorePath) ? file_get_contents($hashStorePath) : '';

    if ($currentHash !== $lastHash) {
        // 更新存储的哈希值
        file_put_contents($hashStorePath, $currentHash);
        return true;
    }
    return false;
}

// 2. 解析XML数据（适配常见的category/subcategory结构）
function parseXml($xmlPath) {
    $xml = simplexml_load_file($xmlPath);
    if (!$xml) {
        throw new Exception('Failed to parse XML file');
    }

    $categories = [];
    foreach ($xml->category as $categoryNode) {
        $categoryName = (string)$categoryNode['name'];
        $subcategories = [];
        foreach ($categoryNode->subcategory as $subNode) {
            $subcategories[] = (string)$subNode['name'];
        }
        $categories[$categoryName] = $subcategories;
    }
    return $categories;
}

// 3. 同步数据到数据库（支持新增/更新/删除）
function syncToDatabase($categories, $pdo) {
    try {
        $pdo->beginTransaction();

        // 先处理主分类：插入或更新
        foreach ($categories as $catName => $subs) {
            // 使用INSERT ... ON DUPLICATE KEY UPDATE避免重复插入
            $stmt = $pdo->prepare("
                INSERT INTO categories (name) 
                VALUES (:name) 
                ON DUPLICATE KEY UPDATE last_updated = CURRENT_TIMESTAMP
            ");
            $stmt->execute([':name' => $catName]);
            // 获取当前分类ID（新插入用lastInsertId，否则查询获取）
            $catId = $pdo->lastInsertId();
            if (!$catId) {
                $stmt = $pdo->prepare("SELECT id FROM categories WHERE name = :name");
                $stmt->execute([':name' => $catName]);
                $catId = $stmt->fetchColumn();
            }

            // 可选：删除当前分类下不在XML中的子分类（按需开启）
            $subNamesStr = implode("','", $subs);
            $stmt = $pdo->prepare("
                DELETE FROM subcategories 
                WHERE category_id = :catId AND name NOT IN ('$subNamesStr')
            ");
            $stmt->execute([':catId' => $catId]);

            // 插入或更新子分类
            foreach ($subs as $subName) {
                $stmt = $pdo->prepare("
                    INSERT INTO subcategories (category_id, name) 
                    VALUES (:catId, :subName) 
                    ON DUPLICATE KEY UPDATE last_updated = CURRENT_TIMESTAMP
                ");
                $stmt->execute([':catId' => $catId, ':subName' => $subName]);
            }
        }

        $pdo->commit();
        echo "Data synced successfully!";
    } catch (Exception $e) {
        $pdo->rollBack();
        throw new Exception('Sync failed: ' . $e->getMessage());
    }
}

// 主执行流程
try {
    // 连接数据库（开启异常模式便于调试）
    $pdo = new PDO(
        "mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4",
        $dbUser,
        $dbPass,
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );

    if (isXmlUpdated($xmlFilePath, $hashStorePath)) {
        $categoriesData = parseXml($xmlFilePath);
        syncToDatabase($categoriesData, $pdo);
    } else {
        echo "XML file has no changes, skip sync.";
    }
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>

把这个脚本放到服务器上，通过Cron任务实现每日自动执行：

1. 登录服务器，执行crontab -e
2. 添加一行配置（比如每天凌晨2点执行，日志输出到指定文件）：

0 2 * * * /usr/bin/php /path/to/your/sync_script.php >> /path/to/sync_logs.log 2>&1

这样脚本就会每天自动检测XML是否变更，有变更就同步数据库了。

小提示：如果XML是远程资源，可以先用curl或file_get_contents下载到本地临时文件再处理，避免频繁请求远程服务器。

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