两个带有链接字段的插入查询
本文关键字:字段 链接 插入 查询 两个 | 更新日期: 2023-09-27 18:12:54
我有以下两个MySQL表:
questions:
question_id (PK, AI), module_id (FK), author_id (FK), approved, question, correct_answer_id (FK)
answers:
answer_id (PK, AI), question_id (FK), answer
我希望能够在'问题'表中插入新行,并在'答案'表中插入多行。
'answers'表中的新行应该具有与'questions'行中新生成的'question_id'值相同的'question_id'。此外,'questions'表中的'correct_answer_id'字段应该等于'answers'表中插入的第一行的'answer_id'。
是否有比以下步骤更有效的方法?:
- 插入值(module_id, author_id, approved, question)"问题"
- get last 'question_id' in 'questions'
- 在'answers'中插入values (question_id, answer)
- 在'questions'中更新值(correct_answer_id)
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connStr);
string queryUpdateQuestions = "INSERT INTO questions (module_id, author_id, approved, question) VALUES (@module_id, @author_id, @approved, @question)";
MySqlCommand cmdUpdateQuestions = new MySqlCommand(queryUpdateQuestions, conn);
cmdUpdateQuestions.Parameters.Add("@module_id", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@module_id"].Value = ddlModules.SelectedValue.ToString();
cmdUpdateQuestions.Parameters.Add("@author_id", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@author_id"].Value = Session["UserID"].ToString();
cmdUpdateQuestions.Parameters.Add("@approved", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@approved"].Value = 'N';
cmdUpdateQuestions.Parameters.Add("@question", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@question"].Value = txtQuestion.Text;
try
{
conn.Open();
cmdUpdateQuestions.ExecuteNonQuery();
}
catch
{
lblError.Text="Unable to add question.";
}
finally
{
conn.Close();
}
//????? = get last question_id in 'questions'
int a = Convert.ToInt32(ddlNoOfAnswers.SelectedValue.ToString());
for (int b=1; b <= a; b++)
{
string queryUpdateAnswers = "INSERT INTO answers (question_id, answer) VALUES (@question_id, @answer)";
MySqlCommand cmdUpdateAnswers = new MySqlCommand(queryUpdateAnswers, conn);
cmdUpdateAnswers.Parameters.Add("@answer", MySqlDbType.VarChar);
cmdUpdateAnswers.Parameters["@answer"].Value = ((TextBox)this.FindControl("txtAnswer" + b)).Text;
cmdUpdateAnswers.Parameters.Add("@question_id", MySqlDbType.VarChar);
cmdUpdateAnswers.Parameters["@question_id"].Value = ?????;
try
{
conn.Open();
cmdUpdateAnswers.ExecuteNonQuery();
}
catch
{
lblError.Text="Unable to add answer.";
}
finally
{
conn.Close();
}
}
//update 'correct_answer_id' in 'questions'
可以进行一些简化。首先,您需要将所有命令包含在事务中,因为这是典型的情况,其中插入的记录严格处于关系中,并且没有意义有一些部分完成的记录集。
using(MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
using(MySqlTransaction tr = conn.BeginTransaction())
{
...
// MySqlCommand code goes here
...
tr.Commit();
}
}
现在,您可以更改插入问题sql,以添加第二个语句,返回最后插入的id
string queryUpdateQuestions = @"INSERT INTO questions (.....);
SELECT LAST_INSERT_ID()";
using(MySqlCommand cmdUpdateQuestions = new MySqlCommand(queryUpdateQuestions, conn, tr))
{
// build the parameters for the question record
......
// Instead of ExecuteNonQuery, run ExecuteScalar to get back the result of the last SELECT
int lastQuestionID = Convert.ToInt32(cmdUpdateQuestions.ExecuteScalar());
..
}
注意,在MySqlCommand构造函数中,如何将引用传递给当前事务。这需要与已打开事务的连接一起工作。
第二部分的情况要复杂一些。添加第二个sql语句的相同技巧也可以应用于插入答案的循环,但是如果第一个问题是正确的,则需要向后循环
string queryUpdateAnswers = @"INSERT INTO answers (question_id, answer)
VALUES (@question_id, @answer);
SELECT LAST_INSERT_ID()";
using(MySqlCommand cmdUpdateAnswers = new MySqlCommand(queryUpdateAnswers, conn, tr))
{
// next move the loop inside the using and prepare the parameter before looping to
// to avoid unnecessary rebuild of the parameters and the command
cmdUpdateAnswers.Parameters.Add("@answer", MySqlDbType.VarChar);
cmdUpdateAnswers.Parameters.Add("@question_id", MySqlDbType.Int32);
int lastAnswerID = 0;
// Loop backward so the last answer inserted is the 'correct' one and we could get its ID
for (int b=a; b >= 1; b--)
{
cmdUpdateAnswers.Parameters["@answer"].Value = ((TextBox)this.FindControl("txtAnswer" + b)).Text;
cmdUpdateAnswers.Parameters["@question_id"].Value = lastQuestionID;
lastAnswerID = Convert.ToInt32(cmdUpdateAnswers.ExecuteScalar());
}
....
}
现在可以运行最后一个命令,用lastAnswerID
更新问题(最后注意,我假设字段question_id和answer_id是数字类型,而不是varchar,这要求这些字段的参数将是Int32而不是varchar)
是的,你概述的方法是最有效的。您需要检索分配给每一行insert的AUTO_INCREMENT列的值。但是要注意如何检索该值。
- 在'questions'表中插入一行
- 检索分配给AUTO_INCREMENT列的last_insert_id值
- 在'answers'表中插入行,使用'question_id'列的检索值
- 在插入"正确答案"行后立即检索last_insert_id值
- 更新'questions'中的行以设置'correct_answer_id'列
MySQL提供了LAST_INSERT_ID()
函数。这是MySQL提供的机制,在成功执行INSERT
语句后检索分配给AUTO_INCREMENT列的值。(对于单例插入,它非常简单;它只需要在插入之后立即调用。)
裁判:http://dev.mysql.com/doc/refman/5.5/en/information-functions.html function_last-insert-id
许多客户端库提供了一个内置函数来完成此操作,因此没有必要准备和执行单独的SELECT语句。(例如PHP, PDO提供lastInsertId
, mysqli提供$insertid
。很可能MySQL的c#连接器也有类似的功能。)