VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

@variable变量

功能描述

Vastbase在MySQL兼容模式下,支持用户变量@variable的以下两种使用形式:

  • 支持使用SET命令声明@variable,形如:set @variable = valueset @variable := value,参考兼容性手册:SET

  • 支持使用SELECT命令为@variable变量赋值,形如select @variable := value

利用@variable特性能够实现SQL中的递归查询,详见示例3。递归查询是一种特殊的查询技术,它通过循环调用一个单独的查询来遍历整个数据集。递归查询通常用于查询树形结构或图形结构数据。这些数据结构通常有父节点和子节点之间的关系。

注意事项

  • 此功能仅在数据库兼容模式为MySQL时支持(即数据库实例初始化时指定DBCOMPATIBILITY='B')。
  • 使用此功能需开启参数enable_set_variable_b_format,表示允许数据库在MySQL兼容模式下使用自定义用户变量的功能。

  • 用户变量是针对当前登录Vastbase的用户的私有变量,声明过的@variable变量在客户端连接到数据库实例的整个过程中都是有效的。

  • SET命令支持的赋值符号可以是:==,而select赋值时仅支持使用:=

  • 若使用prepare from为SQL语句命名时,用户自定义变量存储的字符串仅支持select、insert、update、delete、merge语法,且必须是单条语句。

  • 无论SET或是SELECT都可以同时对多个变量进行赋值。

示例

示例1: 使用SET命令为变量赋值。

1、设置参数enable_set_variable_b_format为on。

SET enable_set_variable_b_format=on;

2、使用SET定义变量。

set @VAR1_1102053=123;
set @var2_1102053:=1111::int4;
set @var3_1102053 := @var6_1102053 := @$var7_1102053:=12345678::int8;

3、直接查看变量的值。

select @VAR1_1102053,@VAR2_1102053,@VAR3_1102053;

返回结果如下:

 @var1_1102053 | @var2_1102053 | @var3_1102053
---------------+---------------+---------------
           123 |          1111 |      12345678
(1 row)

4、创建测试表。

create table table_1102053 (id int,name text);

5、向测试表中插入数据时使用声明过的变量。

insert into table_1102053 values(@VAR1_1102053,'test');

6、查看测试表数据。

select * from table_1102053;

返回结果如下,变量@VAR1_1102053的值被插入到了表中对应位置:

 id  | name
-----+------
 123 | test
(1 row)

示例2: SELECT直接为变量赋值并查看。

1、设置参数enable_set_variable_b_format为on。

SET enable_set_variable_b_format=on;

2、执行如下SELECT语句。

select @var:=1 as col1,@va:=3;

返回结果如下:

 col1 | ?column?
------+----------
    1 |        3
(1 row)

示例3: 使用@变量实现递归查询。

1、设置参数enable_set_variable_b_format为on。

SET enable_set_variable_b_format=on;

2、创建测试表并插入数据。

CREATE TABLE my_table_1162203 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
parent_id INT,
CONSTRAINT uc_name UNIQUE (name),
CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES my_table_1162203(id)
);
-- 插入数据
INSERT INTO my_table_1162203 (name, parent_id) VALUES
('a1', 1),
('a2', 2),
('a3', 1),
('a4', 2),
('a5', 1),
('a6', 2);

3、查看测试表数据。

select * from my_table_1162203;

返回结果如下,三个字段的含义分别记为每条记录的ID,名称,父节点ID。

 id | name | parent_id
----+------+-----------
  1 | a1   |         1
  2 | a2   |         2
  3 | a3   |         1
  4 | a4   |         2
  5 | a5   |         1
  6 | a6   |         2
(6 rows)

4、使用SET命令为变量赋值,指定@parent_id为1。

SET @parent_id := 1;

5、检索给定节点的所有兄弟节点,返回所有父节点ID为1的记录。

SELECT id, name
FROM my_table_1162203
WHERE parent_id = (
SELECT parent_id
FROM my_table_1162203
WHERE id = @parent_id
);

返回结果如下:

 id | name
----+------
  1 | a1
  3 | a3
  5 | a5
(3 rows)

6、以下语句等效于步骤4(定义变量)和步骤5(查询)的结合,在一个语句里实现了同样的递归查询效果:

SELECT id, name
FROM my_table_1162203
WHERE parent_id = (
SELECT parent_id
FROM my_table_1162203
WHERE id =(select @parent_id:= 1)
);

返回结果如下:

 id | name
----+------
  1 | a1
  3 | a3
  5 | a5
(3 rows)

示例4: 使用set语法创建prepare语句。

1、设置参数enable_set_variable_b_format为on。

SET enable_set_variable_b_format=on;

2、创建测试表。

create table tb1(id int);

3、使用set语法将两个自定义变量赋值为字符串,内容为SQL语句。

set @sql1:='insert into tb1 values(5)';  --语句1
set @sql2:='select * from tb1';          --语句2

4、为步骤3中的两条SQL语句命名。

PREPARE stmt1 from @sql1;
PREPARE stmt2 from @sql2;

5、执行语句1,执行插入动作。

EXECUTE stmt1;

6、执行语句2,查看测试表数据。

EXECUTE stmt2;

返回结果为:

 id             
----
  5
(1 row)