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
|
####################################
# 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);
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 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 //
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;
####################################
# 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 e4a84a547a18a89f4708509a720def58 TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 1 0 0 0
statements_digest ab105aea9c15b3842ad161d18349f9c4 SELECT ? FROM t1 1 0 0 0
statements_digest 835083efbaa5d8c29d01d558abb8216b SELECT ? FROM `t1` 1 0 0 0
statements_digest 6ff375c6f4b283de91711a78bd91b953 SELECT ?, ... FROM t1 2 0 0 0
statements_digest 4879fbad051c94ff76e6ad29effa4903 SELECT ? FROM t2 1 0 0 0
statements_digest b1ea4bca7c91ebd647b6b81e80a2ef94 SELECT ?, ... FROM t2 2 0 0 0
statements_digest 14d463345df747d42a036019a5988a9b INSERT INTO t1 VALUES (?) 2 2 0 0
statements_digest ff2d8aa1fd516f5e25b0faf7b1c80b04 INSERT INTO t2 VALUES (?) 1 1 0 0
statements_digest 430116339c3a5bf0a1aa9a96e9cfd354 INSERT INTO t3 VALUES (...) 4 4 0 0
statements_digest 01467137a1045e85119538ea248d52dd INSERT INTO t4 VALUES (...) 1 1 0 0
statements_digest b201a20a2a534d2789750270b7f90fab INSERT INTO t5 VALUES (...) 1 1 0 0
statements_digest b1a5f24770580f243ad6704590165d90 INSERT INTO t1 VALUES (?) /* , ... */ 2 7 0 0
statements_digest bbbf619ec8ca4ec4a4da28a71eb12a2f INSERT INTO t3 VALUES (...) /* , ... */ 1 3 0 0
statements_digest de7bdb298875f4ef826383e3fce53ef9 INSERT INTO t5 VALUES (...) /* , ... */ 1 3 0 0
statements_digest cd4a3e419e2eaed79f66a705ff002910 INSERT INTO t6 VALUES (...) 5 5 0 0
statements_digest f2d57cea9e78e7b37c4509c0564dd1cc SELECT ? + ? 3 0 0 0
statements_digest 02396199eed2345830efcf00e51107ee SELECT ? 1 0 0 0
statements_digest 7c5b403e11cb8fa41954f8b81d47fb44 CREATE SCHEMA statements_digest_temp 2 2 0 0
statements_digest d5c2a9eedc964698407667a633301e69 DROP SCHEMA statements_digest_temp 2 0 0 0
statements_digest add5619cd2761d01c66b68b50a4c0476 SELECT ? FROM no_such_table 1 0 0 1
statements_digest f59e7a7dbcdc342b7ea72ae24e5ef081 CREATE TABLE dup_table ( c CHARACTER (?) ) 2 0 0 1
statements_digest b42311b2b180ba680ebb286f671982f1 DROP TABLE dup_table 1 0 0 0
statements_digest 8af43d157243ebdc9dcb1a9502acdd24 INSERT INTO t11 VALUES (?) 1 1 1 0
statements_digest cee5b131782212e0ba1cd76ba28485c4 SHOW WARNINGS 1 0 0 0
statements_digest 438623439c3a1702203d6190795127ad PREPARE stmt FROM ? 1 0 0 0
statements_digest e53b71815168ad954fd921a6ae1860c9 EXECUTE stmt 2 0 0 0
statements_digest 3bccf5096186ceaf50c11c3deb4e21cf DEALLOCATE PREPARE stmt 1 0 0 0
statements_digest e20f8d5cb15105439af39592b79c0edd CREATE PROCEDURE p1 ( ) BEGIN SELECT * FROM t12 ; END 1 0 0 0
statements_digest 10298a45c9f7114e4985de53ca99bda7 CALL p1 ( ) 2 0 0 0
statements_digest efc046c5d04acb8afa61326f759ad380 DROP PROCEDURE p1 1 0 0 0
statements_digest 8eaf9e776b79f24f2b8fae8efb92d8a4 CREATE FUNCTION `func` ( a INTEGER , b INTEGER ) RETURNS INTEGER (?) RETURN a + b 1 0 0 0
statements_digest b364c71d8c904eb9e95df4d0ca258c00 SELECT func (...) 2 0 0 0
statements_digest 88e7ac9784e4561d12fadccde6ea704a DROP FUNCTION func 1 0 0 0
statements_digest 15deeaae5594a691cf21abd4439ee5e6 CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @? := ? 1 0 0 0
statements_digest 074e38814943a6dce874784d21fea89d INSERT INTO t12 VALUES (?) 2 2 0 0
statements_digest 2b5b02ba54b27638d5d8dbe917ff432d DROP TRIGGER trg 1 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;
|
