仅使用SQL的MySQL插入

Question

是否可以只使用SQL和MySQL获得下面的“输出”？

示例数据:为了更好地说明一个示例，让我们假设我正在尝试加载一个文件，其中包含员工姓名、他们过去占据过的办公室以及他们的职务记录被一个选项卡隔开。

文件：

EmployeeName<tab>OfficeHistory<tab>JobLevelHistory
John Smith<tab>501<tab>Engineer
John Smith<tab>601<tab>Senior Engineer
John Smith<tab>701<tab>Manager
Alex Button<tab>601<tab>Senior Assistant
Alex Button<tab>454<tab>Manager

注意：单个表数据库是完全标准化的(就像单个表一样) --例如，在"John“的情况下，只有一个John；这意味着没有重复会导致引用完整性冲突。

MyOffice数据库模式有以下表：

Employee (nId, name)
Office (nId, number)
JobTitle (nId, titleName)
Employee2Office (nEmpID, nOfficeId)
Employee2JobTitle (nEmpId, nJobTitleID)

输出：，所以在本例中。这些表格应如下所示：

Employee
1 John Smith
2 Alex Button

Office
1 501
2 601
3 701
4 454

JobTitle
1 Engineer
2 Senior Engineer
3 Manager
4 Senior Assistant

Employee2Office
1 1
1 2
1 3
2 2
2 4

Employee2JobTitle
1 1
1 2
1 3
2 4
2 3

这里是创建数据库和表的MySQL DDL：

create database MyOffice2;

use MyOffice2;

CREATE TABLE Employee (
      id MEDIUMINT NOT NULL AUTO_INCREMENT,
      name CHAR(50) NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB;

CREATE TABLE Office (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  office_number INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE JobTitle (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  title CHAR(30) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE Employee2JobTitle (
  employee_id MEDIUMINT NOT NULL,
  job_title_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (job_title_id) REFERENCES JobTitle(id),
  PRIMARY KEY (employee_id, job_title_id)
) ENGINE=InnoDB;

CREATE TABLE Employee2Office (
  employee_id MEDIUMINT NOT NULL,
  office_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (office_id) REFERENCES Office(id),
  PRIMARY KEY (employee_id, office_id)
) ENGINE=InnoDB;

Answer 1

您可以使用“通过”表，并为此使用触发器。无论何时，无论是从调用应用程序，还是从调用应用程序，只要您完成了这个表，都会删除它。

create table TmpEmp (
EmployeeName char(50) not null,
OfficeHistory int null,
JobLevelHistory char(30) null);

在此表上创建触发器

delimiter |
CREATE TRIGGER tg_TmpEmp BEFORE INSERT ON TmpEmp
FOR EACH ROW
BEGIN
IF not exists (select * from Employee where Name = NEW.EmployeeName) THEN
    INSERT INTO Employee(name)
        select NEW.EmployeeName;
END IF;
IF not exists (select * from Office where office_number = NEW.OfficeHistory) THEN
    INSERT INTO Office(office_number)
        select NEW.OfficeHistory;
END IF;
IF not exists (select * from JobTitle where title = NEW.JobLevelHistory) THEN
    INSERT INTO JobTitle(title)
        select NEW.JobLevelHistory;
END IF;
INSERT INTO Employee2JobTitle(employee_id,job_title_id)
    select E.id, T.id
    from Employee E
    inner join JobTitle T on T.title = NEW.JobLevelHistory
    where E.Name = NEW.EmployeeName
        AND not exists (select *
            from Employee2JobTitle J
            where J.employee_id = E.id and J.job_title_id = T.id);
INSERT INTO Employee2Office(employee_id,office_id)
    select E.id, O.id
    from Employee E
    inner join Office O on O.office_number = NEW.OfficeHistory
    where E.Name = NEW.EmployeeName
        AND not exists (select *
            from Employee2Office J
            where J.employee_id = E.id and J.office_id = O.id);
END; |
delimiter ;

Note__：这个触发器和表的优点是它可以工作，无论您使用的是加载文件还是普通插入。触发器被触发，并在需要的地方添加数据。

试试看

insert tmpEmp(EmployeeName,OfficeHistory,JobLevelHistory)
select 'John Smith',501,'Engineer' union all
select 'John Smith',601,'Senior Engineer' union all
select 'John Smith',701,'Manager' union all
select 'Alex Button',601,'Senior Assistant' union all
select 'Alex Button',454,'Manager';

truncate table tmpEmp;

Answer 2

也许您可以通过使用MySQL 负载数据信息语法使其工作。

根据规范，您可以这样使用它：

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

并设置如下选项：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

编辑:添加一项提案：

1) Load the file into a temp file, let's call it table temp (left out in this example)
2) Insert basic data into right tables
  INSERT INTO Employee (name)   
  Select distinct name from temp;

  INSERT INTO Office (office_number)
  Select DISTINCT office from temp;

  INSERT INTO JobTitle (title)
  Select DISTINCT job_level from temp;

3) Create mapping tables by using joins, like:

  INSERT INTO Employee2Office (employee_id, office_id)
  select Employee.id, office.id from temp
  INNER JOIN Employee ON temp.name = Employee.name
  INNER JOIN Office ON temp.office = Office.office_number

  Follow the same approach for the other mapping table. 

Answer 3

至于临时表的建议，我只是不明白临时表与“真实的”表有什么不同；这意味着我知道临时表是什么，只是不知道它如何在加载过程中起作用。

temp表将允许您将数据加载到单个平面表中。例如，可以在SQL中实现以下过程：

1. 关闭外键检查
2. 创建临时表以保存平面文件中的数据，如下所示
3. 将平面文件数据加载到临时表中。
4. 使用INSERT INTO ... SELECT FROM将数据加载到主表(Employee、JobTitle、Offic-SO3查询)
5. 使用查询根据主表和临时表中值的相关性选择两个主表的自动增量列，并将它们插入连接表中(每个联接表将执行两次，每次一次)
6. 打开外键检查

这就是我所说的，必须要按照剧本来写。MySQL不可能从平面数据中神奇地映射关系。你得自己去做。您可以使用上面的步骤用SQL编写它，但是使用您熟悉的脚本语言似乎要简单一些，这样可以避免使用LOAD DATA可能出现的各种权限/访问问题。