Quick tidy MySQL ID numbers in PHPMyAdmin

[Maljonic]

Is there an easy way to tidy up the ID numbers in a table? I just joined two tables together but there was a lot of duplicate names so I went through and deleted them, one at a time, but now there are gaps in the ID numbers, like 1,2,3,7,22,24,25,26,32 etc. It doesn't really affect the functionality but I'd like to reformalise the numbers so there's no gaps, just because I can (if I can).

 

[GReaper]

Rename table, create new table with same schema as the old one.

INSERT INTO newtable SELECT column,column,NULL,column FROM oldtable

List every column name, but use NULL for your primary key with auto_increment.

 

[JingleBells]

Rename table, create new table with same schema as the old one.

INSERT INTO newtable SELECT column,column,NULL,column FROM oldtable

List every column name, but use NULL for your primary key with auto_increment.

Would it not be easier to drop the column and then readd it with the auto_increment bit?

 

[GReaper]

Would it not be easier to drop the column and then readd it with the auto_increment bit?

That's too easy and actually works.

Damn you!!!

 

[Maljonic]

Cool, I didn't know you could drop columns. I don't know why but my head starts to ache whenever I look at databases, which make it seem more complicated than it is I'm sure.

I've been thinking about doing a part-time/one day course on MySQL just to clear the mists in my mind, but I can't find anywhere around here that does one.

 

[Gahn]

Be aware doing that trick, if u got other records referrenced to those Ids u gonna lose consistency of your data.

 

[Maljonic]

Okay, my head is hurting again. I've tried looking up the correct syntax for adding a column into a table but I keep getting errors.

This is a bit of the sql file that creates the table in question:

CREATE TABLE `dk_users` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `bonusTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `username` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1597 ;

So I've deleted 'id', the easy bit, but how do I word the SQL query to put it back in again like you say?

P.S. Gahn I think the only thing the 'id' column is used for elsewhere is to calculate the number of members, and it just uses the highest number to get that, so if I fix this that will be more accurate too.

 

[JingleBells]

The following should work

ALTER TABLE dk_users ADD COLUMN id smallint(5) unsigned NOT NULL auto_increment

Also, and easier way to count the number of members would be:

SELECT COUNT(1) FROM dk_users

 

[Maljonic]

Thanks, I nearly had that right once as well, I think I typed CREATE instead of ADD.

Edit: I still says this error: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Do I need to write something else to make it the primary key at the same time?

 

[Maljonic]

Oh my God, I almost got something to work!

I did this:

ALTER TABLE dk_users ADD COLUMN id smallint(5) unsigned NOT NULL auto_increment,

ADD PRIMARY KEY  (`id`)

I just need to figure out how to make the column 'id' to come first instead of last, and I think I saw written somewhere FIRST regarding this?

Edit: Cool, just stuck FIRST on the end of the top line.