Oracle里,dual是一个只有一行数据的虚表,目的只是为了SQL语句结构的完整性,本文介绍在GBase 8a数据库集群里实现的dual。
目录导航
用法
默认是可以不用写from dual的。如下是对照的例子。
gbase> select 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (Elapsed: 00:00:00.00)
gbase> select 1+1 from dual;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (Elapsed: 00:00:00.03)
gbase> select now() from dual;
+---------------------+
| now() |
+---------------------+
| 2021-02-24 10:03:52 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select now();
+---------------------+
| now() |
+---------------------+
| 2021-02-24 10:03:58 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)
元数据
在GBase 8a里,内部是通过在gclusterdb.dual表来实现的。
如下例子,如果单独写dual会兼容oracle的虚表,为了当成表名字,可以加上库名或者加上界定符,比如`,或者双引号 "。其实质就是一个有一行数据的复制表。
警告:请不要人工修改系统元数据表,否则会造成执行失败或结果集错误。
gbase> use gclusterdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> select * from dual;
ERROR 1096 (HY000): No tables used
gbase>
gbase> select * from `dual`;
+-------+
| dummy |
+-------+
| X |
+-------+
1 row in set (Elapsed: 00:00:00.01)
gbase> select * from gclusterdb.dual;
+-------+
| dummy |
+-------+
| X |
+-------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show create table gclusterdb.dual;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| dual | CREATE TABLE "dual" (
"dummy" varchar(1) DEFAULT NULL
) ENGINE=EXPRESS REPLICATED DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
注意事项
如果是新建用户,需要授权gclusterdb.dual表的select权限,否则查询dual会报访问拒绝的错误。ERROR 1142 (42000): SELECT command denied to user for table 'dual'
gbase> select 1+1 from dual;
ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'dual'
gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli gclusterdb
GBase client 8.6.2.43-R28 .125499. Copyright (c) 2004-2021, GBase. All Rights Reserved.
gbase> grant select on gclusterdb.dual to test;
Query OK, 0 rows affected (Elapsed: 00:00:00.05)
gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -utest -ptest
GBase client 8.6.2.43-R28 .125499. Copyright (c) 2004-2021, GBase. All Rights Reserved.
gbase> select 1+1 from dual;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (Elapsed: 00:00:00.01)