mysql 视图 行号_MySQL踩坑记之视图添加行号
MySQL踩坑记之视图添加行号
问题背景
最近有一个需求, 需要给数据库中某系数据添加一个行号, 简单来说, 可以认为是按照某种规则进行排序后, 输出每一行的顺序. 这里本以为会进展的比较顺利(内心其实慌得一匹), 虽然作为一个老的Django用户, 我已经好久没有动过原生的SQL语句了, 努(绞尽)力(脑汁)回忆一把之前学过的数据库知识, 想到了这个应该是可以利用视图来实现的, 心想这里加一个行号不就OK了吗?
经过一顿冥(上)思(网)苦(搜)想(索), 发现这个对于Oracle来说, 是有自带的函数的, 可怜我用的MySQL, 又不能直接换成Oracle, 只能自己实现, 哎, 感觉自己头发少了好多, 我太难了, 在这里找解决方案的时候, 我遇到了不少的问题, 下面简单记录一下我解决问题的过程, 后面所有的表我都屏蔽了当时的业务, 按照最简化的原则处理.
初始表结构
在这里, 为了简化, 我们只用两个字段, name 和 score, 然后我们按照score的高低, 进行排序, 添加一列rank字段.
| name | score |
------------------
| Tom | 100 |
| Smith | 80 |
然后我们要创建的视图如下:
| name | score | rank |
--------------------------
| Tom | 100 | 1 |
| Smith | 80 | 2 |
解决历程
方案一: 查询添加中间变量
虽然作为一个很久不用原生SQL的人, 但是我还是依稀的记得上课中所提到的中间变量的方法的, 所以说最先想到的方案自然是添加查询中间变量了.
SELECT
@i := @i + 1 AS rank,
demo.name,
demo.score
FROM
demo,
( SELECT @i := 0 ) derivedTable
ORDER BY score DESC
这样查询出来, 结果是对的, 当时的心情, 可谓是一个开心.
但是这种开心, 一会儿就被接下来的问题给我打断了, 也就是说, 这样查询是没毛病的, 但是吧, 如果你用视图, 会发现一个惊喜的错误.
CREATE
OR REPLACE VIEW vwx ( name, score, rank ) AS SELECT
demo.name,
demo.score,
@i := @i + 1 AS rank
FROM
demo,
( SELECT @i := 0 ) derivedTable
ORDER BY score DESC;
错误提示:
1351 - View's SELECT contains a variable or parameter, Time: 0.000000s
这句话的意思是, 您不能再视图中使用参数哦, 哎, 多么友好的提示, 在MySQL中, 数据库视图是不能够使用参数的, 之前学过的知识是还给老师了, 但是不影响, 实践是检验真理的唯一标准, 没成功, 哈哈, 就说明这样不行.
方案二: 自增函数
emmm, 如果不能用变量, 那我们只能换一种思路了, 我们能不能用函数呢? 写一个函数, 让每次返回的值都自动+1不就好了, 下面就是这个函数.
CREATE DEFINER=`root`@`localhost` FUNCTION `func_inc_var_session`(RESET BIT) RETURNS int(11)
NO SQL
BEGIN
IF RESET THEN
SET @var := 0;
ELSE
SET @var := IFNULL(@var,0) + 1;
END IF;
RETURN @var;
END
这个函数也是比较容易理解, 在这里就不多解释了, 实际上有两个功能置零和自增, 为什么要设置置零的操作呢? 在这里先卖一个关子, 后文再说, 然后我们接着再来利用这个函数来创建视图.
CREATE
OR REPLACE VIEW vwx ( name, score, rank ) AS SELECT
demo.name,
demo.score,
func_inc_var_session(0)
FROM
demo
ORDER BY NAME DESC;
我们来看一下效果
这样看起来是不是很"完美"的实现蛤, 大家看到我给完美添加了引号, 也就说明这个写法是有缺陷的, 哎, 如果我们重新建立视图, 这个就会变成.
是不是很惊喜, 这个值是全局变量, 是不会自己改成0的.
方案三: 自增函数+每次重置
哎, 蓝瘦, 这样可不行啊, 这样虽然说是完成自增行号了, 但是吧是不符合我业务上的需求的, 因此, 我们要想办法, 怎么执行的时候给重置一下, 当然你也可以选择创建临时表, 这种操作对于我来说感觉太麻烦了, 为了视图, 我每次创建一张表, 这个开销也不小, 然后, 灵机一动, 有了最新的方案, 我们可以在每次执行的时候都来一个重置, 哈哈, 这里解释一下前文说为什么我要设置重置变量的功能, 到这里就用上了, 因为我已经写到这里了, 但是代码我又懒得改回去了, 加上这个参数多几行代码, 不太影响前文的思路, 因此, 我就没去掉.
OR REPLACE VIEW vwx ( NAME, score, rank ) AS SELECT
demo.name,
demo.score,
func_inc_var_session ( 0 )
FROM
demo
JOIN ( SELECT func_inc_var_session ( 1 ) ) derivedTable
ORDER BY score DESC;
这样就可以了, 每次刷新视图的时候序号都是固定的了, 哈哈, 这个问题我也想了好久, 最终解决了, 感觉我对于SQL的使用还是太生疏了, 不过感觉Oracle是真的好, 自带这个函数, 不过毕竟人家是收费的, 咱用着免费的, 也不能奢求太多, 哈哈, 到这里问题就解决完成了, 可能对于大佬们来说, 这个并不难, 如果大家有更好的写法, 也欢迎告诉我.