是否可以使用mysql顺序写入多个（2）表 [英] Is it possible to write to multiple (2) tables sequentially using mysql

问题描述

我不知道问题，但是这里有我的问题的详细信息：



（如果有任何不清楚的地方，请随时重新提问）明确地解决了）



我想知道是否可以从单个 顺序写入<2>表 MySql语句。以下是我想要实现的目标：



- 我有两个表：表A（员工）和表B（经理）。

*表A存储所有员工详情，包括表B的主键（manID），而表B仅存储以下内容。

（表B布局：

manID INT（11）主键

FK_empID INT（11）



- 员工既可以是员工也可以是经理。



如果员工也是经理，我希望能够插入新员工员工进入表A，获取新创建的empID（使用

I had no idea to word the Question but here goes the details of my question:

(Please feel free to reword the question if anything is not clear or clearly addressed)

I want to know if it is possible to write to 2 tables sequentially from a single MySql statement. Here is what I am trying to achieve:

- I have two tables: Table A (Employee) and Table B (Managers).
* Table A stores all the Employee particulars including the Primary Key (manID) of Table B while Table B stores only the following.
(Table B layout:
manID INT(11) Primary Key
FK_empID INT(11)

- An employee can be both an Employee and a Manager.

If an Employee is also a Manager, I was hoping to Insert the new Employee into Table A, get the newly created empID (using

SELECT LAST_INSERT_ID()

）以插入empID到表B（FK_empID），然后将新创建的manID返回到表A，FK_manID。



它是递归的，写入表A然后写入表B然后返回表A.



我希望使用单个SQL语句来实现所有这些而不是使用3个语句，如果它是第一个可取的，其次是可行的，2x插入和1x更新。



但是，可能有一种更简化的方法来实现这一点，而不会在各种表格中重复信息。



目标是能够在表A中存储员工信息，如果员工是经理，则在表B中引用它。当员工在经理下工作时，经理的信息可以链接回表A中存储的员工信息。



问候，

T



我尝试过：



插入新的员工，使用SELECT LAST_INSERT_ID（）获取新的empID。将表A中的empID插入表B中的FK_empID。在表B中获取新创建的manID，然后更新表A FK_manID。

) to Insert the empID into Table B (FK_empID), then return the newly created manID to Table A, FK_manID.

It is recursive, write to Table A then to Table B then back to Table A.

What I am hoping for, if it is firstly advisable and secondly doable, to use a single SQL statement to achieve all of this instead of using 3 statements, 2x Insert's and 1x Update.

However, there's probably a chance that a more simplified method of achieving this without duplicating information across various tables.

The goal is to be able to store Employee information in Table A, and if an Employee is a Manager have it referenced in Table B. When an Employee is working under a Manager, the Manager's information can be linked back to the Employee information stored in Table A.

Regards,
T

What I have tried:

Insert new Employee, get new empID with "SELECT LAST_INSERT_ID()". Insert empID from Table A to FK_empID in Table B. Get the newly created manID in Table B and then Update Table A FK_manID.

推荐答案

是的，您可以这样做。

但我会提出两点建议。

首先，请确保使用交易MySQL :: MySQL 5.7参考手册:: 13.3.1 START TRANSACTION，COMMIT和ROLLBACK语法 [ ^ ]确保如果任何部分失败，则可以回滚整个更新，以便您的数据库永远不会遗漏数据。

其次，虽然你可以在你的应用程序的单个语句中完成它（只是用分号分隔部分）我建议创建一个存储过程来执行它并从您的应用程序调用。它可以让你更容易改变，并确保你在调用它时做得对，就是这样。 MySQL :: MySQL Connector / Net开发人员指南:: 5.1.5使用存储过程 [ ^ ]

Yes, you can do it.
But I'd make two suggestions.
Firstly, make sure you use a Transaction MySQL :: MySQL 5.7 Reference Manual :: 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax[^] to ensure that if any part fails, then the whole update can be rolled back so that your database is never left with missing data.
Secondly, while you can do it in a single statement from your application (just separate the "parts" with semicolons) I'd recommend creating a Stored Procedure to do it and calling that from your app. It makes it easier to change and be sure you got it right when you call it, is all. MySQL :: MySQL Connector/Net Developer Guide :: 5.1.5 Working with Stored Procedures[^]

这篇关于是否可以使用mysql顺序写入多个（2）表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！