mysql 多对多的表_MySQL-如何插入具有多对多关系的表

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:

firstname:'John'

lastname:'Doe'

properties:'property_A','property_B','property_C'

persons

+-----------+-----------+----------+

| person_id | firstname | lastname |

+-----------+-----------+----------+

| 1 | John | Doe |

+-----------+-----------+----------+

properties

+-------------+------------+

| property_id | property |

+-------------+------------+

| 1 | property_A |

| 2 | property_B |

| 3 | property_C |

+-------------+------------+

has_property

+-----------+-------------+

| 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');

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';

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);