MySQL-如何插入具有多对多关系的表 [英] MySQL - How to insert into table that has many-to-many relationship

问题描述

我有一张桌子.每个人都有财产，许多人可能有一定财产.因此，这是一个多对多的关系.这是模式:

I have a table of persons. Each person has a property and many persons may have a certain property. So this is a many-to-many relationship. This is the schema:

CREATE TABLE persons (
  person_id int(11) NOT NULL AUTO_INCREMENT,
  firstname varchar(30) NOT NULL,
  lastname varchar(30) NOT NULL,
  PRIMARY KEY (person_id)
);

CREATE TABLE properties (
  property_id int(11) NOT NULL AUTO_INCREMENT,
  property varchar(254) NOT NULL UNIQUE,
  PRIMARY KEY (property_id)
);

CREATE TABLE has_property (
  person_id int(11) NOT NULL,
  property_id int(11) NOT NULL,
  PRIMARY KEY (person_id,property_id),
  FOREIGN KEY (person_id) REFERENCES persons (person_id),
  FOREIGN KEY (property_id) REFERENCES properties (property_id)
);

现在让我说我想将此人插入数据库:

Now lets say i want to insert to the database this person:

• 名字:约翰"
• 姓:'Doe'
• 属性:'property_A'，'property_B'，'property_C'

人

+-----------+-----------+----------+
| person_id | firstname | lastname |
+-----------+-----------+----------+
|         1 | John      | Doe      |
+-----------+-----------+----------+

属性

+-------------+------------+
| property_id |  property  |
+-------------+------------+
|           1 | property_A |
|           2 | property_B |
|           3 | property_C |
+-------------+------------+

具有财产

+-----------+-------------+
| person_id | property_id |
+-----------+-------------+
|         1 |           1 |
|         1 |           2 |
|         1 |           3 |
+-----------+-------------+

到目前为止，我认为最好的事情是在人员表中进行常规插入:

So far the best thing i have thought is to do a regular insert in the persons table:

INSERT INTO persons (firstname,lastname) VALUES ('John','Doe');

，然后进行选择以查找我刚刚插入的人的ID

and then do a select to find the id of the person i just inserted

SELECT person_id FROM persons WHERE firstname='John' AND lastname='Doe';

为了插入其他两个表(因为我需要知道person_id). 但是我认为必须有更好的方法，不是吗?

in order to insert into the other two tables (because i need to know the person_id). But i think there must be a better way, isn't it?

推荐答案

这就是我最终要做的事情.我希望它能对某人有所帮助.

Here is what i ended up doing. I hope it helps someone.

INSERT INTO persons (firstname,lastname) VALUES ('John','Doe');
SET @person_id = LAST_INSERT_ID();

INSERT IGNORE INTO properties (property) VALUES ('property_A');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);

INSERT IGNORE INTO properties (property) VALUES ('property_B');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);

INSERT IGNORE INTO properties (property) VALUES ('property_C');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);

这篇关于MySQL-如何插入具有多对多关系的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！