At 15:13 +0000 12/19/02, Jeff Snoxell wrote:
>At 09:46 19/12/02 -0500, you wrote:
>>Jeff Snoxell wrote:
>>
>>>Nope. That doesn't do it either!
>>>
>>>I go:
>>>
>>>TRUNCATE TABLE my_table
>>
>>
>>Are you using InnoDB tables? You'll have to do something akin to
>>ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to
>>Paul ... :)
>
>No, I'm using MyISAM I believe.
Can you check for sure. With MyISAM, TRUNCATE TABLE should always reset
the counter, I believe. Here's an example:
mysql> CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE t;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) NOT NULL auto_increment,
PRIMARY KEY (`i`)
) TYPE=MyISAM |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
mysql> TRUNCATE TABLE t;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
With InnoDB, what I observe is that you have to issue an ALTER TABLE
after truncating the table to force the counter back to 1:
mysql> CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY)
TYPE = INNODB;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE t;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) NOT NULL auto_increment,
PRIMARY KEY (`i`)
) TYPE=InnoDB |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
mysql> TRUNCATE TABLE t;
Query OK, 2 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t;
+---+
| i |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
mysql> TRUNCATE TABLE t;
Query OK, 1 row affected (0.00 sec)
mysql> ALTER TABLE t AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
>
>Jeff
>------------------------
>MySQL, query, SQL
>
