MySQL - 重置自动增量

更新于 2025/6/18 10:37:17

MySQL 中的自动增量
MySQL 重置自动增量
使用 ALTER TABLE 语句重置
使用 TRUNCATE TABLE 语句重置
使用客户端程序重置自动增量

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	name	date	origin
1	housefly	2001-09-10	kitchen
2	millipede	2001-09-10	driveway
3	grasshopper	2001-09-10	front yard

MySQL 重置自动增量

表上的默认 AUTO_INCREMENT 值从 1 开始，也就是说，插入的值通常从 1 开始。但是，MySQL 也提供将这些 AUTO-INCREMENT 值重置为其他数字的功能，使序列能够从指定的重置值开始插入。

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

使用 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	name	date	origin
1	housefly	2001-09-10	kitchen
2	millipede	2001-09-10	driveway
3	grasshopper	2001-09-10	front yard
5	spider	2000-12-12	bathroom
6	larva	2012-01-10	garden

使用 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	name	date	origin
1	housefly	2001-09-10	kitchen
2	millipede	2001-09-10	driveway
3	grasshopper	2001-09-10	front yard
4	spider	2000-12-12	bathroom

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

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

语法

PHP NodeJS Java Python

要通过 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/Python 的 execute() 函数执行"ALTER TABLE"语句，如下所示 -

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

示例

以下是程序 -

PHP NodeJS Java Python

$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....!
");
}
//现在让我们插入一些记录
$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....!
");
}
//显示表记录
$sql = "SELECT * FROM INSECT";
if($result = $mysqli->query($sql)){
    printf("Table records: 
");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Date: %s, Origin: %s", $row['id'], $row['name'], $row['date'], $row['origin']);
        printf("
");
    }
}
//让我们使用 alter table 语句重置自动增量...
$sql = "ALTER TABLE INSECT AUTO_INCREMENT = 5";
if($mysqli->query($sql)){
    printf("Auto_increment reset successfully...!
");
}
//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): 
");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Date: %s, Origin: %s", $row['id'], $row['name'], $row['date'], $row['origin']);
        printf("
");
    }
}
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"
});

// 连接到 MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");
   // 创建新数据库
    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://localhost: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...!");
            //让打印表记录
            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);
            }
            //让我们使用 ALTER 表语句重置自动增量...
            String reset = "ALTER TABLE INSECT AUTO_INCREMENT = 5";
            st.execute(reset);
            System.out.println("Auto-increment reset successsfully...!");
            //让我们插入更多记录..
            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
# 建立连接 
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象 
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!")
# 将记录插入到"insect"表中
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!")
# 显示 'insect' 表的内容
display_table_query = "SELECT * FROM insect;"
cursorObj.execute(display_table_query)
results = cursorObj.fetchall()
print("
insect Table:")
for result in results:
    print(result)
# 重置 'id' 列的自动增量值
reset_auto_inc_query = "ALTER TABLE insect AUTO_INCREMENT = 5;"
cursorObj.execute(reset_auto_inc_query)
print("Auto-increment value reset successfully!")
# 向 'insect' 表中插入更多记录
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!")
# 显示"insect"表的更新内容
display_table_query = "SELECT * FROM insect;"
cursorObj.execute(display_table_query)
results = cursorObj.fetchall()
print("
insect Table:")
for result in results:
    print(result)
# 关闭游标和连接
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')

❮ MySQL 更改列类型 MySQL COALESCE 函数 ❯

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - 重置自动增量

MySQL 中的 AUTO_INCREMENT

示例

MySQL 重置自动增量

使用 ALTER TABLE 语句重置

语法

示例

使用 TRUNCATE TABLE 语句进行重置

示例

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

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！