如何重新设置InnoDB引擎数据库的“自增”列？

Question

我正在为一个表使用一个“人工主键”。该表有两列，一列是“主键”，另一列是“Dates”（数据类型：日期）列。当我尝试从文件中批量加载数据时（该文件仅包含第二列的值），日期的“YYYY”部分被添加到主键列（该列是表中的第一列），其余部分被截断。

因此，我需要重置该表。我尝试使用“Truncate table”语句进行重置，但由于“该表被另一个表的外键约束引用”，所以它失败了并出现了错误。因此，我不得不使用“delete * from table;”语句进行重置。我确实删除了所有记录，但当我再次插入记录时（这次使用insert into语句），它从我之前插入的年份后一年开始递增ID（即“它没有刷新”）。

注意：我正在使用MySQL 5.5和InnoDB引擎。

我的努力：

• I tried ALTER TABLE table1 AUTO_INCREMENT=0; (Reference Second Answer) ---> IT DID NOT HELP.

• I tried ALTER TABLE table1 DROP column; (Reference- answer 1) ---> Error on rename of table1

• Deleted the table again and tried to do:

  DBCC CHECKIDENT('table1', RESEED, 0);
  

  (Reference) ---> Syntax error at "DDBC" - Unexpected INDENT_QUOTED (This statement is right after the delete table statement, if that matters)

• In this article, under the section named "Auto Increment Columns for INNODB Tables" and the heading "Update 17 Feb 2009:", it says that in InnoDB truncate does reset the AUTO_INCREMENT index in versions higher than MySQL 4.1... So I want some way to truncate my table, or do something else to reset the AUTO_INCREMENT index.

问题：

当我删除表中的数据时，有没有一种方法可以重置auto_increment？ 我需要一种解决DDBC CHECKINDENT错误的方法，或者以某种方式截断被另一个表外键约束引用的表。

- Solace

---

不是你的问题，但你可以将(dates)替换为(id, dates)对。这样，你就可以手动分配ID值了。 - Micha Wiedenmann

禁用外键可以让你运行 truncate 吗？（然后当然在之后重新启用它们） - user330315

@MichaWiedenmann谢谢你这么说，因为我有一个问题：我在ID上有一个自增索引，我想知道当我手动插入所有值的时候，自动递增的意义是什么？当使用Load DATA INFILE导入数据时，自动增长索引列是否会失效？如果您能为我解答这个问题，我将不胜感激。 - Solace

@Zarah auto_increment 只是方便之举。想象一下带有 default 选项的列。您可以省略此列的值，而默认值将被使用。auto_increment 列类似地起作用，只是使用递增值。(auto_increment 不是“智能”的，因为它不会搜索第一个空闲值，而是使用 1 + $theLastValueUsedByAutoIncrement（如果我没有弄错的话）)。 - Micha Wiedenmann

@a_horse_with_no_name 非常感谢，帮了大忙！ - Solace

Answer 1

请按照以下步骤操作：

第一步：禁用外键约束后截断表，然后再启用-

set foreign_key_checks=0;
truncate table mytable;
set foreign_key_checks=1;

步骤2：现在，在批量上传时，只选择csv文件中存在的表格列，即取消勾选其余列（包括自动id），并确保csv文件中的列与表格中的列顺序相同。同时，自动id列不应出现在您的csv文件中。

---

您可以使用以下命令上传数据。

LOAD DATA LOCAL INFILE '/root/myfile.csv' INTO TABLE mytable fields terminated by ',' enclosed by '"' lines terminated by '\n' (field2,field3,field5);

注意：如果你是在Windows环境下工作，请相应地进行更改。

- Zafar Malik

---

你确认这个操作能够重置自增计数器吗？ - Micha Wiedenmann

截断总是会重置计数器..你的问题是如何截断表格..所以你可以这样做。 - Zafar Malik

@ZafarMalik 非常感谢！我这样做了，问题得到了解决。关于批量加载时如何检查和取消勾选列，我需要更多的信息。在批量加载时如何选择（或取消选择）列？有命令吗？ - Solace

@MichaWiedenmann 是的，正是这个解决了我的问题。它最初是由 a_horse_with_no_name 在问题的评论中建议的。 - Solace

@Zarah 如果你正在使用任何GUI工具，比如sqlyog，那么在上传数据时你可以简单地勾选和取消勾选框来完成它。如果你想通过命令来完成，则可以使用我回答中的命令。 - Zafar Malik

Answer 2

您只能将自动增量值重置为1（而不是0）。因此，除非我错了，您要找的是：

alter table a auto_increment = 1;

你可以使用以下方法查询下一个已使用的自增值：

select auto_increment from information_schema.tables where 
  table_name='a' and table_schema=schema();

不要忘记用你的表的实际名称替换'a'。

你可以在一个测试数据库中进行试验（很可能你的MySQL安装已经有一个名为test的数据库，否则使用create database test;创建它）。

use test;
create table a (id int primary key auto_increment, x int); -- auto_increment = 1
insert into a (x) values (1), (42), (43), (12);            -- auto_increment = 5
delete from a where id > 1;                                -- auto_increment = 5
alter table a auto_increment = 2;                          -- auto_increment = 2  
delete from a;
alter table a auto_increment = 1;                          -- auto_increment = 1  

- Micha Wiedenmann

---

我曾尝试过这个语句，但是将auto_increment设置为了0。 - Solace