There can be many reasons to delete all records in a MySQL Database table and there are two ways you can acheive this using the “truncate” command or by using the “delete” command. While the ultimate net result is the same, there are differences you should know.
Key differences are
1. “truncate” uses DDL and is fast while “delete” doesn’t and hence slower (depending on the number of records).
2. “truncate” resets the auto increment counter while “delete” doesn’t.
Let’s use this test table with just 2 columns uID and username. uID is set to auto_increment.
mysql> SELECT * FROM users; +-----+----------+ | uID | username | +-----+----------+ | 1 | Joe | | 2 | Foo | | 3 | Alan | | 4 | Martin | +-----+----------+ 4 rows in set (0.00 sec)
WARNING: Make sure you know what you are doing. You are doing this at your risk!!!
1. Delete all records in a database table using “truncate”
mysql> TRUNCATE users; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM users; Empty set (0.00 sec)
Now, lets insert records and see where the uID value starts
mysql> INSERT INTO users (username) VALUES ('Joe'),('Foo'),('Alan'),('Martin');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM users;
+-----+----------+
| uID | username |
+-----+----------+
| 1 | Joe |
| 2 | Foo |
| 3 | Alan |
| 4 | Martin |
+-----+----------+
4 rows in set (0.00 sec)
You can see the uID starts from “1” again.
2. Delete all records in a database table using “delete”
mysql> DELETE FROM users; Query OK, 4 rows affected (0.00 sec) mysql> SELECT * FROM users; Empty set (0.00 sec)
Now, lets insert records and see where the uID value starts
mysql> INSERT INTO users (username) VALUES ('Joe'),('Foo'),('Alan'),('Martin');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM users;
+-----+----------+
| uID | username |
+-----+----------+
| 5 | Joe |
| 6 | Foo |
| 7 | Alan |
| 8 | Martin |
+-----+----------+
4 rows in set (0.00 sec)
There you can see the uID column is auto_increment is not reset and continues from the next value of “5”.