mysql load xml语句产生0条记录

Question

我在 mySQL 工作台查询中使用以下语句:

LOAD XML INFILE 'ProductFile.xml' INTO TABLE myDB.products 
ROWS IDENTIFIED BY '<product>';

生成以下日志:

19:15:52 LOAD XML INFILE 'ProductFile.xml' INTO TABLE myDB.products ROWS IDENTIFIED BY '' 0 row(s) affected Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 0.000 sec

ProductFile.xml 包含以下格式的数据:

<?xml version="1.0" encoding="ISO-8859-1"?>
<products>
  <product>
    <name>Thing 1</name>
    <productUrl>http://www.productsite.com/linkofsomesorttothing1</productUrl>
    <ProductId>1234567890</ProductId>
  </product>
  <product>
    <name>Thing 2</name>
    <productUrl>http://www.productsite.com/linkofsomesorttothing2</productUrl>
    <ProductId>1234567891</ProductId>
  </product>
</products>

myDB.products如下:

CREATE TABLE IF NOT EXISTS `myDB`.`Products` (
  `ProductId` INT UNSIGNED NOT NULL,
  `name` VARCHAR(70) NOT NULL,
  `productUrl` TEXT NULL,
   PRIMARY KEY (`ProductId`),
   UNIQUE INDEX `ProductId_UNIQUE` (`ProductId` ASC)
 ) ENGINE=InnoDB COMMENT='\n';

谁能告诉我为什么它没有产生输出？

Answer 1

我无法重现该问题。

文件:/path/to/file/ProductFile.xml

<?xml version="1.0" encoding="ISO-8859-1"?>
<products>
  <product>
    <name>Thing 1</name>
    <productUrl>http://www.productsite.com/linkofsomesorttothing1</productUrl>
    <ProductId>1234567890</ProductId>
  </product>
  <product>
    <name>Thing 2</name>
    <productUrl>http://www.productsite.com/linkofsomesorttothing2</productUrl>
    <ProductId>1234567891</ProductId>
  </product>
</products>

MySQL 命令行:

mysql> DROP TABLE IF EXISTS `Products`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `Products` (
    ->   `ProductId` INT UNSIGNED NOT NULL,
    ->   `name` VARCHAR(70) NOT NULL,
    ->   `productUrl` TEXT,
    ->   PRIMARY KEY (`ProductId`),
    ->   UNIQUE KEY `ProductId_UNIQUE` (`ProductId`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='\n';
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD XML INFILE '/path/to/file/ProductFile.xml'
    ->   INTO TABLE `Products`
    ->   ROWS IDENTIFIED BY '<product>';
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    ->   `ProductId`,
    ->   `name`,
    ->   `productUrl`
    -> FROM
    ->   `Products`;
+------------+---------+---------------------------------------------------+
| ProductId  | name    | productUrl                                        |
+------------+---------+---------------------------------------------------+
| 1234567890 | Thing 1 | http://www.productsite.com/linkofsomesorttothing1 |
| 1234567891 | Thing 2 | http://www.productsite.com/linkofsomesorttothing2 |
+------------+---------+---------------------------------------------------+
2 rows in set (0.00 sec)