如何在MySQL中合并两个表，其中表1是主要表 [英] How do I merge two tables in MySQL and where table 1 is primary

问题描述

如何在MySQL中合并两个表? 我看过有关该主题的其他几篇文章，但它们对我来说不够详细.

How do I merge two tables in MySQL? I've looked at several other posts on this topic but they don't go into enough detail for me.

我是MySQL的新手，所以请允许我拥有一个看起来像这样的主表和临时表:

I'm a novice MySQL user, so bear with me I have a primary table and a temp table that look like this:

创建表temp_import(id int(11)NOT NULL auto_increment，
Name varchar(255)默认为NULL，
MerchantID int(11)默认为NULL，
SKU varchar(255)默认为NULL，
主键(id))ENGINE = MyISAM AUTO_INCREMENT = 765811默认 CHARSET = utf8;

CREATE TABLE temp_import ( id int(11) NOT NULL auto_increment,
Name varchar(255) default NULL,
MerchantID int(11) default NULL,
SKU varchar(255) default NULL,
PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=765811 DEFAULT CHARSET=utf8;

我将数据插入到临时表中，如下所示:

I'm inserting data into the temp table like this:

LOAD DATA LOCAL INFILE \'29762.txt\' REPLACE INTO TABLE temp_import FIELDS TERMINATED BY \'|\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' (Name, MerchantID, SKU);

每个表的表格式相同，但是数据会随着时间变化.某些项目有数据机会，但SKU保持不变.有些项目将不再提供，需要从数据库中删除.

The table format of each table is the same, but the data changes over time. Some items the data chances but the SKU remains constant. Some items will no longer be offered and need to be removed from the database.

示例:

当前数据库表:

1, dog, 101, dog101
2, cat, 101, cat101
3, chicken, 102, chicken100
4, chicken food, 102, chicken101

新数据库表:

1, dog, 101, dog101
2, cat, 102, cat101
3, chicken, 102, chicken100
5, Frog, 103, frog101

最终结果应该是

1, dog, 101, dog101
2, cat, 102, cat101
3, chicken, 102, chicken100
5, Frog, 103, frog101

删除了鸡肉，添加了青蛙，更新了猫.

Deleted chickenfood, added Frog, updated cat.

这也需要尽可能地高效.我将处理一些大文件.它可以是仅限MySQL的代码.

Also this needs to be as efficient as possible. I'll be working with some huge files. And it can be MySQL only code.

http://www .xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/我已经看过了，但是当我尝试这样做时，它已经超出了我的脑海.工作...

http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/ I've looked over this but it's over my head, when I try to do this it doesn't work...

推荐答案

我认为我会发布最终用于此目的的内容.这可能不是最佳"的方式，删除列是完全可选的.

I thought I would post what I eventually used for this. It's probably not the "BEST" way, and dropping the column is completely optional.

ALTER TABLE `temp_import`
ADD `deleteme` tinyint NOT NULL
DEFAULT 0; -- If you haven't already added a deleteme column 


UPDATE `temp_import` SET  `deleteme` = 1; -- Set the delete field 

LOAD DATA LOW_PRIORITY LOCAL INFILE "import.csv" REPLACE INTO TABLE `temp_import`  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\n"  IGNORE 1 LINES (`id`, `name`, `m_id`, `sku`);

DELETE FROM `temp_import` WHERE  `deleteme` = 1;

ALTER TABLE `tests` DROP COLUMN `deleteme` -- Optional

这篇关于如何在MySQL中合并两个表，其中表1是主要表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！