ps.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 1,690 行 · 第 1/4 页

create table t1 (a int);
insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
prepare stmt from "select sql_calc_found_rows * from t1 limit 2";
execute stmt;
a
1
2
select found_rows();
found_rows()
10
execute stmt;
a
1
2
select found_rows();
found_rows()
10
execute stmt;
a
1
2
select found_rows();
found_rows()
10
deallocate prepare stmt;
drop table t1;
CREATE TABLE t1 (N int, M tinyint);
INSERT INTO t1 VALUES (1,0),(1,0),(2,0),(2,0),(3,0);
PREPARE stmt FROM 'UPDATE t1 AS P1 INNER JOIN (SELECT N FROM t1 GROUP BY N HAVING COUNT(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
prepare stmt from "select ? is null, ? is not null, ?";
select @no_such_var is null, @no_such_var is not null, @no_such_var;
@no_such_var is null	@no_such_var is not null	@no_such_var
1	0	NULL
execute stmt using @no_such_var, @no_such_var, @no_such_var;
? is null	? is not null	?
1	0	NULL
set @var='abc';
select @var is null, @var is not null, @var;
@var is null	@var is not null	@var
0	1	abc
execute stmt using @var, @var, @var;
? is null	? is not null	?
0	1	abc
set @var=null;
select @var is null, @var is not null, @var;
@var is null	@var is not null	@var
1	0	NULL
execute stmt using @var, @var, @var;
? is null	? is not null	?
1	0	NULL
create table t1 (pnum char(3));
create table t2 (pnum char(3));
prepare stmt from "select pnum from t2 having pnum in (select 'p1' from t1)";
execute stmt;
pnum
execute stmt;
pnum
execute stmt;
pnum
deallocate prepare stmt;
drop table t1, t2;
drop table if exists t1;
create temporary table if not exists t1 (a1 int);
prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
drop temporary table t1;
create temporary table if not exists t1 (a1 int);
execute stmt;
drop temporary table t1;
create temporary table if not exists t1 (a1 int);
execute stmt;
drop temporary table t1;
create temporary table if not exists t1 (a1 int);
execute stmt;
drop temporary table t1;
deallocate prepare stmt;
create table t1 (a varchar(20));
insert into t1 values ('foo');
prepare stmt FROM 'SELECT char_length (a) FROM t1';
ERROR 42000: FUNCTION test.char_length does not exist
drop table t1;
prepare stmt from "SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0";
execute stmt;
foo
SELECT FOUND_ROWS();
FOUND_ROWS()
2
execute stmt;
foo
SELECT FOUND_ROWS();
FOUND_ROWS()
2
deallocate prepare stmt;
drop table if exists t1;
Warnings:
Note	1051	Unknown table 't1'
create table t1 (c1 int(11) not null, c2 int(11) not null,
primary key  (c1,c2), key c2 (c2), key c1 (c1));
insert into t1 values (200887, 860);
insert into t1 values (200887, 200887);
select * from t1 where (c1=200887 and c2=200887) or c2=860;
c1	c2
200887	860
200887	200887
prepare stmt from
"select * from t1 where (c1=200887 and c2=200887) or c2=860";
execute stmt;
c1	c2
200887	860
200887	200887
prepare stmt from
"select * from t1 where (c1=200887 and c2=?) or c2=?";
set @a=200887, @b=860;
execute stmt using @a, @b;
c1	c2
200887	860
200887	200887
deallocate prepare stmt;
drop table t1;
create table t1 (
id bigint(20) not null auto_increment,
code varchar(20) character set utf8 collate utf8_bin not null default '',
company_name varchar(250) character set utf8 collate utf8_bin default null,
setup_mode tinyint(4) default null,
start_date datetime default null,
primary key  (id), unique key code (code)
);
create table t2 (
id bigint(20) not null auto_increment,
email varchar(250) character set utf8 collate utf8_bin default null,
name varchar(250) character set utf8 collate utf8_bin default null,
t1_id bigint(20) default null,
password varchar(250) character set utf8 collate utf8_bin default null,
primary_contact tinyint(4) not null default '0',
email_opt_in tinyint(4) not null default '1',
primary key  (id), unique key email (email), key t1_id (t1_id),
constraint t2_fk1 foreign key (t1_id) references t1 (id)
);
insert into t1 values
(1, 'demo', 'demo s', 0, current_date()),
(2, 'code2', 'name 2', 0, current_date()),
(3, 'code3', 'name 3', 0, current_date());
insert into t2 values
(2, 'email1', 'name1', 3, 'password1', 0, 0),
(3, 'email2', 'name1', 1, 'password2', 1, 0),
(5, 'email3', 'name3', 2, 'password3', 0, 0);
prepare stmt from 'select t2.id from t2, t1 where (t1.id=? and t2.t1_id=t1.id)';
set @a=1;
execute stmt using @a;
id
3
select t2.id from t2, t1 where (t1.id=1 and t2.t1_id=t1.id);
id
3
deallocate prepare stmt;
drop table t1, t2;
create table t1 (id int);
prepare stmt from "insert into t1 (id) select id from t1 union select id from t1";
execute stmt;
execute stmt;
deallocate prepare stmt;
drop table t1;
create table t1 (
id int(11) unsigned not null primary key auto_increment,
partner_id varchar(35) not null,
t1_status_id int(10) unsigned
);
insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
("3", "partner3", "10"), ("4", "partner4", "10");
create table t2 (
id int(11) unsigned not null default '0',
t1_line_id int(11) unsigned not null default '0',
article_id varchar(20),
sequence int(11) not null default '0',
primary key  (id,t1_line_id)
);
insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
("4", "1", "sup", "0");
create table t3 (
id int(11) not null default '0',
preceeding_id int(11) not null default '0',
primary key  (id,preceeding_id)
);
create table t4 (
user_id varchar(50) not null,
article_id varchar(20) not null,
primary key  (user_id,article_id)
);
insert into t4 values("nicke", "imp");
prepare stmt from
'select distinct t1.partner_id
from t1 left join t3 on t1.id = t3.id
     left join t1 pp on pp.id = t3.preceeding_id
where
  exists (
    select *
    from t2 as pl_inner
    where pl_inner.id = t1.id
    and pl_inner.sequence <= (
      select min(sequence) from t2 pl_seqnr
      where pl_seqnr.id = t1.id
    )
    and exists (
      select * from t4
      where t4.article_id = pl_inner.article_id
      and t4.user_id = ?
    )
  )
  and t1.id = ?
group by t1.id
having count(pp.id) = 0';
set @user_id = 'nicke';
set @id = '2';
execute stmt using @user_id, @id;
partner_id
execute stmt using @user_id, @id;
partner_id
deallocate prepare stmt;
drop table t1, t2, t3, t4;
prepare stmt from 'select ?=?';
set @a='CHRISTINE           ';
set @b='CHRISTINE';
execute stmt using @a, @b;
?=?
1
execute stmt using @a, @b;
?=?
1
set @a=1, @b=2;
execute stmt using @a, @b;
?=?
0
set @a='CHRISTINE           ';
set @b='CHRISTINE';
execute stmt using @a, @b;
?=?
1
deallocate prepare stmt;
create table t1 (a int);
prepare stmt from "select ??";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
prepare stmt from "select ?FROM t1";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?FROM t1' at line 1
prepare stmt from "select FROM t1 WHERE?=1";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM t1 WHERE?=1' at line 1
prepare stmt from "update t1 set a=a+?WHERE 1";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?WHERE 1' at line 1
select ?;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
select ??;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??' at line 1
select ? from t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? from t1' at line 1
drop table t1;
prepare stmt from "select @@time_zone";
execute stmt;
@@time_zone
SYSTEM
set @@time_zone:='Japan';
execute stmt;
@@time_zone
Japan
prepare stmt from "select @@tx_isolation";
execute stmt;
@@tx_isolation
REPEATABLE-READ
set transaction isolation level read committed;
execute stmt;
@@tx_isolation
READ-COMMITTED
set transaction isolation level serializable;
execute stmt;
@@tx_isolation
SERIALIZABLE
set @@tx_isolation=default;
execute stmt;
@@tx_isolation
REPEATABLE-READ
deallocate prepare stmt;
prepare stmt from "create temporary table t1 (letter enum('','a','b','c')
not null)";
execute stmt;
drop table t1;
execute stmt;
drop table t1;
execute stmt;
drop table t1;
set names latin1;
prepare stmt from "create table t1 (a enum('test') default 'test')
 character set utf8";
execute stmt;
drop table t1;
execute stmt;
drop table t1;
execute stmt;
drop table t1;
set names default;
deallocate prepare stmt;
create table t1 (
word_id mediumint(8) unsigned not null default '0',
formatted varchar(20) not null default ''
);
insert into t1 values
(80,'pendant'), (475,'pretendants'), (989,'tendances'),
(1019,'cependant'),(1022,'abondance'),(1205,'independants'),
(13,'lessiver'),(25,'lambiner'),(46,'situer'),(71,'terminer'),
(82,'decrocher');
select count(*) from t1 where formatted like '%NDAN%';
count(*)
6
select count(*) from t1 where formatted like '%ER';
count(*)
5
prepare stmt from "select count(*) from t1 where formatted like ?";
set @like="%NDAN%";
execute stmt using @like;
count(*)
6
set @like="%ER";
execute stmt using @like;
count(*)
5
set @like="%NDAN%";
execute stmt using @like;
count(*)
6
set @like="%ER";
execute stmt using @like;
count(*)
5
deallocate prepare stmt;
drop table t1;
prepare stmt from 'create table t1 (a varchar(10) character set utf8)';
execute stmt;
insert into t1 (a) values (repeat('a', 20));
select length(a) from t1;
length(a)
10
drop table t1;
execute stmt;
insert into t1 (a) values (repeat('a', 20));
select length(a) from t1;
length(a)
10
drop table t1;
deallocate prepare stmt;
create table t1 (col1 integer, col2 integer);
insert into t1 values(100,100),(101,101),(102,102),(103,103);
prepare stmt from 'select col1, col2 from t1 where (col1, col2) in ((?,?))';
set @a=100, @b=100;
execute stmt using @a,@b;
col1	col2
100	100
set @a=101, @b=101;
execute stmt using @a,@b;
col1	col2
101	101
set @a=102, @b=102;
execute stmt using @a,@b;
col1	col2
102	102
set @a=102, @b=103;
execute stmt using @a,@b;
col1	col2
deallocate prepare stmt;
drop table t1;
set @old_max_prepared_stmt_count= @@max_prepared_stmt_count;
show variables like 'max_prepared_stmt_count';
Variable_name	Value
max_prepared_stmt_count	16382
show status like 'prepared_stmt_count';
Variable_name	Value
Prepared_stmt_count	0
select @@max_prepared_stmt_count;
@@max_prepared_stmt_count
16382
set global max_prepared_stmt_count=-1;
select @@max_prepared_stmt_count;
@@max_prepared_stmt_count
0
set global max_prepared_stmt_count=10000000000000000;
select @@max_prepared_stmt_count;
@@max_prepared_stmt_count
1048576
set global max_prepared_stmt_count=default;
select @@max_prepared_stmt_count;
@@max_prepared_stmt_count
16382
set @@max_prepared_stmt_count=1;
ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL
set max_prepared_stmt_count=1;
ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL
set local max_prepared_stmt_count=1;
ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL
set global max_prepared_stmt_count=1;
select @@max_prepared_stmt_count;
@@max_prepared_stmt_count
1
set global max_prepared_stmt_count=0;
select @@max_prepared_stmt_count;
@@max_prepared_stmt_count
0
show status like 'prepared_stmt_count';
Variable_name	Value
Prepared_stmt_count	0
prepare stmt from "select 1";
ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 0)
show status like 'prepared_stmt_count';
Variable_name	Value
Prepared_stmt_count	0
set global max_prepared_stmt_count=1;
prepare stmt from "select 1";
show status like 'prepared_stmt_count';
Variable_name	Value
Prepared_stmt_count	1
prepare stmt1 from "select 1";
ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 1)
show status like 'prepared_stmt_count';
Variable_name	Value