如何将制表符分隔的文件插入到带有关系的mysql中 [英] how to insert tab delimited file into mysql with relation

问题描述

我想要在mysql表中插入数据，但是我找不到从一行建立关系的方法

What i want is to insert data in mysql table but i unable to find the way to make relationship from one row

假设我有一个文件 file.tab ，其中包含类似的数据

suppose i have a file file.tab it contains data in like

parent_1    parent_details_1    child_1.1   child_details_1.1   child_1.2   child_details_1.2
parent_2    parent_details_2    child_2.1   child_details_2.1
parent_3    parent_details_3    child_3.1   child_details_3.1   child_3.2   child_details_3.2   child_3.3   child_details_3.3

我要达到的目标是在

            parent_table
+---+-----------+-------------------+
|id |   name    |      details      |
+---+-----------+-------------------+
| 1 |  parent_1 |  parent_details_1 |
| 2 |  parent_2 |  parent_details_2 |
| 3 |  parent_3 |  parent_details_3 |
+---+-----------+-------------------+


              child_table
+---+-----+-----------+-------------------+
|id | pid |   name    |      details      |
+---+-----+-----------+-------------------+
| 1 |  1  | child_1.1 | child_details_1.1 |
| 2 |  1  | child_1.2 | child_details_1.2 |
| 3 |  2  | child_2.1 | child_details_2.1 |
| 4 |  3  | child_3.1 | child_details_3.1 |
| 5 |  3  | child_3.2 | child_details_3.2 |
| 6 |  3  | child_3.3 | child_details_3.3 |
+---+-----+-----------+-------------------+

前两列是父母的，其后两两列是孩子的，但我不知道父母有多少个孩子.

first two columns is for parent and after that two-two columns belongs to child but i don't know how many child a parent has.

我试图以这种方式加载文件.

i have tried to load file that way.

LOAD DATA INFILE '/tmp/file.tab INTO TABLE ... 

但是接下来我该怎么办.

but what do i do next i have no idea.

请在这个问题上帮助我.

so kindly help me out in this question.

推荐答案

创建具有很多列的表(Staging).对于parent_id留空(NULL)列，并为孩子提供ID.

Create a table (Staging) with lots of columns. Have empty (NULL) columns for parent_id and ids for the children.

希望在LOAD DATA期间，短"行将在缺少的子栏中添加空值.

Hope that the 'short' lines will put nulls in the missing children columns during the LOAD DATA.

INSERT .. SELECT ..将parent和parent_detail放入Parents表.将ids从Parents拉回到Staging.parent_id.有关这两个SQL的详细信息，请参见 http://mysql.rjweb.org/doc .php/staging_table#normalization

INSERT .. SELECT .. to get the parent and parent_detail into the Parents table. Pull back the ids from Parents into Staging.parent_id. The details on the two SQLs for these are in http://mysql.rjweb.org/doc.php/staging_table#normalization

现在对每个可能的子"列集执行类似的操作:child1和child1_detail(可能为NULL对)以及当前为NULL的child1_id.等同于child2 *，等等.请注意，在填充Children表时，您已经具有parent_id可用.

Now do something similar for each possible "child" set of columns: child1 and child1_detail (possibly NULL pair) and the currently NULL child1_id. Ditto for child2*, etc. Note that when populating the Children table, you already have parent_id available.

这是执行任务的全SQL方式.它只比编写Perl/PHP/Java/VB/执行任何任务的代码少一点混乱.

This is an all-SQL way of doing the task. It is only slightly less messy than writing Perl/PHP/Java/VB/whatever code to do the task.

这篇关于如何将制表符分隔的文件插入到带有关系的mysql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！