MYSQL 之伤, 那些我们看不见的坑。
说说本地变量在SQL语句中的作用。
利用变量如@a,常见的,用作排序,作数据分析计算等。
但是当我们的SQL语句足够复杂,这时我的大脑还没疯掉,可是MYSQL的SQL Engineer却傻B了。
而且更气人的是,这玩意儿竟然还不报错。默默地返回一个错误的数值给我。
我们还能不能愉快地玩耍了,作为小小的DBA,表示很受伤。
扔个代码给你们,自己体会我的痛苦:
select
content_id, title, media_path, media_duration, media_size, img_addr, channel_id, owner_id,owner_name,
is_virtual_publish, upload_user_id, update_date, status, review_status, review_time, show_order, release_date, modify_time,offline_reason,transcode_status
from (
select
b.content_id,
b.title,
ifnull(b.media_path,'null') media_path,
b.media_duration,
b.media_size,
ifnull(b.title_img, c.img_addr ) img_addr,
a.channel_id,
a.user_id owner_id,
u.nickname owner_name,
case when a.user_id in (1,2) then '1' else '0' end is_virtual_publish,
a.user_id upload_user_id,
a.update_date,
'0'status,
'1' review_status,
b.release_date review_time,
case when @cn=c.id then @so:=@so+1 else @so:=1 end show_order ,
case when @cn=c.id then @so:=@so else @cn:= c.id end emo ,
b.release_date,
null modify_time,
null offline_reason,
'0' transcode_status
from jc_content a, jc_content_ext b, ayst_share.album_info c , jc_user u,(select @cn:=0, @so:=1) m
where a.content_id = b.content_id
and a.channel_id = c.id
and a.user_id = u.user_id
and a.status=2
order by b.release_date,c.id
)v
;
我特擅长写复杂及NB的SQL,毕竟我干了10多年了纯DBA了。但是我也极其讨厌写复杂的SQL。
不是因为我不会写,而是因为我的理念和追求 。
代码应该是简洁的,清晰明了的。就像架构一样。 愚蠢的人才会搞得那么复杂,到最后自己被自己搞乱。
言归正传,说上面的这个代码,错误根本在于MYSQL在多表关联时,变量计算的时间不是最后排序以后,而是在排序之前都计算好了,这完全不是我的本意。知道这个原因后,解决起来也简单,正确姿势如下:
解决方法:
1, 可以在数据导入到目标表后再计算:
set @so=0, @cn=0 ;
update audio_file_info a
set a.show_order= case when @cn= album_id then @so:=@so+1 else @so:=1 end ,
a.album_id= case when @cn= album_id then @cn:=@cn else @cn:= album_id end
ORDER BY album_id, release_time desc, a.id
2, 多加一层嵌套,在临时表(虚拟视图)上作变量计算:
select
content_id, title, media_path, media_duration, media_size, img_addr, channel_id, owner_id,owner_name,
is_virtual_publish, upload_user_id, update_date, status, review_status, review_time, release_date, modify_time,offline_reason,transcode_status,
case when @cn=channel_id then @so:=@so+1 else @so:=1 end show_order ,
case when @cn=channel_id then @cn:=@cn else @cn:= channel_id end emo
from (
select
b.content_id,
b.title,
ifnull(b.media_path,'null') media_path,
b.media_duration,
b.media_size,
ifnull(b.title_img, c.img_addr ) img_addr,
a.channel_id,
a.user_id owner_id,
u.nickname owner_name,
case when a.user_id in (1,2) then '1' else '0' end is_virtual_publish,
a.user_id upload_user_id,
a.update_date,
'0'status,
'1' review_status,
b.release_date review_time,
b.release_date,
null modify_time,
null offline_reason,
'0' transcode_status
from jc_content a, jc_content_ext b, ayst_share.album_info c , jc_user u
where a.content_id = b.content_id
and a.channel_id = c.id
and a.user_id = u.user_id
and a.status=2
order by c.id ,b.release_date,b.content_id
)v,(select @cn:=0, @so:=1) m
好了,方法我都教给你们了。师傅领进门,修行靠个人。
最后,希望大家都能每天愉快地工作。少些BUG,少些烦恼。