我的公司使用内部管理软件来存储产品。他们想将所有的产品转移到一个 MySql 数据库中，这样他们就可以在公司网站上提供他们的产品。

注意:他们将继续使用自己的内部软件。该软件可以以各种文件格式(包括 XML)导出所有产品。

同步不一定是实时的，每天(深夜)同步一次MySql数据库就满足了。

此外，他们软件中的每个产品都有一张或多张图片，那么我也必须在网站上提供图片。

这是一个 XML 导出的例子:

<?xml version="1.0" encoding="UTF-8"?>
<export_management userid="78643">
    <product id="1234">
        <version>100</version>
        <insert_date>2013-12-12 00:00:00</insert_date>
        <warrenty>true</warrenty>
        <price>139,00</price>
        <model>
            <code>324234345</code>
            <model>Notredame</model>
            <color>red</color>
            <size>XL</size>
        </model>
        <internal>
            <color>green</color>
            <size>S</size>
        </internal>
        <options>
            <s_option>aaa</s_option>
            <s_option>bbb</s_option>
            <s_option>ccc</s_option>
            <s_option>ddd</s_option>
            <s_option>eee</s_option>
            <s_option>fff</s_option>
      ...
            <extra_option>ggg</extra_option>
            <extra_option>hhh</extra_option>
            <extra_option>jjj</extra_option>
            <extra_option>kkk</extra_option>
      ...
        </options>
        <images>
            <image>
                <small>1234_0.jpg</small>
            </image>
            <image>
                <small>1234_1.jpg</small>
            </image>
        </images>
    </product>
    <product id="5321">
    ...
    </product>
    <product id="2621">
    ...
    </product>
  ...
</export_management>

关于我该怎么做的一些想法？

如果我的问题不清楚，请告诉我。谢谢

编辑: 我对每个表使用了这样的 SQL 来用 XML 数据填充它们:

LOAD XML LOCAL INFILE '/products.xml' INTO TABLE table_name ROWS IDENTIFIED BY '<tag_name>';

然后，检查表格内容我可以看到字段“id”(主键)自动为每个表格中的每个产品行保持相同。这是正确的，而且非常棒!

现在的问题是参数 <options>因为它包含具有相同名称的子参数( <s_option> 和 <extra_option> )。这些标签的值总是不同的(也就是说，没有特定的值列表，它们是由员工手动插入的)而且我也不知道每个产品有多少。我读到将它们存储为数组不太好，但如果这是唯一简单的解决方案，我可以得到它。

最佳答案

在您的情况下，我处理问题的方式是:

1. 在数据库中创建一组相应的表，这些表将通过从给定的 XML 中提取模型来代表公司的产品模型。

2. 创建并使用计划的每日同步作业，该作业可能会执行少量 SQL 命令以刷新数据或通过解析产品 XML 将产品 XML 引入创建的表中来引入新命令。

为了更加实际:

• 至于数据库的表，我可以根据您的 XML 轻松识别要创建的三个表，请查看黄色标记的元素:

  1. 产品
  2. 产品选项
  3. 产品图片

(此图表基于从您的 XML 生成的 XSD 创建)

所有其余部分都可以视为 Products 表中的常规列，因为它们仅构成 1-1 关系。

接下来，在您的数据库中创建所需的表(您可以使用 XSD2DB Schema 转换器工具来创建 DDL 脚本，我是手动完成的):

companydb.products

CREATE TABLE companydb.products (
  Id INT(11) NOT NULL,
  Version INT(11) DEFAULT NULL,
  InsertDate DATETIME DEFAULT NULL,
  Warrenty TINYINT(1) DEFAULT NULL,
  Price DECIMAL(19, 2) DEFAULT NULL,
  ModelCode INT(11) DEFAULT NULL,
  ModelColor VARCHAR(10) DEFAULT NULL,
  Model VARCHAR(255) DEFAULT NULL,
  ModelSize VARCHAR(10) DEFAULT NULL,
  InternalColor VARCHAR(10) DEFAULT NULL,
  InternalSize VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (Id)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Company''s Products';

companydb.productsimages

CREATE TABLE companydb.productimages (
  Id INT(11) NOT NULL AUTO_INCREMENT,
  ProductId INT(11) DEFAULT NULL,
  Size VARCHAR(10) DEFAULT NULL,
  FileName VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (Id),
  CONSTRAINT FK_productsimages_products_Id FOREIGN KEY (ProductId)
    REFERENCES companydb.products(Id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Products'' Images';

companydb.productsoptions

CREATE TABLE companydb.productoptions (
  Id INT(11) NOT NULL AUTO_INCREMENT,
  ProductId INT(11) DEFAULT NULL,
  Type VARCHAR(255) DEFAULT NULL,
  `Option` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (Id),
  CONSTRAINT FK_producstsoptions_products_Id FOREIGN KEY (ProductId)
    REFERENCES companydb.products(Id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;

• 至于要进行的同步作业过程，您可以轻松地创建一个 MySql event并使用 Event Scheduler为了控制它，我创建了所需的事件，它调用了一个存储过程，您将在下面找到它 (SyncProductsDataFromXML)，看:

CREATE DEFINER = 'root'@'localhost' EVENT companydb.ProductsDataSyncEvent ON SCHEDULE EVERY '1' DAY STARTS '2014-06-13 01:27:38' COMMENT 'Synchronize Products table with Products XMLs' DO BEGIN SET @productsXml = LOAD_FILE('C:/MySqlXmlSync/products.xml'); CALL SyncProductsDataFromXML(@productsXml); END; ALTER EVENT companydb.ProductsDataSyncEvent ENABLE

现在有趣的部分开始了，这是同步存储过程(注意上面的 event 是如何调用它的):

CREATE DEFINER = 'root'@'localhost'
PROCEDURE companydb.SyncProductsDataFromXML(IN productsXml MEDIUMTEXT)
BEGIN

  DECLARE totalProducts INT;
  DECLARE productIndex INT;

  SET totalProducts = ExtractValue(productsXml, 'count(//export_management/product)');

  SET productIndex = 1;
  WHILE productIndex <= totalProducts DO
        SET @productId = CAST(ExtractValue(productsXml, 'export_management/product[$productIndex]/@id') AS UNSIGNED);

        INSERT INTO products(`Id`, `Version`, InsertDate, Warrenty, Price, ModelCode, Model, ModelColor, ModelSize, InternalColor, InternalSize)
        VALUES(
                @productId,
                ExtractValue(productsXml, 'export_management/product[$productIndex]/version'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/insert_date'),
                CASE WHEN (ExtractValue(productsXml, 'export_management/product[$productIndex]/warrenty')) <> 'false' THEN 1 ELSE 0 END,
                CAST(ExtractValue(productsXml, 'export_management/product[$productIndex]/price') as DECIMAL),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/model/code'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/model/model'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/model/color'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/model/size'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/internal/color'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/internal/size')
              );

        SET @totalImages = ExtractValue(productsXml, 'count(//export_management/product[$productIndex]/images/image)');
        SET @imageIndex = 1;
        WHILE (@imageIndex <= @totalImages) DO
          INSERT INTO productimages(ProductId, Size, FileName) VALUES(@productId, 'small', EXTRACTVALUE(productsXml, 'export_management/product[$productIndex]/images/image[$@imageIndex]/small'));
          SET @imageIndex = @imageIndex + 1;        
        END WHILE;

        SET @totalStandardOptions = ExtractValue(productsXml, 'count(//export_management/product[$productIndex]/options/s_option)');
        SET @standardOptionIndex = 1;
        WHILE (@standardOptionIndex <= @totalStandardOptions) DO
          INSERT INTO productoptions(ProductId, `Type`, `Option`) VALUES(@productId, 'Standard Option', EXTRACTVALUE(productsXml, 'export_management/product[$productIndex]/options/s_option[$@standardOptionIndex]'));
          SET @standardOptionIndex = @standardOptionIndex + 1;
        END WHILE;

        SET @totalExtraOptions = ExtractValue(productsXml, 'count(//export_management/product[$productIndex]/options/extra_option)');
        SET @extraOptionIndex = 1;
        WHILE (@extraOptionIndex <= @totalExtraOptions) DO
          INSERT INTO productoptions(ProductId, `Type`, `Option`) VALUES(@productId, 'Extra Option', EXTRACTVALUE(productsXml, 'export_management/product[$productIndex]/options/extra_option[$@extraOptionIndex]'));
          SET @extraOptionIndex = @extraOptionIndex + 1;        
        END WHILE;

        SET productIndex = productIndex + 1;

  END WHILE;
END

大功告成，这是此过程的最终预期结果:

注意:我已将整个代码提交到我的 GitHub 存储库之一:XmlSyncToMySql

更新:

因为您的 XML 数据可能大于 TEXT 字段允许的最大值，我已将 productsXml 参数更改为 MEDIUMTEXT .查看此答案，其中概述了各种文本数据类型的最大允许大小: Maximum length for MYSQL type text

关于mysql - 将 XML 文件同步到 MySQL 数据库，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24080516/