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 thatdelete
Not Limitedwhere
If the table is cleared under the condition, it will not be reset.auto_increment
The new value will increase with the previous value.truncate
Otherwise, 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,truncate
Is cleared, while delete is deleted row by row. In comparison,truncate
High 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
.trigger
Is 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,delete
Win.
4,truncate
Operations are not recorded in logs, whiledelete
It 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 table
Insteadwhere
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.