高性能MySQL 244页
1.指定表的访问顺序
实验数据:
user是活跃用户表,
user_archived是长时间不活跃的用户表.
两个表的结构相同,冷热数据分开.
create table user
(
id int primary key,
name varchar(20)
);
create table user_archived
(
id int primary key,
name varchar(20)
);
insert into user select 1,‘user1‘;
insert into user select 2,‘user2‘;
insert into user_archived select 3,‘user3‘;
insert into user_archived select 4,‘user4‘;
commit;
需求:
先访问user表数据,
如果有结果,则直接返回,不再查询user_archived表;
如果没有结果,则查询user_archived表的数据.
实现:
set @userid:=1;
select
greatest(@found:=-1,id) as id,name,‘user‘ tabname
from user where id=@userid
union all
select id,name,‘user_archived‘
from user_archived where id=@userid and @found is null
union all
select 1,1,‘reset‘ from dual where (@found:=null) is not null;
第三个SQL先执行赋值,
第一个SQL如果查到任意一个数据,就会修改@found的值,
第二个SQL根据@found的值,决定是否执行SQL
2.更新的同时,顺带查询数据
实验数据:
drop table test;
create table test
(
id int primary key,
name varchar(20),
ts timestamp ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
insert into test(id,name) select 1,‘user1‘;
commit;
更新这个数据,并查询这个数据之前的时间戳.
update test set name=‘u1‘ where id=1 and @now:=ts ;
select @now;
3.统计on duplicate key update更新的数量
实验数据:
create table t1
(
a int primary key,
b int
);
insert into t1 values(1,1),(2,2),(3,3),(4,4);
commit;
执行on duplicate key update
set @x:=0;
insert into t1 values(2,100),(3,100)
on duplicate key update b=values(b)+(0*(@x:=@x+1));
MySQL自定义变量的三个小技巧
原文：http://blog.itpub.net/29254281/viewspace-1414176/
