如何链接不同的表并在mysql中写入它们 [英] How to link different tables and write to them in mysql

问题描述

我有一个包含不同表的数据库，我目前正在写所有这些表。我目前正在使用计数器管理每个表的ID，但问题是我被告知每个Id应该自动递增，并且所有表应该与前一个表的id链接在一起。



我尝试过的事情：



我的表结构如下：

活动：idactivity，idResult，活动

结果：idResult，idObjective，Results

目标：idObjective，idProject

项目：idProject，project



我能够写入所有表，但我不知道如何使用上一个表中的id链接所有表。

I have a database with different tables, and I am currently writing to all of these tables. I am currently managing the ID of each table with a counter, but the thing is that I was told that each Id should auto increment and all the tables should be linked together with the id of the previous table.

What I have tried:

My table structure is this:
Activities: idactivity, idResult, Activity
Results: idResult, idObjective, Results
Objectives: idObjective, idProject
Projects: idProject, project

Iam able to write to all the tables, but I don't know how to link all the tables with the id from the previous table.

推荐答案

就个人而言，我会使用内置的

Personally, I would use the built in

LAST_INSERT_ID()

函数。 [ ^ ]这将返回最近插入的Id范围内的连接。



这样做，您将需要使表依赖关系的层次结构正确。从它的外观来看，插入序列应如下所示：

function. [^] This will return the most recently inserted Id within the scope of the connection.

In doing so, you will need to get your hierarchy of table dependencies correct. From the look of it, the sequence of inserts should go like this:

Insert into Projects (your values)
SET @last_id_from_insert = LAST_INSERT_ID(); --which will return the Id for the new row you have inserted into the Projects table

Insert into Objectives (idProject)
Values (@last_id_from_insert)

SET @last_id_from_insert = LAST_INSERT_ID(); --update our variable to now be the Id of the row we have just inserted into the Objectives table

Insert into Results(idObjective)
Values (@last_id_from_insert)

SET @last_id_from_insert = LAST_INSERT_ID(); --update our variable to now be the Id of the row we have just insObjectives to the Results table

Insert into Activities(idResult)
Values (@last_id_from_insert)

正如Dileep所说，最好在存储过程中执行此操作，以便每次插入和检索最新Id的连接范围相同。



希望有所帮助。

As Dileep says, it would be good practice to do this within a stored procedure so that the connection scope is the same for each insert and retrieval of the latest Id.

Hope that helps.

这篇关于如何链接不同的表并在mysql中写入它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！