MySQL Data Table clearing method instance and analysis, mysql DATA clearing instance

MySQL Data Table clearing method instance and analysis, mysql DATA clearing instance

In the MySQL database, if we want to clear a data table (delete all content in the data table), we can use the following two statements:

truncate table table_n;delete from table_n;

Instance

Let's first look at the process and results of clearing the database through the two methods through the instance.

# Delete demonstrate mysql> create table testforde (-> number int not null auto_increment,-> name varchar (20) not null,-> primary key (number)-> ); query OK, 0 rows affected (0.05 sec) # truncate demo mysql> create table testfortr (-> number int not null auto_increment,-> name varchar (20) not null, -> primary key (number)->); Query OK, 0 rows affected (0.04 sec)

The above are two data tables with the same structure. Next we will insert the data and then usedelete Andtruncate Operate on them and check the result

mysql> insert into testforde(name) values('zhagnsan'),('lisi'),('wanger'),('zhaoliu'),('xiaosan'),('qiqi'),('hanba');Query OK, 7 rows affected (0.04 sec)Records: 7 Duplicates: 0 Warnings: 0mysql> insert into testfortr(name) values('zhagnsan'),('lisi'),('wanger'),('zhaoliu'),('xiaosan'),('qiqi'),('hanba');Query OK, 7 rows affected (0.00 sec)Records: 7 Duplicates: 0 Warnings: 0mysql> select * from testforde;+--------+----------+| number | name  |+--------+----------+|  1 | zhagnsan ||  2 | lisi  ||  3 | wanger ||  4 | zhaoliu ||  5 | xiaosan ||  6 | qiqi  ||  7 | hanba |+--------+----------+7 rows in set (0.01 sec)mysql> select * from testfortr;+--------+----------+| number | name  |+--------+----------+|  1 | zhagnsan ||  2 | lisi  ||  3 | wanger ||  4 | zhaoliu ||  5 | xiaosan ||  6 | qiqi  ||  7 | hanba |+--------+----------+7 rows in set (0.00 sec)mysql> delete from testforde;Query OK, 7 rows affected (0.02 sec)mysql> truncate table testfortr;Query OK, 0 rows affected (0.02 sec)mysql> insert into testforde(name) values('zhagnsan'),('lisi'),('wanger'),('zhaoliu'),('xiaosan'),('qiqi'),('hanba');Query OK, 7 rows affected (0.00 sec)Records: 7 Duplicates: 0 Warnings: 0mysql> insert into testfortr(name) values('zhagnsan'),('lisi'),('wanger'),('zhaoliu'),('xiaosan'),('qiqi'),('hanba');Query OK, 7 rows affected (0.00 sec)Records: 7 Duplicates: 0 Warnings: 0mysql> select * from testforde;+--------+----------+| number | name  |+--------+----------+|  8 | zhagnsan ||  9 | lisi  ||  10 | wanger ||  11 | zhaoliu ||  12 | xiaosan ||  13 | qiqi  ||  14 | hanba |+--------+----------+7 rows in set (0.00 sec)mysql> select * from testfortr;+--------+----------+| number | name  |+--------+----------+|  1 | zhagnsan ||  2 | lisi  ||  3 | wanger ||  4 | zhaoliu ||  5 | xiaosan ||  6 | qiqi  ||  7 | hanba |+--------+----------+7 rows in set (0.00 sec)

The biggest difference between the above operations and results is thatdeleteNot LimitedwhereIf the table is cleared under the condition, it will not be reset.auto_incrementThe new value will increase with the previous value.truncateOtherwise, it will be directly reset to 1 (in fact, the operation here can be understood as directly deleting the table structure and content, and then recreating the table according to the table structure)

In addition to the obvious differences shown above, they also have the following differences:

1,truncateIs cleared, while delete is deleted row by row. In comparison,truncateHigh efficiency.

2. In terms of transaction processing,truncate It may not be so secure. Becausedelete The statement is the database operation language (dml), which is placed inrollback segement.triggerIs triggered during execution. Whiletruncate Is the database Definition Language (ddl), the operation takes effect immediately, the original data is not putrollback segment Cannot be rolled back, and the operation is not triggeredtrigger.

3,truncate Only empty tables can be used, whiledelete Can be used togetherwhere,order by,limit In terms of flexibility,deleteWin.

4,truncateOperations are not recorded in logs, whiledeleteIt is recorded.

5. It is precisely becausetruncate The operation is not recorded in the log, so it does not activate the trigger. Thereforeforeign The table referenced by the constraint cannot be used.truncate tableInsteadwhere Clausedelete Statement.

6,truncate The table cannot be used in the index view.

Summary

The above is all the content of the MySQL DATA table. I hope the content in this article will be helpful for your study or work. If you have any questions, please leave a message.