1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
|
SET sql_mode='NO_ENGINE_SUBSTITUTION';
####################################
# SETUP
####################################
CREATE DATABASE statements_digest;
USE statements_digest;
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);
CREATE TABLE t3(a int, b int);
CREATE TABLE t4(a int, b int);
CREATE TABLE t5(a int, b int, c int);
CREATE TABLE t6(a int, b int, c int, d int);
CREATE TABLE t11 (c CHAR(4));
CREATE TABLE t12 (c CHAR(4));
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
####################################
# EXECUTION
####################################
SELECT 1 FROM t1;
1
SELECT 1 FROM `t1`;
1
SELECT 1,2 FROM t1;
1 2
SELECT 1, 2, 3, 4 FROM t1;
1 2 3 4
SELECT 1 FROM t2;
1
SELECT 1,2 FROM t2;
1 2
SELECT 1, 2, 3, 4 FROM t2;
1 2 3 4
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1, 2);
INSERT INTO t4 VALUES (1, 2);
INSERT INTO t5 VALUES (1, 2, 3);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t3 VALUES (1, 2), (3, 4), (5, 6);
INSERT INTO t5 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
SELECT * FROM t1 WHERE a IN (1);
a
1
1
1
SELECT * FROM t1 WHERE a IN (1, 2, 3);
a
1
1
2
3
1
2
3
INSERT INTO t1 VALUES (NULL);
INSERT INTO t3 VALUES (NULL,NULL);
INSERT INTO t3 VALUES (1,NULL);
INSERT INTO t3 VALUES (NULL,1);
INSERT INTO t6 VALUES (NULL, NULL, NULL, NULL);
INSERT INTO t6 VALUES (1, NULL, NULL, NULL);
INSERT INTO t6 VALUES (NULL, 2, NULL, NULL);
INSERT INTO t6 VALUES (1, 2, 3, NULL);
INSERT INTO t6 VALUES (1, 2, NULL, 4);
SELECT 1 + 1;
1 + 1
2
SELECT 1;
1
1
SELECT 1 /* This is an inline comment */ + 1;
1 /* This is an inline comment */ + 1
2
SELECT 1+
/*
this is a
multiple-line comment
*/
1;
1+
/*
this is a
multiple-line comment
*/
1
2
CREATE SCHEMA statements_digest_temp;
DROP SCHEMA statements_digest_temp;
CREATE DATABASE statements_digest_temp;
DROP DATABASE statements_digest_temp;
SELECT 1 FROM no_such_table;
ERROR 42S02: Table 'statements_digest.no_such_table' doesn't exist
CREATE TABLE dup_table (c char(4));
CREATE TABLE dup_table (c char(4));
ERROR 42S01: Table 'dup_table' already exists
DROP TABLE dup_table;
INSERT IGNORE INTO t11 VALUES("MySQL");
Warnings:
Warning 1265 Data truncated for column 'c' at row 1
PREPARE stmt FROM "SELECT * FROM t12";
EXECUTE stmt;
c
EXECUTE stmt;
c
DEALLOCATE PREPARE stmt;
CREATE PROCEDURE p1() BEGIN SELECT * FROM t12; END//
CALL p1();
c
CALL p1();
c
DROP PROCEDURE p1;
CREATE FUNCTION `func`(a INT, b INT) RETURNS int(11) RETURN a+b //
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
select func(3,4);
func(3,4)
7
select func(13,42);
func(13,42)
55
DROP FUNCTION func;
CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @a:=1;
INSERT INTO t12 VALUES ("abc");
INSERT INTO t12 VALUES ("def");
DROP TRIGGER trg;
DELETE FROM t1;
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM (SELECT a,1,1 FROM t1) AS dt (x,y,z);
x y z
1 1 1
2 1 1
SELECT * FROM (SELECT a,2,2 FROM t1) AS dt (x,y,z);
x y z
1 2 2
2 2 2
WITH cte (x,y,z)AS (SELECT a,1,1 FROM t1) SELECT 1, cte.* FROM cte;
1 x y z
1 1 1 1
1 2 1 1
WITH cte (x,y,z)AS (SELECT a,2,2 FROM t1) SELECT 2, cte.* FROM cte;
2 x y z
2 1 2 2
2 2 2 2
WITH cte (x,y,z)AS (SELECT a,1,1 FROM t1) SELECT 1, cte.*, cte2.* FROM cte, cte AS cte2;
1 x y z x y z
1 1 1 1 1 1 1
1 1 1 1 2 1 1
1 2 1 1 1 1 1
1 2 1 1 2 1 1
WITH cte (x,y,z)AS (SELECT a,2,2 FROM t1) SELECT 2, cte.*, cte2.* FROM cte, cte AS cte2;
2 x y z x y z
2 1 2 2 1 2 2
2 1 2 2 2 2 2
2 2 2 2 1 2 2
2 2 2 2 2 2 2
WITH RECURSIVE cte (x,y,z) AS
(SELECT a,1,1 FROM t1 UNION ALL SELECT a+1,a*1,11 FROM t1 WHERE 0)
SELECT 1, cte.*, cte2.* FROM cte, cte AS cte2;
1 x y z x y z
1 1 1 1 1 1 1
1 1 1 1 2 1 1
1 2 1 1 1 1 1
1 2 1 1 2 1 1
WITH RECURSIVE cte (x,y,z) AS
(SELECT a,2,2 FROM t1 UNION ALL SELECT a+2,a*2,22 FROM t1 WHERE 0)
SELECT 2, cte.*, cte2.* FROM cte, cte AS cte2;
2 x y z x y z
2 1 2 2 1 2 2
2 1 2 2 2 2 2
2 2 2 2 1 2 2
2 2 2 2 2 2 2
####################################
# QUERYING PS STATEMENT DIGEST
####################################
SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS,
SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest;
SCHEMA_NAME DIGEST DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS
statements_digest 5e18b503343466373cc0ce1922b04a77902f0cce5386ce599cb95b682d29415e TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 0 0 0
statements_digest 6234405961c90550fa353e725e66b1a3f6636c837aa8be33d0c02d787cbb4e30 SELECT ? FROM `t1` 2 0 0 0
statements_digest 13340eb12b11a4dc5ad207455ace28dfb18518f0be7ca9c52ea512b829acc4bb SELECT ?, ... FROM `t1` 2 0 0 0
statements_digest 80b7b03ea7ad60fdaaf4c83b3848075c5b8e18a725b2b8684a5648e3e5a67b2d SELECT ? FROM `t2` 1 0 0 0
statements_digest 5dd25cacf042c7f42ea2fd43b03314e060e5129c7eb7429032a5f886a413bf54 SELECT ?, ... FROM `t2` 2 0 0 0
statements_digest f19c00f38783fc93a3b043787b0eb19055052c2e8cee8be072139d2ee4bcabec INSERT INTO `t1` VALUES (?) 2 2 0 0
statements_digest 55c0e5a4110056fcbf2654778c977a2e91ce65785504832368e2c3443a35ec2b INSERT INTO `t2` VALUES (?) 1 1 0 0
statements_digest 096d3a6bb89d2960ee049bc4e7ee92ee53caeacce26b6a5d1894c75c6ba166b8 INSERT INTO `t3` VALUES (...) 4 4 0 0
statements_digest bc3ee85651efdbb61bd2e41c019ee2ebacd7bba21d70e9a1b4648481f00915d8 INSERT INTO `t4` VALUES (...) 1 1 0 0
statements_digest f10269a8e5a0a07dbf4bfc8af804df8bac94936c0e057aa443f7d68c122419dd INSERT INTO `t5` VALUES (...) 1 1 0 0
statements_digest 09c41a6d6775695a1cc75b4c47560944db5cacbdaf1d9718c564c3181817d959 INSERT INTO `t1` VALUES (?) /* , ... */ 3 9 0 0
statements_digest 677122d96027696ab9a5b50681f4c0edb724f17dd48b1306c7a81751fc7ae9aa INSERT INTO `t3` VALUES (...) /* , ... */ 1 3 0 0
statements_digest 094381517c9f93213124e2b25d6c15cc05af408565a15193c6436c6f6971dd1d INSERT INTO `t5` VALUES (...) /* , ... */ 1 3 0 0
statements_digest eb95fa873ecbbf6c0c7c11521a8d033f11a7952a7e4238f2afb2a1b2beaf1718 SELECT * FROM `t1` WHERE `a` IN (...) 2 0 0 0
statements_digest 1b3cf21a7d3fe78c20f121bec240b1134b85a938574a446cd2fb6c48a8816496 INSERT INTO `t6` VALUES (...) 5 5 0 0
statements_digest 18c24a99168954090331d4686d78ade5498aa5cfc6b125c260cd8183fa150bd5 SELECT ? + ? 3 0 0 0
statements_digest d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae SELECT ? 1 0 0 0
statements_digest 14158db2b231eee34e43b673f89f20da355f396c07002a786fe6831e42b02bec CREATE SCHEMA `statements_digest_temp` 2 2 0 0
statements_digest 00f71885ed8726c0de2c6cdc12758739f0e3158d3cc020dee8d3de66e6bb4e03 DROP SCHEMA `statements_digest_temp` 2 0 0 0
statements_digest bfe83f596734eb72a658cd3847c76f2b6ea78ead58635b904229af9141675c30 SELECT ? FROM `no_such_table` 1 0 0 1
statements_digest 166e59a64bcabf837a372db82ad05e170088a34510d5f29424bf203a1f8351cf CREATE TABLE `dup_table` ( `c` CHARACTER (?) ) 2 0 0 1
statements_digest 84722ddfe551d900effd94247897a0a505de0b6518d68cb3620abb11284e3f86 DROP TABLE `dup_table` 1 0 0 0
statements_digest 29fcb1f90bb1bf86ffcd07c630176dcb1e63714190a35e86c36f1eab4008dd8b INSERT IGNORE INTO `t11` VALUES (?) 1 1 1 0
statements_digest 118d653c380580e38366e6c2f770958c117ff4937f4a30ea3b324cc43674dc3e SHOW WARNINGS 2 0 0 0
statements_digest 0fc3831925a98cfd206b3e9af177d971ce92b8bf0de882c27e822ee367d60e82 PREPARE `stmt` FROM ? 1 0 0 0
statements_digest 295b5d2afaac2df4dc74d5f30c540a81f2964e0884d139fdd36ea403f3da0a27 EXECUTE `stmt` 2 0 0 0
statements_digest 04bb606f5dcfc5a96683c5f930d785fd49efd0d51ea2ade4eed5dfaf18af9ae9 DEALLOCATE PREPARE `stmt` 1 0 0 0
statements_digest bde8727a37457c198c908837eab6a98f36c6a2ab50c7a798ce3478a8d0700696 CREATE PROCEDURE `p1` ( ) BEGIN SELECT * FROM `t12` ; END 1 0 0 0
statements_digest c03fdf94b9e31e778006a5042f68e3fb029a331a0013d182b536a9b5e50424ce CALL `p1` ( ) 2 0 0 0
statements_digest 1ad7991c7ae3f8636a600eaafdae2fbce754a60bb5faa4a0086f5adf9dbd48c1 DROP PROCEDURE `p1` 1 0 0 0
statements_digest 274dc42d8d4de884e52e3a6a423f0b663e974c8c54f4d7e2cb6eb9e073bbee27 CREATE FUNCTION `func` ( `a` INTEGER , `b` INTEGER ) RETURNS INTEGER (?) RETURN `a` + `b` 1 0 1 0
statements_digest f4514aabd6e6206595008f5bfe10dcbd61b9e7e8c103935ef0a50aa484b01e9b SELECT `func` (...) 2 0 0 0
statements_digest 02960b80d42b54bb47554ffba92a3148d1b99bee51d150fb15f25115c123b543 DROP FUNCTION `func` 1 0 0 0
statements_digest 49df6079a4cc940671f9329b2ceadd245696c1d2a48e7f264bcd6967885dc71f CREATE TRIGGER `trg` BEFORE INSERT ON `t12` FOR EACH ROW SET @? := ? 1 0 0 0
statements_digest 4e4aabf127e7888c85f659c2fe79420df6f4ca24e15a99720d5283af055e5432 INSERT INTO `t12` VALUES (?) 2 2 0 0
statements_digest 342789b396c3cca4a7e1491d49ca4255e412aee4bb1dbf077a7bf6bda8790e0a DROP TRIGGER `trg` 1 0 0 0
statements_digest 648ac29b93e6a26e394fa82725aa3968c1fe12603f512fa491c50b64e2226adf DELETE FROM `t1` 1 9 0 0
statements_digest 432e0ca37d8ec0d5aa35531d2f57667b6b9a6d846a40595be20395d02b5f6c9f SELECT * FROM ( SELECT `a` , ?, ... FROM `t1` ) AS `dt` ( `x` , `y` , `z` ) 2 0 0 0
statements_digest 43cac6c54084dd66cecd8cfc12f7b405c17d8b4073e5845f086895daced248d9 WITH `cte` ( `x` , `y` , `z` ) AS ( SELECT `a` , ?, ... FROM `t1` ) SELECT ? , `cte` . * FROM `cte` 2 0 0 0
statements_digest 85e633d824b5f672f8f09af17a9c5f6fc197431e24e5fdb14ef794a0c349e61f WITH `cte` ( `x` , `y` , `z` ) AS ( SELECT `a` , ?, ... FROM `t1` ) SELECT ? , `cte` . * , `cte2` . * FROM `cte` , `cte` AS `cte2` 2 0 0 0
statements_digest ee9c0d5a3c0f2e813620c8c67ddb6e6dbd7138c7e54dc27b2ccaf3398f32f11f WITH RECURSIVE `cte` ( `x` , `y` , `z` ) AS ( SELECT `a` , ?, ... FROM `t1` UNION ALL SELECT `a` + ? , `a` * ?, ... FROM `t1` WHERE ? ) SELECT ? , `cte` . * , `cte2` . * FROM `cte` , `cte` AS `cte2` 2 0 0 0
####################################
# CLEANUP
####################################
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t5;
DROP TABLE IF EXISTS t6;
DROP TABLE IF EXISTS t11;
DROP TABLE IF EXISTS t12;
DROP DATABASE IF EXISTS statements_digest;
SET sql_mode=default;
|
