3.2.9.4. Oceanbase-Mysql
3.2.9.4.1. Sequence 序列
- 描述
- 序列（Sequence）是数据库按照一定规则生成的自增数字序列。因其自增的特性，通常被用作主键和唯一键
- 序列的取值方法
CURRVAL：返回序列的当前值。
NEXTVAL：返回序列的下一个自增值。
- 说明
- 使用序列伪列时，必须在 CURRVAL 和 NEXTVAL 前带上序列的名称，并用句点（.）引用。例如：序列的名称为 SEQ_1，则可以通过 SEQ_1.CURRVAL 获取 SEQ_1 序列的当前值。同样，可以通过 SEQ_1.NEXTVAL 获取 SEQ_1 序列的下一个自增值。
序列值的应用场景
通过 CURRVAL 和 NEXTVAL 引用的序列值可以用于以下位置：
非子查询或者视图中的 SELECT 语句的选择列表中。
INSERT 语句中子查询的选择列表中。
INSERT 语句中的 VALUE 子句中。
UPDATE 语句中的 SET 子句中。
序列 CURRVAL 和 NEXTVAL 的值不能用于以下位置：
DELETE、SELECT 或者 UPDATE 语句的子查询中。
视图的查询中。
带 DISTINCT 运算符的 SELECT 语句中。
带 GROUP BY 子句或者 ORDER BY 子句的 SELECT 语句中。
与另一个 SELECT 语句通过 UNION、INTERSECT 或者 MINUS 集合运算符进行联合的 SELECT 语句中。
SELECT 语句的 WHERE 子句中。
CREATE TABLE 或者 ALTER TABLE 语句中列的 DEFAULT 值。
CHECK 约束的条件中。
序列值迁移到Oceanbase-MySQL
为了能够在Oceanbase-MySQL顺利使用序列（Sequence）需要执行 MySQL 目标脚本；
-- 创建序列表
CREATE TABLE if not exists unisql.`UNISQL_SEQUENCE` (
`SEQ_CODE` VARCHAR(64) NOT NULL COMMENT '序列代码',
`SEQ_NAME` varchar(256) DEFAULT NULL COMMENT '序列名称',
`CURRENT_VAL` BIGINT(16) NOT NULL COMMENT '当前序列值',
`INCREMENT_VAL` BIGINT(16) NOT NULL DEFAULT '1' COMMENT '增长值',
PRIMARY KEY (`SEQ_CODE`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='序列表';
-- 创建CURRVAL函数
DELIMITER $$
DROP FUNCTION IF EXISTS unisql.`CURRVAL` $$
CREATE FUNCTION unisql.`CURRVAL` (V_SEQ_CODE VARCHAR (64))
RETURNS BIGINT (16)
READS SQL DATA
BEGIN
DECLARE VALUE BIGINT;
SELECT CURRENT_VAL INTO VALUE FROM unisql.`UNISQL_SEQUENCE` WHERE SEQ_CODE = V_SEQ_CODE;
if VALUE is null then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Sequence not found';
end if;
RETURN VALUE;
END $$
DELIMITER ;
-- 创建NEXTVAL函数
DELIMITER $$
DROP FUNCTION IF EXISTS unisql.`NEXTVAL`$$
CREATE FUNCTION unisql.`NEXTVAL`(V_SEQ_CODE VARCHAR(64))
RETURNS BIGINT(16)
MODIFIES SQL DATA
BEGIN
UPDATE unisql.UNISQL_SEQUENCE SET CURRENT_VAL = CURRENT_VAL + INCREMENT_VAL WHERE SEQ_CODE = V_SEQ_CODE;
if ROW_COUNT() = 0 then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Sequence not found';
end if;
RETURN unisql.CURRVAL(V_SEQ_CODE);
END$$
DELIMITER ;
-- 创建SETVAL函数
DELIMITER $$
DROP FUNCTION IF EXISTS unisql.`SETVAL`$$
CREATE FUNCTION unisql.`SETVAL`(V_SEQ_CODE VARCHAR(64), VALUE BIGINT)
RETURNS BIGINT(16)
BEGIN
UPDATE unisql.UNISQL_SEQUENCE SET CURRENT_VAL = VALUE WHERE SEQ_CODE = V_SEQ_CODE;
if ROW_COUNT() = 0 then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Sequence not found';
end if;
RETURN unisql.CURRVAL(V_SEQ_CODE);
END$$
DELIMITER ;
示例
-- 创建序列
-- 转换前Oracle SQL：
CREATE SEQUENCE UNISQL_SEQ
START WITH 3
INCREMENT BY 2
NOCACHE;
-- 转换后Oceanbase-MySQL：
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('UNISQL_SEQ',1,2)
-- 执行NEXTVAL函数
-- 转换前Oracle SQL：
SELECT UNISQL_SEQ.nextval FROM dual;
NEXTVAL|
-------+
3|
-- 转换后Oceanbase-MySQL：
SELECT `unisql`.nextval('UNISQL_SEQ') FROM dual;
`unisql`.nextval('SEQ_1')|
-------------------------+
3|
-- 执行CURRVAL函数
-- 转换前Oracle SQL：
SELECT UNISQL_SEQ.currval FROM dual;
CURRVAL|
-------+
3|
-- 转换后Oceanbase-MySQL：
SELECT `unisql`.currval('UNISQL_SEQ') FROM dual;
`unisql`.currval('UNISQL_SEQ')|
--------------------------------+
3|
3.2.9.4.1.1. 使用注意事项
当currval,nextval作为表字段和sequence_name.currval,sequence_name.nextval出现在同一个SQL语句时,建议表字段使用双引号引起来,否则转换后可能得到和预期不一致的结果。 同时注意表的别名和sequence_name不要相同,会造成一定的歧义。
-- 注意以下SQL语句为原始的Oracle语句,非转换后的语句
-- 如果表结构定义如下：
DROP TABLE unisql_key_test_2;
CREATE TABLE unisql_key_test_2(
id NUMBER,
currval varchar(10),
nextval varchar(10)
);
INSERT INTO unisql_key_test_2(currval,nextval) values('currval1','nextval1');
-- 同时使用不支持,转换结果可能和预期不一致。
SELECT unisql_seq_1.nextval,nextval,unisql_seq_1.currval,currval FROM unisql_key_test_2;
-- 推荐使用方式：
-- 如果需要同时使用，表字段请用双引号包裹起来
CREATE TABLE unisql_key_test_3(
id NUMBER,
"currval" varchar(10),
"nextval" varchar(10)
);
INSERT INTO unisql_key_test_3("currval","nextval") values('currval1','nextval1');
SELECT unisql_seq_1.nextval,"nextval",unisql_seq_1.currval,"currval" FROM unisql_key_test_3;
-- 注意以下语句转换会得到和预期不一样的结果
SELECT unisql_seq_1.nextval,unisql_seq_1.lnextval,unisql_seq_1.currval,unisql_seq_1.currval FROM unisql_key_test_1 unisql_seq_1;
3.2.9.4.2. ROWID 伪列
暂不支持
- 描述
- ROWID 伪列提供了快速定位表中某一行的能力，ROWID 值由主键编码得到，不会进行实际的存储
3.2.9.4.3. ROWNUM 伪列
- 描述
- ROWNUM 伪列会对查询结果中的每一行进行编号，其值为该行在查询结果集中的具体位置。例如，第一行返回值 1，第二行返回值 2，之后以此类推
示例
-- 创建表
CREATE TABLE unisql_emp_msg (
deptno INT,
ename VARCHAR(50),
sal INT
);
-- 准备数据
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (10, 'CLARK', 2750);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (10, 'KING', 5300);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (10, 'MILLER', 1600);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (20, 'ADAMS', 1400);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (20, 'FORD', 3300);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (20, 'JONES', 3275);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (20, 'SMITH', 3300);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (20, 'CLARK', 1100);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (30, 'ALLEN', 1900);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (30, 'BLAKE', 3150);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (30, 'JAMES', 1250);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (30, 'MARTIN', 1550);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (30, 'TURNER', 1800);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (30, 'WARD', 1550);
INSERT INTO unisql_emp_msg (deptno, ename, sal) VALUES (30, 'SCLARK', 1750);
-- 转换前Oracle SQL：
SELECT rownum,deptno,ename FROM unisql_emp_msg;
ROWNUM|DEPTNO|ENAME |
------+------+------+
1| 10|CLARK |
2| 10|KING |
3| 10|MILLER|
4| 20|ADAMS |
5| 20|FORD |
6| 20|JONES |
7| 20|SMITH |
8| 20|CLARK |
9| 30|ALLEN |
10| 30|BLAKE |
11| 30|JAMES |
12| 30|MARTIN|
13| 30|TURNER|
14| 30|WARD |
15| 30|SCLARK|
-- 转换后Oceanbase-MySQL：
SELECT @`rownum1`:=@`rownum1`+1,`uni_sub_t`.* FROM (SELECT `deptno`,`ename` FROM `unisql_emp_msg`) AS `uni_sub_t`, (SELECT @`rownum1`:=0) AS `unisql_rownum_tmp`;
@`rownum1`:=@`rownum1`+1|deptno|ename |
------------------------+------+------+
1.0| 10|CLARK |
2.0| 10|KING |
3.0| 10|MILLER|
4.0| 20|ADAMS |
5.0| 20|FORD |
6.0| 20|JONES |
7.0| 20|SMITH |
8.0| 20|CLARK |
9.0| 30|ALLEN |
10.0| 30|BLAKE |
11.0| 30|JAMES |
12.0| 30|MARTIN|
13.0| 30|TURNER|
14.0| 30|WARD |
15.0| 30|SCLARK|
-- 转换前Oracle SQL：
select * from (SELECT rownum,deptno,ename,sal FROM unisql_emp_msg);
ROWNUM|DEPTNO|ENAME |SAL |
------+------+------+----+
1| 10|CLARK |2750|
2| 10|KING |5300|
3| 10|MILLER|1600|
4| 20|ADAMS |1400|
5| 20|FORD |3300|
6| 20|JONES |3275|
7| 20|SMITH |3300|
8| 20|CLARK |1100|
9| 30|ALLEN |1900|
10| 30|BLAKE |3150|
11| 30|JAMES |1250|
12| 30|MARTIN|1550|
13| 30|TURNER|1800|
14| 30|WARD |1550|
15| 30|SCLARK|1750|
-- 转换后Oceanbase-MySQL：
SELECT * FROM (SELECT @`rownum1`:=@`rownum1`+1,`uni_sub_t`.* FROM (SELECT `deptno`,`ename`,`sal` FROM `unisql_emp_msg`) AS `uni_sub_t`, (SELECT @`rownum1`:=0) AS `unisql_rownum_tmp`) AS `uni_sub`;
@`rownum1`:=@`rownum1`+1|deptno|ename |sal |
------------------------+------+------+----+
1.0| 10|CLARK |2750|
2.0| 10|KING |5300|
3.0| 10|MILLER|1600|
4.0| 20|ADAMS |1400|
5.0| 20|FORD |3300|
6.0| 20|JONES |3275|
7.0| 20|SMITH |3300|
8.0| 20|CLARK |1100|
9.0| 30|ALLEN |1900|
10.0| 30|BLAKE |3150|
11.0| 30|JAMES |1250|
12.0| 30|MARTIN|1550|
13.0| 30|TURNER|1800|
14.0| 30|WARD |1550|
15.0| 30|SCLARK|1750|
-- 转换前Oracle SQL：
SELECT rownum,m.* FROM (SELECT rownum,deptno,ename,sal FROM unisql_emp_msg UNION ALL SELECT rownum,deptno,ename,sal FROM unisql_emp_msg) m;
ROWNUM|ROWNUM|DEPTNO|ENAME |SAL |
------+------+------+------+----+
1| 1| 10|CLARK |2750|
2| 2| 10|KING |5300|
3| 3| 10|MILLER|1600|
4| 4| 20|ADAMS |1400|
5| 5| 20|FORD |3300|
6| 6| 20|JONES |3275|
7| 7| 20|SMITH |3300|
8| 8| 20|CLARK |1100|
9| 9| 30|ALLEN |1900|
10| 10| 30|BLAKE |3150|
11| 11| 30|JAMES |1250|
12| 12| 30|MARTIN|1550|
13| 13| 30|TURNER|1800|
14| 14| 30|WARD |1550|
15| 15| 30|SCLARK|1750|
16| 1| 10|CLARK |2750|
17| 2| 10|KING |5300|
18| 3| 10|MILLER|1600|
19| 4| 20|ADAMS |1400|
20| 5| 20|FORD |3300|
21| 6| 20|JONES |3275|
22| 7| 20|SMITH |3300|
23| 8| 20|CLARK |1100|
24| 9| 30|ALLEN |1900|
25| 10| 30|BLAKE |3150|
26| 11| 30|JAMES |1250|
27| 12| 30|MARTIN|1550|
28| 13| 30|TURNER|1800|
29| 14| 30|WARD |1550|
30| 15| 30|SCLARK|1750|
-- 转换后Oceanbase-MySQL：
SELECT @`rownum1`:=@`rownum1`+1,`uni_sub_t`.* FROM (SELECT `m`.* FROM (SELECT @`rownum2`:=@`rownum2`+1,`uni_sub_t`.* FROM (SELECT `deptno`,`ename`,`sal` FROM `unisql_emp_msg`) AS `uni_sub_t`, (SELECT @`rownum2`:=0) AS `unisql_rownum_tmp` UNION ALL SELECT @`rownum3`:=@`rownum3`+1,`uni_sub_t`.* FROM (SELECT `deptno`,`ename`,`sal` FROM `unisql_emp_msg`) AS `uni_sub_t`, (SELECT @`rownum3`:=0) AS `unisql_rownum_tmp`) AS `m`) AS `uni_sub_t`, (SELECT @`rownum1`:=0) AS `unisql_rownum_tmp`;
@`rownum1`:=@`rownum1`+1|@`rownum2`:=@`rownum2`+1|deptno|ename |sal |
------------------------+------------------------+------+------+----+
1.0| 1.0| 10|CLARK |2750|
2.0| 2.0| 10|KING |5300|
3.0| 3.0| 10|MILLER|1600|
4.0| 4.0| 20|ADAMS |1400|
5.0| 5.0| 20|FORD |3300|
6.0| 6.0| 20|JONES |3275|
7.0| 7.0| 20|SMITH |3300|
8.0| 8.0| 20|CLARK |1100|
9.0| 9.0| 30|ALLEN |1900|
10.0| 10.0| 30|BLAKE |3150|
11.0| 11.0| 30|JAMES |1250|
12.0| 12.0| 30|MARTIN|1550|
13.0| 13.0| 30|TURNER|1800|
14.0| 14.0| 30|WARD |1550|
15.0| 15.0| 30|SCLARK|1750|
16.0| 1.0| 10|CLARK |2750|
17.0| 2.0| 10|KING |5300|
18.0| 3.0| 10|MILLER|1600|
19.0| 4.0| 20|ADAMS |1400|
20.0| 5.0| 20|FORD |3300|
21.0| 6.0| 20|JONES |3275|
22.0| 7.0| 20|SMITH |3300|
23.0| 8.0| 20|CLARK |1100|
24.0| 9.0| 30|ALLEN |1900|
25.0| 10.0| 30|BLAKE |3150|
26.0| 11.0| 30|JAMES |1250|
27.0| 12.0| 30|MARTIN|1550|
28.0| 13.0| 30|TURNER|1800|
29.0| 14.0| 30|WARD |1550|
30.0| 15.0| 30|SCLARK|1750|
-- 转换前Oracle SQL
SELECT rownum,deptno,ename FROM unisql_emp_msg ke WHERE rownum = 1;
ROWNUM|DEPTNO|ENAME|
------+------+-----+
1| 10|CLARK|
-- 转换后Oceanbase-MySQL：
SELECT @`rownum1`:=@`rownum1`+1,`uni_sub_t`.* FROM (SELECT `deptno`,`ename` FROM `unisql_emp_msg` AS `ke` LIMIT 1) AS `uni_sub_t`, (SELECT @`rownum1`:=0) AS `unisql_rownum_tmp` LIMIT 1;
@`rownum1`:=@`rownum1`+1|deptno|ename|
------------------------+------+-----+
1.0| 10|CLARK|
-- 转换前Oracle SQL：
SELECT rownum,deptno,ename FROM unisql_emp_msg ke WHERE rownum <= 4;
ROWNUM|DEPTNO|ENAME |
------+------+------+
1| 10|CLARK |
2| 10|KING |
3| 10|MILLER|
4| 20|ADAMS |
-- 转换后Oceanbase-MySQL：
SELECT @`rownum1`:=@`rownum1`+1,`uni_sub_t`.* FROM (SELECT `deptno`,`ename` FROM `unisql_emp_msg` AS `ke` LIMIT 4) AS `uni_sub_t`, (SELECT @`rownum1`:=0) AS `unisql_rownum_tmp` LIMIT 4;
@`rownum1`:=@`rownum1`+1|deptno|ename |
------------------------+------+------+
1.0| 10|CLARK |
2.0| 10|KING |
3.0| 10|MILLER|
4.0| 20|ADAMS |