MySQL 更改自动增量起始编号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/970597/
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
Change auto increment starting number?
提问by John Jones
In MySQL, I have a table, and I want to set the auto_incrementvalue to 5instead of 1. Is this possible and what query statement does this?
在 MySQL 中，我有一个表，我想将auto_increment值设置为5而不是 1. 这是可能的，什么查询语句可以做到这一点？
回答by Daniel Vandersluis
You can use ALTER TABLEto change the auto_increment initial value:
您可以使用ALTER TABLE来更改 auto_increment 初始值：
ALTER TABLE tbl AUTO_INCREMENT = 5;
See the MySQL referencefor more details.
有关更多详细信息，请参阅MySQL 参考。
回答by Cosimo
Yes, you can use the ALTER TABLE t AUTO_INCREMENT = 42statement. However, you need to be aware that this will cause the rebuilding of your entire table, at least with InnoDB and certain MySQL versions. If you have an already existing dataset with millions of rows, it could take a very long time to complete.
是的，您可以使用该ALTER TABLE t AUTO_INCREMENT = 42语句。但是，您需要注意这将导致整个表的重建，至少对于 InnoDB 和某些 MySQL 版本。如果您已有包含数百万行的现有数据集，则可能需要很长时间才能完成。
In my experience, it's better to do the following:
根据我的经验，最好执行以下操作：
BEGIN WORK;
-- You may also need to add other mandatory columns and values
INSERT INTO t (id) VALUES (42);
ROLLBACK;
In this way, even if you're rolling backthe transaction, MySQL will keep the auto-increment value, and the change will be applied instantly.
这样，即使您正在回滚事务，MySQL 也会保留自动增量值，并且更改将立即应用。
You can verify this by issuing a SHOW CREATE TABLE tstatement. You should see:
您可以通过发布SHOW CREATE TABLE t声明来验证这一点。你应该看到：
> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
...
) ENGINE=InnoDB AUTO_INCREMENT=43 ...
回答by Eric Leschinski
How to auto increment by one, starting at 10 in MySQL:
如何自动递增 1，在 MySQL 中从 10 开始：
create table foobar(
id INT PRIMARY KEY AUTO_INCREMENT,
moobar VARCHAR(500)
);
ALTER TABLE foobar AUTO_INCREMENT=10;
INSERT INTO foobar(moobar) values ("abc");
INSERT INTO foobar(moobar) values ("def");
INSERT INTO foobar(moobar) values ("xyz");
select * from foobar;
'10', 'abc'
'11', 'def'
'12', 'xyz'
This auto increments the id column by one starting at 10.
这会自动将 id 列从 10 开始递增 1。
Auto increment in MySQL by 5, starting at 10:
在 MySQL 中自动递增 5，从 10 开始：
drop table foobar
create table foobar(
id INT PRIMARY KEY AUTO_INCREMENT,
moobar VARCHAR(500)
);
SET @@auto_increment_increment=5;
ALTER TABLE foobar AUTO_INCREMENT=10;
INSERT INTO foobar(moobar) values ("abc");
INSERT INTO foobar(moobar) values ("def");
INSERT INTO foobar(moobar) values ("xyz");
select * from foobar;
'11', 'abc'
'16', 'def'
'21', 'xyz'
This auto increments the id column by 5 each time, starting at 10.
这会自动将 id 列每次增加 5，从 10 开始。
回答by Zhitko Vladimir
Procedure to auto fix AUTO_INCREMENT value of table
自动修复表的 AUTO_INCREMENT 值的过程
DROP PROCEDURE IF EXISTS update_auto_increment;
DELIMITER //
CREATE PROCEDURE update_auto_increment (_table VARCHAR(64))
BEGIN
DECLARE _max_stmt VARCHAR(1024);
DECLARE _stmt VARCHAR(1024);
SET @inc := 0;
SET @MAX_SQL := CONCAT('SELECT IFNULL(MAX(`id`), 0) + 1 INTO @inc FROM ', _table);
PREPARE _max_stmt FROM @MAX_SQL;
EXECUTE _max_stmt;
DEALLOCATE PREPARE _max_stmt;
SET @SQL := CONCAT('ALTER TABLE ', _table, ' AUTO_INCREMENT = ', @inc);
PREPARE _stmt FROM @SQL;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;
END//
DELIMITER ;
CALL update_auto_increment('your_table_name')
回答by Thomas K.
If you need this procedure for variable fieldnames instead of idthis might be helpful:
如果您需要此过程来处理变量字段名而不是id这可能会有所帮助：
DROP PROCEDURE IF EXISTS update_auto_increment;
DELIMITER //
CREATE PROCEDURE update_auto_increment (_table VARCHAR(128), _fieldname VARCHAR(128))
BEGIN
DECLARE _max_stmt VARCHAR(1024);
DECLARE _stmt VARCHAR(1024);
SET @inc := 0;
SET @MAX_SQL := CONCAT('SELECT IFNULL(MAX(',_fieldname,'), 0) + 1 INTO @inc FROM ', _table);
PREPARE _max_stmt FROM @MAX_SQL;
EXECUTE _max_stmt;
DEALLOCATE PREPARE _max_stmt;
SET @SQL := CONCAT('ALTER TABLE ', _table, ' AUTO_INCREMENT = ', @inc);
PREPARE _stmt FROM @SQL;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;
END //
DELIMITER ;
CALL update_auto_increment('your_table_name', 'autoincrement_fieldname');
回答by North-Wind
You can also do it using phpmyadmin. Just select the table than go to actions. And change the Auto increment below table options. Don't forget to click on start
您也可以使用 phpmyadmin 来完成。只需选择表格而不是去行动。并更改表格下方的自动增量选项。别忘了点击开始
回答by user1911721
just export the table with data .. then copy its sql like
只需导出带有数据的表 .. 然后复制它的 sql 就像
CREATE TABLE IF NOT EXISTS `employees` (
`emp_badgenumber` int(20) NOT NULL AUTO_INCREMENT,
`emp_fullname` varchar(100) NOT NULL,
`emp_father_name` varchar(30) NOT NULL,
`emp_mobile` varchar(20) DEFAULT NULL,
`emp_cnic` varchar(20) DEFAULT NULL,
`emp_gender` varchar(10) NOT NULL,
`emp_is_deleted` tinyint(4) DEFAULT '0',
`emp_registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`emp_overtime_allowed` tinyint(4) DEFAULT '1',
PRIMARY KEY (`emp_badgenumber`),
UNIQUE KEY `bagdenumber` (`emp_badgenumber`),
KEY `emp_badgenumber` (`emp_badgenumber`),
KEY `emp_badgenumber_2` (`emp_badgenumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=111121326 ;
now change auto increment value and execute sql.
现在更改自动增量值并执行 sql。
