mysql - 加载 XML 空值和不正确的导入
问题描述
我在将 XML 文档导入 MySQL 数据库时遇到问题。使用 Load XML Local 会导入空值和不正确的值。我已经详细研究了这可能是什么原因，但我找不到任何相关的东西。这一切都是在我们的本地开发环境中使用 WAMPServer 3.1.0 完成的。
我从创建导入表开始。我使用 XML 文件提供程序指定的数据类型对其进行初始化，并使用我们自己的 localID 作为主键。由于 XML 文件有 encoding="iso-8859-1"，我将字符集设置为 latin1。使用 utf8 初始化表时也会发生错误。
CREATE TABLE propertyImport (
localID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
propertyID BIGINT(20),
branchID TINYINT(3),
clientName VARCHAR(100),
branchName VARCHAR(50),
department ENUM('Sales','Lettings','Commercial','Agricultural'),
referenceNumber VARCHAR(20),
addressName VARCHAR(50),
addressNumber VARCHAR(50),
addressStreet VARCHAR(50),
address2 VARCHAR(50),
address3 VARCHAR(50),
address4 VARCHAR(50),
addressPostcode VARCHAR(15),
country VARCHAR(40),
displayAddress VARCHAR(100),
propertyFeature1 VARCHAR(100),
propertyFeature2 VARCHAR(100),
propertyFeature3 VARCHAR(100),
propertyFeature4 VARCHAR(100),
propertyFeature5 VARCHAR(100),
propertyFeature6 VARCHAR(100),
propertyFeature7 VARCHAR(100),
propertyFeature8 VARCHAR(100),
propertyFeature9 VARCHAR(100),
propertyFeature10 VARCHAR(100),
dateLastModified DATE,
timeLastModified TIME,
featuredProperty TINYINT(1),
regionID INT(11),
latitude DECIMAL(9,7),
longitude DECIMAL(9,7),
mainSummary LONGTEXT,
fullDescription LONGTEXT,
propertyBedrooms TINYINT(2),
propertyBathrooms TINYINT(2),
propertyEnsuites TINYINT(2),
propertyReceptionRooms TINYINT(2),
propertyKitchens TINYINT(2),
propertyAge TINYINT(4),
floorArea DECIMAL(6,2),
floorAreaUnits VARCHAR(8),
displayPropertyType VARCHAR(50),
propertyType TINYINT(4),
propertyStyle TINYINT(4),
availability TINYINT(2),
price INT(11),
forSalePOA TINYINT(1),
priceQualifier SMALLINT(4),
propertyTenure SMALLINT(4),
saleBy SMALLINT(4),
developmentOpportunity TINYINT(1),
investmentOpportunity TINYINT(1),
estimatedRentalIncome DECIMAL(7,2),
rent DECIMAL(7,2),
rentFrequency SMALLINT(4),
toLetPOA TINYINT(1),
studentProperty TINYINT(1),
lettingFeePolicyHeadline VARCHAR(50),
lettingFeePolicyDetails TEXT,
forSale TINYINT(1),
toLet TINYINT(1),
priceTo INT(11),
priceFrom INT(11),
rentTo DECIMAL(7,2),
rentFrom DECIMAL(7,2),
floorAreaTo DECIMAL(6,2),
floorAreaFrom DECIMAL(6,2),
siteArea DECIMAL(7,2),
siteAreaUnits VARCHAR(8),
strapLine VARCHAR(255),
landAreaTo DECIMAL(7,2),
landAreaFrom DECIMAL(7,2),
landAreaUnits VARCHAR(8),
forSaleLeaseLength SMALLINT(4),
featured TINYINT(1),
available TINYINT(1)
) DEFAULT CHARACTER SET latin1;
接下来，我使用 Load XML Local 导入 XML。在此示例中，我使用“propertyID”标识行。
LOAD XML LOCAL INFILE '/wamp64/tmp/output.xml' INTO TABLE propertyimport ROWS IDENTIFIED BY '<propertyID>';
这是我正在导入的 XML 文件的简化示例。
<?xml version="1.0" encoding="iso-8859-1" ?>
<properties>
<property>
<propertyID>101466000123</propertyID>
<branchID>1</branchID>
<clientName>Unihousing</clientName>
<branchName>Birmingham</branchName>
<department>Lettings</department>
<referenceNumber>174</referenceNumber>
<addressName>Downstairs Front Apartment</addressName>
<addressNumber>174</addressNumber>
<addressStreet>Dawlish Road</addressStreet>
<address2></address2>
<address3>Birmingham</address3>
<address4></address4>
<addressPostcode>B29 7AR</addressPostcode>
<country>United Kingdom</country>
<displayAddress>Dawlish Road</displayAddress>
<propertyBedrooms>0</propertyBedrooms>
<propertyBathrooms>1</propertyBathrooms>
<propertyEnsuites>0</propertyEnsuites>
<propertyReceptionRooms>0</propertyReceptionRooms>
<propertyKitchens>0</propertyKitchens>
<displayPropertyType>Studio</displayPropertyType>
<propertyType>2</propertyType>
<propertyStyle>20</propertyStyle>
<propertyAge>0</propertyAge>
<floorArea>0.00</floorArea>
<floorAreaUnits>sq ft</floorAreaUnits>
<propertyFeature1>Fitted Kitchen With Combi Oven </propertyFeature1>
<propertyFeature2>Fitted Shower Room</propertyFeature2>
<propertyFeature3>Shared Utility Area</propertyFeature3>
<propertyFeature4>Next to the University of Birmingham</propertyFeature4>
<propertyFeature5>Close to Selly Oak Village</propertyFeature5>
<propertyFeature6>HMO studio style living </propertyFeature6>
<propertyFeature7></propertyFeature7>
<propertyFeature8></propertyFeature8>
<propertyFeature9></propertyFeature9>
<propertyFeature10></propertyFeature10>
<rent>140</rent>
<rentFrequency>2</rentFrequency>
<toLetPOA>0</toLetPOA>
<studentProperty>1</studentProperty>
<availability>2</availability>
<mainSummary> A brand new HMO style property consisting of: A brand new modern fitted snack area, an open plan living/ dining area and a fitted shower room. This spacious accommodation on offer is fully double glazed and fully furnished with student essentials. The cleaning and upkeep of the communal stairs and landing are carried out by us. It is located next to the university and the busy night life of Selly Oak Village is just a short walk away. You wouldn't want to miss out so call now to arrange your viewing!!!</mainSummary>
<fullDescription><![CDATA[]]></fullDescription>
<dateLastModified>2018-05-23</dateLastModified>
<timeLastModified>14:07:32</timeLastModified>
<featuredProperty>0</featuredProperty>
<regionID>56</regionID>
<latitude>52.4408</latitude>
<longitude>-1.92888</longitude>
<flags>
</flags>
<images>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_17_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_11_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_8_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_14_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_15_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_18_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_19_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_20_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_21_large.jpg</image>
<image modified="2018-02-15 10:34:07">http://media2.jupix.co.uk/v3/clients/1466/properties/123/IMG_123_22_large.jpg</image>
</images>
<floorplans>
</floorplans>
<epcGraphs>
</epcGraphs>
<epcFrontPages><epcFrontPage modified="2015-06-09 09:53:42">http://epc.jupix.co.uk/647u85t</epcFrontPage>
</epcFrontPages>
<brochures>
</brochures>
<virtualTours>
</virtualTours>
<externalLinks>
</externalLinks>
</property>
<property>
<propertyID>101466000124</propertyID>
<branchID>1</branchID>
<clientName>Unihousing</clientName>
<branchName>Birmingham</branchName>
<department>Lettings</department>
<referenceNumber>174</referenceNumber>
<addressName>Middle Front Apartment</addressName>
<addressNumber>174</addressNumber>
<addressStreet>Dawlish Road</addressStreet>
<address2></address2>
<address3>Selly Oak </address3>
<address4></address4>
<addressPostcode>B29 7AR</addressPostcode>
<country>United Kingdom</country>
<displayAddress>Dawlish Road</displayAddress>
<propertyBedrooms>0</propertyBedrooms>
<propertyBathrooms>1</propertyBathrooms>
<propertyEnsuites>0</propertyEnsuites>
<propertyReceptionRooms>0</propertyReceptionRooms>
<propertyKitchens>0</propertyKitchens>
<displayPropertyType>Studio</displayPropertyType>
<propertyType>2</propertyType>
<propertyStyle>20</propertyStyle>
<propertyAge>0</propertyAge>
<floorArea>0.00</floorArea>
<floorAreaUnits>sq ft</floorAreaUnits>
<propertyFeature1>Next to the University of Birmingham</propertyFeature1>
<propertyFeature2>Electric Heating</propertyFeature2>
<propertyFeature3>Combi Oven </propertyFeature3>
<propertyFeature4>Shared Utility Areas</propertyFeature4>
<propertyFeature5>HMO studio style living</propertyFeature5>
<propertyFeature6></propertyFeature6>
<propertyFeature7></propertyFeature7>
<propertyFeature8></propertyFeature8>
<propertyFeature9></propertyFeature9>
<propertyFeature10></propertyFeature10>
<rent>140</rent>
<rentFrequency>2</rentFrequency>
<toLetPOA>1</toLetPOA>
<studentProperty>1</studentProperty>
<availability>2</availability>
<mainSummary>AVAILABLE JULY 2018 newly refurbished HMO studio style. Features a fitted snack area, a bedroom/living area &amp; fitted shower room. Shared utility area. Refurbished to a high standard, inclusive of bills and internet, TV also supplied at no extra cost. The property is laminated throughout and benefits from central heating and double glazing. The cleaning and upkeep of the communal stairs and landing are carried out by us. The property is in prime location within a short walking distance from the University of Birmingham &amp; Selly Oak train station.</mainSummary>
<fullDescription><![CDATA[<strong></strong> <strong></strong> Newly refurbished student accommodation. This Open plan 1 bed flat in Selly Oak, features a fitted kitchen with combi-oven, a bedroom/living area & fitted shower room. Shared utility area. A must see property. This apartment is suitable for a couple sharing. We have 6 more studio apartments available in the same block. Selly Oak, is an ideal location for any student within a few minutes' walk there are supermarkets, shops, bars and restaurants. ]]></fullDescription>
<dateLastModified>2018-05-23</dateLastModified>
<timeLastModified>12:42:16</timeLastModified>
<featuredProperty>0</featuredProperty>
<regionID>56</regionID>
<latitude>52.4404</latitude>
<longitude>-1.92931</longitude>
<flags>
<flag>Price Reduction</flag>
</flags>
<images>
<image modified="2017-11-09 12:16:39">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_9_large.jpg</image>
<image modified="2017-11-09 12:16:39">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_7_large.jpg</image>
<image modified="2017-11-09 12:16:39">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_5_large.jpg</image>
<image modified="2017-11-09 12:16:39">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_6_large.jpg</image>
<image modified="2017-11-09 12:16:39">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_8_large.jpg</image>
<image modified="2017-11-25 13:22:12">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_10_large.jpg</image>
<image modified="2017-11-25 13:22:13">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_11_large.jpg</image>
<image modified="2017-11-25 13:22:13">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_12_large.jpg</image>
<image modified="2017-11-25 13:22:13">http://media2.jupix.co.uk/v3/clients/1466/properties/124/IMG_124_13_large.jpg</image>
</images>
<floorplans>
</floorplans>
<epcGraphs>
</epcGraphs>
<epcFrontPages><epcFrontPage modified="2015-06-09 10:06:56">http://epc.jupix.co.uk/65rsea0</epcFrontPage>
</epcFrontPages>
<brochures>
</brochures>
<virtualTours>
</virtualTours>
<externalLinks>
</externalLinks>
</property>
</properties>
导入成功，没有任何警告。当我查询表以选择所有记录时，我得到下面的输出。显然第一个属性根本没有导入，但是，第二个属性实际上也有错误的数据，它有第一个属性中缺失的数据，尽管它有正确的 propertyID。
| localID | propertyID | branchID | clientName | branchName | department | referenceNumber | addressName | addressNumber | addressStreet | address2 | address3 | address4 | addressPostcode | country | displayAddress | propertyFeature1 | propertyFeature2 | propertyFeature3 | propertyFeature4 | propertyFeature5 | propertyFeature6 | propertyFeature7 | propertyFeature8 | propertyFeature9 | propertyFeature10 | dateLastModified | timeLastModified | featuredProperty | regionID | latitude | longitude | mainSummary | fullDescription | propertyBedrooms | propertyBathrooms | propertyEnsuites | propertyReceptionRooms | propertyKitchens | propertyAge | floorArea | floorAreaUnits | displayPropertyType | propertyType | propertyStyle | availability | price | forSalePOA | priceQualifier | propertyTenure | saleBy | developmentOpportunity | investmentOpportunity | estimatedRentalIncome | rent | rentFrequency | toLetPOA | studentProperty | lettingFeePolicyHeadline | lettingFeePolicyDetails | forSale | toLet | priceTo | priceFrom | rentTo | rentFrom | floorAreaTo | floorAreaFrom | siteArea | siteAreaUnits | strapLine | landAreaTo | landAreaFrom | landAreaUnits | forSaleLeaseLength | featured | available |
+---------+--------------+----------+------------+------------+------------+-----------------+----------------------------+---------------+---------------+----------+------------+----------+-----------------+----------------+----------------+---------------------------------+--------------------+---------------------+--------------------------------------+----------------------------+--------------------------+------------------+------------------+------------------+-------------------+------------------+------------------+------------------+----------+----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+------------------+-------------------+------------------+------------------------+------------------+-------------+-----------+----------------+---------------------+--------------+---------------+--------------+-------+------------+----------------+----------------+--------+------------------------+-----------------------+-----------------------+--------+---------------+----------+-----------------+--------------------------+-------------------------+---------+-------+---------+-----------+--------+----------+-------------+---------------+----------+---------------+-----------+------------+--------------+---------------+--------------------+----------+-----------+
| 1 | 101466000123 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 101466000124 | 1 | Unihousing | Birmingham | Lettings | 174 | Downstairs Front Apartment | 174 | Dawlish Road | NULL | Birmingham | NULL | B29 7AR | United Kingdom | Dawlish Road | Fitted Kitchen With Combi Oven | Fitted Shower Room | Shared Utility Area | Next to the University of Birmingham | Close to Selly Oak Village | HMO studio style living | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | A brand new HMO style property consisting of: A brand new modern fitted snack area, an open plan living/ dining area and a fitted shower room. This spacious accommodation on offer is fully double glazed and fully furnished with student essentials. The cleaning and upkeep of the communal stairs and landing are carried out by us. It is located next to the university and the busy night life of Selly Oak Village is just a short walk away. You wouldn't want to miss out so call now to arrange your viewing!!! | NULL | 0 | 1 | 0 | 0 | 0 | 0 | 0.00 | sq ft | Studio | 2 | 20 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 140.00 | 2 | 0 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
在下一个示例中，我从表中删除所有内容并再次导入，这次通过“属性”标识行。
LOAD XML LOCAL INFILE '/wamp64/tmp/output.xml' INTO TABLE propertyimport ROWS IDENTIFIED BY '<property>';
同样，没有警告或错误。查询数据库中的所有条目会返回以下输出。在这里，我们可以看到它已经导入了更多，但仍然不正确。第 4 行只是第 3 行的直接副本，包括 propertyID。
| localID | propertyID | branchID | clientName | branchName | department | referenceNumber | addressName | addressNumber | addressStreet | address2 | address3 | address4 | addressPostcode | country | displayAddress | propertyFeature1 | propertyFeature2 | propertyFeature3 | propertyFeature4 | propertyFeature5 | propertyFeature6 | propertyFeature7 | propertyFeature8 | propertyFeature9 | propertyFeature10 | dateLastModified | timeLastModified | featuredProperty | regionID | latitude | longitude | mainSummary | fullDescription | propertyBedrooms | propertyBathrooms | propertyEnsuites | propertyReceptionRooms | propertyKitchens | propertyAge | floorArea | floorAreaUnits | displayPropertyType | propertyType | propertyStyle | availability | price | forSalePOA | priceQualifier | propertyTenure | saleBy | developmentOpportunity | investmentOpportunity | estimatedRentalIncome | rent | rentFrequency | toLetPOA | studentProperty | lettingFeePolicyHeadline | lettingFeePolicyDetails | forSale | toLet | priceTo | priceFrom | rentTo | rentFrom | floorAreaTo | floorAreaFrom | siteArea | siteAreaUnits | strapLine | landAreaTo | landAreaFrom | landAreaUnits | forSaleLeaseLength | featured | available |
+---------+--------------+----------+------------+------------+------------+-----------------+----------------------------+---------------+---------------+----------+------------+----------+-----------------+----------------+----------------+---------------------------------+--------------------+---------------------+--------------------------------------+----------------------------+--------------------------+------------------+------------------+------------------+-------------------+------------------+------------------+------------------+----------+------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+------------------+-------------------+------------------+------------------------+------------------+-------------+-----------+----------------+---------------------+--------------+---------------+--------------+-------+------------+----------------+----------------+--------+------------------------+-----------------------+-----------------------+--------+---------------+----------+-----------------+--------------------------+-------------------------+---------+-------+---------+-----------+--------+----------+-------------+---------------+----------+---------------+-----------+------------+--------------+---------------+--------------------+----------+-----------+
| 3 | 101466000123 | 1 | Unihousing | Birmingham | Lettings | 174 | Downstairs Front Apartment | 174 | Dawlish Road | NULL | Birmingham | NULL | B29 7AR | United Kingdom | Dawlish Road | Fitted Kitchen With Combi Oven | Fitted Shower Room | Shared Utility Area | Next to the University of Birmingham | Close to Selly Oak Village | HMO studio style living | NULL | NULL | NULL | NULL | 2018-05-23 | 14:07:32 | 0 | 56 | 52.4408000 | -1.9288800 | A brand new HMO style property consisting of: A brand new modern fitted snack area, an open plan living/ dining area and a fitted shower room. This spacious accommodation on offer is fully double glazed and fully furnished with student essentials. The cleaning and upkeep of the communal stairs and landing are carried out by us. It is located next to the university and the busy night life of Selly Oak Village is just a short walk away. You wouldn't want to miss out so call now to arrange your viewing!!! | NULL | 0 | 1 | 0 | 0 | 0 | 0 | 0.00 | sq ft | Studio | 2 | 20 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 140.00 | 2 | 0 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4 | 101466000123 | 1 | Unihousing | Birmingham | Lettings | 174 | Downstairs Front Apartment | 174 | Dawlish Road | NULL | Birmingham | NULL | B29 7AR | United Kingdom | Dawlish Road | Fitted Kitchen With Combi Oven | Fitted Shower Room | Shared Utility Area | Next to the University of Birmingham | Close to Selly Oak Village | HMO studio style living | NULL | NULL | NULL | NULL | 2018-05-23 | 12:42:16 | 0 | 56 | 52.4404000 | -1.9293100 | A brand new HMO style property consisting of: A brand new modern fitted snack area, an open plan living/ dining area and a fitted shower room. This spacious accommodation on offer is fully double glazed and fully furnished with student essentials. The cleaning and upkeep of the communal stairs and landing are carried out by us. It is located next to the university and the busy night life of Selly Oak Village is just a short walk away. You wouldn't want to miss out so call now to arrange your viewing!!! | NULL | 0 | 1 | 0 | 0 | 0 | 0 | 0.00 | sq ft | Studio | 2 | 20 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 140.00 | 2 | 0 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
我使用在线工具检查了 XML 文档的有效性，但没有发现任何错误。如上所述，我使用了 latin1 和 utf8 字符集，但没有任何区别。我已经删除并重新制作了表格，包括多次再次导入。什么可能导致这种行为，我该如何解决它或绕过它？
解决方案
推荐阅读
- sql - 每个距离与最大值的距离
- cryptography - 是什么让 ECDH 单独依赖两个公钥？
- linux - 将 echo netcat 输出重定向到 docker log 或 stdout
- fortran - 使用 NAG fortran 6.2 “未初始化的值是由堆栈分配创建的”
- javascript - JS 数组没有选择正确的值
- c++ - 如何实现 constexpr 伪多态性？
- dart - 如何在flutter中记录http请求
- vb.net - VB.NET访问winforms非公开成员
- python - 调用 fig.add_axes() 时，Jupyter 笔记本中的 Python 内核崩溃
- vue.js - 在 Classic ASP 应用程序中使用 Vue.js 组件