时间戳

首页 > 解决方案 > Mysql：标记从转储表中插入的行

mysql - Mysql：标记从转储表中插入的行

问题描述

我有一个由批量插入数据填充的转储表，我想按分类将它们隔离在其他表上。

这是我的转储表，其中包含从文本文件中提取的数据。

==========================DUMP===============================
| Employee Name | Company | Family Tree Name | Relationship |
=============================================================
| Bryan Fury    | Guugle  | Jenny Fury       | Wife         |
|               |         | Peter Fury       | Son          |
|               |         | Mary Fury        | Daughter     |
| Paul Pheonix  | Soony   | Linda Phoenix    | Wife         |
|               |         | Peter Phoenix    | Son          |
|               |         | John Phoenix     | Son          |
| Gwen Zamora   | Aple    | Sebastian Zamora | Husband      |
|               |         | Ryan Zamora      | Son          |
=============================================================

我想用这样的标识符将它们分成两个表

================EMPLOYEE===============
| Employee Name | Company |  Tagging  |
=======================================
| Bryan Fury    | Guugle  | Family 1  |
| Paul Pheonix  | Soony   | Family 2  |
| Gwen Zamora   | Aple    | Family 3  |
=======================================

==============FAMILY TREE===================
| Name            | Relationship|  Tagging  |
============================================
| Jenny Fury      | Wife        | Family 1  |
| Peter Fury      | Son         | Family 1  |
| Mary Fury       | Daughter    | Family 1  |
| Linda Phoenix   | Wife        | Family 2  |
| Peter Phoenix   | Son         | Family 2  |
| John Phoenix    | Son         | Family 2  |
| Sebastian Zamora| Husband     | Family 3  |
| Ryan Zamora     | Son         | Family 3  |
============================================

标签： mysqlsqldatabasedatabase-design

解决方案

经典的“标准化”。

假设这些是所需的两个表：

CREATE TABLE Employee (
    family_id INT UNSIGNED AUTO_INCREMENT,
    name ...,
    company ...,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE FamilyTree (
    id INT UNSIGNED AUTO_INCREMENT,
    family_id INT UNSIGNED,
    name ...,
    relationship ...,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

这是填充它们的 SQL：

-- Create ids for each "family" (`id` will be automatically set):
INSERT INTO Employee (name, company)
    SELECT DISTINCT employee_name, company
        FROM Dump;

-- Build the other table:
INSERT INTO FamilyTree (name, relationship, family_id)
    SELECT d.family_tree_name, d.relationship,
           e.family_id
        FROM Employee AS e
        JOIN Dump AS d  ON d.employee_name = e.name
                       AND d.company = e.company;

这需要更少的输入，并且是使用 SQL 的一个很好的教训，而不是在编程语言中繁琐地编写类似 SQL 的操作。

如果有裙带关系，你就有问题了。

推荐阅读