github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/prepare/prepare.test (about) 1 2 -- @label:bvt 3 drop table if exists t1; 4 create table t1 (a int, b int); 5 prepare stmt1 from 'select * from t1 where a > ?'; 6 insert into t1 values (1, 11), (2, 22), (3, 33); 7 set @a_var = 1; 8 execute stmt1 using @a_var; 9 set @a_var = 2; 10 execute stmt1 using @a_var; 11 insert into t1 values (4, 44); 12 execute stmt1 using @a_var; 13 delete from t1 where a > 3; 14 execute stmt1 using @a_var; 15 deallocate prepare stmt1; 16 execute stmt1 using @a_var; 17 18 prepare stmt1 from 'update t1 set a=999 where b = ?'; 19 set @b_var = 33; 20 execute stmt1 using @b_var; 21 select * from t1; 22 deallocate prepare stmt1; 23 24 prepare stmt1 from 'delete from t1 where b = ?'; 25 execute stmt1 using @b_var; 26 select * from t1; 27 deallocate prepare stmt1; 28 29 prepare stmt1 from "insert into t1 values (?, ?), (?, 99)"; 30 set @a_var=5; 31 set @b_var=55; 32 set @c_var=9; 33 execute stmt1 using @a_var, @b_var, @c_var; 34 select * from t1; 35 deallocate prepare stmt1; 36 37 drop table t1; 38 39 create table t1(a timestamp(6)); 40 prepare stmt1 from "insert into t1 values (current_timestamp())"; 41 execute stmt1; 42 execute stmt1; 43 select count(*) from (select distinct a from t1) t; 44 drop table t1; 45 46 prepare stmt2 from 'select @var_t1'; 47 set @var_t1 = 0; 48 execute stmt2; 49 set @var_t1= 'aa'; 50 execute stmt2; 51 deallocate prepare stmt2; 52 53 create table t1 (a decimal(20,4), b int); 54 insert into t1 values (12.2222, 1); 55 prepare stmt1 from 'update t1 set a=a+? where b = 1'; 56 set @a=0.1111; 57 execute stmt1 using @a; 58 select a, b from t1; 59 60 drop table if exists t1; 61 create table t1 (a decimal(12,2)); 62 insert into t1 values (30000); 63 prepare stmt1 from 'update t1 set a = a + ?'; 64 set @a=4418.59; 65 execute stmt1; 66 execute stmt1 using @a; 67 select a from t1; 68 drop table if exists t1; 69 prepare s6 from 'create table t1(a int)'; 70 execute s6; 71 show tables; 72 prepare s7 from 'drop table t1'; 73 execute s7; 74 show tables; 75 drop table if exists t1; 76 create table t1(a int); 77 set autocommit=0; 78 insert into t1 values (1); 79 prepare s1 from "create table t2(a int)"; 80 prepare s1 from "drop table t1"; 81 rollback; 82 prepare s1 from "create table t2(a int)"; 83 commit; 84 prepare s2 from "drop table t1"; 85 commit; 86 insert into t1 values (1); 87 execute s1; 88 execute s2; 89 rollback; 90 set autocommit=1; 91 92 create table bmsql_district ( d_w_id integer not null, 93 d_id integer not null, 94 d_ytd decimal(12,2), 95 d_tax decimal(4,4), 96 d_next_o_id integer, 97 d_name varchar(10), 98 d_street_1 varchar(20), 99 d_street_2 varchar(20), 100 d_city varchar(20), 101 d_state char(2), 102 d_zip char(9), 103 primary key (d_w_id, d_id) ) PARTITION BY KEY(d_w_id); 104 prepare __mo_stmt_id_1 from UPDATE bmsql_district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = ? AND d_id = ?; 105 set @__mo_stmt_var_0=1, @__mo_stmt_var_1=7; 106 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1; 107 deallocate prepare __mo_stmt_id_1; 108 drop table if exists user; 109 CREATE TABLE user 110 ( 111 id BIGINT(20) NOT NULL COMMENT '主键ID', 112 name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', 113 age INT(11) NULL DEFAULT NULL COMMENT '年龄', 114 email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', 115 PRIMARY KEY (id) 116 ); 117 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 118 set @__mo_stmt_var_0 = 0,@__mo_stmt_var_1 = "test_save_0" ,@__mo_stmt_var_2 = 0; 119 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 120 deallocate prepare __mo_stmt_id_1; 121 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 122 execute __mo_stmt_id_1; 123 deallocate prepare __mo_stmt_id_1; 124 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 125 set @__mo_stmt_var_0 = 1,@__mo_stmt_var_1 = "test_save_1" ,@__mo_stmt_var_2 = 1; 126 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 127 deallocate prepare __mo_stmt_id_1; 128 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 129 execute __mo_stmt_id_1; 130 deallocate prepare __mo_stmt_id_1; 131 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 132 set @__mo_stmt_var_0 = 2,@__mo_stmt_var_1 = "test_save_2" ,@__mo_stmt_var_2 = 2; 133 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 134 deallocate prepare __mo_stmt_id_1; 135 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 136 execute __mo_stmt_id_1; 137 deallocate prepare __mo_stmt_id_1; 138 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 139 set @__mo_stmt_var_0 = 3,@__mo_stmt_var_1 = "test_save_3" ,@__mo_stmt_var_2 = 3; 140 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 141 deallocate prepare __mo_stmt_id_1; 142 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 143 execute __mo_stmt_id_1; 144 deallocate prepare __mo_stmt_id_1; 145 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 146 set @__mo_stmt_var_0 = 4,@__mo_stmt_var_1 = "test_save_4" ,@__mo_stmt_var_2 = 4; 147 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 148 deallocate prepare __mo_stmt_id_1; 149 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 150 execute __mo_stmt_id_1; 151 deallocate prepare __mo_stmt_id_1; 152 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 153 set @__mo_stmt_var_0 = 5,@__mo_stmt_var_1 = "test_save_5" ,@__mo_stmt_var_2 = 5; 154 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 155 deallocate prepare __mo_stmt_id_1; 156 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 157 execute __mo_stmt_id_1; 158 deallocate prepare __mo_stmt_id_1; 159 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 160 set @__mo_stmt_var_0 = 6,@__mo_stmt_var_1 = "test_save_6" ,@__mo_stmt_var_2 = 6; 161 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 162 deallocate prepare __mo_stmt_id_1; 163 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 164 execute __mo_stmt_id_1; 165 deallocate prepare __mo_stmt_id_1; 166 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 167 set @__mo_stmt_var_0 = 7,@__mo_stmt_var_1 = "test_save_7" ,@__mo_stmt_var_2 = 7; 168 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 169 deallocate prepare __mo_stmt_id_1; 170 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 171 execute __mo_stmt_id_1; 172 deallocate prepare __mo_stmt_id_1; 173 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 174 set @__mo_stmt_var_0 = 8,@__mo_stmt_var_1 = "test_save_8" ,@__mo_stmt_var_2 = 8; 175 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 176 deallocate prepare __mo_stmt_id_1; 177 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 178 execute __mo_stmt_id_1; 179 deallocate prepare __mo_stmt_id_1; 180 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 181 set @__mo_stmt_var_0 = 9,@__mo_stmt_var_1 = "test_save_9" ,@__mo_stmt_var_2 = 9; 182 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 183 deallocate prepare __mo_stmt_id_1; 184 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 185 execute __mo_stmt_id_1; 186 deallocate prepare __mo_stmt_id_1; 187 create table algo_offline (algo_id int(11) unsigned not null, algo_name varchar(50) not null); 188 prepare stmt1 from select count(*) from algo_offline where (algo_name like ? or algo_id like ?); 189 set @a="aa"; 190 set @b="bb"; 191 execute stmt1 using @a, @b; 192 deallocate prepare stmt1; 193 drop table if exists t1; 194 create table t1 (a int, b int); 195 prepare stmt1 from 'show columns from t1'; 196 execute stmt1; 197 prepare stmt1 from 'show variables like "aaaa"'; 198 execute stmt1; 199 200 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 201 set @__mo_stmt_var_0 = 7,@__mo_stmt_var_1 = "test_save_7" ,@__mo_stmt_var_2 = 7; 202 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 203 reset prepare __mo_stmt_id_1; 204 deallocate prepare __mo_stmt_id_1; 205 206 drop table if exists t1; 207 create table t1( a int unique key, b int, c int ); 208 prepare stmt1 from 'INSERT INTO t1 values(1, 2, 3)'; 209 execute stmt1; 210 execute stmt1; 211 select * from t1; 212 213 prepare stmt1 from 'update t1 set b = ? where a = ?'; 214 set @varb = 22; 215 set @vara = 1; 216 execute stmt1 using @varb,@vara; 217 select * from t1; 218 drop table t1; 219 220 CREATE TABLE numbers(pk INTEGER PRIMARY KEY, ui BIGINT UNSIGNED, si BIGINT); 221 INSERT INTO numbers VALUES (0, 0, -9223372036854775808), (1, 18446744073709551615, 9223372036854775807); 222 SELECT * FROM numbers WHERE ui=-9223372036854775808; 223 SET @si_min = -9223372036854775808; 224 PREPARE s1 FROM 'SELECT * FROM numbers WHERE ui=?'; 225 EXECUTE s1 USING @si_min; 226 DEALLOCATE PREPARE s1; 227 drop table numbers; 228 229 drop table if exists t1; 230 create table t1 (a int); 231 prepare stmt1 from select * from t1; 232 execute stmt1; 233 alter table t1 add column b int after a; 234 execute stmt1; 235 prepare stmt1 from select * from t1; 236 execute stmt1; 237 truncate table t1; 238 execute stmt1; 239 prepare stmt1 from select * from t1; 240 execute stmt1; 241 drop table t1; 242 execute stmt1; 243 deallocate prepare stmt1; 244 245 create database abc; 246 use abc; 247 drop table if exists t1; 248 create table t1 (a int, b int); 249 insert into t1 values(1, 1); 250 insert into t1 values(2, 2); 251 prepare stmt1 from select * from t1 limit ?; 252 set @a_var = 1; 253 execute stmt1 using @a_var; 254 prepare stmt2 from select * from t1 limit ?; 255 set @b_var = '1'; 256 execute stmt2 using @b_var; 257 deallocate prepare stmt1; 258 deallocate prepare stmt2; 259 insert into t1 values(3, 3); 260 insert into t1 values(4, 4); 261 insert into t1 values(5, 5); 262 prepare stmt3 from select * from t1 limit ? offset ?; 263 set @a_var = 2; 264 set @b_var = 0; 265 execute stmt3 using @a_var, @b_var; 266 select * from t1 where a > ?; 267 deallocate prepare stmt3; 268 drop database abc; 269 270 create database prepare_test; 271 use prepare_test; 272 CREATE TABLE m_user ( 273 m_id INT NOT NULL, 274 m_name CHAR(25) NOT NULL 275 ); 276 277 prepare stmt1 from 'INSERT INTO m_user SET m_id=?, m_name=? ON DUPLICATE KEY UPDATE m_name=?;'; 278 set @a_var_1 = 111; 279 set @a_var_2 = "aaaa"; 280 set @a_var_3 = "bbb"; 281 execute stmt1 using @a_var_1, @a_var_2, @a_var_3; 282 select * from m_user; 283 deallocate prepare stmt1; 284 285 prepare stmt1 from 'INSERT INTO m_user SET m_id=?, m_name=? ON DUPLICATE KEY UPDATE m_name= substring(?, 1, 3);'; 286 set @a_var_1 = 111; 287 set @a_var_2 = "aaaa"; 288 set @a_var_3 = "bbbbbbb"; 289 execute stmt1 using @a_var_1, @a_var_2, @a_var_3; 290 select * from m_user; 291 deallocate prepare stmt1; 292 293 prepare stmt1 from 'INSERT INTO m_user SET m_id=?, m_name=? ON DUPLICATE KEY UPDATE m_id= m_id + ?;'; 294 set @a_var_1 = 111; 295 set @a_var_2 = "aaaa"; 296 set @a_var_3 = 1; 297 execute stmt1 using @a_var_1, @a_var_2, @a_var_3; 298 select * from m_user; 299 deallocate prepare stmt1; 300 301 drop table m_user; 302 303 CREATE TABLE m_user ( 304 m_id INT NOT NULL primary key, 305 m_name CHAR(25) NOT NULL 306 ); 307 308 prepare stmt1 from 'INSERT INTO m_user SET m_id=?, m_name=? ON DUPLICATE KEY UPDATE m_name=?;'; 309 set @a_var_1 = 111; 310 set @a_var_2 = "aaaa"; 311 set @a_var_3 = "bbb"; 312 execute stmt1 using @a_var_1, @a_var_2, @a_var_3; 313 select * from m_user; 314 deallocate prepare stmt1; 315 316 drop table m_user; 317 drop database prepare_test;