C＃MySQL id列继续计数 [英] C# MySQL id column keep counting

问题描述

我正在尝试使用重复键更新将一些数据插入MySQL数据库...但是插入后，如果我多次单击保存按钮id（自动增量）继续计数。有什么方法可以解决这个问题吗？请帮助我。

I am trying insert some data into a MySQL database using on Duplicate key Update... But after inserting, if I click save button for several times id (auto increment) keep counting. Is there any way to fix this problem? please help me.

MySqlConnection conn = new MySqlConnection(baglanti);
                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "INSERT INTO hekim_terifi (personal_id,  qeyd_tarixi, hekim_ixtisas_id ) VALUES (@personal_id,  CURRENT_TIME, @hekim_ixtisas_id) on DUPLICATE KEY UPDATE hekim_ixtisas_id = '" + label3.Tag + "'";

                try
                {
                   // command.Parameters.AddWithValue("@id", "");
                    command.Parameters.AddWithValue("@personal_id", "" + label2.Tag + "");
                    command.Parameters.AddWithValue("@hekim_ixtisas_id", "" + label3.Tag + "");

                    command.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                conn.Close();

推荐答案

显然你使用的是MySQL的事务引擎（可能是InnoDB）...

设想一个场景：

It is clear that you use a transaction engine for MySQL (probably InnoDB)...
Let imagine a scenario:

1. START INSERT 1
2. START INSERT 2
3. INERT 1 FAILS AND ROLL BACK
4. INSERT 2 SUCCEED...

因此，当 INSERT 2 选择他的ID INSERT 1 仍然有效，所以 INSERT 2 在 INSERT 1 之后会有下一个ID，但是在 INSERT 1 之后会丢弃其ID和差距创建...

但是在跨国环境中没有别的方法 - 要始终顺序，你必须按顺序发送INSERT请求并且你失去了很多性能......

http://dev.mysql.com/doc/ refman / 5.0 / en / innodb-auto-increment-handling.html [ ^ ]

So while INSERT 2 picks his ID INSERT 1 still active so INSERT 2 will have the next ID after INSERT 1, but after than INSERT 1 drops its ID and a gap created...
But in a transnational environment there is no other way - to be always sequential you have to send your INSERT request sequentially and you lost a lot of in performance...
http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html[^]

这篇关于C＃MySQL id列继续计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！