Question

I have two databases with the same design, and different records in Mysql There are two main tables with a unique primary key. (I mean if two records in different databases has the same primary keys they are the same.)

这两张表格与其他表格的关系相差不多到很多。但问题是,在所有银行中,小型的PK并非独一无二(一)

现在我想合并这两个数据库的数据。

任何想法?

Answer 1

EDIT:我认为我误解了你的说法,即钥匙在数据库中是独特的。在此情况下,如果原数据库已经输入目标数据库,我就不要再加一行。你们仍然可以在外国关键表格中复制,然后是同样的外国钥匙。

so basically:

INSERT INTO MainTable (PK, field1, field2)
SELECT (PK, field1, field2)
FROM OldMainTable
WHERE PK NOT IN (SELECT PK FROM MainTable)

仅举一例,便提供以下旧答案。

您的最好办法是修改表格,以便有一个古老的领域。然后,在目标数据库中插入数据,在旧财产领域储存原来的PK。然后,当你用外国钥匙插入任何东西时,将FK与旧财产所在地相匹配,并更新外国钥匙。 (或许是一个好的主意,就是要制作外国钥匙的复印件!)

SO the queries might look something like this:

INSERT INTO MainTableTarget  (OldPrimaryKey, field1, field2...)
SELECT PrimaryKey as OldPrimaryKey, field1, field2...
FROM OldMainTable

INSERT INTO Table2Target ( OldTable2PK, MainTableForeignKey, fielda, fieldb...)
SELECT Table2PK as OldTable2PK, 
        (SELECT PK FROM MainTableTarget) as MainTableForeignKey,  --This get the FK to point at the newly created database record
        fielda,
        fieldb
FROM OldTable2

这可能是痛苦的,但我认为这里没有任何非和平的选择。

Oh, and sorry if the syntax is a little off... I m a T-SQL guy so don t really know MySQL s syntax. Hopefully you get the idea though...

Answer 2

This answer depends heavily on your assumption that "if two records in different databases has the same primary keys they are the same." Assuming that your data is clean, set foreign key checks to 0. Then

INSERT INTO db2.sometable

SELECT * FROM db1.sometable on duplicate key ignore

而不要忽视任何错误,而不仅仅是重复错误。在每个桌子,然后将外国钥匙检查定为1。如果你想得到额外的安全,你就可以在学上安排自己的桌子,在保持外国关键制衡的同时,首先插入根基表。如果你有合成的主轴,所有床位都会消失,你将需要重新调配更加困难的关键。

Answer 3

简单的方法或许是,在每一表格中都使用同样的问题:

INSERT IGNORE INTO db2.sometable
SELECT * FROM db1.sometable

Just make sure you handle the tables in the right order so that you don t run into foreign key problems (e.g. do your two main tables first, then the related tables)

友情链接