mySQL - 插入三个表

我最近问了这个问题.

<块引用><块引用>

我有一个包含三个表的关系数据库.第一个包含 id这与第二个有关.第二包含与第三.第三个包含结果我在追.

是否可以通过单个查询在第一个表中查询一个 id给出第三个表中的所有结果与它有关吗?

我选择的解决方案是:

<块引用>

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

添加 where 子句进行搜索表 1 中的某些行

其中 t1.field = 'value'

我的新问题是:

我意识到我也需要插入三个表.我正在处理的是一个预订系统.是否可以编写一个查询，在查询后直接插入三个表(使用连接?).

我还有另一个考虑是我应该使用事务来确保同时运行两个查询...两者都发现 ID 是未保留的"，然后导致双重预订，还是有更简单的方法?

解决方案

您绝对应该在事务中执行三个插入操作.我可能会编写一个存储过程来处理插入.

这是一个带有事务的存储过程的示例.请注意使用 LAST_INSERT_ID() 来获取先前插入的记录的 ID.这只是两张表，但您应该可以将其扩展为三张表.

DELIMITER//创建程序 new_engineer_with_task(第一个 CHAR(35)，最后一个 CHAR(35)，电子邮件 CHAR(255)，tool_id INT)开始开始交易；INSERT INTO 工程师(名字、姓氏、电子邮件)值(第一个，最后一个，电子邮件)；插入任务(engineer_id、tool_id)VALUES(LAST_INSERT_ID(), tool_id);犯罪;结尾//分隔符；

你这样称呼它:

CALL new_engineer_with_task('Jerry', 'Fernholz', 'me@somewhere.com', 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', 'me@somewhere.com', 1);