author : gh-xiaohe
gh-xiaohe The blog of
If you think the blogger's article is good , I hope you'll make it three times in a row （ Focus on , give the thumbs-up , Comment on ）, Give me more support ！！

DCL in COMMIT and ROllBACK

DCL in COMMIT and ROllBACK

COMMIT

     COMMIT: Submit data . Once executed COMMIT, The data is permanently stored in the database , This means that data cannot be rolled back .

ROLLBACK

     ROLLBACK: Undo Data . Once executed ROLLBACK, Data rollback can be realized . Roll back to the most recent COMMIT after .

contrast

• The same thing ： All data in the table can be deleted , While preserving the table structure .
• Difference ：
  • TRUNCATE TABLE： Once you do this , Clear all table data . meanwhile , Data cannot be rolled back .
  • DELETE FROM： Once you do this , All table data can be cleared （ No WHERE）. meanwhile , Data can be rolled back .

DDL and DML Explanation

    ① DDL The operation of Once executed , Just Cannot roll back . Instructions SET autocommit = FALSE Yes DDL Operation failure .( Because at the end of execution DDL After the operation , A certain Will execute once COMMIT. And this COMMIT The operation is not affected by SET autocommit = FALSE Affected .)

    ② DML By default , Once executed , It's also Non rollback . however , If In execution DML Before , Yes SET autocommit = FALSE, Then the execution of DML Operation can realize rollback .

Case study

COMMIT、DELETE

#  demonstration ：DELETE FROM 
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
DELETE FROM myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

COMMIT、 ROLLBACK

#  demonstration ：TRUNCATE TABLE
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

MySQL8.0 New features ：DDL Atomization of

      stay MySQL 8.0 In the version ,InnoDB Tabular DDL Support transaction integrity , namely DDL The operation either succeeds or rolls back .DDL Operation rollback log write to data dictionary Data dictionary table mysql.innodb_ddl_log（ The table is a hidden table , adopt show tables Can't see ） in , For rollback operations . By setting parameters , Can be DDL Print and output operation log to MySQL In the error log .

Respectively in MySQL 5.7 Version and MySQL 8.0 Create databases and data tables in version , give the result as follows ：

CREATE DATABASE mytest; #  Create database  mytest

USE mytest; #  Switch to  mytest  Under database 

CREATE TABLE book1( #  establish  book1  surface 
book_id INT ,
book_name VARCHAR(255)
);

SHOW TABLES; #  View the table under the current data 

（1） stay MySQL 5.7 In the version , The test steps are as follows ：

#  stay  mysql5.7  Next 
DROP TABLE book1,book2;  #  The database is deleted  book1  and  book2  Notice that there is no  book2

SHOW TABLES;

（2） stay MySQL 8.0 In the version , The test steps are as follows ：

#  stay  mysql8.0  Next 
DROP TABLE book1,book2;  #  The database is deleted  book1  and  book2  Notice that there is no  book2

SHOW TABLES;