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
|
####################################
# 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
NULL NULL NULL 68 43 2 2
statements_digest 5e18b503343466373cc0ce1922b04a77902f0cce5386ce599cb95b682d29415e TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 0 0 0
SHOW GLOBAL VARIABLES LIKE "performance_schema_digests_size";
Variable_name Value
performance_schema_digests_size 2
SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest;
COUNT(*)
2
select count(*)
from performance_schema.events_statements_histogram_by_digest;
count(*)
900
select SCHEMA_NAME, DIGEST IS NULL, BUCKET_NUMBER
from performance_schema.events_statements_histogram_by_digest
where BUCKET_NUMBER= 1;
SCHEMA_NAME DIGEST IS NULL BUCKET_NUMBER
NULL 1 1
statements_digest 0 1
####################################
# 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;
|
