ps_4heap.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

update t1 set b='two' where a=@arg00;
set @arg00=2000;
execute stmt1 using @arg00;
select a,b from t1 where a=@arg00;
a	b
set @arg00=2;
set @arg01=22;
prepare stmt1 from 'update t1 set a=? where a=?' ;
execute stmt1 using @arg00, @arg00;
select a,b from t1 where a=@arg00;
a	b
2	two
execute stmt1 using @arg01, @arg00;
select a,b from t1 where a=@arg01;
a	b
22	two
execute stmt1 using @arg00, @arg01;
select a,b from t1 where a=@arg00;
a	b
2	two
set @arg00=NULL;
set @arg01=2;
execute stmt1 using @arg00, @arg01;
Warnings:
Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'a' at row 1
select a,b from t1 order by a;
a	b
0	two
1	one
3	three
4	four
set @arg00=0;
execute stmt1 using @arg01, @arg00;
select a,b from t1 order by a;
a	b
1	one
2	two
3	three
4	four
set @arg00=23;
set @arg01='two';
set @arg02=2;
set @arg03='two';
set @arg04=2;
drop table if exists t2;
create table t2 as select a,b from t1 ;
prepare stmt1 from 'update t1 set a=? where b=?
                    and a in (select ? from t2
                              where b = ? or a = ?)';
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select a,b from t1 where a = @arg00 ;
a	b
23	two
prepare stmt1 from 'update t1 set a=? where b=?
                    and a not in (select ? from t2
                              where b = ? or a = ?)';
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select a,b from t1 order by a ;
a	b
1	one
2	two
3	three
4	four
drop table t2 ;
create table t2
(
a int, b varchar(30),
primary key(a)
) engine = 'HEAP'  ;
insert into t2(a,b) select a, b from t1 ;
prepare stmt1 from 'update t1 set a=? where b=?
                    and a in (select ? from t2
                              where b = ? or a = ?)';
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select a,b from t1 where a = @arg00 ;
a	b
23	two
prepare stmt1 from 'update t1 set a=? where b=?
                    and a not in (select ? from t2
                              where b = ? or a = ?)';
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select a,b from t1 order by a ;
a	b
1	one
2	two
3	three
4	four
drop table t2 ;
set @arg00=1;
prepare stmt1 from 'update t1 set b=''bla''
where a=2
limit 1';
execute stmt1 ;
select a,b from t1 where b = 'bla' ;
a	b
2	bla
prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
execute stmt1 using @arg00;
test_sequence
------ insert tests ------
delete from t1 ;
insert into t1 values (1,'one');
insert into t1 values (2,'two');
insert into t1 values (3,'three');
insert into t1 values (4,'four');
commit ;
delete from t9 ;
insert into t9
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
c10= 1, c11= 1, c12 = 1,
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
c16= '11:11:11', c17= '2004',
c18= 1, c19=true, c20= 'a', c21= '123456789a', 
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
insert into t9
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
c10= 9, c11= 9, c12 = 9,
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
c16= '11:11:11', c17= '2004',
c18= 1, c19=false, c20= 'a', c21= '123456789a', 
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
commit ;
prepare stmt1 from 'insert into t1 values(5, ''five'' )';
execute stmt1;
select a,b from t1 where a = 5;
a	b
5	five
set @arg00='six' ;
prepare stmt1 from 'insert into t1 values(6, ? )';
execute stmt1 using @arg00;
select a,b from t1 where b = @arg00;
a	b
6	six
execute stmt1 using @arg00;
ERROR 23000: Duplicate entry '6' for key 1
set @arg00=NULL ;
prepare stmt1 from 'insert into t1 values(0, ? )';
execute stmt1 using @arg00;
select a,b from t1 where b is NULL;
a	b
0	NULL
set @arg00=8 ;
set @arg01='eight' ;
prepare stmt1 from 'insert into t1 values(?, ? )';
execute stmt1 using @arg00, @arg01 ;
select a,b from t1 where b = @arg01;
a	b
8	eight
set @NULL= null ;
set @arg00= 'abc' ;
execute stmt1 using @NULL, @NULL ;
ERROR 23000: Column 'a' cannot be null
execute stmt1 using @NULL, @NULL ;
ERROR 23000: Column 'a' cannot be null
execute stmt1 using @NULL, @arg00 ;
ERROR 23000: Column 'a' cannot be null
execute stmt1 using @NULL, @arg00 ;
ERROR 23000: Column 'a' cannot be null
set @arg01= 10000 + 2 ;
execute stmt1 using @arg01, @arg00 ;
set @arg01= 10000 + 1 ;
execute stmt1 using @arg01, @arg00 ;
select * from t1 where a > 10000 order by a ;
a	b
10001	abc
10002	abc
delete from t1 where a > 10000 ;
set @arg01= 10000 + 2 ;
execute stmt1 using @arg01, @NULL ;
set @arg01= 10000 + 1 ;
execute stmt1 using @arg01, @NULL ;
select * from t1 where a > 10000 order by a ;
a	b
10001	NULL
10002	NULL
delete from t1 where a > 10000 ;
set @arg01= 10000 + 10 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 9 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 8 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 7 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 6 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 5 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 4 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 3 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 2 ;
execute stmt1 using @arg01, @arg01 ;
set @arg01= 10000 + 1 ;
execute stmt1 using @arg01, @arg01 ;
select * from t1 where a > 10000 order by a ;
a	b
10001	10001
10002	10002
10003	10003
10004	10004
10005	10005
10006	10006
10007	10007
10008	10008
10009	10009
10010	10010
delete from t1 where a > 10000 ;
set @arg00=81 ;
set @arg01='8-1' ;
set @arg02=82 ;
set @arg03='8-2' ;
prepare stmt1 from 'insert into t1 values(?,?),(?,?)';
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
select a,b from t1 where a in (@arg00,@arg02) ;
a	b
81	8-1
82	8-2
set @arg00=9 ;
set @arg01='nine' ;
prepare stmt1 from 'insert into t1 set a=?, b=? ';
execute stmt1 using @arg00, @arg01 ;
select a,b from t1 where a = @arg00 ;
a	b
9	nine
set @arg00=6 ;
set @arg01=1 ;
prepare stmt1 from 'insert into t1 set a=?, b=''sechs''
                    on duplicate key update a=a + ?, b=concat(b,''modified'') ';
execute stmt1 using @arg00, @arg01;
select * from t1 order by a;
a	b
0	NULL
1	one
2	two
3	three
4	four
5	five
7	sixmodified
8	eight
9	nine
81	8-1
82	8-2
set @arg00=81 ;
set @arg01=1 ;
execute stmt1 using @arg00, @arg01;
ERROR 23000: Duplicate entry '82' for key 1
drop table if exists t2 ;
create table t2 (id int auto_increment primary key) 
ENGINE= 'HEAP'  ;
prepare stmt1 from ' select last_insert_id() ' ;
insert into t2 values (NULL) ;
execute stmt1 ;
last_insert_id()
1
insert into t2 values (NULL) ;
execute stmt1 ;
last_insert_id()
2
drop table t2 ;
set @1000=1000 ;
set @x1000_2="x1000_2" ;
set @x1000_3="x1000_3" ;
set @x1000="x1000" ;
set @1100=1100 ;
set @x1100="x1100" ;
set @100=100 ;
set @updated="updated" ;
insert into t1 values(1000,'x1000_1') ;
insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3)
on duplicate key update a = a + @100, b = concat(b,@updated) ;
select a,b from t1 where a >= 1000 order by a ;
a	b
1000	x1000_3
1100	x1000_1updated
delete from t1 where a >= 1000 ;
insert into t1 values(1000,'x1000_1') ;
prepare stmt1 from ' insert into t1 values(?,?),(?,?)
               on duplicate key update a = a + ?, b = concat(b,?) ';
execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ;
select a,b from t1 where a >= 1000 order by a ;
a	b
1000	x1000_3
1100	x1000_1updated
delete from t1 where a >= 1000 ;
insert into t1 values(1000,'x1000_1') ;
execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ;
select a,b from t1 where a >= 1000 order by a ;
a	b
1200	x1000_1updatedupdated
delete from t1 where a >= 1000 ;
prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' ';
execute stmt1;
execute stmt1;
execute stmt1;
test_sequence
------ multi table tests ------
delete from t1 ;
delete from t9 ;
insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ;
insert into t9 (c1,c21)
values (1, 'one'), (2, 'two'), (3, 'three') ;
prepare stmt_delete from " delete t1, t9 
  from t1, t9 where t1.a=t9.c1 and t1.b='updated' ";
prepare stmt_update from " update t1, t9 
  set t1.b='updated', t9.c21='updated'
  where t1.a=t9.c1 and t1.a=? ";
prepare stmt_select1 from " select a, b from t1 order by a" ;
prepare stmt_select2 from " select c1, c21 from t9 order by c1" ;
set @arg00= 1 ;
execute stmt_update using @arg00 ;
execute stmt_delete ;
execute stmt_select1 ;
a	b
2	two
3	three
execute stmt_select2 ;
c1	c21
2	two
3	three
set @arg00= @arg00 + 1 ;
execute stmt_update using @arg00 ;
execute stmt_delete ;
execute stmt_select1 ;
a	b
3	three
execute stmt_select2 ;
c1	c21
3	three
set @arg00= @arg00 + 1 ;
execute stmt_update using @arg00 ;
execute stmt_delete ;
execute stmt_select1 ;
a	b
execute stmt_select2 ;
c1	c21
set @arg00= @arg00 + 1 ;
delete from t1 ;
insert into t1 values (1,'one');
insert into t1 values (2,'two');
insert into t1 values (3,'three');
insert into t1 values (4,'four');
commit ;
delete from t9 ;
insert into t9
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
c10= 1, c11= 1, c12 = 1,
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
c16= '11:11:11', c17= '2004',
c18= 1, c19=true, c20= 'a', c21= '123456789a', 
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
insert into t9
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
c10= 9, c11= 9, c12 = 9,
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
c16= '11:11:11', c17= '2004',
c18= 1, c19=false, c20= 'a', c21= '123456789a', 
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
commit ;
insert into t1 values(0,NULL) ;
set @duplicate='duplicate ' ;
set @1000=1000 ;
set @5=5 ;
select a,b from t1 where a < 5 order by a ;
a	b
0	NULL
1	one
2	two
3	three
4	four
insert into t1 select a + @1000, concat(@duplicate,b) from t1
where a < @5 ;
affected rows: 5
info: Records: 5  Duplicates: 0  Warnings: 0
select a,b from t1 where a >= 1000 order by a ;
a	b
1000	NULL
1001	duplicate one
1002	duplicate two
1003	duplicate three
1004	duplicate four
delete from t1 where a >= 1000 ;
prepare stmt1 from ' insert into t1 select a + ?, concat(?,b) from t1
where a < ? ' ;
execute stmt1 using @1000, @duplicate, @5;
affected rows: 5
info: Records: 5  Duplicates: 0  Warnings: 0
select a,b from t1 where a >= 1000 order by a ;
a	b
1000	NULL
1001	duplicate one
1002	duplicate two
1003	duplicate three
1004	duplicate four
delete from t1 where a >= 1000 ;
set @1=1 ;
set @2=2 ;
set @100=100 ;
set @float=1.00;
set @five='five' ;
drop table if exists t2;
create table t2 like t1 ;
insert into t2 (b,a) 
select @duplicate, sum(first.a) from t1 first, t1 second
where first.a <> @5 and second.b = first.b