mysql - 在 MYSQL 中使用 AUTO_INCRMENT

Question

为什么我这样做时会出现以下异常，我犯了什么错误？将元组插入具有 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)

Answer 1

这些行是问题所在:

string s1 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'john\' )";
string s2 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'mary\' )";

看，LAST_INSERT_ID()函数返回最后生成的auto_increment值的值。第一次插入时，尚未发生任何插入，并且 LAST_INSERT_ID() 的计算结果为 NULL。假设您的表为空，则插入的行的生成值为 1。在第二次插入时，由于您插入的前一行，LAST_INSERT_ID() 将为 1。所以，这一次，数据库中已经有一行 1，第二次插入由于重复的 1 而没有成功。

这样重写:

string s1 = "INSERT INTO school.student (name) VALUES (\'john\' )";
string s2 = "INSERT INTO school.student (name) VALUES (\'mary\' )";