如何使用 XML_LOAD() 将 XML 文件导入 MySQL 数据库表；功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5491056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to import XML file into MySQL database table using XML_LOAD(); function
提问by Cninroh
I have an XML file which looks like this :
我有一个如下所示的 XML 文件：
<?xml version="1.0" encoding="UTF-8"?>
<resultset statement="YOUR SQL STATEMENTS TO GENERATE THIS XML FILE" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="personal_number">539</field>
<field name="firstname">Name</field>
<field name="lastname">Surname</field>
<field name="email">email.domain.com</field>
<field name="start_time">2011-04-02 13:30:00</field>
<field name="end_time">2011-04-02 18:15:00</field>
<field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
</row>
<row>
<field name="personal_number">539</field>
<field name="firstname">Name</field>
<field name="lastname">Surname</field>
<field name="email">email.domain.com</field>
<field name="start_time">2011-04-02 13:30:00</field>
<field name="end_time">2011-04-02 18:15:00</field>
<field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
</row>
<row>
<field name="personal_number">539</field>
<field name="firstname">Name</field>
<field name="lastname">Surname</field>
<field name="email">email.domain.com</field>
<field name="start_time">2011-04-02 13:30:00</field>
<field name="end_time">2011-04-02 18:15:00</field>
<field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
</row>
I am trying to import it in MySQL using SQL statement :
我正在尝试使用 SQL 语句将其导入 MySQL：
use databasename;
LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename;
The table my_tablename has the following fields :
表 my_tablename 具有以下字段：
id (auto increment id)
personal_number(varchar)
firstname(varchar)
lastname(varchar)
email(varchar)
start_time(varchar)
end_time(varchar)
employee_category(varchar)
I get error : Error Code: 1136 Column count doesn't match value count at row 1
我收到错误：错误代码：1136 列计数与第 1 行的值计数不匹配
I am using MySQL 5.1.56
我正在使用 MySQL 5.1.56
I assume this error occurs because the database table has field id, which is not present in the XML file. How is it possible to import this XML file using MySQL queries of built in functions such that it skips id column during the import and relies on the auto increment function for the id column? Is there some smarter way of handling XML file imports im MySQL? Maybe there is better statement which allows to specify column mapping?
我假设发生此错误是因为数据库表具有字段 id，该字段在 XML 文件中不存在。如何使用内置函数的 MySQL 查询导入此 XML 文件，以便在导入过程中跳过 id 列并依赖于 id 列的自动增量功能？是否有一些更智能的方法来处理 MySQL 中的 XML 文件导入？也许有更好的语句允许指定列映射？
Thank you!
谢谢！
采纳答案by dnagirl
you can specify fields like this:
您可以指定如下字段：
LOAD XML LOCAL INFILE '/pathtofile/file.xml'
INTO TABLE my_tablename(personal_number, firstname, ...);
回答by Jayanth
Since ID is auto increment, you can also specify ID=NULL as,
由于 ID 是自动递增的，您还可以将 ID=NULL 指定为，
LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename SET ID=NULL;
LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename SET ID=NULL;
