在 MYSQL 中使用 AUTO_INCRMENT
为什么我这样做时会出现以下异常，我犯了什么错误？ 将元组插入具有 AUTO_INCRMENT 字段的表的正确方法是什么？ 删除怎么样，可以更新id吗？
string connStr = "server=localhost;user=root;database=test;port=3306;password=XXX;";
MySqlConnection conn = new MySqlConnection(connStr);
try{
MessageBox.Show("Hello");
conn.Open();
string s0 = "CREATE TABLE school.student ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(45) NOT NULL, PRIMARY KEY (id))";
string s1 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'john\' )";
string s2 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'mary\' )";
MySqlCommand cmd = new MySqlCommand(s0, conn);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand(s1, conn);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand(s2, conn);
cmd.ExecuteNonQuery();
conn.Close();
}catch(Exception ex){
TextWriter tw = new StreamWriter("C:\\test.txt");
MessageBox.Show(ex.ToString());
tw.WriteLine(ex.ToString());
}
MySql.Data.MySqlClient.MySqlException: Duplicate entry '1' for key 'PRIMARY'
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadResult()
at MySql.Data.MySqlClient.ResultSet.NextResult()
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at ProjectInfo.Connect.Exec(String commandName, vsCommandExecOption executeOption, Object& varIn, Object& varOut, Boolean& handled)
Why do I get the following exception when I did this, what mistake did i made?
What is the right way to insert tuple into tables with AUTO_INCREMENT fields?
How about delete, is it possible to update the ids?
string connStr = "server=localhost;user=root;database=test;port=3306;password=XXX;";
MySqlConnection conn = new MySqlConnection(connStr);
try{
MessageBox.Show("Hello");
conn.Open();
string s0 = "CREATE TABLE school.student ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(45) NOT NULL, PRIMARY KEY (id))";
string s1 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'john\' )";
string s2 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'mary\' )";
MySqlCommand cmd = new MySqlCommand(s0, conn);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand(s1, conn);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand(s2, conn);
cmd.ExecuteNonQuery();
conn.Close();
}catch(Exception ex){
TextWriter tw = new StreamWriter("C:\\test.txt");
MessageBox.Show(ex.ToString());
tw.WriteLine(ex.ToString());
}
MySql.Data.MySqlClient.MySqlException: Duplicate entry '1' for key 'PRIMARY'
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadResult()
at MySql.Data.MySqlClient.ResultSet.NextResult()
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at ProjectInfo.Connect.Exec(String commandName, vsCommandExecOption executeOption, Object& varIn, Object& varOut, Boolean& handled)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这些行是问题所在：
看，LAST_INSERT_ID() 函数返回最后生成的 auto_increment 值的值。第一次插入时，尚未发生任何插入，并且 LAST_INSERT_ID() 的计算结果为 NULL。假设您的表为空，则插入的行的生成值为 1。在第二次插入时，由于您插入的前一行，LAST_INSERT_ID() 将为 1。所以，这一次，数据库中已经有一行 1 了，第二次插入没有成功，因为重复了 1。
重写如下：
These lines are the problem:
See, the LAST_INSERT_ID() function returns the value of the last generated auto_increment value. At th time of your first insert, no insert has taken place yet and LAST_INSERT_ID() evaluates to NULL. Suppose your table was empty the row is inserted with the generated value of 1. At the time of the second insertt, LAST_INSERT_ID() will be 1 due to the previous row you inserted. So, this time, there is already a row with 1 in the db, and the second insert does not succeed because of the duplicate 1.
Rewrite like this:
只需省略具有 AUTO_INCRMENT 的字段，MySQL 将处理其余的事情。
您可以在删除后更新 ID，但我建议您不要这样做。
Just omit the field that has AUTO_INCREMENT, MySQL will take care of the rest.
You can update the IDs after DELETE, but I would advise you to not do that.
您不应该尝试将值插入自动增量字段 - 因为（提示在名称中）这将由 MySQL 自动增量。
You shouldn't be attempting to insert a value into an auto increment field - as (the hint is in the name) this will be automatically incremented by MySQL.