mySQL - 插入三个表

发布于 2024-08-10 13:13:06 字数 728 浏览 9 评论 0原文

我最近问了这个问题。

<块引用>

我有一个包含三个表的关系数据库。第一个包含 id 与第二个相关的。第二个 包含与以下内容相关的 id 第三。第三个包含结果 我在追。

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

我选择的解决方案是:

从表 1 t1 中选择 * 连接表 2 t2 在 t1.t2ref = t2.id 上加入 table3 t3 t2.t3ref = t3.id

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

其中 t1.field = '值'

我的新问题是:

我意识到我也需要插入到这三个表中。我正在处理的是一个预订系统。是否可以编写一个查询,在查询三个表后直接插入它们(使用联接?)。

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

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

往昔成烟 2024-08-17 13:13:06

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

编辑:

这是带有事务的存储过程的示例。请注意使用 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 engineers (firstname, lastname, email) 
     VALUES(first, last, email);

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

你这样称呼它:

CALL new_engineer_with_task('Jerry', 'Fernholz', '[email protected]', 1);

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', '[email protected]', 1);
潇烟暮雨 2024-08-17 13:13:06

您无法使用一个查询插入多个表,您必须将其分解为多个查询。

You can't insert into multiple tables with one query, You'll have to break it up to multiple queries.

幽梦紫曦~ 2024-08-17 13:13:06

执行多个更新时始终使用事务。如果一次更新失败,您将需要回滚之前成功的更新,这样就不会违反关系模型的任何非强制约束。

You always use transactions when performing multiple updates. If one update fails you will want to roll back previous successful updates so you don't violate any unenforced constraints of the relational model.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文