当前位置:网站首页>16MySQL之DCL 中 COMMIT和ROllBACK
16MySQL之DCL 中 COMMIT和ROllBACK
2022-04-23 20:17:00 【gh-xiaohe】
文章目錄
作者: gh-xiaohe
gh-xiaohe的博客
覺得博主文章寫的不錯的話,希望大家三連(關注,點贊,評論),多多支持一下!!
DCL 中 COMMIT和ROllBACK
DCL 中 COMMIT和ROllBACK
COMMIT
COMMIT:提交數據。一旦執行COMMIT,則數據就被永久的保存在了數據庫中,意味著數據不可以回滾。
ROLLBACK
ROLLBACK:回滾數據。一旦執行ROLLBACK,則可以實現數據的回滾。回滾到最近的一次COMMIT之後。
對比
- 相同點:都可以實現對錶中所有數據的删除,同時保留錶結構。
- 不同點:
- TRUNCATE TABLE:一旦執行此操作,錶數據全部清除。同時,數據是不可以回滾的。
- DELETE FROM:一旦執行此操作,錶數據可以全部清除(不帶WHERE)。同時,數據是可以實現回滾的。
DDL 和 DML 的說明
① DDL的操作一旦執行,就不可回滾。指令SET autocommit = FALSE對DDL操作失效。(因為在執行完DDL操作之後,一定會執行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影響的。)
② DML的操作默認情况,一旦執行,也是不可回滾的。但是,如果在執行DML之前,執行了 SET autocommit = FALSE,則執行的DML操作就可以實現回滾。
案例
COMMIT、DELETE
# 演示: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
# 演示: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的新特性:DDL的原子化
在MySQL 8.0版本中,InnoDB錶的DDL支持事務完整性,即DDL操作要麼成功要麼回滾。DDL操作回滾日志寫入到data dictionary數據字典錶mysql.innodb_ddl_log(該錶是隱藏的錶,通過show tables無法看到)中,用於回滾操作。通過設置參數,可將DDL操作日志打印輸出到MySQL錯誤日志中。
分別在MySQL 5.7版本和MySQL 8.0版本中創建數據庫和數據錶,結果如下:
CREATE DATABASE mytest; # 創建數據庫 mytest USE mytest; # 切換到 mytest 數據庫下 CREATE TABLE book1( # 創建 book1 錶 book_id INT , book_name VARCHAR(255) ); SHOW TABLES; # 查看當前數據下的錶
(1)在MySQL 5.7版本中,測試步驟如下:
# 在 mysql5.7 下 DROP TABLE book1,book2; # 此時删除數據庫 book1 和 book2 注意此時沒有 book2 SHOW TABLES;
(2)在MySQL 8.0版本中,測試步驟如下:
# 在 mysql8.0 下 DROP TABLE book1,book2; # 此時删除數據庫 book1 和 book2 注意此時沒有 book2 SHOW TABLES;
版权声明
本文为[gh-xiaohe]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204232017307463.html
边栏推荐
- Still using listview? Use animatedlist to make list elements move
- nc基础用法
- Leetcode dynamic planning training camp (1-5 days)
- Azkaban recompile, solve: could not connect to SMTP host: SMTP 163.com, port: 465 [January 10, 2022]
- Software College of Shandong University Project Training - Innovation Training - network security shooting range experimental platform (8)
- The market share of the financial industry exceeds 50%, and zdns has built a solid foundation for the financial technology network
- Database query - course selection system
- NC basic usage 2
- Electron入门教程4 —— 切换应用的主题
- Unity 模型整体更改材质
猜你喜欢
Project training of Software College of Shandong University - Innovation Training - network security shooting range experimental platform (6)
SIGIR'22「微软」CTR估计:利用上下文信息促进特征表征学习
The textarea cursor cannot be controlled by the keyboard due to antd dropdown + modal + textarea
After route link navigation, the sub page does not display the navigation style problem
Project training of Software College of Shandong University - Innovation Training - network security shooting range experimental platform (V)
Sqoop imports tinyint type fields to boolean type
CVPR 2022 | QueryDet:使用级联稀疏query加速高分辨率下的小目标检测
[talkative cloud native] load balancing - the passenger flow of small restaurants has increased
山东大学软件学院项目实训-创新实训-网络安全靶场实验平台(八)
CVPR 2022 | querydet: use cascaded sparse query to accelerate small target detection under high resolution
随机推荐
Investigate why close is required after sqlsession is used in mybatties
nc基础用法3
[2022] regard 3D target detection as sequence prediction - point2seq: detecting 3D objects as sequences
Intersection calculation of straight line and plane in PCL point cloud processing (53)
使用 WPAD/PAC 和 JScript在win11中进行远程代码执行3
How does onlyoffice solve no route to host
Grafana shares links with variable parameters
An error is reported when sqoop imports data from Mysql to HDFS: sqlexception in nextkeyvalue
Openharmony open source developer growth plan, looking for new open source forces that change the world!
Click an EL checkbox to select all questions
DNS cloud school rising posture! Three advanced uses of authoritative DNS
【文本分类案例】(4) RNN、LSTM 电影评价倾向分类,附TensorFlow完整代码
[text classification cases] (4) RNN and LSTM film evaluation Tendency Classification, with tensorflow complete code attached
PCL点云处理之基于PCA的几何形状特征计算(五十二)
Understanding various team patterns in scrum patterns
selenium. common. exceptions. WebDriverException: Message: ‘chromedriver‘ executable needs to be in PAT
程序设计语言基础(2)
Handwritten Google's first generation distributed computing framework MapReduce
Mysql database - single table query (I)
Project training of Software College of Shandong University - Innovation Training - network security shooting range experimental platform (VII)