mysql reset表_如何使用另一個表的最大值重置MySQL自動增量?

I know this won't work, tried it in various forms and failed all times. What is the simplest way to achieve the following result?

我知道這行不通，我嘗試過各種形式，但都失敗了。達到以下結果的最簡單方法是什么?

ALTER TABLE XYZ AUTO_INCREMENT = (select max(ID) from ABC);

This is great for automation projects. Thank you!

這對於自動化項目非常有用。謝謝你！

SELECT @max := (max(ID)+1) from ABC; -> This works!

select ID from ABC where ID = (@max-1); -> This works!

ALTER TABLE XYZ AUTO_INCREMENT = (@max+1); -> This fails :( Why?

9 个解决方案

#1

30

用一份事先准備好的聲明中:

SELECT @max := MAX(ID)+ 1 FROM ABC;

PREPARE stmt FROM 'ALTER TABLE ABC AUTO_INCREMENT = ?';

EXECUTE stmt USING @max;

DEALLOCATE PREPARE stmt;

#2

8

following the mysql docs, this worked for me in mysql 5.7:

在mysql文檔之后，這在mysql 5.7中適用:

SET @m = (SELECT MAX(id) + 1 FROM ABC);

SET @s = CONCAT('ALTER TABLE XYZ AUTO_INCREMENT=', @m);

PREPARE stmt1 FROM @s;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

#3

7

Who is having problem with PREPARE stmt FROM 'ALTER TABLE XYZ AUTO_INCREMENT = ?' can use

從“ALTER TABLE XYZ AUTO_INCREMENT = ?”中准備stmt有什么問題?

CREATE PROCEDURE reset_xyz_autoincrement

BEGIN

SELECT @max := MAX(ID)+ 1 FROM ABC;

set @alter_statement = concat('ALTER TABLE temp_job_version AUTO_INCREMENT = ', @max);

PREPARE stmt FROM @alter_statement;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END

#4

6

I'm creating an automated database transformation script for a new version of my application.

我正在為我的應用程序的新版本創建一個自動的數據庫轉換腳本。

In one table, I needed to change the primary auto-increment field to a different field. Since this page came up first many times while I googled a solution for it, here's a solution that eventually worked for me:

在一個表中，我需要將主自動增量字段更改為另一個字段。自從我在google上搜索了一個解決方案后，這個頁面出現了很多次，這里有一個最終為我工作的解決方案:

-- Build a new ID field from entry_id, make it primary and fix the auto_increment for it:

ALTER TABLE `entries` ADD `id` INT UNSIGNED NOT NULL FIRST;

UPDATE entries SET id = entry_id;

ALTER TABLE `entries` ADD PRIMARY KEY ( `id` );

-- ...the tricky part of it:

select @ai := (select max(entry_id)+1 from entries);

set @qry = concat('alter table entries auto_increment=',@ai);

prepare stmt from @qry; execute stmt;

-- ...And now it's possible to switch on the auto_increment:

ALTER TABLE `entries` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;

#5

3

Reset Auto Increment IDs.

重置自動增量id。

http://community.spiceworks.com/scripts/show/3042-reset-auto-increment-ids

update all auto increment columns in a database to the smallest possible value based on current values in the databases. We needed to do this after cleaning out a database.

根據數據庫中的當前值，將數據庫中的所有自動增量列更新為最小的可能值。在清理完數據庫之后，我們需要這樣做。

Use a Prepared Statement within a Stored Procedure:

在存儲過程中使用准備好的語句:

drop PROCEDURE if exists reset_autoincrement;

DELIMITER //

CREATE PROCEDURE reset_autoincrement (IN schemaName varchar(255))

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE o_name VARCHAR(255);

DECLARE o_table VARCHAR(255);

DECLARE cur1 CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.`COLUMNS` WHERE extra LIKE '%auto_increment%' and table_schema=schemaName;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

read_loop: LOOP

FETCH cur1 INTO o_name, o_table;

IF done THEN

LEAVE read_loop;

END IF;

set @qry1 = concat('SELECT MAX(`',o_name,'`) + 1 as autoincrement FROM `',o_table,'` INTO @ai');

PREPARE stmt1 FROM @qry1;

EXECUTE stmt1;

IF @ai IS NOT NULL THEN

SELECT o_name, o_table;

select @qry1;

select @ai;

set @qry2 = concat('ALTER TABLE `',o_table,'` AUTO_INCREMENT = ', @ai);

select @qry2;

PREPARE stmt2 FROM @qry2;

EXECUTE stmt2;

END IF;

END LOOP;

CLOSE cur1;

END //

DELIMITER ;

call reset_autoincrement('my_schema_name');

#6

2

Ok guys. I have come up with a not so intuitive solution. The best part is that it works!

好男人。我想出了一個不那么直觀的解決辦法。最棒的是，它確實有效!

SELECT @max := max(ID) from ABC;

ALTER TABLE XYZ AUTO_INCREMENT = 1;

ALTER TABLE XYZ ADD column ID INTEGER primary key auto_increment;

UPDATE XYZ SET ContactID = (ContactID + @max);

#7

1

Personally I'd probably use either a shell script or a little C#/C++ application or PHP/Ruby/Perl script to do this in 2 queries:

就我個人而言，我可能會使用shell腳本或一個小的c# / c++應用程序或PHP/Ruby/Perl腳本來完成這兩個查詢:

Grab the value you want SELECT MAX(ID) FROM ABC;

獲取您想從ABC中選擇MAX(ID)的值;

Alter the table using the value ALTER TABLE XYZ AUTO_INCREMENT =

使用值Alter table XYZ AUTO_INCREMENT =

中檢索到的值來修改表

Obviously being careful that the new auto increment won't cause any key clashes with existing data in the XYZ table.

顯然要小心，新的自動增量不會導致與XYZ表中的現有數據發生任何關鍵沖突。

#8

0

If you really want to do this in MySQL alone, you can just dump the dynamically built alter command to a file on disk and then execute it.

如果您真的想在MySQL中這樣做，您可以將動態構建的alter命令轉儲到磁盤上的文件，然后執行它。

Like so:

像這樣:

select concat('ALTER TABLE XYZ AUTO_INCREMENT = ',max(ID)+1,';') as alter_stmt

into outfile '/tmp/alter_xyz_auto_increment.sql'

from ABC;

\. /tmp/alter_xyz_auto_increment.sql

#9

-2

I am only an amateur, but I have a tip :-) If you fills your entire table with new data, you can just use before filling:

我只是一個業余愛好者，但是我有一個提示:-)如果你用新數據填充整個表格，你可以在填寫之前使用:

mysql_query( "TRUNCATE TABLE `your_table`" ) or die( mysql_error());

I mean, it also resets auto_increment (and erases all content; Yes, table will stay).

我的意思是，它還重置auto_increment(並刪除所有內容;是的,表將保持)。

You can backup form your data, the after truncate insert into this without id.

您可以備份您的數據，在沒有id的情況下，將after truncatetable insert插入到其中。