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
|
EXECUTE "SELECT TABLE_NAME FROM DATA_DICTIONARY.tables LIMIT 2";
TABLE_NAME
#
#
SET @t1=1, @t2=2, @t3:=4;
SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
@t1 @t2 @t3 @t4 := @t1+@t2+@t3
1 2 4 7
EXECUTE "SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3";
@t1 @t2 @t3 @t4 := @t1+@t2+@t3
1 2 4 7
SET @bugus_syntax= "3";
EXECUTE "SELECT * FROM DATA_DICTIONARY.tables LIMIT @bugus_syntax";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '@bugus_syntax' at line 1
EXECUTE "SELECT TABLE_NAME FROM DATA_DICTIONARY.tables LIMIT 2";
TABLE_NAME
#
#
EXECUTE "EXECUTE SELECT * FROM DATA_DICTIONARY.tables LIMIT 2";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SELECT * FROM DATA_DICTIONARY.tables LIMIT 2' at line 1
EXECUTE "SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3";
@t1 @t2 @t3 @t4 := @t1+@t2+@t3
1 2 4 7
set @sub_var="SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3";
EXECUTE @sub_var;
@t1 @t2 @t3 @t4 := @t1+@t2+@t3
1 2 4 7
EXECUTE "CREATE TABLE t1 (a SERIAL)";
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
UNIQUE KEY `a` (`a`) USING BTREE
) ENGINE=InnoDB COLLATE = utf8_general_ci
EXECUTE "INSERT INTO t1 VALUES ()";
EXECUTE "DROP TABLE t1";
SHOW TABLES;
Tables_in_test
EXECUTE "SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3; SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '; SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3' at line 1
set @foo="SELECT 1+2+3";
execute @foo;
1+2+3
6
set @create="CREATE TABLE t1 (a SERIAL)";
set @insert="INSERT INTO t1 VALUES ()";
set @select="SELECT a FROM t1";
set @drop="DROP TABLE t1";
execute @create;
execute @insert;
execute @insert;
execute @insert;
execute @select;
a
1
2
3
execute @drop;
execute @create WITH NO RETURN;
execute @insert WITH NO RETURN;
execute @insert WITH NO RETURN;
execute @insert WITH NO RETURN;
execute @select WITH NO RETURN;
execute @drop WITH NO RETURN;
EXECUTE @does_not_exist;
ERROR HY000: Incorrect arguments to Invalid Variable
EXECUTE "";
ERROR HY000: Incorrect arguments to Invalid Variable
EXECUTE "SELECT (SELECT 1) UNION SELECT (SELECT 2)";
(SELECT 1)
1
2
EXECUTE "explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2)";
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1249 Select 4 was reduced during optimization
Note 1003 select 1 AS `(SELECT 1)` union select 2 AS `(SELECT 2)`
EXECUTE "SELECT (SELECT (SELECT 0 UNION SELECT 0))";
(SELECT (SELECT 0 UNION SELECT 0))
0
EXECUTE "explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0))";
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))`
EXECUTE "SELECT (SELECT 1) UNION SELECT (SELECT 2)" WITH NO RETURN;
EXECUTE "explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2)" WITH NO RETURN;
EXECUTE "SELECT (SELECT (SELECT 0 UNION SELECT 0))" WITH NO RETURN;
EXECUTE "explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0))" WITH NO RETURN;
SET @var = "SET @var='test value'";
EXECUTE @var;
SELECT @var;
@var
test value
CREATE TABLE t1 (a int);
SET @var = "ALTER TABLE t1 ADD COLUMN (b char(100))";
EXECUTE @var;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` INT DEFAULT NULL,
`b` VARCHAR(100) COLLATE utf8_general_ci DEFAULT NULL
) ENGINE=InnoDB COLLATE = utf8_general_ci
DROP TABLE t1;
CREATE TABLE t1 (a int, b char(20));
SET @var = "INSERT INTO t1 VALUES (@a_val, @b_val)";
SET @a_val = 1;
SET @b_val = 'TNETENNBA';
EXECUTE @var;
SELECT * FROM t1;
a b
1 TNETENNBA
SET @a_val = 2;
SET @b_val = 'OVERNUMEROUSNESS';
EXECUTE @var;
SELECT * FROM t1;
a b
1 TNETENNBA
2 OVERNUMEROUSNESS
DROP TABLE t1;
CREATE TABLE t1 (a int, b char(100), c char(100));
INSERT INTO t1 VALUES (1, 'Moss', 'IT');
INSERT INTO t1 VALUES (2, 'Roy', 'IT');
INSERT INTO t1 VALUES (3, 'Jen', 'IT');
SET @var = "UPDATE t1 SET c = @new_dept WHERE b = @name";
SET @new_dept = "Relationship Manager";
SET @name = 'Jen';
EXECUTE @var;
SELECT * FROM t1;
a b c
1 Moss IT
2 Roy IT
3 Jen Relationship Manager
DROP TABLE t1;
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (42);
INSERT INTO t1 VALUES (9);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (2);
SET @var = "DELETE FROM t1 WHERE a%2=0";
EXECUTE @var;
SELECT * FROM t1;
a
9
1
DROP TABLE t1;
CREATE TABLE t1 (a int);
SET @var = "CREATE INDEX a_index ON t1 (a)";
EXECUTE @var;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` INT DEFAULT NULL,
KEY `a_index` (`a`) USING BTREE
) ENGINE=InnoDB COLLATE = utf8_general_ci
SET @var = "DROP INDEX a_index ON t1";
EXECUTE @var;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` INT DEFAULT NULL
) ENGINE=InnoDB COLLATE = utf8_general_ci
DROP TABLE t1;
|