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 | 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 程序重置自动增量，我们需要使用 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)
示例
以下是程序 -
$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')
