The TRUNCATE statement is prepared to delete all the data contained in the table. Use the following format.
TRUNCATE TABLE tbl_name;
Deletes all data contained in the specified table.
You can do the same with “DELETE FROM tbl_name” if you want to delete all data.
DELETE FROM tbl_name;
The deifferenct between the two is that “DELETE FROM tbl_name” deltes data one by one, while “TRUNCATE TABLE tbl_name” once creates “the same table” after “DROP TABLE tbl_name” is exected. It is often faster to delete and recreate the table itself rather than delete the data one by one.
If the table is of InnoDB type and is a parent table of external reference, executing “TRANCATE TABLE tbl_name” will delete data using “DELETE FROM tbl_name”. In this case, the ON DELETE clause is called in the child table. In the case of MyISAM type, the ON DELETE clause of the child table is not called because the data is deleted using delete table.
Let’s try sample script.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| items |
| news |
+—————-+
2 rows in set (0.06 sec)
mysql> create table user(
-> id int auto_increment,
-> name varchar(20),
-> index(id));
Query OK, 0 rows affected (0.46 sec)
mysql> inser into user(name) values(‘flame’),(‘grilled’),(‘beef’),(‘crown’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘inser into user(name) values(‘flame’),(‘grilled’),(‘beef’),(‘crown’)’ at line 1
mysql> insert into user(name) values(‘flame’),(‘grilled’),(‘beef’),(‘crown’);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from user;
+—-+———+
| id | name |
+—-+———+
| 1 | flame |
| 2 | grilled |
| 3 | beef |
| 4 | crown |
+—-+———+
4 rows in set (0.00 sec)
mysql> truncate table user;
Query OK, 0 rows affected (0.17 sec)
mysql> insert into user(name) values(‘standard’);
Query OK, 1 row affected (0.06 sec)
mysql> select * from user;
+—-+———-+
| id | name |
+—-+———-+
| 1 | standard |
+—-+———-+
1 row in set (0.00 sec)
ほう、
mysql> drop table user;
Query OK, 0 rows affected (0.11 sec)
mysql> create table user(
-> id int auto_increment,
-> name varchar(20),
-> index(id));
Query OK, 0 rows affected (0.14 sec)
mysql> insert into user(name) values(‘flame’),(‘grilled’),(‘beef’),(‘crown’);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> delete from user;
Query OK, 4 rows affected (0.00 sec)
mysql> inser into user(name) values(‘standard’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘inser into user(name) values(‘standard’)’ at line 1
mysql> insert into user(name) values(‘standard’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+—-+———-+
| id | name |
+—-+———-+
| 5 | standard |
+—-+———-+
1 row in set (0.00 sec)
おおおおおおおおおおおおおおおおおおおお、すげえええええええええええええええええええええええええええええええ
