Let's say that I'm just that anal retentive and the gaps in my autoincrement field bug me. Is there a MySQL command that I can run to re-index/re-number all the values in that column? I'd be happy to empty the table and reinsert all the values if I could figure out to get the export form MySQL w/o the key values.
Any ideas?
i've seen this type of question come up here numerous times before and here's the short answer: don't be that anal retentive, it's more trouble than it's worth. many people new to database design mistakenly think that the primary key field of a database is used to keep a running count of records...wrong. unique primary key fields are supposed to be just that...unique...meaning they are not re-used. it is perfectly normal to have gaps in the sequence. don't think of the data as being in a specific order, it's not, it's just a big collection of data. you can order it any way you like via the SELECT syntax.
this whole idea becomes even more complex when you are dealing with several related tables. if table 2 has a field that relates to the primary key field if table 1 and you re-order table 1 then all your relationships are screwed. you would have to update both tables, or more than just 2 depending on the complexity of your design.
i hope this will dissuade you...but, if you absolutely must...
i've never seen any RDBMS that supports a specific feature to automagically renumber fields like you are asking but i suppose you could write a script that spools the contents of the db into a CSV file without the primary keys and then reparse it back in (thereby letting mySQL do the auto numbering.)