On Fri, Jan 31, 2003 at 06:48:45AM -0800, Robert Mena wrote:
> Hi, I have been using autoincrement fields for some
> time but was wondering how does it work in some
> "special" situations.
>
> Ex. suppose I have an autoincrement field called num
> and the last one has value of 10.
>
> I delete the last on and insert a new one. Which
> value will it have ? 10 or 11
Hi Robert,
Why not test them on your own? :)
Anyhow...
11
> I read that if I issue a query delete * from table and
> delete from table I have diferent results (in regards
> to the auto increment field). Is this correct ?
For MyISAM tables, if you run DELETE FROM table, the AUTO_INCREMENT
field will be reset to default.
There is an exception: if you set AUTO_INCREMENT in a secondary column
in a multi-value primary key, then deleting the highest value in the
secondary column will allow that value to be reused in the
auto-increment sequence.
i.e.
CREATE TABLE user_log (
id SMALLINT UNSIGNED NOT NULL,
event MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (user_name, event)
);
mysql> INSERT user_log (id) VALUES (1),(1),(1);
Query OK, 3 rows affected (0.00 sec)
mysql> SELECT * FROM user_log;
+----+-------+
| id | event |
+----+-------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
+----+-------+
3 rows in set (0.00 sec)
mysql> DELETE FROM user_log WHERE id=1 AND event=3;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM user_log;
+----+-------+
| id | event |
+----+-------+
| 1 | 1 |
| 1 | 2 |
+----+-------+
2 rows in set (0.00 sec)
mysql> INSERT user_log (id) VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user_log;
+----+-------+
| id | event |
+----+-------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
+----+-------+
3 rows in set (0.00 sec)
Cheers!
--
Zak Greant <[EMAIL PROTECTED]> | MySQL Advocate | http://zak.fooassociates.com
Using and Managing MySQL
MySQL Training: Hamburg, March 24-28, 2003
Visit http://mysql.com/training for more information
"While we are postponing, life speeds by." --Lucius Annaeus Seneca
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
