高性能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博客

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

最新文章