列名称不一致的LOAD XML LOCAL INFILE [英] LOAD XML LOCAL INFILE with Inconsistent Column Names

问题描述

MySQL有一个不错的声明:LOAD XML LOCAL INFILE

MySQL has a nice statement: LOAD XML LOCAL INFILE

例如，如果您有此表:

 CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL
 );

以及以下名为person.xml的XML文件:

and the following XML file called person.xml:

<list>
      <person>
          <person_id>1</person_id>
          <fname>Mikael</fname>
          <lname>Ronström</lname>
      </person>
      <person>
          <person_id>2</person_id>
          <fname>Lars</fname>
          <lname>Thalmann</lname>
      </person>
</list>

您可以执行以下操作:

LOAD XML LOCAL INFILE 'person.xml'
INTO TABLE person
ROWS IDENTIFIED BY '<person>';

我的问题是，如果XML文件中的列名与表中的列名不同，该怎么办?例如:

My question is, what if the column names were different in the XML file than they are in the table? For example:

<list>
      <person>
          <PersonId>1</PersonId>
          <FirstName>Mikael</FirstName>
          <LastName>Ronström</LastName>
      </person>
      <person>
          <PersonId>2</PersonId>
          <FirstName>Lars</FirstName>
          <LastName>Thalmann</LastName>
      </person>
</list>

如何在不处理XML文件的情况下用MySQL语句完成相同的工作?我到处搜索，但找不到答案.

How can you accomplish the same thing with a MySQL statement without manipulating the XML file? I searched everywhere but couldn't find an answer.

推荐答案

以下是可供我使用的选项:

The following were the options available to me:

选项1:使用其他字段名称创建一个临时表(如其他答案所建议).这本来是令人满意的方法.但是，当我尝试它时，出现了一个新问题:由于某种原因，LOAD XML语句不接受最小化格式的空元素(例如<person />).因此，该语句失败了，因为偶尔需要加载的XML文件具有该格式的空元素.

Option 1: Create a temporary table with different field names (as suggested by the other answers). This would have been a satisfactory approach. However, when I tried it, a new problem emerged: the LOAD XML statement does not, for some reason, accept minimized format empty elements (for example <person />). So, the statement failed because the XML files I need to load occasionally have empty elements in that format.

选项2:在运行LOAD XML语句以更改元素名称和修改空元素格式之前，请先使用XSLT转换XML文件.这是不可行的，因为XML文件非常大，并且XSLT处理引擎在处理之前将整个XML加载到内存中.

Option 2: Transform the XML file with XSLT before running the LOAD XML statement to change the element names and modify the empty element formats. This was not feasible because the XML files are very large and XSLT processing engines load the entire XML into memory before processing.

选项3:完全绕过LOAD XML语句，并使用SAX解析器来解析XML文件，并使用JDBC和预处理语句将记录直接插入数据库中.尽管原始JDBC和预处理语句通常很有效，但事实证明这太慢了.比LOAD XML语句慢得多.

Option 3: Bypass the LOAD XML statement entirely and use a SAX parser to parse the XML file and insert the records directly into the database using JDBC and prepared statements. Even though raw JDBC and prepared statements are generally efficient, this proved to be too slow. MUCH slower than the LOAD XML statement.

选项4:使用LOAD DATA语句而不是LOAD XML语句，并试用与该语句关联的可选子句来满足我的需要(例如，行之间用等号分隔).这本来可以工作，但容易出错且不稳定.

Option 4: Use the LOAD DATA statement instead of the LOAD XML statement and play around with the optional clauses associated with that statement to fit my needs (e.g. lines separated by, etc.). This could have worked but would have been error prone and unstable.

选项5:使用仅快速前进的解析器解析文件，并同时读取/写入XML元素，并以LOAD XML语句所需的格式生成具有修改后名称的新XML文件.

Option 5: Parse the file with a fast forward-only parser and read/write XML elements simultaneously and generate a new XML file with the modified names in the desired format for the LOAD XML statement.

我最终使用了选项5.我使用Java XML流API(StAX)读取XML文件并生成修改后的XML文件，然后从Web应用程序内部通过JDBC运行LOAD XML LOCAL INFILE.它运行完美，速度超快.

I ended up using option 5. I used the Java Streaming API for XML (StAX) for both reading the XML file and generating the modified XML file and then running the LOAD XML LOCAL INFILE through JDBC from inside the web application. It works perfectly and it is super fast.

这篇关于列名称不一致的LOAD XML LOCAL INFILE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！