MogDB 学习笔记之 -- truncate 属于dml语句
# 概念描述
验证create 语句、alter 语句、truncate语句、drop语句 是属于ddl 还是dml
# 测试验证
1、环境准备
```
修改log_statement 参数
miao=# show log_statement;
log_statement
---------------
none
(1 row)
miao=# ALTER DATABASE miao SET log_statement TO ddl;
ALTER DATABASE
miao=> show log_statement;
log_statement
---------------
ddl
(1 row)
```
2、测试 create 的语句
```
创建新表t_p_t
create table t_p_t as select * from t_o_t;
create 对应的日志
2022-10-24 09:34:13.659 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [DBL_WRT] LOG: [batch flush] DW truncate end: file_head[dwn 442, start 21798], total_pages 0
2022-10-24 09:34:13.659 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [DBL_WRT] LOG: [single flush] DW truncate end: file_head[dwn 97, start 0], total_pages 0
2022-10-24 09:34:13.659 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [UNDO] LOG: [CheckPointUndoSystemMeta:353]undo metadata checkPointRedo = 20564483160.
2022-10-24 09:34:13.663 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 01000 0 [BACKEND] WARNING: replicationSlotMinLSN is InvalidXLogRecPtr!!!
2022-10-24 09:34:13.663 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 01000 0 [BACKEND] WARNING: replicationSlotMaxLSN is InvalidXLogRecPtr!!!
2022-10-24 09:34:13.664 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: CreateCheckPoint PrintCkpXctlControlFile: [checkPoint] oldCkpLoc:4/C9BC9DE0, oldRedo:4/C9BC9D60, newCkpLoc:4/C9BD1CD8, newRedo:4/C9BD1C58, preCkpLoc:4/C9BC3E58
2022-10-24 09:34:13.664 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: will update control file (create checkpoint), shutdown:0
2022-10-24 09:34:13.672 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: attempting to remove WAL segments older than log file 0000000100000004000000B7
2022-10-24 09:34:23.635 6355eb9f.3060 postgres 140420592314112 Clean Statement thread 0 dn_6001 00000 0 [BACKEND] LOG: clean statement thread start
2022-10-24 09:34:35.848 6355eb43.3061 miao 140421053867776 gsql 0 dn_6001 00000 0 [BACKEND] LOG: statement: create table t_p_t as select * from t_o_t; <<<<<<<<<<<<
```
结论：经过以上实验验证，发现create 语句在日志里体现出来了，证明create语句属于ddl语句。
3、测试 alter table的语句
```
修改表t_p_t 字段大小
alter table t_p_t modify b character varying(200);
miao=> \d t_p_t
Table "dbmt.t_p_t"
Column | Type | Modifiers
--------+------------------------+-----------
a | integer |
b | character varying(255) |
miao=> alter table t_p_t modify b character varying(200);
ALTER TABLE
miao=> \d t_p_t
Table "dbmt.t_p_t"
Column | Type | Modifiers
--------+------------------------+-----------
a | integer |
b | character varying(200) |
alter table 对应的日志
2022-10-24 09:46:14.749 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: CreateCheckPoint PrintCkpXctlControlFile: [checkPoint] oldCkpLoc:4/C9C12A88, oldRedo:4/C9C12A08, newCkpLoc:4/C9C1E2B8, newRedo:4/C9C186B0, preCkpLoc:4/C9C0CDE0
2022-10-24 09:46:14.749 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: will update control file (create checkpoint), shutdown:0
2022-10-24 09:46:14.752 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: attempting to remove WAL segments older than log file 0000000100000004000000B7
2022-10-24 09:46:26.636 6355ee72.3060 postgres 140420185052928 Clean Statement thread 0 dn_6001 00000 0 [BACKEND] LOG: clean statement thread start
2022-10-24 09:47:07.321 6355eb43.3061 miao 140421053867776 gsql 0 dn_6001 00000 0 [BACKEND] LOG: statement: alter table t_p_t modify b character varying(200); <<<<<<<<<<<<
```
结论：经过以上实验验证，发现alter 语句在日志里体现出来了，证明alter 语句属于ddl语句。
4、测试 truncate 的语句
```
truncate table t_p_t；
truncate 没有在ddl状态下输出日志
```
5、测试 drop 的语句
```
drop table t_p_t;
drop 对应的日志
2022-10-24 09:52:28.317 6355efdc.3060 postgres 140420185052928 Clean Statement thread 0 dn_6001 00000 0 [BACKEND] LOG: clean statement thread start
2022-10-24 09:52:33.182 6355eb43.3061 miao 140421053867776 gsql 0 dn_6001 00000 0 [BACKEND] LOG: statement: drop table t_p_t; <<<<<<<<<
```
结论：经过以上实验验证，发现drop 语句在日志里体现出来了，证明drop 语句属于ddl语句。
6、修改log_statement 参数为mod 再次测试truncate语句
```
miao=> ALTER DATABASE miao SET log_statement TO mod;
ALTER DATABASE
miao=> \q
[omm@db1 gs_dump]$ gsql -d miao -p 26000 -U dbmt
Password for user dbmt:
gsql ((MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
miao=> show log_statement;
log_statement
---------------
mod
(1 row)
miao=> truncate table t_p_t;
TRUNCATE TABLE
truncate 对应的日志
2022-10-24 09:58:08.817 6355f0ee.3061 miao 140421053867776 gsql 0 dn_6001 00000 0 [BACKEND] LOG: statement: truncate table t_p_t; <<<<<<<<<<
2022-10-24 09:58:08.817 6355f0ee.3061 miao 140421053867776 gsql 1588850 dn_6001 00000 17732923532782651 [BACKEND] LOG: Relation t_p_t(165435) set newfilenode 165441 oldfilenode 165438 xid 1588850
2022-10-24 09:58:14.810 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [DBL_WRT] LOG: [batch flush] DW truncate end: file_head[dwn 442, start 26764], total_pages 0
```
结论：经过以上实验验证，发现truncate 语句在日志里体现出来了，证明truncate 语句属于dML语句。
# 知识总结
drop 语句、create 语句、drop 语句、alter 语句 都属于ddl
truncate 属于dml语句
最新文章
- PHP用户注册与登录【1】
- PHP基础知识之遍历
- php-长文章分页函数
- WebView加载HTML图片大小自适应与文章自动换行
- 新手上路之Hibernate：第一个Hibernate例子
- POJ 3349 HASH
- thinkphp的伪静态化
- 2014-09-19.xml
- C#创建Excel
- 七参数计算正确性验证——Coord软件使用
- 黄聪：WordPress 函数：add_filter()（添加过滤器）
- jQuery之动画效果show()......animate()
- 使QQ窗口八字形转圈
- 关于Java泛型的新解
- 【转载】ASP.NET Core Web 支付功能接入 支付宝-电脑网页支付篇
- Docker -d : Running modprobe bridge nf_nat failed with message: exit status 1
- SQL Server2008 R2 数据库镜像实施手册(双机)SQL Server2014同样适用
- 《WEB渗透一.信息收集》
- element upload 一次性上传多张图片(包含自定义上传不走action)
- 发布aar到jcenter