MySQL的自动增量错误 [英] mysql auto increment error

问题描述

我有一个简单的

INSERT INTO t1 (fields...) (SELECT fields... FROM t2);

它会插入约8.000行，并且表的自动增量值设置为〜16.000，但不完全是表的2倍.我没有在查询中包含自动递增的ID，并且这些表被截断了.可能是个错误吗?我有版本5.5.24. 为什么会这样，我该如何避免呢?

it inserts around 8.000 rows and the auto increment value of the table is set to ~16.000, but it's not exactly 2x as much. I don't include the auto incremented id in the query, and the tables are truncated. Can it be a bug? I have version 5.5.24. Why is that, and how can I avoid this?

CREATE TABLE `order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `` int(4) NOT NULL COMMENT '',
  `` int(6) NOT NULL COMMENT '',
  `` varchar(255) NOT NULL COMMENT '',
  `` varchar(255) NOT NULL COMMENT '',
  `` bigint(10) unsigned NOT NULL COMMENT '',
  `` int(5) unsigned NOT NULL COMMENT '',
  `` int(5) unsigned NOT NULL COMMENT '',
  `` int(8) unsigned NOT NULL COMMENT '',
  `` varchar(255) DEFAULT NULL COMMENT '',
  `` int(3) DEFAULT NULL COMMENT '',
  `` int(3) DEFAULT NULL COMMENT '',
  `` date NOT NULL COMMENT '',
  `` date DEFAULT NULL,
  `` date DEFAULT NULL COMMENT '',
  `` int(5) DEFAULT NULL COMMENT '',
  `` varchar(2) DEFAULT NULL COMMENT '',
  `` int(5) DEFAULT NULL COMMENT '',
  `` varchar(255) DEFAULT NULL,
  `` varchar(255) DEFAULT NULL,
  `` char(1) DEFAULT NULL,
  `` datetime DEFAULT NULL,
  `` int(10) unsigned DEFAULT NULL,
  `` datetime DEFAULT NULL,
  `` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `` (``),
  KEY `` (``),
  KEY `` (``),
  CONSTRAINT `` FOREIGN KEY (``) REFERENCES `users` (`id`),
  CONSTRAINT `` FOREIGN KEY (``) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8440 DEFAULT CHARSET=utf8

谢谢.

推荐答案

这可能是由于服务器的配置(例如/etc/my.cnf)引起的: 您可以设置auto_increment_increment和auto_increment_offset.

This could be caused by your server's configuration (/etc/my.cnf for example): You can set the auto_increment_increment and the auto_increment_offset.

如果第一个设置为x，则下一个ID将为(last_id + x). 如果第二个ID设置为y，则始终以y开头(然后每次添加x).

If the first is set to x, you will have (last_id + x) for your next id. If the second id is set to y, you start always with y (and then add xeach time).

更有可能:您刚刚删除了表中的所有行，然后再次插入.这样，auto_increment值不会再次设置为1.您必须TRUNCATE tablename再次重置此计数器(如果您确实要删除所有行).

More probably: You just deleted all rows in your table and inserted again. This way the auto_increment value is not set to 1 again. You have to TRUNCATE tablename to reset this counter again (if you really want to delete all rows).

刚刚看到了您的CREATE TABLE声明.如果您确实在选项中使用AUTO_INCREMENT=8440创建了表，那么插入8000行之后，您的ID当然会为16000.如果这只是插入语句的SHOW CREATE TABLE 之后，我仍然不知道发生了什么.

Just saw your CREATE TABLE statement. If you really created your table with AUTO_INCREMENT=8440 in the options, than of course, after inserting 8000 rows you will have an ID at 16000. If this is just a SHOW CREATE TABLE after the insert statement, I still don't know what's happening.

这篇关于MySQL的自动增量错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！