On Mon, 1999-10-04 00:03:51 -0400, Chris W. wrote:
> Hello, I have a little problem, I created a database and I have
> auto_increment on, after a while I removed a lot of entries from the
> database and optimized it to get rid of the empty spaces left after
> the deleted records, but now my question is: Is there a way to do
> some reindexing on the table to get the auto_increment sorted out?
> Like to get the first column in order sorted 1 then next record 2
> and so on until it reaches the end of last record so it is fairly
> sorted out? Thank you.
The maybe best method is to do it with a small program, because I
don't know an easy way of doing such a renumbering within MySQL.
Example Bash shell script:
echo "SELECT id FROM yourtable ORDER BY id" |
mysql -s test |
while read old
printf "UPDATE yourtable SET id=%s WHERE id=%s;\n" $new $old
and then feed the output to mysql again.
But is it wise to change an identificator field? In normal database
design an id is to identificate a row, and therefore should remain the
same for the life-time of this row.
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826 E4EC 8058 7B31 3AD7