Mysql 简明教程
MySQL - Reset Auto-Increment
MySQL 中的大多数表使用顺序值来表示记录，就像序列号。MySQL 使用“AUTO_INCREMENT”自动处理此问题，而不是逐个手动插入每个值。
Most of the tables in MySQL use sequential values to represent records, like serial numbers. Instead of manually inserting each value one by one, MySQL uses the "AUTO_INCREMENT" to handle this automatically.
AUTO-INCREMENT in MySQL
MySQL 中的 AUTO_INCREMENT 用于在向表中添加新记录时自动按升序生成唯一数字。对于需要每行都有一个不同值的应用程序非常有用。
AUTO_INCREMENT in MySQL is used to generate unique numbers in ascending order automatically as you add new records to a table. It is very useful for applications that require each row to have a distinct value.
将列定义为 AUTO_INCREMENT 列时，MySQL 会处理其余部分。它从值 1 开始，并且为插入的每个新记录递增 1，为表创建唯一数字序列。
When you define a column as an AUTO_INCREMENT column, MySQL takes care of the rest. It starts with the value 1 and increments it by 1 for each new record you insert, creating a sequence of unique numbers for your table.
Example
以下示例演示了在数据库表中的列上使用 AUTO_INCREMENT。在这里，我们正在创建一个名为“insect”的表，并对“id”列应用 AUTO_INCREMENT。
The following example demonstrates the usage of AUTO_INCREMENT on a column in database table. Here, we are creating a table named 'insect' with AUTO_INCREMENT applied to the 'id' column.
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。要插入表中其他列的值，请使用以下查询：
Now, you don’t need to manually specify values for the 'id' column when inserting records. Instead, MySQL handles it for you, starting with 1 and incrementing by 1 for each new record. To insert values in other columns of the table, use the following query −
INSERT INTO insect (name,date,origin) VALUES
('housefly','2001-09-10','kitchen'),
('millipede','2001-09-10','driveway'),
('grasshopper','2001-09-10','front yard');显示的虫子表如下。在这里，我们可以看到“id”列的值是 MySQL 自动生成的：
The insect table displayed is as follows. Here, we can see that the 'id' column values are automatically generated by MySQL −
The MySQL RESET Auto-Increment
表上的默认 AUTO_INCREMENT 值从 1 开始，即插入的值通常从 1 开始。但是，MySQL 还提供了将这些 AUTO-INCREMENT 值重置为另一个数字的规定，使序列可以从指定的重置值开始插入。
The default AUTO_INCREMENT values on a table start from 1, i.e., the values being inserted usually start from 1. However, MySQL also has a provision to reset these AUTO-INCREMENT values to another number, enabling the sequence to start inserting from the specified reset value.
您可以使用三种方式重置 AUTO_INCREMENT 值：使用 ALTER TABLE、TRUNCATE TABLE 或删除并重新创建表。
You can reset the AUTO_INCREMENT value in three ways: using ALTER TABLE, TRUNCATE TABLE, or dropping and recreating the table.
RESET using ALTER TABLE Statement
MySQL 中的 ALTER TABLE 语句用于更新表或对其进行任何更改。因此，使用此语句重置 AUTO_INCREMENT 值是完全有效的选择。
The ALTER TABLE statement in MySQL is used to update a table or make any alterations in it. Hence, using this statement to reset an AUTO_INCREMENT value is perfectly valid choice.
Syntax
以下是使用 ALTER TABLE 重置自动增量的语法：
Following is the syntax to reset autoincrement using ALTER TABLE −
ALTER TABLE table_name AUTO_INCREMENT = new_value;Example
在此示例中，我们使用 ALTER TABLE 语句将 AUTO_INCREMENT 值重置为 5。请注意，新的 AUTO_INCREMENT 值必须大于表中已经存在的记录数：
In this example, we are using the ALTER TABLE statement to reset the AUTO_INCREMENT value to 5. Note that the new AUTO_INCREMENT value be greater than the number of records already present in the table −
ALTER TABLE insect AUTO_INCREMENT = 5;以下是所获得的输出 −
Following is the output obtained −
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0现在，让我们在上面创建的“insect”表中插入另一个值，并使用以下查询检查新的结果集：
Now, let us insert another value into the table 'insect' created above and check the new result-set, using the following queries −
INSERT INTO insect (name,date,origin) VALUES
('spider', '2000-12-12', 'bathroom'),
('larva', '2012-01-10', 'garden');我们得到的如下结果 −
We get the result as shown below −
Query OK, 2 row affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0要验证您插入的新记录是否从 AUTO_INCREMENT 值 5 开始，请使用以下 SELECT 查询：
To verify whether the new records you inserted will start with the AUTO_INCREMENT value set to 5, use the following SELECT query −
SELECT * FROM insect;获得的表如下所示：
The table obtained is as shown below −
RESET using TRUNCATE TABLE Statement
重置自动增量列为默认值的另一种方法是使用 TRUNCATE TABLE 命令。这将删除表中的现有数据，当您插入新记录时，AUTO_INCREMENT 列从头开始（通常为 1）。
Another way to reset auto-incrementing column to the default value is by using the TRUNCATE TABLE command. This will delete the existing data of a table, and when you insert new records, the AUTO_INCREMENT column starts from the beginning (usually 1).
Example
请遵循以下步骤将 AUTO_INCREMENT 值重置为默认值（即“0”）。为此，首先使用 TRUNCATE TABLE 命令截断上述创建的“insect”表，如下所示：
Following is an example to reset the AUTO_INCREMENT value to default, i.e. '0'. For that, firstly truncate the 'insect' table created above using the TRUNCATE TABLE Command as follows −
TRUNCATE TABLE insect;获得的输出如下 −
The output obtained is as follows −
Query OK, 0 rows affected (0.04 sec)要验证表中记录是否已删除，请使用以下 SELECT 查询：
To verify whether the records of the table is deleted, use the following SELECT query −
SELECT * FROM insect;产生的结果如下 −
The result produced is as follows −
Empty set (0.00 sec)现在，再次使用以下 INSERT 语句插入值。
Now, insert values again using the following INSERT statement.
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');执行上面的代码后，我们得到以下输出： -
After executing the above code, we get the following output −
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0您可以使用以下 SELECT 查询验证表中的记录是否已重置：
You can verify whether the records in the table have been reset using the following SELECT query −
SELECT * FROM insect;显示的表如下 −
The table displayed is as follows −