Xiaobo Chen wrote:
Hi, allI follow the example: CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich'); SELECT * FROM animals; The thing is that after delete one of them like this: delete from animals where id=4; how can I have the 'id' to be continuous. Now it will be like: 1 2 3 5 6 Or shall I create the table differently at first? If so, how? Thanks for your help in advance. Xiaobo
The best answer is that you should not change id values. The id is the key used to find a particular value. It should never change. Think of it like this: If a house down the street from you were demolished, should your street address change to avoid gaps in street numbers? Imagine the headaches that would cause.
Now, if you need numbered lines of output without gaps in the line-numbering sequence, you can handle that in your app, or you can do it in mysql like this:
mysql> SET @i = 0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @i:= @i + 1 AS row, animals.* FROM animals; +------+----+---------+ | row | id | name | +------+----+---------+ | 1 | 1 | dog | | 2 | 2 | cat | | 3 | 3 | penguin | | 4 | 5 | whale | | 5 | 6 | ostrich | +------+----+---------+ 5 rows in set (0.00 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
