MySQL LOAD DATA INFILE 并填充另一个表

Question

考虑以下 CSV 文件:

child_fname,child_sname,child_dob,child_gender,guardian_phone,child_activities
Jack,Horner,07/02/1999,M,0777-1863036,Art
Bill,Flowerpot,13/11/2002,M,0777-8302987,Football
Ben,Flowerpot,13/11/2002,M,0777-8302987,Football
Bart,Simpson,26/10/1999,M,0777-9384756,"Football,Art"
Lisa,Simpson,04/05/2004,F,0777-9384756,Art
Jemima,Puddleduck,16/07/2005,F,0777-4938756,"Art,IT"
Angelica,Pickles,29/03/2003,F,0777-2947856,Football
Eric,Cartman,12/04/1999,M,0770-2947104,IT
Stan,Marsh,16/08/1999,M,0770-2947104,IT
Kyle,Broflovski,30/05/1999,M,0770-2947104,IT
Kenny,McCormick,27/11/1999,M,0770-2947104,IT
Florence,Roundabout,05/05/2004,F,0757-3243137,"Art,IT"
Dennis,Menace,14/02/1999,M,0784-1836412,"Football,IT"
Minnie,Minx,31/08/2001,F,0773-4836741,"Football,IT,Art"
Miracle,Cure,24/04/2002,F,0776-1846374,Art
Beatrice,Menace,04/05/2005,F,0784-1836412,Art
Robin,Hood,10/07/2000,M,0779-3857344,"Football,IT"
Marian,Maid,26/09/2002,F,0779-3857344,Football

我希望将其加载到名为 Child 的 MySQL 数据库中，但是我不想插入仅用于在另一个数据库中查找外键的 guardian_phone table 。例如

LOAD DATA INFILE 'example.csv'    
    INTO TABLE `Child`
    IGNORE 1 LINES
    FIELDS
        TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES
        TERMINATED BY '\n'
    (child_fname,child_sname,child_dob,child_gender,@guardian_phone,@child_activities)
    SET guardian_id = (SELECT guardian_id FROM `Guardian` WHERE guardian_phone = @guardian_phone);

这很棒，但我也不想插入 child_activities。我有另一个名为 Activity 的表，其中包含这些事件，如下所示:

activity_id | activity_name 
1             Art
2             Football
3             IT

我有另一个名为 ActivityRegister 的表，我想在加载此字段的同时填充该表，它由两列 activity_id 和 child_id.

我想做的是在将此数据加载到 Child 的同时，按 , 分割 @child_activities 中的信息> (如有必要，可以将 CSV 输出更改为更方便的内容)并创建一个或多个字段，插入新生成的 child_id 以及通过执行 SELECT 找到的 activity_id Activity_id FROM Activity WHERE Activity_name = @child_activities (注意:需要首先拆分 @child_activities 列以获得单独的值)

这可能吗？我宁愿一次性完成此操作，而不是进行多次传递，因为所有必需的信息都在那里。

Answer 1

您可以评估的一个选项是这样的，我更改了guardian_phone列以方便编写一个小脚本:

example.csv:
+-------------+-------------+------------+--------------+-------------+------------------+
| child_fname | child_sname | child_dob  | child_gender | guardian_id | child_activities |
+-------------+-------------+------------+--------------+-------------+------------------+
| Jack        | Horner      | 07/02/1999 | M            |           1 | Art              |
| Bill        | Flowerpot   | 13/11/2002 | M            |           2 | Football         |
| Ben         | Flowerpot   | 13/11/2002 | M            |           3 | Football         |
| Bart        | Simpson     | 26/10/1999 | M            |           4 | "Football,Art"   |
| Lisa        | Simpson     | 04/05/2004 | F            |           5 | Art              |
| Jemima      | Puddleduck  | 16/07/2005 | F            |           6 | "Art,IT"         |
| Angelica    | Pickles     | 29/03/2003 | F            |           7 | Football         |
| Eric        | Cartman     | 12/04/1999 | M            |           8 | IT               |
| Stan        | Marsh       | 16/08/1999 | M            |           9 | IT               |
| Kyle        | Broflovski  | 30/05/1999 | M            |          10 | IT               |
| Kenny       | McCormick   | 27/11/1999 | M            |          11 | IT               |
| Florence    | Roundabout  | 05/05/2004 | F            |          12 | "Art,IT"         |
| Dennis      | Menace      | 14/02/1999 | M            |          13 | "Football,IT"    |
| Minnie      | Minx        | 31/08/2001 | F            |          14 | "Football,IT,Art"|
| Miracle     | Cure        | 24/04/2002 | F            |          15 | Art              |
| Beatrice    | Menace      | 04/05/2005 | F            |          16 | Art              |
| Robin       | Hood        | 10/07/2000 | M            |          17 | "Football,IT"    |
| Marian      | Maid        | 26/09/2002 | F            |          18 | Football         |
+-------------+-------------+------------+--------------+-------------+------------------+

/*Table structure for table `activity` */

DROP TABLE IF EXISTS `activity`;

CREATE TABLE `activity` (
  `activity_id` INT(10) UNSIGNED NOT NULL,
  `activity_name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`activity_id`)
) ENGINE=INNODB;

/*Data for the table `activity` */

INSERT INTO `activity`(`activity_id`,`activity_name`)
VALUES
(1,'Art'),(2,'Football'),(3,'IT');

/*Table structure for table `activityregister` */

DROP TABLE IF EXISTS `activityregister`;

CREATE TABLE `activityregister` (
  `activity_id` INT(10) UNSIGNED NOT NULL,
  `child_id` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`activity_id`,`child_id`)
) ENGINE=INNODB;

/*Table structure for table `child` */

DROP TABLE IF EXISTS `child`;

CREATE TABLE `child` (
  `child_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `child_fname` VARCHAR(50) DEFAULT NULL,
  `child_sname` VARCHAR(50) DEFAULT NULL,
  `child_dob` VARCHAR(10) DEFAULT NULL,
  `child_gender` CHAR(1) DEFAULT NULL,
  `guardian_id` INT(10) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`child_id`)
) ENGINE=INNODB;

/*Table structure for table `intermediate_child` */

DROP TABLE IF EXISTS `intermediate_child`;

CREATE TABLE `intermediate_child` (
  `child_fname` VARCHAR(50) DEFAULT NULL,
  `child_sname` VARCHAR(50) DEFAULT NULL,
  `child_dob` VARCHAR(10) DEFAULT NULL,
  `child_gender` CHAR(1) DEFAULT NULL,
  `guardian_id` INT(10) UNSIGNED DEFAULT NULL,
  `child_activities` VARCHAR(50) DEFAULT NULL
) ENGINE=INNODB;

/* Trigger structure for table `intermediate_child` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `trg_ai_intermediate_child` */$$

CREATE TRIGGER `trg_ai_intermediate_child` AFTER INSERT ON `intermediate_child`
FOR EACH ROW
BEGIN
    DECLARE `delimiter` CHAR(1) DEFAULT ',';
    DECLARE `child_activities_length`, `_child_id`, `_activity_id` INT UNSIGNED DEFAULT 0;
    DECLARE `index` INT UNSIGNED DEFAULT 1;
    DECLARE `_activity_name` VARCHAR(50);
    INSERT INTO `child` (`child_fname`, `child_sname`, `child_dob`, `child_gender`, `guardian_id`)
        VALUES (new.`child_fname`, new.`child_sname`, new.`child_dob`, new.`child_gender`, new.`guardian_id`);
    SET `_child_id` := LAST_INSERT_ID();
    SET `child_activities_length` := `common_schema`.get_num_tokens(new.`child_activities`, `delimiter`);
    IF (`child_activities_length` > 0) THEN
        WHILE (`index` <= `child_activities_length`) DO
            SET `_activity_name` := common_schema.split_token(new.`child_activities`, `delimiter`, `index`);
            SET `_activity_id` := (SELECT `activity_id` FROM `activity` WHERE `activity_name` = `_activity_name`);
            IF (`_activity_id` IS NOT NULL) THEN
                INSERT INTO `activityregister` (`activity_id`, `child_id`) VALUES (`_activity_id`, `_child_id`);
            END IF;
            SET `index` := `index` + 1;
        END WHILE;
    END IF;
END$$

DELIMITER ;

LOAD DATA INFILE '/path/to/example.csv'
INTO TABLE `intermediate_child`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(`child_fname`, `child_sname`, `child_dob`, `child_gender`, `guardian_id`, `child_activities`);

TRUNCATE TABLE `intermediate_child`;

为了方便处理所使用的字符串:common_schema ，具体功能:get_num_tokens和 split_token .

根据需要进行调整并更改您需要的一切，这只是一个可以评估的选项。