加载XML更新表-MySQL [英] Load XML Update Table--MySQL

问题描述

 LOAD XML LOCAL INFILE 'file1.xml'
 INTO TABLE my_table
 ROWS IDENTIFIED BY '<product>'"

是否可以使用此功能更新表?

Is it possible to use this function to update a table?

我使用了REPLACE INTO TABLE my_table，但是它仅添加了新行，并且没有更新现有行.

I used REPLACE INTO TABLE my_table but that only added new rows and it did not update the existing rows.

推荐答案

 LOAD XML LOCAL INFILE 'file1.xml'
 REPLACE 
 INTO TABLE my_table
 ROWS IDENTIFIED BY '<product>'"

请参阅: http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

请注意:

REPLACE的工作原理与INSERT完全相同，不同之处在于，如果表中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值，则在插入新行之前会删除该旧行.请参见第12.2.5节"INSERT语法".

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, "INSERT Syntax".

由于外键限制，MySQL可能无法删除现有行.

Probably MySQL is unable to delete the exiting rows due to foreign key restrictions.

您可以通过以下方式解决此问题:

You can fix this by:

SET FOREIGN_KEY_CHECKS=0;
...load xml
SET FOREIGN_KEY_CHECKS=1;

这篇关于加载XML更新表-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！