mysql针对某个表记录操作日志_填写MySQL在一个操作中记录一对多相关的表

I have two MySQL tables with an one-to-many relationship between them. For example:

CREATE TABLE test1 (

pk1 INTEGER AUTO_INCREMENT PRIMARY KEY,

testvalue1 INTEGER

);

CREATE TABLE test2 (

pk2 INTEGER AUTO_INCREMENT PRIMARY KEY,

testvalue2 VARCHAR(50),

fk2 INTEGER NOT NULL,

FOREIGN KEY (fk2) REFERENCES test1 (pk1)

);

If I want to insert records in both tables I can first insert a record in the PK table (e.g. INSERT INTO test1 SET testvalue1=100), determine the PK value (e.g. SELECT MAX(pk1) AS LastId FROM test1 or use LAST_INSERT_ID())

and finally use that value to fill the FK column in the second table.

But is it possible to achieve this all in 1 command/query/action? So let's MySQL fill in the PK- and FK-values using the AUTO INCREMENTs?

解决方案

You should use two INSERT commands; or try to use an INSERT-trigger.

EDIT:

--An example with trigger:

CREATE TABLE dept(

id INT(11) NOT NULL AUTO_INCREMENT,

dept_name VARCHAR(255) DEFAULT NULL,

PRIMARY KEY (id)

)

ENGINE = INNODB;

CREATE TABLE emp(

id INT(11) NOT NULL AUTO_INCREMENT,

emp_name VARCHAR(255) DEFAULT NULL,

dept_id INT(11) DEFAULT NULL,

PRIMARY KEY (id),

CONSTRAINT FK_emp_dept_id FOREIGN KEY (dept_id)

REFERENCES dept (id) ON DELETE RESTRICT ON UPDATE RESTRICT

)

ENGINE = INNODB;

DELIMITER $$

CREATE TRIGGER trigger1

AFTER INSERT

ON dept

FOR EACH ROW

BEGIN

INSERT INTO emp VALUES (NULL, 'Someone', NEW.id);

END

$$

DELIMITER ;

-- Try to add new department.

INSERT INTO dept VALUES(NULL, 'Sales');

-- Is there new default employee?

SELECT * FROM emp;

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

| id | emp_name | dept_id |

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

| 1 | Someone | 1 |

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