mySQL - 插入三个表 [英] mySQL - Insert into three tables

问题描述

我最近问过此问题。

我有一个有三个表的关系数据库。第一个包含id的
与第二个相关。第二个
包含与第三个
相关的id。第三个包含结果
我之后。

可以通过单个查询到
查询id在第一个表中，
给出了与它相关的第三个表
的所有结果？

strong>我选择的解决方案是：

select * from table1 t1 join table2 t2
on t1.t2ref = t2.id join table3 t3 on
t2.t3ref = t3.id

添加一个where子句来搜索
table1中的某些行

其中t1.field ='value'

是：

我意识到我需要插入到三个表中。我所处理的是一个预订系统。是否可以编写一个查询，在查询后直接插入三个表（使用连接？）。

另一个考虑因素是我应该使用事务来确保两个查询同时运行...都发现id是'unreserved'，然后导致双重预订或有一个更简单的方法？

解决方案

你应该在一个事务中做三个插入。我可能会写一个存储过程来处理插入。

编辑：

存储过程与事务。请注意，使用LAST_INSERT_ID（）来获取先前插入的记录的ID。这只有两个表，但您应该可以将其扩展到三个表。

  DELIMITER // 
 CREATE PROCEDURE new_engineer_with_task（
 first CHAR（35），last CHAR（35），email CHAR（255），tool_id INT）
 BEGIN 
 START TRANSACTION; 
 INSERT INTO工程师（姓，名，电子邮件）
 VALUES（first，last，email）; 
 
 INSERT INTO任务（engineer_id，tool_id）
 VALUES（LAST_INSERT_ID（），tool_id）; 
 COMMIT; 
 END // 
 DELIMITER; 
  

你这样调用：

  CALL new_engineer_with_task（'Jerry'，'Fernholz'，'[e-mail]'，1）; 
  

I recently asked this question.

I have a relational database with three tables. The first containts id's that relate to the second. The second contains id's that relate to the third. The third contains the results I am after.

Is it possible with a single query to query an id in the first table which gives all results from the third table that relate to it?

My chosen solution was:

select * from table1 t1 join table2 t2 on t1.t2ref = t2.id join table3 t3 on t2.t3ref = t3.id

Add a where clause to search for certain rows in table1

where t1.field = 'value'

My new question is:

I have realised that I need to insert into the three tables too. What I am dealing with is a reservation system. Is it possible to write a query that inserts into three tables directly after it queries them (using joins?).

Also another consideration I have is should I use transactions to ensure that two queries are run at the same time... both find that the id's are 'unreserved' and then resulting in a double booking or is there a more simple way?

解决方案

You should definitely do the three inserts in a transaction. I would probably write a stored procedure to handle the inserts.

EDIT:

Here is an example of a stored procedure with a transaction. Note the use of LAST_INSERT_ID() to get the ID of the previously inserted record. This is only two tables, but you should be able to extend it to three tables.

DELIMITER //
CREATE PROCEDURE new_engineer_with_task(
  first CHAR(35), last CHAR(35), email CHAR(255), tool_id INT)
BEGIN
START TRANSACTION;
   INSERT INTO engineers (firstname, lastname, email) 
     VALUES(first, last, email);

   INSERT INTO tasks (engineer_id, tool_id) 
     VALUES(LAST_INSERT_ID(), tool_id);
COMMIT;
END//
DELIMITER ;

And you call it like so:

CALL new_engineer_with_task('Jerry', 'Fernholz', '[e-mail]', 1);

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