MySQL - 重置自动递增



大多数 MySQL 表使用顺序值来表示记录,例如序列号。MySQL 使用“AUTO_INCREMENT”来自动处理此问题,而不是逐个手动插入每个值。

MySQL 中的 AUTO_INCREMENT

MySQL 中的 AUTO_INCREMENT 用于在向表中添加新记录时自动生成按升序排列的唯一数字。对于需要每一行都有唯一值的应用程序,它非常有用。

当您将列定义为 AUTO_INCREMENT 列时,MySQL 会处理其余部分。它从值 1 开始,并为每条新记录递增 1,为您的表创建一系列唯一数字。

示例

以下示例演示了在数据库表中的列上使用 AUTO_INCREMENT。在这里,我们正在创建一个名为“insect”的表,并将 AUTO_INCREMENT 应用于“id”列。

CREATE TABLE insect ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL, origin VARCHAR(30) NOT NULL );

现在,您无需在插入记录时手动指定“id”列的值。相反,MySQL 会为您处理它,从 1 开始,每条新记录递增 1,为您的表创建一系列唯一数字。要插入表中其他列的值,请使用以下查询:

INSERT INTO insect (name,date,origin) VALUES ('housefly','2001-09-10','kitchen'), ('millipede','2001-09-10','driveway'), ('grasshopper','2001-09-10','front yard');

显示的 insect 表如下所示。在这里,我们可以看到“id”列的值是由 MySQL 自动生成的:

id 名称 日期 来源
1 家蝇 2001-09-10 厨房
2 千足虫 2001-09-10 车道
3 蚱蜢 2001-09-10 前院

MySQL 重置自动递增

表上的默认 AUTO_INCREMENT 值从 1 开始,即插入的值通常从 1 开始。但是,MySQL 还提供将这些 AUTO_INCREMENT 值重置为另一个数字的规定,使序列能够从指定重置值开始插入。

您可以通过三种方式重置 AUTO_INCREMENT 值:使用 ALTER TABLE、TRUNCATE TABLE 或删除并重新创建表。

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

使用 ALTER TABLE 语句重置

MySQL 中的 ALTER TABLE 语句用于更新表或对其进行任何更改。因此,使用此语句重置 AUTO_INCREMENT 值是一个完全有效的选择。

语法

以下是使用 ALTER TABLE 重置自动递增的语法:

ALTER TABLE table_name AUTO_INCREMENT = new_value;

示例

在这个示例中,我们使用 ALTER TABLE 语句将 AUTO_INCREMENT 值重置为 5。请注意,新的 AUTO_INCREMENT 值必须大于表中已存在的记录数:

ALTER TABLE insect AUTO_INCREMENT = 5;

获得的输出如下:

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在,让我们将另一个值插入上面创建的“insect”表中,并使用以下查询检查新的结果集:

INSERT INTO insect (name,date,origin) VALUES ('spider', '2000-12-12', 'bathroom'), ('larva', '2012-01-10', 'garden');

我们得到的结果如下所示:

Query OK, 2 row affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

要验证您插入的新记录是否将从设置为 5 的 AUTO_INCREMENT 值开始,请使用以下 SELECT 查询:

SELECT * FROM insect;

获得的表如下所示:

id 名称 日期 来源
1 家蝇 2001-09-10 厨房
2 千足虫 2001-09-10 车道
3 蚱蜢 2001-09-10 前院
5 蜘蛛 2000-12-12 浴室
6 幼虫 2012-01-10 花园

使用 TRUNCATE TABLE 语句重置

另一种将自动递增列重置为默认值的方法是使用 TRUNCATE TABLE 命令。这将删除表的现有数据,当您插入新记录时,AUTO_INCREMENT 列将从头开始(通常为 1)。

示例

以下是如何将 AUTO_INCREMENT 值重置为默认值“0”的示例。为此,首先使用 TRUNCATE TABLE 命令清空上面创建的“insect”表,如下所示:

TRUNCATE TABLE insect;

获得的输出如下:

Query OK, 0 rows affected (0.04 sec)

要验证表中的记录是否已删除,请使用以下 SELECT 查询:

SELECT * FROM insect;

产生的结果如下:

Empty set (0.00 sec)

现在,使用以下 INSERT 语句再次插入值。

INSERT INTO insect (name,date,origin) VALUES ('housefly','2001-09-10','kitchen'), ('millipede','2001-09-10','driveway'), ('grasshopper','2001-09-10','front yard'), ('spider', '2000-12-12', 'bathroom');

执行上述代码后,我们得到以下输出:

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

您可以使用以下 SELECT 查询验证表中的记录是否已重置:

SELECT * FROM insect;

显示的表如下所示:

id 名称 日期 来源
1 家蝇 2001-09-10 厨房
2 千足虫 2001-09-10 车道
3 蚱蜢 2001-09-10 前院
4 蜘蛛 2000-12-12 浴室

使用客户端程序重置自动递增

我们还可以使用客户端程序重置自动递增。

语法

要通过 PHP 程序重置自动递增,我们需要使用mysqli函数query()执行“ALTER TABLE”语句,如下所示:

$sql = "ALTER TABLE INSECT AUTO_INCREMENT = 5"; $mysqli->query($sql);

要通过 JavaScript 程序重置自动递增,我们需要使用mysql2库的query()函数执行“ALTER TABLE”语句,如下所示:

sql = "ALTER TABLE insect AUTO_INCREMENT = 5"; con.query(sql)

要通过 Java 程序重置自动递增,我们需要使用JDBC函数execute()执行“ALTER TABLE”语句,如下所示:

String sql = "ALTER TABLE insect AUTO_INCREMENT = 5"; statement.execute(sql);

要通过 Python 程序重置自动递增,我们需要使用MySQL Connector/Pythonexecute()函数执行“ALTER TABLE”语句,如下所示:

reset_auto_inc_query = "ALTER TABLE insect AUTO_INCREMENT = 5" cursorObj.execute(reset_auto_inc_query)

示例

以下是程序:

$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $db = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit(); } //printf('Connected successfully.'); //lets create a table $sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)"; if($mysqli->query($sql)){ printf("Insect table created successfully....!\n"); } //now lets insert some records $sql = "INSERT INTO insect (name,date,origin) VALUES ('housefly','2001-09-10','kitchen'), ('millipede','2001-09-10','driveway'), ('grasshopper','2001-09-10','front yard')"; if($mysqli->query($sql)){ printf("Records inserted successfully....!\n"); } //display table records $sql = "SELECT * FROM INSECT"; if($result = $mysqli->query($sql)){ printf("Table records: \n"); while($row = mysqli_fetch_array($result)){ printf("Id: %d, Name: %s, Date: %s, Origin: %s", $row['id'], $row['name'], $row['date'], $row['origin']); printf("\n"); } } //lets reset the autoincrement using alter table statement... $sql = "ALTER TABLE INSECT AUTO_INCREMENT = 5"; if($mysqli->query($sql)){ printf("Auto_increment reset successfully...!\n"); } //now lets insert some more records.. $sql = "INSERT INTO insect (name,date,origin) VALUES ('spider', '2000-12-12', 'bathroom'), ('larva', '2012-01-10', 'garden')"; $mysqli->query($sql); $sql = "SELECT * FROM INSECT"; if($result = $mysqli->query($sql)){ printf("Table records(after resetting autoincrement): \n"); while($row = mysqli_fetch_array($result)){ printf("Id: %d, Name: %s, Date: %s, Origin: %s", $row['id'], $row['name'], $row['date'], $row['origin']); printf("\n"); } } if($mysqli->error){ printf("Error message: ", $mysqli->error); } $mysqli->close();

输出

获得的输出如下所示:

Insect table created successfully....!
Records inserted successfully....!
Table records: 
Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard
Auto_increment reset successfully...!
Table records(after resetting autoincrement):
Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard
Id: 5, Name: spider, Date: 2000-12-12, Origin: bathroom
Id: 6, Name: larva, Date: 2012-01-10, Origin: garden    

var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); // Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); // Create a new database sql = "Create Database TUTORIALS"; con.query(sql); sql = "USE TUTORIALS"; con.query(sql); sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL);" con.query(sql); sql = "INSERT INTO insect (name,date,origin) VALUES ('housefly','2001-09-10','kitchen'),('millipede','2001-09-10','driveway'),('grasshopper','2001-09-10','front yard');" con.query(sql); sql = "SELECT * FROM insect;" con.query(sql, function(err, result){ if (err) throw err console.log("**Records of INSECT Table:**"); console.log(result); console.log("--------------------------"); }); sql = "ALTER TABLE insect AUTO_INCREMENT = 5"; con.query(sql); sql = "INSERT INTO insect (name,date,origin) VALUES ('spider', '2000-12-12', 'bathroom'), ('larva', '2012-01-10', 'garden');" con.query(sql); sql = "SELECT * FROM insect;" con.query(sql, function(err, result){ console.log("**Records after modifying the AUTO_INCREMENT to 5:**"); if (err) throw err console.log(result); }); });

输出

获得的输出如下所示:

 
Connected!
--------------------------
**Records of INSECT Table:**
[
  {id: 1,name: 'housefly',date: 2001-09-09T18:30:00.000Z,origin: 'kitchen'},
  {id: 2,name: 'millipede',date: 2001-09-09T18:30:00.000Z,origin: 'driveway'},
  {id: 3,name: 'grasshopper',date: 2001-09-09T18:30:00.000Z,origin: 'front yard'}
]
--------------------------
**Records after modifying the AUTO_INCREMENT to 5:**
[
  {id: 1,name: 'housefly',date: 2001-09-09T18:30:00.000Z,origin: 'kitchen'},
  {id: 2,name: 'millipede',date: 2001-09-09T18:30:00.000Z,origin: 'driveway'},
  {id: 3,name: 'grasshopper',date: 2001-09-09T18:30:00.000Z,origin: 'front yard'},
  {id: 5,name: 'spider',date: 2000-12-11T18:30:00.000Z,origin: 'bathroom'},
  {id: 6,name: 'larva',date: 2012-01-09T18:30:00.000Z,origin: 'garden'}
]    
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ResetAutoIncrement { public static void main(String[] args) { String url = "jdbc:mysql://127.0.0.1:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)"; st.execute(sql); System.out.println("Table insect created successfully....!"); //lets insert some records into it String sql1 = "INSERT INTO insect (name,date,origin) VALUES ('housefly','2001-09-10','kitchen'), ('millipede','2001-09-10','driveway'), ('grasshopper','2001-09-10','front yard')"; st.execute(sql1); System.out.println("Records inserted successfully...!"); //let print table records String sql2 = "SELECT * FROM insect"; rs = st.executeQuery(sql2); System.out.println("Table records(before resetting auto-increment): "); while(rs.next()) { String name = rs.getString("name"); String date = rs.getString("date"); String origin = rs.getString("origin"); System.out.println("Name: " + name + ", Date: " + date + ", Origin: " + origin); } //lets reset auto increment using ALTER table statement... String reset = "ALTER TABLE INSECT AUTO_INCREMENT = 5"; st.execute(reset); System.out.println("Auto-increment reset successsfully...!"); //lets insert some more records.. String sql3 = "INSERT INTO insect (name,date,origin) VALUES ('spider', '2000-12-12', 'bathroom'), ('larva', '2012-01-10', 'garden')"; st.execute(sql3); System.out.println("Records inserted successfully..!"); String sql4 = "SELECT * FROM insect"; rs = st.executeQuery(sql4); System.out.println("Table records(after resetting auto-increment): "); while(rs.next()) { String name = rs.getString("name"); String date = rs.getString("date"); String origin = rs.getString("origin"); System.out.println("Name: " + name + ", Date: " + date + ", Origin: " + origin); } }catch(Exception e) { e.printStackTrace(); } } }

输出

获得的输出如下所示:

Table insect created successfully....!
Records inserted successfully...!
Table records(before resetting auto-increment): 
Name: housefly, Date: 2001-09-10, Origin: kitchen
Name: millipede, Date: 2001-09-10, Origin: driveway
Name: grasshopper, Date: 2001-09-10, Origin: front yard
Auto-increment reset successsfully...!
Records inserted successfully..!
Table records(after resetting auto-increment): 
Name: housefly, Date: 2001-09-10, Origin: kitchen
Name: millipede, Date: 2001-09-10, Origin: driveway
Name: grasshopper, Date: 2001-09-10, Origin: front yard
Name: spider, Date: 2000-12-12, Origin: bathroom
Name: larva, Date: 2012-01-10, Origin: garden     
import mysql.connector # Establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) # Creating a cursor object cursorObj = connection.cursor() # Creating the 'insect' table create_table_query = ''' CREATE TABLE insect ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL, origin VARCHAR(30) NOT NULL ); ''' cursorObj.execute(create_table_query) print("Table 'insect' is created successfully!") # Inserting records into the 'insect' table insert_query = "INSERT INTO insect (Name, Date, Origin) VALUES (%s, %s, %s);" values = [ ('housefly', '2001-09-10', 'kitchen'), ('millipede', '2001-09-10', 'driveway'), ('grasshopper', '2001-09-10', 'front yard') ] cursorObj.executemany(insert_query, values) print("Values inserted successfully!") # Displaying the contents of the 'insect' table display_table_query = "SELECT * FROM insect;" cursorObj.execute(display_table_query) results = cursorObj.fetchall() print("\ninsect Table:") for result in results: print(result) # Resetting the auto-increment value of the 'id' column reset_auto_inc_query = "ALTER TABLE insect AUTO_INCREMENT = 5;" cursorObj.execute(reset_auto_inc_query) print("Auto-increment value reset successfully!") # Inserting additional records into the 'insect' table insert_query = "INSERT INTO insect (name, date, origin) VALUES ('spider', '2000-12-12', 'bathroom');" cursorObj.execute(insert_query) print("Value inserted successfully!") insert_again_query = "INSERT INTO insect (name, date, origin) VALUES ('larva', '2012-01-10', 'garden');" cursorObj.execute(insert_again_query) print("Value inserted successfully!") # Displaying the updated contents of the 'insect' table display_table_query = "SELECT * FROM insect;" cursorObj.execute(display_table_query) results = cursorObj.fetchall() print("\ninsect Table:") for result in results: print(result) # Closing the cursor and connection cursorObj.close() connection.close()

输出

获得的输出如下所示:

Table 'insect' is created successfully!
Values inserted successfully!

insect Table:
(1, 'housefly', datetime.date(2001, 9, 10), 'kitchen')
(2, 'millipede', datetime.date(2001, 9, 10), 'driveway')
(3, 'grasshopper', datetime.date(2001, 9, 10), 'front yard')
Auto-increment value reset successfully!
Value inserted successfully!
Value inserted successfully!

insect Table:
(1, 'housefly', datetime.date(2001, 9, 10), 'kitchen')
(2, 'millipede', datetime.date(2001, 9, 10), 'driveway')
(3, 'grasshopper', datetime.date(2001, 9, 10), 'front yard')
(5, 'spider', datetime.date(2000, 12, 12), 'bathroom')
(6, 'larva', datetime.date(2012, 1, 10), 'garden')
广告