【LightDB】 mysq用户变量

MySQL用户变量

mysql用户变量是一个常用语法特性。常用来计算行号和实现分析函数类似的功能。LightDB为了方便用户迁移，23.1支持这一重要特性了。下面以MySQL变量常用的两个场景场景为例演示下用法：

计算行号

create database test with lightdb_syntax_compatible_type mysql;
\c test;
lightdb@test=# create table test_int(a int, b int);
t_int values(8,2);
insert into test_int values(9,1);CREATE TABLE
lightdb@test=# insert into test_int values(2,1);
INSERT 0 1
lightdb@test=# insert into test_int values(1,3);
INSERT 0 1
lightdb@test=# insert into test_int values(3,4);
INSERT 0 1
lightdb@test=# insert into test_int values(4,6);
INSERT 0 1
lightdb@test=# insert into test_int values(5,7);
INSERT 0 1
lightdb@test=# insert into test_int values(7,9);
INSERT 0 1
lightdb@test=# insert into test_int values(8,2);
INSERT 0 1
lightdb@test=# insert into test_int values(9,1);
INSERT 0 1
lightdb@test=# select * from (select @row_num := @row_num + 1 as row_num ,t.* from test_int t,(select @row_num:= 0) as s  order by a ) s 
where s.row_num between 5 and 10;
 row_num | a | b 
---------+---+---
       5 | 5 | 7
       6 | 7 | 9
       7 | 8 | 2
       8 | 9 | 1
(4 rows)

模拟分析函数

lightdb@test=# CREATE TABLE products(
lightdb@test(#     id varchar(10),
lightdb@test(#     name text,
lightdb@test(#     price numeric,
lightdb@test(#     uid varchar(14),
lightdb@test(#     type varchar(100)
lightdb@test(# );
, '电器');
lightdb@test=# INSERT INTO products VALUES ('0006', 'iPhone X', '9600', null, '电器');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0012', '电视', '3299', '4', '电器');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0004', '辣条', '5.6', '4', '零食');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0007', '薯条', '7.5', '1', '零食');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0009', '方便面', '3.5', '1', '零食');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0005', '铅笔', '7', '4', '文具');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0014', '作业本', '1', null, '文具');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0001', '鞋子', '27', '2', '衣物');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0002', '外套', '110.9', '3', '衣物');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0013', '围巾', '93', '5', '衣物');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0008', '香皂', '17.5', '2', '日用品');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0010', '水杯', '27', '3', '日用品');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0015', '洗发露', '36', '1', '日用品');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0011', '毛巾', '15', '1', '日用品');
INSERT 0 1
lightdb@test=# INSERT INTO products VALUES ('0003', '手表', '1237.55', '5', '电器');
INSERT 0 1
lightdb@test=# select id,type,name,price, @rownumber := @rownumber + 1 as  idx from products as a,(select @rownumber := 0) as b order by price
lightdb@test-# ;
  id  |  type  |   name   |  price  | idx 
------+--------+----------+---------+-----
 0014 | 文具   | 作业本   |       1 |   1
 0009 | 零食   | 方便面   |     3.5 |   2
 0004 | 零食   | 辣条     |     5.6 |   3
 0005 | 文具   | 铅笔     |       7 |   4
 0007 | 零食   | 薯条     |     7.5 |   5
 0011 | 日用品 | 毛巾     |      15 |   6
 0008 | 日用品 | 香皂     |    17.5 |   7
 0010 | 日用品 | 水杯     |      27 |   8
 0001 | 衣物   | 鞋子     |      27 |   9
 0015 | 日用品 | 洗发露   |      36 |  10
 0013 | 衣物   | 围巾     |      93 |  11
 0002 | 衣物   | 外套     |   110.9 |  12
 0003 | 电器   | 手表     | 1237.55 |  13
 0012 | 电器   | 电视     |    3299 |  14
 0006 | 电器   | iPhone X |    9600 |  15
(15 rows)