重置 SQLite3/MySQL 中的行数计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3443630/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reset the row number count in SQLite3/MySQL
提问by Maddy
I am using SQLite3. I load a table with say 30 rows using integer as Primary ID and it auto-increments.
我正在使用 SQLite3。我使用整数作为主 ID 加载一个包含 30 行的表，它会自动递增。
Now I delete all the rows from the table and then, reload some new information onto the table.
现在我从表中删除所有行，然后将一些新信息重新加载到表中。
Problem is: the row count (my PrimaryID) now starts with 31. Is there any way that I can start loading new rows from the number 1 onwards?
问题是：行数（我的 PrimaryID）现在从 31 开始。有什么办法可以从数字 1 开始加载新行？
回答by OMG Ponies
SQLite
SQLite
Use:
用：
DELETE FROM your_table;
DELETE FROM sqlite_sequence WHERE name = 'your_table';
SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.
SQLite 使用特殊的 SQLITE_SEQUENCE 表跟踪一个表曾经拥有的最大 ROWID。每当创建包含 AUTOINCREMENT 列的普通表时，都会自动创建和初始化 SQLITE_SEQUENCE 表。可以使用普通的 UPDATE、INSERT 和 DELETE 语句修改 SQLITE_SEQUENCE 表的内容。但是对该表进行修改可能会干扰 AUTOINCREMENT 密钥生成算法。在进行此类更改之前，请确保您知道自己在做什么。
Found the answer on SO: SQLite Reset Primary Key Field
在 SO 上找到答案：SQLite Reset Primary Key Field
MySQL
MySQL
Use:
用：
ALTER TABLE tbl AUTO_INCREMENT = 1;
In either case, the database doesn't care if the id numbers are sequencial - only that the values are unique. If users never see the primary key value (they shouldn't, because the data can change & won't always be at that primary key value), I wouldn't bother with these options.
在任何一种情况下，数据库都不关心 id 号是否是顺序的——只关心这些值是唯一的。如果用户从来没有看到主键值（他们不应该看到，因为数据可能会改变并且不会总是在那个主键值上），我不会打扰这些选项。
回答by quantumSoup
For MySQL:
对于 MySQL：
Use TRUNCATE TABLE tablenameto empty the table (delete all records) and reset auto increment count.
使用TRUNCATE TABLE tablename空表（删除所有记录），并复位自动增量次数。
You can also use ALTER TABLE tablename AUTO_INCREMENT = 0;if you just want to reset the count.
ALTER TABLE tablename AUTO_INCREMENT = 0;如果您只想重置计数，也可以使用。
For SQLite:
对于 SQLite：
DELETE FROM tablename;
DELETE FROM SQLITE_SEQUENCE WHERE name='tablename';
References
参考
回答by williambarau
For SQLite use (not need to delete and create the table)
供 SQLite 使用（不需要删除和创建表）
UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='table_name';
For MySql use
对于 MySql 使用
ALTER TABLE table_name AUTO_INCREMENT = 1;
回答by Tom Ace
You should not use AUTOINCREMENTin this case. Simply define your primary key as INTEGER PRIMARY KEYand the count will be reset to 1 after a DELETE FROMquery. Without AUTOINCREMENT, the default behaviour will still be an automatic increment of the primary key as long as you don't run out of space in your table (in that case, old - deleted - values will be reused).
你不应该AUTOINCREMENT在这种情况下使用。只需将主键定义为INTEGER PRIMARY KEY，DELETE FROM查询后计数将重置为 1 。如果没有 AUTOINCREMENT，默认行为仍将是主键的自动增量，只要您的表中没有用完空间（在这种情况下，旧的 - 删除的 - 值将被重用）。
More information available in the SQLite Autoincrementdocument.
SQLite Autoincrement文档中提供了更多信息。
回答by websch01ar
ALTER TABLE tbl AUTO_INCREMENT = 0;
