SQL自动化审核工具测试
- 2019 年 11 月 6 日
- 筆記
最近在做一些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 :
