MySQL: AUTO_INCREMENT on existing tables.
Moderator: General Moderators
MySQL: AUTO_INCREMENT on existing tables.
I have an existing table. I just added a new field to this table called id. This new field is typed INT. How do I add AUTO_INCREMENT to this new field?
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MySQL: AUTO_INCREMENT on existing tables.
Are you in phpMyAdmin or sql command line?
Re: MySQL: AUTO_INCREMENT on existing tables.
I'm in phpMyAdmin, but doesn't phpMyAdmin have a command line in it anyway?jaoudestudios wrote:Are you in phpMyAdmin or sql command line?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: MySQL: AUTO_INCREMENT on existing tables.
phpMyAdmin has a selection for autoincrement when you create the column. You may be able to modify the column and add autoincrement. But you may need to delete and recreate it.
(#10850)
Re: MySQL: AUTO_INCREMENT on existing tables.
How? When I go to create a new column in a table AUTO_INCREMENT is nowhere to be found. I'm using Startfield phpMyAdmin.
Also, if I could do it with an SQL statement I could just run it in the command line thingy.
Also, if I could do it with an SQL statement I could just run it in the command line thingy.
Re: MySQL: AUTO_INCREMENT on existing tables.
Aha!
This works. Except it doesn't give the exact behavior I want. Every time I add a new row to this table the id auto increments, which is want I want it to do. When I delete and then add a new row that one that was deleted waste that number forever and no new row will every be able to replace it.
Will this cause an eventual situation where I have not the amount of rows as of an unsigned integer(4294967295). I hope you understand what I mean. If you don't then I will illustrate.
If I have 20 rows in my table, then each row will have a number from 1-20 in the id field respectively; the first row will have 1, the second will have 2, etc. If I delete row 11 and then add a new row, that new row will have the number 21 in the id field/column, rather then 11. You can see that I have still 20 rows (because I deleted one before I added a new one) but I've used 21 unique id numbers already. As I add and delete rows this will allocate till I've gotten to the id's data type limit of 4294967295 (unsigned integers are whole numbers from 0 to 4294967295).
To you see the problem that I am delineating? Is this really an issue or once the the id field has reached 4294967295 will it then start back at 0 and then from there find a number available?
EDIT: I found this. It is the exact issue I'm having. The solution is good, only if all the row's ids are contiguous.
So I've decided not to use a unique ID field in my table until I get this problem straighten out.
Code: Select all
ALTER TABLE `chart_room` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);Will this cause an eventual situation where I have not the amount of rows as of an unsigned integer(4294967295). I hope you understand what I mean. If you don't then I will illustrate.
If I have 20 rows in my table, then each row will have a number from 1-20 in the id field respectively; the first row will have 1, the second will have 2, etc. If I delete row 11 and then add a new row, that new row will have the number 21 in the id field/column, rather then 11. You can see that I have still 20 rows (because I deleted one before I added a new one) but I've used 21 unique id numbers already. As I add and delete rows this will allocate till I've gotten to the id's data type limit of 4294967295 (unsigned integers are whole numbers from 0 to 4294967295).
To you see the problem that I am delineating? Is this really an issue or once the the id field has reached 4294967295 will it then start back at 0 and then from there find a number available?
EDIT: I found this. It is the exact issue I'm having. The solution is good, only if all the row's ids are contiguous.
So I've decided not to use a unique ID field in my table until I get this problem straighten out.
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: MySQL: AUTO_INCREMENT on existing tables.
Such is the life of unsigned integers. I wouldn't worry about it: if you're terribly concerned- alter table chat modify id bigint; - because they are just numbers.JellyFish wrote:...When I delete and then add a new row that one that was deleted waste that number forever and no new row will every be able to replace it.
That's great expectations man. Worst case scenario, reset the next value to increment from: ALTER TABLE chat AUTO_INCREMENT=1[/quote]JellyFish wrote:Will this cause an eventual situation where I have not the amount of rows as of an unsigned integer(4294967295). I hope you understand what I mean. If you don't then I will illustrate.
All I can say is not having a unique ID field as a primary key would drive me insane. I think you'll find you can do exactly what you're looking to do and retain your current database schema.JellyFish wrote:So I've decided not to use a unique ID field in my table until I get this problem straighten out.
-Andy
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: MySQL: AUTO_INCREMENT on existing tables.
When you are showing the table, select a field for revision. One of the items will be a dropdown named "Extra." the selection in that dropdown is "auto-increment."
If you fear that it is you could go to a BIGINT and get 18446744073709551615 entries.
Making 1 entry per second continuously, 24/7/365, I think it will take about 136 years to reach that level, so I doubt it will become a problem in your lifetime.To you see the problem that I am delineating? Is this really an issue or once the the id field has reached 4294967295 will it then start back at 0 and then from there find a number available?
If you fear that it is you could go to a BIGINT and get 18446744073709551615 entries.