MySQL自定义变量的三个小技巧

高性能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));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1414176/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1414176/