3.2.9.3. Tdsql-Mysql

3.2.9.3.1. Sequence 序列

描述
序列(Sequence)是数据库按照一定规则生成的自增数字序列。因其自增的特性,通常被用作主键和唯一键
序列的取值方法
  1. CURRVAL:返回序列的当前值。

  2. 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 约束的条件中。

序列值迁移到TDSQL-MySQL

为了能够在TDSQL-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;

-- 转换后TDSQL-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|

-- 转换后TDSQL-MySQL:
SELECT `unisql`.nextval('UNISQL_SEQ') FROM dual;
`unisql`.nextval('SEQ_1')|
-------------------------+
                        3|

-- 执行CURRVAL函数
-- 转换前Oracle SQL:
SELECT UNISQL_SEQ.currval FROM dual;
CURRVAL|
-------+
    3|

-- 转换后TDSQL-MySQL:
SELECT `unisql`.currval('UNISQL_SEQ') FROM dual;
`unisql`.currval('UNISQL_SEQ')|
--------------------------------+
                              3|

3.2.9.3.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.3.2. ROWID 伪列

暂不支持

描述
ROWID 伪列提供了快速定位表中某一行的能力,ROWID 值由主键编码得到,不会进行实际的存储

3.2.9.3.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|

-- 转换后TDSQL-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|

-- 转换后TDSQL-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|

-- 转换后TDSQL-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|

-- 转换后TDSQL-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 |

-- 转换后TDSQL-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 |