598 lines
8.3 KiB
Plaintext
598 lines
8.3 KiB
Plaintext
create temporary table t1
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc), index sec_index(c1)
|
|
) engine = innodb;
|
|
create temporary table t2
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc), index sec_index(c1)
|
|
) engine = innodb;
|
|
create procedure populate_t1()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 200) do
|
|
insert into t1 values (i, 'a', 'b');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
create procedure populate_t1_small()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 20) do
|
|
insert into t1 values (i, 'c', 'd');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
create procedure populate_t1_small2()
|
|
begin
|
|
declare i int default 30;
|
|
while (i <= 50) do
|
|
insert into t1 values (i, 'e', 'f');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
begin;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
1 a b
|
|
2 a b
|
|
3 a b
|
|
4 a b
|
|
5 a b
|
|
6 a b
|
|
7 a b
|
|
8 a b
|
|
9 a b
|
|
10 a b
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
begin;
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
commit;
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
truncate table t1;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
call populate_t1_small();
|
|
select count(*) from t1;
|
|
count(*)
|
|
20
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
20
|
|
truncate table t1;
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
delete from t1 where keyc <= 60;
|
|
select count(*) from t1;
|
|
count(*)
|
|
140
|
|
call populate_t1_small();
|
|
select count(*) from t1;
|
|
count(*)
|
|
160
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
1 c d
|
|
2 c d
|
|
3 c d
|
|
4 c d
|
|
5 c d
|
|
6 c d
|
|
7 c d
|
|
8 c d
|
|
9 c d
|
|
10 c d
|
|
begin;
|
|
call populate_t1_small2();
|
|
select count(*) from t1;
|
|
count(*)
|
|
181
|
|
select * from t1 where keyc > 30 limit 10;
|
|
keyc c1 c2
|
|
31 e f
|
|
32 e f
|
|
33 e f
|
|
34 e f
|
|
35 e f
|
|
36 e f
|
|
37 e f
|
|
38 e f
|
|
39 e f
|
|
40 e f
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
160
|
|
select * from t1 where keyc > 30 limit 10;
|
|
keyc c1 c2
|
|
61 a b
|
|
62 a b
|
|
63 a b
|
|
64 a b
|
|
65 a b
|
|
66 a b
|
|
67 a b
|
|
68 a b
|
|
69 a b
|
|
70 a b
|
|
update t1 set keyc = keyc + 2000;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
rollback;
|
|
begin;
|
|
update t1 set keyc = keyc + 2000;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
4001 c d
|
|
4002 c d
|
|
4003 c d
|
|
4004 c d
|
|
4005 c d
|
|
4006 c d
|
|
4007 c d
|
|
4008 c d
|
|
4009 c d
|
|
4010 c d
|
|
rollback;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
commit;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
insert into t2 select * from t1 where keyc < 2101;
|
|
select count(*) from t2;
|
|
count(*)
|
|
60
|
|
drop procedure populate_t1;
|
|
drop procedure populate_t1_small;
|
|
drop procedure populate_t1_small2;
|
|
drop temporary table t1,t2;
|
|
create temporary table t1
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc), index sec_index(c1)
|
|
) engine = innodb key_block_size = 4;
|
|
ERROR HY000: CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
|
|
create temporary table t1
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc), index sec_index(c1)
|
|
) engine = innodb;
|
|
set innodb_strict_mode=off;
|
|
create temporary table t2
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc), index sec_index(c1)
|
|
) engine = innodb key_block_size = 8;
|
|
set innodb_strict_mode=on;
|
|
create procedure populate_t1()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 200) do
|
|
insert into t1 values (i, 'a', 'b');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
create procedure populate_t1_small()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 20) do
|
|
insert into t1 values (i, 'c', 'd');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
create procedure populate_t1_small2()
|
|
begin
|
|
declare i int default 30;
|
|
while (i <= 50) do
|
|
insert into t1 values (i, 'e', 'f');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
begin;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
1 a b
|
|
2 a b
|
|
3 a b
|
|
4 a b
|
|
5 a b
|
|
6 a b
|
|
7 a b
|
|
8 a b
|
|
9 a b
|
|
10 a b
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
begin;
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
commit;
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
truncate table t1;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
call populate_t1_small();
|
|
select count(*) from t1;
|
|
count(*)
|
|
20
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
20
|
|
truncate table t1;
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
delete from t1 where keyc <= 60;
|
|
select count(*) from t1;
|
|
count(*)
|
|
140
|
|
call populate_t1_small();
|
|
select count(*) from t1;
|
|
count(*)
|
|
160
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
1 c d
|
|
2 c d
|
|
3 c d
|
|
4 c d
|
|
5 c d
|
|
6 c d
|
|
7 c d
|
|
8 c d
|
|
9 c d
|
|
10 c d
|
|
begin;
|
|
call populate_t1_small2();
|
|
select count(*) from t1;
|
|
count(*)
|
|
181
|
|
select * from t1 where keyc > 30 limit 10;
|
|
keyc c1 c2
|
|
31 e f
|
|
32 e f
|
|
33 e f
|
|
34 e f
|
|
35 e f
|
|
36 e f
|
|
37 e f
|
|
38 e f
|
|
39 e f
|
|
40 e f
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
160
|
|
select * from t1 where keyc > 30 limit 10;
|
|
keyc c1 c2
|
|
61 a b
|
|
62 a b
|
|
63 a b
|
|
64 a b
|
|
65 a b
|
|
66 a b
|
|
67 a b
|
|
68 a b
|
|
69 a b
|
|
70 a b
|
|
update t1 set keyc = keyc + 2000;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
rollback;
|
|
begin;
|
|
update t1 set keyc = keyc + 2000;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
4001 c d
|
|
4002 c d
|
|
4003 c d
|
|
4004 c d
|
|
4005 c d
|
|
4006 c d
|
|
4007 c d
|
|
4008 c d
|
|
4009 c d
|
|
4010 c d
|
|
rollback;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
commit;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
insert into t2 select * from t1 where keyc < 2101;
|
|
select count(*) from t2;
|
|
count(*)
|
|
60
|
|
drop procedure populate_t1;
|
|
drop procedure populate_t1_small;
|
|
drop procedure populate_t1_small2;
|
|
drop temporary table t1, t2;
|
|
create temporary table t1
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc), index sec_index(c1)
|
|
) engine = innodb;
|
|
create temporary table t2
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc), index sec_index(c1)
|
|
) engine = innodb;
|
|
create procedure populate_t1()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 200) do
|
|
insert into t1 values (i, 'a', 'b');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
create procedure populate_t1_small()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 20) do
|
|
insert into t1 values (i, 'c', 'd');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
create procedure populate_t1_small2()
|
|
begin
|
|
declare i int default 30;
|
|
while (i <= 50) do
|
|
insert into t1 values (i, 'e', 'f');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
begin;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
1 a b
|
|
2 a b
|
|
3 a b
|
|
4 a b
|
|
5 a b
|
|
6 a b
|
|
7 a b
|
|
8 a b
|
|
9 a b
|
|
10 a b
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
begin;
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
commit;
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
truncate table t1;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
call populate_t1_small();
|
|
select count(*) from t1;
|
|
count(*)
|
|
20
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
20
|
|
truncate table t1;
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
delete from t1 where keyc <= 60;
|
|
select count(*) from t1;
|
|
count(*)
|
|
140
|
|
call populate_t1_small();
|
|
select count(*) from t1;
|
|
count(*)
|
|
160
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
1 c d
|
|
2 c d
|
|
3 c d
|
|
4 c d
|
|
5 c d
|
|
6 c d
|
|
7 c d
|
|
8 c d
|
|
9 c d
|
|
10 c d
|
|
begin;
|
|
call populate_t1_small2();
|
|
select count(*) from t1;
|
|
count(*)
|
|
181
|
|
select * from t1 where keyc > 30 limit 10;
|
|
keyc c1 c2
|
|
31 e f
|
|
32 e f
|
|
33 e f
|
|
34 e f
|
|
35 e f
|
|
36 e f
|
|
37 e f
|
|
38 e f
|
|
39 e f
|
|
40 e f
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
160
|
|
select * from t1 where keyc > 30 limit 10;
|
|
keyc c1 c2
|
|
61 a b
|
|
62 a b
|
|
63 a b
|
|
64 a b
|
|
65 a b
|
|
66 a b
|
|
67 a b
|
|
68 a b
|
|
69 a b
|
|
70 a b
|
|
update t1 set keyc = keyc + 2000;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
rollback;
|
|
begin;
|
|
update t1 set keyc = keyc + 2000;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
4001 c d
|
|
4002 c d
|
|
4003 c d
|
|
4004 c d
|
|
4005 c d
|
|
4006 c d
|
|
4007 c d
|
|
4008 c d
|
|
4009 c d
|
|
4010 c d
|
|
rollback;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
commit;
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
2001 c d
|
|
2002 c d
|
|
2003 c d
|
|
2004 c d
|
|
2005 c d
|
|
2006 c d
|
|
2007 c d
|
|
2008 c d
|
|
2009 c d
|
|
2010 c d
|
|
insert into t2 select * from t1 where keyc < 2101;
|
|
select count(*) from t2;
|
|
count(*)
|
|
60
|
|
drop procedure populate_t1;
|
|
drop procedure populate_t1_small;
|
|
drop procedure populate_t1_small2;
|