sp.result

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

cs	90	cs2	92
drop table t3|
delete from t1|
drop procedure create_select|
drop function if exists e|
create function e() returns double
return 2.7182818284590452354|
set @e = e()|
select e(), @e|
e()	@e
2.718281828459	2.718281828459
drop function if exists inc|
create function inc(i int) returns int
return i+1|
select inc(1), inc(99), inc(-71)|
inc(1)	inc(99)	inc(-71)
2	100	-70
drop function if exists mul|
create function mul(x int, y int) returns int
return x*y|
select mul(1,1), mul(3,5), mul(4711, 666)|
mul(1,1)	mul(3,5)	mul(4711, 666)
1	15	3137526
drop function if exists append|
create function append(s1 char(8), s2 char(8)) returns char(16)
return concat(s1, s2)|
select append("foo", "bar")|
append("foo", "bar")
foobar
drop function if exists fac|
create function fac(n int unsigned) returns bigint unsigned
begin
declare f bigint unsigned default 1;
while n > 1 do
set f = f * n;
set n = n - 1;
end while;
return f;
end|
select fac(1), fac(2), fac(5), fac(10)|
fac(1)	fac(2)	fac(5)	fac(10)
1	2	120	3628800
drop function if exists fun|
create function fun(d double, i int, u int unsigned) returns double
return mul(inc(i), fac(u)) / e()|
select fun(2.3, 3, 5)|
fun(2.3, 3, 5)
176.58213176229
insert into t2 values (append("xxx", "yyy"), mul(4,3), e())|
insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))|
select * from t2 where s = append("a", "b")|
s	i	d
ab	24	1324.36598821719
select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)|
s	i	d
xxxyyy	12	2.71828182845905
ab	24	1324.36598821719
select * from t2 where d = e()|
s	i	d
xxxyyy	12	2.71828182845905
select * from t2|
s	i	d
xxxyyy	12	2.71828182845905
ab	24	1324.36598821719
delete from t2|
drop function e|
drop function inc|
drop function mul|
drop function append|
drop function fun|
drop procedure if exists hndlr1|
create procedure hndlr1(val int)
begin
declare x int default 0;
declare foo condition for 1136;
declare bar condition for sqlstate '42S98';        # Just for testing syntax
declare zip condition for sqlstate value '42S99';  # Just for testing syntax
declare continue handler for foo set x = 1;
insert into test.t1 values ("hndlr1", val, 2);  # Too many values
if (x) then
insert into test.t1 values ("hndlr1", val);   # This instead then
end if;
end|
call hndlr1(42)|
select * from t1|
id	data
hndlr1	42
delete from t1|
drop procedure hndlr1|
drop procedure if exists hndlr2|
create procedure hndlr2(val int)
begin
declare x int default 0;
begin
declare exit handler for sqlstate '21S01' set x = 1;
insert into test.t1 values ("hndlr2", val, 2); # Too many values
end;
insert into test.t1 values ("hndlr2", x);
end|
call hndlr2(42)|
select * from t1|
id	data
hndlr2	1
delete from t1|
drop procedure hndlr2|
drop procedure if exists hndlr3|
create procedure hndlr3(val int)
begin
declare x int default 0;
declare continue handler for sqlexception        # Any error
begin
declare z int;
set z = 2 * val;
set x = 1;
end;
if val < 10 then
begin
declare y int;
set y = val + 10;
insert into test.t1 values ("hndlr3", y, 2);  # Too many values
if x then
insert into test.t1 values ("hndlr3", y);
end if;
end;
end if;
end|
call hndlr3(3)|
select * from t1|
id	data
hndlr3	13
delete from t1|
drop procedure hndlr3|
create table t3 ( id   char(16), data int )|
drop procedure if exists hndlr4|
create procedure hndlr4()
begin
declare x int default 0;
declare val int;	                           # No default
declare continue handler for sqlstate '02000' set x=1;
select data into val from test.t3 where id='z' limit 1;  # No hits
insert into test.t3 values ('z', val);
end|
call hndlr4()|
select * from t3|
id	data
z	NULL
drop table t3|
drop procedure hndlr4|
drop procedure if exists cur1|
create procedure cur1()
begin
declare a char(16);
declare b int;
declare c double;
declare done int default 0;
declare c cursor for select * from test.t2;
declare continue handler for sqlstate '02000' set done = 1;
open c;
repeat
fetch c into a, b, c;
if not done then
insert into test.t1 values (a, b+c);
end if;
until done end repeat;
close c;
end|
insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)|
call cur1()|
select * from t1|
id	data
foo	40
bar	15
zap	663
drop procedure cur1|
create table t3 ( s char(16), i int )|
drop procedure if exists cur2|
create procedure cur2()
begin
declare done int default 0;
declare c1 cursor for select id,data from test.t1;
declare c2 cursor for select i from test.t2;
declare continue handler for sqlstate '02000' set done = 1;
open c1;
open c2;
repeat
begin
declare a char(16);
declare b,c int;
fetch from c1 into a, b;
fetch next from c2 into c;
if not done then
if b < c then
insert into test.t3 values (a, b);
else
insert into test.t3 values (a, c);
end if;
end if;
end;
until done end repeat;
close c1;
close c2;
end|
call cur2()|
select * from t3|
s	i
foo	40
bar	3
zap	663
delete from t1|
delete from t2|
drop table t3|
drop procedure cur2|
drop procedure if exists chistics|
create procedure chistics()
language sql
modifies sql data
not deterministic
sql security definer
comment 'Characteristics procedure test'
  insert into t1 values ("chistics", 1)|
show create procedure chistics|
Procedure	sql_mode	Create Procedure
chistics		CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
    MODIFIES SQL DATA
    COMMENT 'Characteristics procedure test'
insert into t1 values ("chistics", 1)
call chistics()|
select * from t1|
id	data
chistics	1
delete from t1|
alter procedure chistics sql security invoker|
show create procedure chistics|
Procedure	sql_mode	Create Procedure
chistics		CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Characteristics procedure test'
insert into t1 values ("chistics", 1)
drop procedure chistics|
drop function if exists chistics|
create function chistics() returns int
language sql
deterministic
sql security invoker
comment 'Characteristics procedure test'
  return 42|
show create function chistics|
Function	sql_mode	Create Function
chistics		CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Characteristics procedure test'
return 42
select chistics()|
chistics()
42
alter function chistics
no sql
comment 'Characteristics function test'|
show create function chistics|
Function	sql_mode	Create Function
chistics		CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Characteristics function test'
return 42
drop function chistics|
insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
set @@sql_mode = 'ANSI'|
drop procedure if exists modes$
create procedure modes(out c1 int, out c2 int)
begin
declare done int default 0;
declare x int;
declare c cursor for select data from t1;
declare continue handler for sqlstate '02000' set done = 1;
select 1 || 2 into c1;
set c2 = 0;
open c;
repeat
fetch c into x;
if not done then
set c2 = c2 + 1;
end if;
until done end repeat;
close c;
end$
set @@sql_mode = ''|
set sql_select_limit = 1|
call modes(@c1, @c2)|
set sql_select_limit = default|
select @c1, @c2|
@c1	@c2
12	3
delete from t1|
drop procedure modes|
create database sp_db1|
drop database sp_db1|
create database sp_db2|
use sp_db2|
create table t3 ( s char(4), t int )|
insert into t3 values ("abcd", 42), ("dcba", 666)|
use test|
drop database sp_db2|
create database sp_db3|
use sp_db3|
drop procedure if exists dummy|
create procedure dummy(out x int)
set x = 42|
use test|
drop database sp_db3|
select type,db,name from mysql.proc where db = 'sp_db3'|
type	db	name
drop procedure if exists rc|
create procedure rc()
begin
delete from t1;
insert into t1 values ("a", 1), ("b", 2), ("c", 3);
end|
call rc()|
select row_count()|
row_count()
3
update t1 set data=42 where id = "b";
select row_count()|
row_count()
1
delete from t1|
select row_count()|
row_count()
3
delete from t1|
select row_count()|
row_count()
0
select * from t1|
id	data
select row_count()|
row_count()
-1
drop procedure rc|
drop function if exists f0|
drop function if exists f1|
drop function if exists f2|
drop function if exists f3|
drop function if exists f4|
drop function if exists f5|
drop function if exists f6|
drop function if exists f7|
drop function if exists f8|
drop function if exists f9|
drop function if exists f10|
drop function if exists f11|
drop function if exists f12_1|
drop function if exists f12_2|
drop view if exists v0|
drop view if exists v1|
drop view if exists v2|
delete from t1|
delete from t2|
insert into t1 values ("a", 1), ("b", 2) |
insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
create function f1() returns int
return (select sum(data) from t1)|
select f1()|
f1()
3
select id, f1() from t1|
id	f1()
a	3
b	3
create function f2() returns int
return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
select f2()|
f2()
1
select id, f2() from t1|
id	f2()
a	1
b	1
create function f3() returns int
begin
declare n int;
declare m int;
set n:= (select min(data) from t1);
set m:= (select max(data) from t1);
return n < m;
end|
select f3()|
f3()
1
select id, f3() from t1|
id	f3()
a	1
b	1
select f1(), f3()|
f1()	f3()
3	1
select id, f1(), f3() from t1|
id	f1()	f3()
a	3	1
b	3	1
create function f4() returns double 
return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
select f4()|
f4()
2
select s, f4() from t2|
s	f4()
a	2
b	2
c	2
create function f5(i int) returns int
begin
if i <= 0 then
return 0;
elseif i = 1  then
return (select count(*) from t1 where data = i);
else
return (select count(*) + f5( i - 1) from t1 where data = i);
end if;
end|
select f5(1)|
f5(1)
1
select f5(2)|
ERROR HY000: Recursive stored functions and triggers are not allowed.
select f5(3)|
ERROR HY000: Recursive stored functions and triggers are not allowed.
create function f6() returns int
begin
declare n int;
set n:= f1();
return (select count(*) from t1 where data <= f7() and data <= n);
end|
create function f7() returns int
return (select sum(data) from t1 where data <= f1())|
select f6()|
f6()
2
select id, f6() from t1|
id	f6()
a	2
b	2
create view v1 (a) as select f1()|
select * from v1|
a
3
select id, a from t1, v1|
id	a
a	3
b	3
select * from v1, v1 as v|
a	a
3	3
create view v2 (a) as select a*10 from v1|
select * from v2|
a
30
select id, a from t1, v2|
id	a
a	30
b	30
select * from v1, v2|
a	a
3	30
create function f8 () returns int
return (select count(*) from v2)|
select *, f8() from v1|
a	f8()
3	1
drop function f1|
select * from v1|
ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
create function f1() returns int
return (select sum(data) from t1) + (select sum(data) from v1)|
select f1()|
ERROR HY000: Recursive stored functions and triggers are not allowed.
select * from v1|
ERROR HY000: Recursive stored functions and triggers are not allowed.
select * from v2|
ERROR HY000: Recursive stored functions and triggers are not allowed.
drop function f1|
create function f1() returns int
return (select sum(data) from t1)|
create function f0() returns int
return (select * from (select 100) as r)|
select f0()|
f0()
100
select *, f0() from (select 1) as t|
1	f0()
1	100
create view v0 as select f0()|
select * from v0|
f0()
100
select *, f0() from v0|
f0()	f0()
100	100
lock tables t1 read, t1 as t11 read|
select f3()|
f3()
1
select id, f3() from t1 as t11|
id	f3()
a	1
b	1
select f0()|
f0()
100
select * from v0|
f0()
100
select *, f0() from v0, (select 123) as d1|
f0()	123	f0()
100	123	100
select id, f3() from t1|
ERROR HY000: Table 't1' was not locked with LOCK TABLES
select f4()|
ERROR HY000: Table 't2' was not locked with LOCK TABLES
unlock tables|
lock tables v2 read, mysql.proc read|
select * from v2|
a
30
select * from v1|
a
3
select * from v1, t1|
ERROR HY000: Table 't1' was not locked with LOCK TABLES
select f4()|
ERROR HY000: Table 't2' was not locked with LOCK TABLES
unlock tables|
create function f9() returns int
begin
declare a, b int;
drop temporary table if exists t3;
create temporary table t3 (id int);
insert into t3 values (1), (2), (3);
set a:= (select count(*) from t3);
set b:= (select count(*) from t3 t3_alias);
return a + b;
end|
select f9()|
f9()
6
Warnings:
Note	1051	Unknown table 't3'
select f9() from t1 limit 1|
f9()
6
create function f10() returns int
begin
drop temporary table if exists t3;
create temporary table t3 (id int);
insert into t3 select id from t4;
return (select count(*) from t3);
end|
select f10()|
ERROR 42S02: Table 'test.t4' doesn't exist
create table t4 as select 1 as id|
select f10()|
f10()
1
create function f11() returns int