前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL自动化审核工具测试

SQL自动化审核工具测试

作者头像
AsiaYe
发布2019-11-06 14:30:32
2.9K0
发布2019-11-06 14:30:32
举报
文章被收录于专栏:DBA随笔

最近在做一些SQL审核的事情,这里讲SQL审核的前期工作简单总结一下,做的一些测试的结果,希望对大家认识这两个自动化审核工具有所帮助。

一、DDL测试

1.1 Create测试

#初始创建表语句create table datatype(d_tinyint tinyint,d_smallint smallint,d_mediumint mediumint,d_int int primary key auto_increment,d_bigint bigint,d_decimal decimal,d_datetime datetime not null,d_timestamp timestamp not null,d_char char,d_varchar varchar(20),d_text text);

SQL Advisor审核建议:

2018-07-26 17:34:49 53989 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 17:34:49 53989 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-26 17:34:49 53989 [Note] 第3步: SQLAdvisor结束!

Inception审核建议:

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Table 'datatype' already exists.

Set engine to innodb for table 'datatype'.

Set charset to one of 'utf8mb4' for table 'datatype'.

Set comments for table 'datatype'.

Column 'd_tinyint' in table 'datatype' have no comments.

Column 'd_tinyint' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_tinyint' in table 'datatype'

Column 'd_smallint' in table 'datatype' have no comments.

Column 'd_smallint' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_smallint' in table 'datatype'

Column 'd_mediumint' in table 'datatype' have no comments.

Column 'd_mediumint' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_mediumint' in table 'datatype'

Column 'd_int' in table 'datatype' have no comments.

Set unsigned attribute on auto increment column in table 'datatype'.

Column 'd_bigint' in table 'datatype' have no comments.

Column 'd_bigint' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_bigint' in table 'datatype'

Column 'd_decimal' in table 'datatype' have no comments.

Column 'd_decimal' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_decimal' in table 'datatype'

Column 'd_datetime' in table 'datatype' have no comments.

Set Default value for column 'd_datetime' in table 'datatype'

Column 'd_timestamp' in table 'datatype' have no comments.

Set default value for timestamp column 'd_timestamp'.

Set Default value for column 'd_timestamp' in table 'datatype'

Invalid default value for column 'd_timestamp'.

Column 'd_char' in table 'datatype' have no comments.

Column 'd_char' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_char' in table 'datatype'

Column 'd_varchar' in table 'datatype' have no comments.

Column 'd_varchar' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_varchar' in table 'datatype'

Column 'd_text' in table 'datatype' have no comments.

Auto increment column 'd_int' is meaningful? it's dangerous!

SQL : create table datatype (d_tinyint tinyint, d_smallint smallint, d_mediumint mediumint, d_int int primary key auto_increment, d_bigint bigint, d_decimal decimal, d_datetime datetime not null, d_timestamp timestamp not null, d_char char, d_varchar varchar(20), d_text text )

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

#补充添加not null限制、存储引擎、字符集create table datatype(d_tinyint tinyint not null,d_smallint smallint not null,d_mediumint mediumint not null,d_int int primary key auto_increment,d_bigint bigint not null,d_decimal decimal not null,d_datetime datetime not null,d_timestamp timestamp not null,d_char char not null,d_varchar varchar(20) not null,d_text text)engine=innodb default charset=utf8mb4;

SQL Advisor审核建议

2018-07-27 11:41:26 19940 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-27 11:41:26 19940 [Note] 第2步:表datatype1 的SQL太逆天,没有优化建议

2018-07-27 11:41:26 19940 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Table 'datatype1' already exists.

Set comments for table 'datatype1'.

Column 'd_tinyint' in table 'datatype1' have no comments.

Set Default value for column 'd_tinyint' in table 'datatype1'

Column 'd_smallint' in table 'datatype1' have no comments.

Set Default value for column 'd_smallint' in table 'datatype1'

Column 'd_mediumint' in table 'datatype1' have no comments.

Set Default value for column 'd_mediumint' in table 'datatype1'

Column 'd_int' in table 'datatype1' have no comments.

Set unsigned attribute on auto increment column in table 'datatype1'.

Column 'd_bigint' in table 'datatype1' have no comments.

Set Default value for column 'd_bigint' in table 'datatype1'

Column 'd_decimal' in table 'datatype1' have no comments.

Set Default value for column 'd_decimal' in table 'datatype1'

Column 'd_datetime' in table 'datatype1' have no comments.

Set Default value for column 'd_datetime' in table 'datatype1'

Column 'd_timestamp' in table 'datatype1' have no comments.

Set default value for timestamp column 'd_timestamp'.

Set Default value for column 'd_timestamp' in table 'datatype1'

Invalid default value for column 'd_timestamp'.

Column 'd_char' in table 'datatype1' have no comments.

Set Default value for column 'd_char' in table 'datatype1'

Column 'd_varchar' in table 'datatype1' have no comments.

Set Default value for column 'd_varchar' in table 'datatype1'

Column 'd_text' in table 'datatype1' have no comments.

Auto increment column 'd_int' is meaningful? it's dangerous!

SQL : create table datatype1 (d_tinyint tinyint not null, d_smallint smallint not null, d_mediumint mediumint not null, d_int int primary key auto_increment, d_bigint bigint not null, d_decimal decimal not null, d_datetime datetime not null, d_timestamp timestamp not null, d_char char not null, d_varchar varchar(20) not null, d_text text )engine=innodb default charset=utf8mb4

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

#添加comment注释create table datatype(d_tinyint tinyint not null comment 'int',d_smallint smallint not null,d_mediumint mediumint not null,d_int int primary key auto_increment,d_bigint bigint not null,d_decimal decimal not null,d_datetime datetime not null,d_timestamp timestamp not null,d_char char not null,d_varchar varchar(20) not null,d_text text)engine=innodb default charset=utf8mb4;

SQL Advisor审核建议

2018-07-27 11:43:07 20011 [Note] 第1步: 对SQL解析优化之后得到的SQL:select had some error

2018-07-27 11:43:07 20011 [Note] 第2步:表datatype1 的SQL太逆天,没有优化建议

2018-07-27 11:43:07 20011 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : You have an error in your SQL syntax, near 'int, d_smallint smallint not null, d_mediumint mediumint not null, d_int int pri' at line 1

SQL : create table datatype1 (d_tinyint tinyint not null comment int, d_smallint smallint not null, d_mediumint mediumint not null, d_int int primary key auto_increment, d_bigint bigint not null, d_decimal decimal not null, d_datetime datetime not null, d_timestamp timestamp not null, d_char char not null, d_varchar varchar(20) not null, d_text text )engine=innodb default charset=utf8mb4;

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

create table datatype(d_tinyint tinyint not null default 10,d_smallint smallint not null default 10,d_mediumint mediumint not null default 10,d_int int primary key auto_increment,d_bigint bigint not null default 10,d_decimal decimal not null default 10.0,d_datetime datetime not null default '2018-07-27 11:35:00',d_timestamp timestamp not null default '20180727100000',d_char char not null default '1',d_varchar varchar(20) not null default '10',d_text text)engine=innodb default charset=utf8mb4;

2018-07-27 11:38:35 19849 [Note] 第1步: 对SQL解析优化之后得到的SQL:select had some error

2018-07-27 11:38:35 19849 [Note] 第2步:表datatype1 的SQL太逆天,没有优化建议

2018-07-27 11:38:35 19849 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议:此处没有建议

CREATE TABLE fruits(fruit_id varchar(10) NOT NULL,supplier_id INT NOT NULL,fruit_name char(255) NOT NULL,fruit_price decimal(8,2) NOT NULL,PRIMARY KEY(fruit_id))engine=innodb default charset=utf8mb4;

SQL Advisor审核建议

2018-07-27 11:50:17 20299 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-27 11:50:17 20299 [Note] 第2步:表fruits 的SQL太逆天,没有优化建议

2018-07-27 11:50:17 20299 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Table 'fruits' already exists.

Set comments for table 'fruits'.

Column 'fruit_id' in table 'fruits' have no comments.

Set Default value for column 'fruit_id' in table 'fruits'

Column 'supplier_id' in table 'fruits' have no comments.

Set Default value for column 'supplier_id' in table 'fruits'

Column 'fruit_name' in table 'fruits' have no comments.

Set column 'fruit_name' to VARCHAR type.

Set Default value for column 'fruit_name' in table 'fruits'

Column 'fruit_price' in table 'fruits' have no comments.

Set Default value for column 'fruit_price' in table 'fruits'

SQL : CREATE TABLE fruits (fruit_id varchar(10) NOT NULL, supplier_id INT NOT NULL, fruit_name char(255) NOT NULL, fruit_price decimal(8,2) NOT NULL, PRIMARY KEY(fruit_id) )engine=innodb default charset=utf8mb4

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

#添加comment限制CREATE TABLE fruits(fruit_id varchar(10) NOT NULL default 'a1' comment 'fruit_id',supplier_id INT NOT NULL default '001' comment 'supplier_id',fruit_name char(255) NOT NULL default 'apple' comment 'fruit_name',fruit_price decimal(8,2) NOT NULL default '2.5' comment 'fruit_price',PRIMARY KEY(fruit_id))engine=innodb default charset=utf8mb4;

2018-07-27 10:55:45 17401 [Note] 第1步: 对SQL解析优化之后得到的SQL:select had some error

2018-07-27 10:55:45 17401 [Note] 第2步:表fruits 的SQL太逆天,没有优化建议

2018-07-27 10:55:45 17401 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : You have an error in your SQL syntax, near 'a1 comment fruit_id, supplier_id INT NOT NULL default 001 comment supplier_id, f' at line 1

SQL : CREATE TABLE fruits (fruit_id varchar(10) NOT NULL default a1 comment fruit_id, supplier_id INT NOT NULL default 001 comment supplier_id, fruit_name char(255) NOT NULL default apple comment fruit_name, fruit_price decimal(8,2) NOT NULL default 2.5 comment fruit_price, PRIMARY KEY(fruit_id) )engine=innodb default charset=utf8mb4;

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

1.2 Alter测试

alter table datatype modify d_tinyint int;

SQL Advisor审核建议:

2018-07-26 17:35:59 54046 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 17:35:59 54046 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-26 17:35:59 54046 [Note] 第3步: SQLAdvisor结束!

Inception审核建议:

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : Column 'd_tinyint' in table 'datatype' have no comments.

Column 'd_tinyint' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_tinyint' in table 'datatype'

SQL : alter table datatype modify d_tinyint int

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

alter table datatype modify d_tinyint int first;

2018-07-26 17:36:36 54096 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 17:36:36 54096 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-26 17:36:36 54096 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : Column 'd_tinyint' in table 'datatype' have no comments.

Column 'd_tinyint' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_tinyint' in table 'datatype'

SQL : alter table datatype modify d_tinyint int first

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

alter table datatype change d_tinyint d_tinyint tinyint;

2018-07-26 17:37:21 54146 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 17:37:21 54146 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-26 17:37:21 54146 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : Column 'd_tinyint' in table 'datatype' have no comments.

Column 'd_tinyint' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'd_tinyint' in table 'datatype'

SQL : alter table datatype change d_tinyint d_tinyint tinyint

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

alter table datatype add id int;

2018-07-26 17:38:14 54197 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 17:38:14 54197 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-26 17:38:14 54197 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : Column 'id' in table 'datatype' have no comments.

Column 'id' in table 'datatype' is not allowed to been nullable.

Set Default value for column 'id' in table 'datatype'

SQL : ALTER TABLE datatype ADD id int

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

alter table datatype drop id;

2018-07-26 17:38:54 54245 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 17:38:54 54245 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-26 17:38:54 54245 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Column 'id' not existed.

SQL : alter table datatype drop id

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

Sql审核发现无法找到id这一列,无法进行删除操作,此时查看数据库中的元素,发现数据库的字段中没有id字段,在sql审核测试界面手动添加id字段:

alter table datatype add id int;Desc datatype;

结果如下:

在审核界面再次审核drop字段语句:

alter table datatype drop id;

Inception审核结果如下:

*************************** 2 .row ***************************

ID : 2

stage : EXECUTED

errlevel : 0

stagestatus : Execute Successfully

errormessage : None

SQL : alter table datatype drop id

Affected_rows : 0

sequence : '1532597271_1240_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0.010

sqlsha1 :

此时在SQL审核测试界面再次查询表结构,发现相应的id字段被删除,结果如下:

alter table datatype engine=mysiam;

2018-07-26 17:50:25 54832 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 17:50:25 54832 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-26 17:50:25 54832 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : Set engine to innodb for table 'datatype'.

SQL : Alter table datatype engine=mysiam

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

#外键测试CREATE TABLE tb_dept(id INT(11) PRIMARY KEY,name VARCHAR(22) NOT NULL,location VARCHAR(50));CREATE TABLE tb_emp (id INT(11) PRIMARY KEY,name VARCHAR(25),deptId INT(11), salary FLOAT,); alter table tb_emp add constraint FK_ID foreign key(deptID) REFERENCES tb_dept(id)

2018-07-26 18:37:05 56615 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 18:37:05 56615 [Note] 第2步:表tb_emp 的SQL太逆天,没有优化建议

2018-07-26 18:37:05 56615 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : Foreign key is not allowed in table 'tb_emp'.

Index 'FK_ID' in table 'tb_emp' need 'idx_' prefix.

Not supported statement type.

SQL : alter table tb_emp add constraint FK_ID foreign key(deptID) REFERENCES tb_dept(id)

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

1.3 DROP审核

执行drop命令之前,首先对表进行查询,show tables的结果如下:

drop table datatype;

2018-07-26 18:45:31 56835 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 18:45:31 56835 [Note] 第2步:表tb_dept 的SQL太逆天,没有优化建议

2018-07-26 18:45:31 56835 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : EXECUTED

errlevel : 0

stagestatus : Execute Successfully

errormessage : None

SQL : drop table datatype

Affected_rows : 0

sequence : '1532602012_1304_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0.000

sqlsha1 :

再次执行

drop table datatype;

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Unknown error 1146

SQL : drop table datatype

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

1.4 truncate审核

Truncate table datatype;

2018-07-26 20:39:24 60527 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where 1 having 1

2018-07-26 20:39:24 60527 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-26 20:39:24 60527 [Note] 第3步: SQLAdvisor结束!

*************************** 2 .row ***************************

ID : 2

stage : EXECUTED

errlevel : 0

stagestatus : Execute Successfully

errormessage : None

SQL : truncate table datatype

Affected_rows : 0

sequence : '1532608765_1337_1'

backup_dbname : None

execute_time : 0.010

sqlsha1 :

二、DML测试

2.1.insert into测试

Insert into datatype values;

insert into datatype values(10,150,200,2500,300000,2.501,'2018-07-26 10:00:00','20180726100000','a','test','010'),(100,1500,2000,25000,3000000,2.501,'2018-07-26 10:00:00','20180726100000','a','test','010');

SQL Advisor审核建议

2018-07-27 10:37:56 16607 [Note] 第1步: 对SQL解析优化之后得到的SQL:select had some error

2018-07-27 10:37:56 16607 [Note] 第2步: SQLAdvisor结束!

Inception 审核建议

2.1.1 insert into...values审核

INSERT INTO fruits (fruit_id, supplier_id, fruit_name, fruit_price) VALUES ('a1', 101,'apple',5.2), ('b1',101,'blackberry', 10.2);

SQL Advisor审核建议

2018-07-27 13:34:36 23297 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual

2018-07-27 13:34:36 23297 [Note] 第2步:表fruits 的SQL太逆天,没有优化建议

2018-07-27 13:34:36 23297 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Column 'a1' not existed.

Column 'apple' not existed.

Column 'b1' not existed.

Column 'blackberry' not existed.

SQL : INSERT INTO fruits (fruit_id, supplier_id, fruit_name, fruit_price) VALUES (a1, 101,apple,5.2), (b1,101,blackberry, 10.2)

Affected_rows : 2

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

此处省去列表名

INSERT INTO fruits VALUES ('a1', 101,'apple',5.2), ('b1',101,'blackberry', 10.2);

SQL Advisor审核建议

2018-07-27 13:41:07 23465 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual

2018-07-27 13:41:07 23465 [Note] 第2步:表fruits 的SQL太逆天,没有优化建议

2018-07-27 13:41:07 23465 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Set the field list for insert statements.

Column 'a1' not existed.

Column 'apple' not existed.

Column 'b1' not existed.

Column 'blackberry' not existed.

SQL : INSERT INTO fruits VALUES (a1, 101,apple,5.2), (b1,101,blackberry, 10.2)

Affected_rows : 2

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

2.1.2 insert into...select审核

create table datatype_bak(d_tinyint tinyint not null ,d_smallint smallint not null,d_mediumint mediumint not null,d_int int primary key auto_increment,d_bigint bigint not null,d_decimal decimal not null,d_datetime datetime not null,d_timestamp timestamp not null,d_char char not null,d_varchar varchar(20) not null,d_text text)engine=innodb default charset=utf8mb4; Insert into datatype_bak select * from datatype;

SQL Advisor审核建议

2018-07-27 14:35:15 25007 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `testdb`.`datatype`

2018-07-27 14:35:15 25007 [Note] 第2步:开始选择驱动表,一共有1个候选驱动表

2018-07-27 14:35:15 25007 [Note] explain select * from datatype

Inception 审核建议

*************************** 1 .row ***************************

ID : 1

stage : CHECKED

errlevel : 0

stagestatus : Audit completed

errormessage : None

SQL : use testdb

Affected_rows : 0

sequence : '0_0_0'

backup_dbname : None

execute_time : 0

sqlsha1 :

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Set the field list for insert statements.

Column count doesn't match value count at row 1.

set the where condition for select statement.

Select only star is not allowed.

SQL : Insert into datatype_bak select * from datatype

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

2.1.3 insert into...set审核

INSERT INTO datatype SET d_tinyint =11, d_smallint=151, d_mediumint=201, d_int=11, d_bigint=300001, d_decimal=2.501, d_datetime='2018-07-26 10:00:00', d_timestamp='20180726100000', d_char='a', d_varchar='test', d_text='010';

SQL Advisor审核建议

2018-07-27 14:45:03 25309 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual

2018-07-27 14:45:03 25309 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-27 14:45:03 25309 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

INSERT INTO fruits set fruit_id='t1', supplier_id=102, fruit_name='banana', fruit_price=10.3;

SQL Advisor审核建议

2018-07-27 14:47:16 25393 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual

2018-07-27 14:47:16 25393 [Note] 第2步:表fruits 的SQL太逆天,没有优化建议

2018-07-27 14:47:16 25393 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 2

stagestatus : Audit completed

errormessage : Column 't1' not existed.

Column 'banana' not existed.

SQL : INSERT INTO fruits set fruit_id=t1, supplier_id=102, fruit_name=banana, fruit_price=10.3

Affected_rows : 1

sequence : '0_0_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0

sqlsha1 :

2.2 Delete测试

执行之前先保存当前表的状态

Delete from datatype where d_tinyint=10;

SQL Advisor审核建议

2018-07-27 14:57:50 26123 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual where (`d_tinyint` = 10)

2018-07-27 14:57:50 26123 [Note] 第2步:开始解析where中的条件:(`d_tinyint` = 10)

2018-07-27 14:57:50 26123 [Note] show index from datatype

2018-07-27 14:57:50 26123 [Note] 第3步:SQLAdvisor结束!错误日志:

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : EXECUTED

errlevel : 0

stagestatus : Execute Successfully

errormessage : None

SQL : Delete from datatype where d_tinyint=10

Affected_rows : 1

sequence : '1532674671_1490_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0.020

sqlsha1 :

2.3 Update审核

Update datatype set d_tinyint=99 where d_tinyint=100;

SQL Advisor审核建议

2018-07-27 15:05:25 26654 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `d_tinyint` AS `d_tinyint` from `testdb`.`datatype` where (`d_tinyint` = 100)

2018-07-27 15:05:25 26654 [Note] 第2步:开始解析where中的条件:(`d_tinyint` = 100)

2018-07-27 15:05:25 26654 [Note] show index from datatype

2018-07-27 15:05:26 26654 [Note] 第3步:SQLAdvisor结束!错误日志:

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : EXECUTED

errlevel : 0

stagestatus : Execute Successfully

errormessage : None

SQL : Update datatype set d_tinyint=99 where d_tinyint=100

Affected_rows : 0

sequence : '1532675126_1504_1'

backup_dbname : 127_0_0_1_4316_testdb

execute_time : 0.000

sqlsha1 :

2.4 replace into 审核

replace into datatype(d_char, d_datetime) values('b', now());

SQL Advisor审核建议

2018-07-27 15:09:47 26845 [Note] 第1步: 对SQL解析优化之后得到的SQL:select from dual

2018-07-27 15:09:47 26845 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-27 15:09:47 26845 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : Not supported statement type.

SQL : replace into datatype(d_char, d_datetime) values(b, now())

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : None

execute_time : 0

sqlsha1 :

三、DQL审核

3.1 select

Select * from datatype;

SQL Advisor审核建议

2018-07-27 15:12:22 26936 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `testdb`.`datatype`

2018-07-27 15:12:22 26936 [Note] 第2步:表datatype 的SQL太逆天,没有优化建议

2018-07-27 15:12:22 26936 [Note] 第3步: SQLAdvisor结束!

Inception 审核建议

*************************** 1 .row ***************************

ID : 1

stage : CHECKED

errlevel : 0

stagestatus : Audit completed

errormessage : None

SQL : use testdb

Affected_rows : 0

sequence : '0_0_0'

backup_dbname : None

execute_time : 0

sqlsha1 :

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : set the where condition for select statement.

Select only star is not allowed.

SQL : select * from datatype

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : None

execute_time : 0

sqlsha1 :

3.2 连接查询

CREATE TABLE fruit(f_id char(10) NOT NULL,s_id INT NOT NULL,f_name char(255) NOT NULL,f_price decimal(8,2) NOT NULL,PRIMARY KEY(f_id) );INSERT INTO fruit(f_id, s_id, f_name, f_price) VALUES('a1', 101,'apple',5.2), ('b1',101,'blackberry', 10.2), ('bs1',102,'orange', 11.2), ('bs2',105,'melon',8.2), ('t1',102,'banana', 10.3), ('t2',102,'grape', 5.3), ('o2',103,'coconut', 9.2), ('c0',101,'cherry', 3.2), ('a2',103, 'apricot',2.2), ('l2',104,'lemon', 6.4), ('b2',104,'berry', 7.6), ('m1',106,'mango', 15.6), ('m2',105,'xbabay', 2.6), ('t4',107,'xbababa', 3.6), ('m3',105,'xxtt', 11.6),('b5',107,'xxxx', 3.6); CREATE TABLE suppliers( s_id int NOT NULL AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id)) ;INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),(102,'LT Supplies','Chongqing','400000','44333'),(103,'ACME','Shanghai','200000','90046'),(104,'FNK Inc.','Zhongshan','528437','11111'),(105,'Good Set','Taiyuang','030000', '22222'),(106,'Just Eat Ours','Beijing','010', '45678'),(107,'DK Inc.','Zhengzhou','450000', '33332'); SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits ,suppliers WHERE fruits.s_id = suppliers.s_id;

SQL Advisor审核建议

2018-07-27 15:25:08 27681 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `suppliers`.`s_id` AS `s_id`,`s_name` AS `s_name`,`f_name` AS `f_name`,`f_price` AS `f_price` from `testdb`.`fruit` join `testdb`.`suppliers` where (`fruit`.`s_id` = `suppliers`.`s_id`)

2018-07-27 15:25:08 27681 [Note] 第2步:开始解析join on条件:fruit.s_id=suppliers.s_id

2018-07-27 15:25:08 27681 [Note] 第3步:开始选择驱动表,一共有2个候选驱动表

2018-07-27 15:25:08 27681 [Note] explain select * from fruit

Inception 审核建议

*************************** 1 .row ***************************

ID : 1

stage : RERUN

errlevel : 0

stagestatus : Execute Successfully

errormessage : None

SQL : use testdb

Affected_rows : 0

sequence : '1532676309_1536_0'

backup_dbname : None

execute_time : 0.000

sqlsha1 :

*************************** 2 .row ***************************

ID : 2

stage : EXECUTED

errlevel : 2

stagestatus : Execute failed

errormessage : Execute: Not supported statement type.

SQL : SELECT suppliers.s_id, s_name,f_name, f_price FROM fruit ,suppliers WHERE fruit.s_id = suppliers.s_id

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : None

execute_time : 0

sqlsha1 :

SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;

SQL Advisor审核建议

2018-07-27 15:29:15 27802 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `suppliers`.`s_id` AS `s_id`,`s_name` AS `s_name`,`f_name` AS `f_name`,`f_price` AS `f_price` from (`testdb`.`fruits` join `testdb`.`suppliers` on((`fruits`.`s_id` = `suppliers`.`s_id`)))

2018-07-27 15:29:15 27802 [Note] 第2步:开始解析join on条件:fruits.s_id=suppliers.s_id

2018-07-27 15:29:15 27802 [Note] 第3步:开始选择驱动表,一共有2个候选驱动表

2018-07-27 15:29:15 27802 [Note] explain select * from fruits

Inception 审核建议

*************************** 1 .row ***************************

ID : 1

stage : CHECKED

errlevel : 0

stagestatus : Audit completed

errormessage : None

SQL : use testdb

Affected_rows : 0

sequence : '0_0_0'

backup_dbname : None

execute_time : 0

sqlsha1 :

*************************** 2 .row ***************************

ID : 2

stage : CHECKED

errlevel : 1

stagestatus : Audit completed

errormessage : set the where condition for select statement.

SQL : SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : None

execute_time : 0

sqlsha1 :

3.3 union all 审核

SELECT s_id, f_name, f_price FROM fruitWHERE f_price < 9.0UNION ALLSELECT s_id, f_name, f_price FROM fruitWHERE s_id IN(101,103);

SQL Advisor审核建议

2018-07-27 16:03:24 28803 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `s_id` AS `s_id`,`f_name` AS `f_name`,`f_price` AS `f_price` from `testdb`.`fruit` where (`f_price` < 9.0)

2018-07-27 16:03:24 28803 [Note] 第2步:开始解析where中的条件:(`f_price` < 9.0)

2018-07-27 16:03:24 28803 [Note] show index from fruit

2018-07-27 16:03:24 28803 [Note] 第3步:SQLAdvisor结束!错误日志:

Inception 审核建议

*************************** 1 .row ***************************

ID : 1

stage : RERUN

errlevel : 0

stagestatus : Execute Successfully

errormessage : None

SQL : use testdb

Affected_rows : 0

sequence : '1532678605_1543_0'

backup_dbname : None

execute_time : 0.000

sqlsha1 :

*************************** 2 .row ***************************

ID : 2

stage : EXECUTED

errlevel : 2

stagestatus : Execute failed

errormessage : Execute: Not supported statement type.

SQL : SELECT s_id, f_name, f_price FROM fruit WHERE f_price < 9.0 UNION ALL SELECT s_id, f_name, f_price FROM fruit WHERE s_id IN(101,103)

Affected_rows : 0

sequence : '0_0_1'

backup_dbname : None

execute_time : 0

sqlsha1 :

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2018-08-27,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云顾问
云顾问(Tencent Cloud Smart Advisor)是一款提供可视化云架构IDE和多个ITOM领域垂直应用的云上治理平台,以“一个平台,多个应用”为产品理念,依托腾讯云海量运维专家经验,助您打造卓越架构,实现便捷、灵活的一站式云上治理。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档