I know this won't work. I tried it in various forms and failed all times. What is the simplest way to achieve the following result? Show
This is great for automation projects.
asked Mar 9, 2010 at 16:37
Use a prepared statement:
answered Mar 9, 2010 at 19:13
OMG PoniesOMG Ponies 318k78 gold badges511 silver badges494 bronze badges 7 Following the MySQL documentation, this worked for me in MySQL 5.7:
answered Jan 4, 2017 at 14:57
SaeidSaeid 2,5945 gold badges19 silver badges20 bronze badges 1 Whoever is having a problem with PREPARE stmt FROM 'ALTER TABLE XYZ AUTO_INCREMENT = ?' can use:
answered Jul 12, 2013 at 14:24
AnuragAnurag 98010 silver badges30 bronze badges 2 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:
answered Jun 3, 2010 at 13:47
user357516user357516 911 silver badge1 bronze badge 0 Reset Auto Increment IDs. 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:
answered Feb 19, 2015 at 20:45
ArtistanArtistan 1,92221 silver badges33 bronze badges 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:
Obviously being careful that the new auto increment won't cause any key clashes with existing data in the answered Mar 9, 2010 at 16:52
Andy ShellamAndy Shellam 15.2k1 gold badge25 silver badges41 bronze badges 1 Ok guys. I have come up with a not so intuitive solution. The best part is that it works!
answered Mar 9, 2010 at 17:30
ThinkCodeThinkCode 7,56919 gold badges71 silver badges91 bronze badges 1 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. Like so:
answered Mar 9, 2010 at 17:36
Ike WalkerIke Walker 62.8k14 gold badges106 silver badges108 bronze badges 0 How do I reset my Autoincrement ID?In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
Can we change auto increment value in MySQL?In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
How can reset primary key ID after delete the row?So add one to that number and run the following command: ALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
What happens when auto increment reaches limit MySQL?When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails. That is why it is advised to use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value required by us.
|