我有一个非常大(2.7mb)的XML文件,其结构如下:
<?xml version="1.0"?>
<Destinations>
<Destination>
<DestinationId>W4R1FG</DestinationId>
<Country>Pakistan</Country>
<City>Karachi</City>
<State>Sindh</State>
</Destination>
<Destination>
<DestinationId>D2C2FV</DestinationId>
<Country>Turkey</Country>
<City>Istanbul</City>
<State>Istanbul</State>
</Destination>
<Destination>
<DestinationId>5TFV3E</DestinationId>
<Country>Canada</Country>
<City>Toronto</City>
<State>Ontario</State>
</Destination>
... ... ...
</Destinations>
MySQL表的"目的地"如下:
+---+--------------+----------+---------+----------+
|id |DestinationId |Country |City |State |
+---+--------------+----------+---------+----------+
|1 |W4R1FG |Pakistan |Karachi |Sindh |
+---+--------------+----------+---------+----------+
|2 |D2C2FV |Turkey |Istanbul |Istanbul |
+---+--------------+----------+---------+----------+
|3 |5TFV3E |Canada |Toronto |Ontario |
+---+--------------+----------+---------+----------+
|. |...... |...... |....... |....... |
+---+--------------+----------+---------+----------+
现在我想处理我的XML并检查MySQL表中的每个目的地记录。我只需要将DestinationId
与每条记录进行比较,并检查它是否存在于我的DB表中。如果它确实存在,则保留该记录并继续前进,如果它不存在,则执行INSERT
查询将该记录插入该表中。
我最初尝试使用PHP foreach循环机制来实现这一点,但由于数据太大,这给我带来了严重的性能和速度问题。然后我提出了一个MySQL过程方法,如下所示:
DELIMITER $$
USE `destinations`$$
DROP PROCEDURE IF EXISTS `p_import_destinations`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_import_destinations`(
p_xml TEXT
)
BEGIN
DECLARE v_row_index INT UNSIGNED DEFAULT 0;
DECLARE v_row_count INT UNSIGNED;
DECLARE v_xpath_row VARCHAR(255);
-- calculate the number of row elements.
SET v_row_count := extractValue(p_xml,'count(/Destinations/Destination)');
-- loop through all the row elements
WHILE v_row_index < v_row_count DO
SET v_row_index := v_row_index + 1;
SET v_xpath_row := CONCAT('/Destinations/Destination[',v_row_index,']');
INSERT IGNORE INTO destinations VALUES (
NULL,
extractValue(p_xml,CONCAT(v_xpath_row, '/child::DestinationId')),
extractValue(p_xml,CONCAT(v_xpath_row, '/child::Country')),
extractValue(p_xml,CONCAT(v_xpath_row, '/child::City')),
extractValue(p_xml,CONCAT(v_xpath_row, '/child::State'))
);
END WHILE;
END$$
DELIMITER ;
查询调用此过程:
SET @xml := LOAD_FILE('C:/Users/Muhammad Ali/Desktop/dest.xml');
CALL p_import_destinations(@xml);
这非常完美,但我仍然不确定这种方法的可扩展性、性能和速度。该过程中使用的IGNORE
子句跳过重复记录,但累积自动递增键值。就像它用id
3306
检查行一样,如果该记录是重复的,它不会将其插入表中(这是一件好事),而是使用自动递增键3307
,并且当下次插入非重复记录时,它会将其插入3308
。这看起来不太好。
任何其他满足此类要求的方法都将不胜感激。如果我可以继续这个解决方案,请指导我?如果没有,为什么?
请记住,我正在处理大量的数据。
这非常完美,但我仍然不确定这种方法的可扩展性、性能和速度。
衡量速度,测试它的伸缩性。那你肯定了。再次询问您是否在场景中发现了会对您造成伤害的问题,但要使性能/可扩展性问题更加具体。最有可能这样的部分是问答;已经是了。如果不是在Stackoverflow上,而是在DBA站点上:https://dba.stackexchange.com/
该过程中使用的
IGNORE
子句跳过重复记录,但累积自动递增键值
这是相似的。如果这些缺口对你来说是个问题,这通常表明你的数据库设计有缺陷,因为这些缺口通常毫无意义(比较:如何填补自动递增字段中的"漏洞"?)。
然而,这并不意味着其他人也不会有这个问题。你可以找到很多关于这方面的材料,以及如何在数据库服务器的特定版本中防止这种情况的"技巧"。但老实说,我不在乎差距。约定是标识列具有唯一的值。仅此而已。
无论如何,对于性能和ID来说:为什么不把处理分开呢?首先从XML导入到导入表中,然后可以轻松地从该导入表中删除不想导入的每一行,然后根据需要插入到目标表中。
使用下面描述的另一个逻辑解决了这个问题。。
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `import_destinations_xml`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `import_destinations_xml`(
path VARCHAR(255),
node VARCHAR(255)
)
BEGIN
DECLARE xml_content TEXT;
DECLARE v_row_index INT UNSIGNED DEFAULT 0;
DECLARE v_row_count INT UNSIGNED;
DECLARE v_xpath_row VARCHAR(255);
-- set xml content.
SET xml_content = LOAD_FILE(path);
-- calculate the number of row elements.
SET v_row_count = extractValue(xml_content, CONCAT('count(', node, ')'));
-- create a temporary destinations table
DROP TABLE IF EXISTS `destinations_temp`;
CREATE TABLE `destinations_temp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`DestinationId` VARCHAR(32) DEFAULT NULL,
`Country` VARCHAR(255) DEFAULT NULL,
`City` VARCHAR(255) DEFAULT NULL,
`State` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
-- loop through all the row elements
WHILE v_row_index < v_row_count DO
SET v_row_index = v_row_index + 1;
SET v_xpath_row = CONCAT(node, '[', v_row_index, ']');
INSERT INTO destinations_temp VALUES (
NULL,
extractValue(xml_content, CONCAT(v_xpath_row, '/child::DestinationId')),
extractValue(xml_content, CONCAT(v_xpath_row, '/child::Country')),
extractValue(xml_content, CONCAT(v_xpath_row, '/child::City')),
extractValue(xml_content, CONCAT(v_xpath_row, '/child::State'))
);
END WHILE;
-- delete existing records from temporary destinations table
DELETE FROM destinations_temp WHERE DestinationId IN (SELECT DestinationId FROM destinations);
-- insert remaining (unmatched) records from temporary destinations table to destinations table
INSERT INTO destinations (DestinationId, Country, City, State)
SELECT DestinationId, Country, City, State
FROM destinations_temp;
-- creating a log file
SELECT *
INTO OUTFILE 'C:/Users/Muhammad Ali/Desktop/Destination_Import_Procedure/log/destinations_log.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY ''r'n'
FROM `destinations_temp`;
-- removing temporary destinations table
DROP TABLE destinations_temp;
END$$
DELIMITER ;
查询以调用此过程。
CALL import_destinations_xml('C:'Users'Muhammad Ali'Desktop'Destination_Import_Procedure'dest.xml', '/Destinations/Destination');