mysql - 使用不一致的列名加载 XML LOCAL INFILE

Question

MySQL 有一个很好的语句: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 文件:

<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 文件中的列名与表中的列名不同怎么办？例如:

<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 语句完成同样的事情？我到处搜索但找不到答案。

Answer 1

XML 文件中与物理列名称不对应的字段将被忽略。并且在 XML 中没有对应字段的表中的列被设置为 NULL。

我要做的是按照@Kolink 的建议加载到临时表中，但要有额外的列。从 XML 加载数据时添加 SET 子句。

CREATE TEMP TABLE person_xml LIKE person;

ALTER TABLE person_xml 
  ADD COLUMN FirstName VARCHAR(40),
  ADD COLUMN LastName  VARCHAR(40),
  ADD COLUMN PersonId  INT;

LOAD XML LOCAL INFILE 'person.xml' INTO TABLE person_xml
  SET person_id = PersonId, fname = FirstName, lname = LastName;

SELECT * FROM person_xml;
+-----------+--------+-------------+-----------+-------------+----------+
| person_id | fname  | lname       | FirstName | LastName    | PersonId |
+-----------+--------+-------------+-----------+-------------+----------+
|         1 | Mikael | Ronström    | Mikael    | Ronström    |        1 |
|         2 | Lars   | Thalmann    | Lars      | Thalmann    |        2 |
+-----------+--------+-------------+-----------+-------------+----------+

然后复制到真实表，选择列的子集。

INSERT INTO person SELECT person_id, fname, lname FROM person_xml;

或者，删除多余的列并使用 SELECT *。

ALTER TABLE person_xml 
  DROP COLUMN PersonId, 
  DROP COLUMN FirstName, 
  DROP COLUMN LastName;

INSERT INTO person SELECT * FROM person_xml;

SELECT * FROM person;

+-----------+--------+-------------+
| person_id | fname  | lname       |
+-----------+--------+-------------+
|         1 | Mikael | Ronström    |
|         2 | Lars   | Thalmann    |
+-----------+--------+-------------+