C# - 检索最大的 MySQL 列值，递增 1，并在 INSERT 语句中使用

Question

我使用的是 C#、MySQL 和 Visual Studio 2015。

我有一个:

• 名为“cp_users”的数据库
• 名为“users”的表
• 名为“account_no”的列

account_no 列是 int(11) 和主键。

我只是想获取最大帐号的值，将其加1，然后在语句中使用新值插入新的用户数据，从而确保帐号永远不会冲突。

信息:

public partial class Form1 : Form
{
    MySqlConnection mcon = new MySqlConnection();
    public Form1()
    {
        InitializeComponent();
        mcon.ConnectionString = "datasource=166.XXX.XXX.XXX;port=3306;initial catalog=cp_users; username=XXXXXX;password=XXXXXX";
    }

我遇到问题的代码:

            if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && textBox6.Text != "" && listBox1.SelectedItem != null)
                             {
                                mcon.Open();
                                //Upload New User Information to 'users' Database
                                try
                                {

        //CODE TO RECALL LARGEST ACCOUNT NUMBER AS reader3 VALUE,
        //INCREASE IT BY 1, ASSIGN IT AS A STRING
        //FOR USE BELOW (NOT WORKING)

                                    MySqlCommand mda3 = new MySqlCommand();
                                    mda3.Connection = mcon;
                                    mda3.CommandText = "SELECT max(account_no) value FROM users";
                                    MySqlDataReader reader3 = mda3.ExecuteReader();
                                    reader3 = reader3++;


        //UPLOAD NEW USER DATA INCLUDING 'reader3' VALUE FOR 'account_no'

                                    MySqlCommand mda2 = new MySqlCommand();
                                    mda2.Connection = mcon;
                                    mda2.CommandText = ("insert into cp_users.users(account_no, first_name, last_name, email_1, company_industry, user_password) values('" + reader3 + "','" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + listBox1.Text + "', '" + textBox5.Text + "');");
                                    MySqlDataReader reader2 = mda2.ExecuteReader();
                                    mcon.Close();

                                    this.Hide();
                                    Form2 frm4 = new Form2();
                                    frm4.ShowDialog();
                                    MessageBox.Show("Registration Successful - You can now login to our Desktop, Web and App Interfaces", "Welcome to ConnectPlanet");

                            }
                                    catch
                            {
                                MessageBox.Show("New Registration Failed - We're Sorry, Please Contact Customer Support", "Oops!");
                                mcon.Close();
                            }

任何建议都表示赞赏，我猜这对于有更多经验的人来说很简单，但我似乎无法在网上或我的(小)图书收藏中找到答案。

Answer 1

您正在寻找 AUTO_INCRMENT 列。请参阅:Using AUTO_INCREMENT

CREATE TABLE users
(
    account_no   INT         NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email_1 VARCHAR(100) NOT NULL,
    company_industry VARCHAR(100) NOT NULL,
    user_password VARCHAR(128) NOT NUL
);

因此您在插入数据时甚至不需要指定 id 列:

insert into cp_users.users(first_name, last_name, email_1, company_industry, user_password) values( ....