5.3. SQL 语法
警告
下面列举的是一些在使用过程中会遇到的部分疑问点，在使用数据库时，如果对数据完全一致要求较高，现有的函数处理能力不能满足业务需求，建议进行业务SQL改写或者对结果进行进一步处理
Oracle和 TDSQL-MySQL 在字符集和排序上存在一定的差异，排序时请注意根据参与排序字段要能得到唯一序列结果集，否则可能会出现在两个库查询的结果顺序不一致。
5.3.1. 查询和子查询
查询是指数据库中用来获取数据的方式，它可搭配条件限制子句（例如 WHERE），排列顺序子句（例如 ORDER BY）等语句来获取查询结果。
子查询是指嵌套在一个上层查询中的查询。上层的查询一般被称为父查询或外部查询。子查询的结果作为输入传递回父查询或外部查询。父查询将这个值结合到计算中，以便确定最后的输出。
SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。同时，子查询可以出现在 SQL 语句中的各种子句中，例如 SELECT 语句、FROM 语句和 WHERE 语句等。
SQL 语句中常见的查询类型如下：
5.3.1.1. 简单查询
简单查询是指从数据库的一个或多个选择列表或视图中检索一个或多个列数据，列的数量以及它们的数据类型和长度由选择列表的元素确定。而选择列表指的是
SELECT关键字之后和FROM子句之前的表达式列表。如果两个或多个表有一些共同的列名，则必须用表名限定列名。您可以使用列别名
col_alias来标记紧接在选择列表之前的表达式。别名可以在ORDER BY子句中使用，但不能在查询中的其他子句中使用。简单查询的
SELECT语法如下：simple_select: SELECT [ DISTINCT | UNIQUE | ALL] select_expr_list FROM from_list [WHERE condition] [GROUP BY group_expression_list [{ROLLUP | GROUPING SETS} group_expression_list] [HAVING condition] ] [ORDER BY order_expression_list] [FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ] [row_limiting_clause ] select_expr_list: table_name.* | table_alias_name.* | expr [[AS] column_alias_name] from_list: table_reference [, table_reference...] table_reference: simple_table | joined_table | pivot_clause | unpivot_clause | table_name@dblink_name simple_table: table_factor [partition_option] [[table_alias_name] | (select_stmt) table_alias_name | (table_reference_list) joined_table: table_reference [INNER] JOIN simple_table [join_condition] | table_reference outer_join_type JOIN simple_table join_condition partition_option: PARTITION (partition_name_list) partition_name_list: partition_name [, partition_name...] outer_join_type: {LEFT | RIGHT | FULL} [OUTER] join_condition: ON expression condition: expression group_expression_list: group_expression [, group_expression...] group_expression: expression [ASC | DESC] order_expression_list: order_expression [, order_expression...] order_expression: expression [ASC | DESC] row_limiting_clause: [ OFFSET offset { ROW | ROWS } ] [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES } ] pivot_clause： PIVOT (aggregate_function ( expr ) [[AS] alias ] [, aggregate_function ( expr ) [[AS] alias ]... ] pivot_for_clause pivot_in_clause ) pivot_for_clause： FOR { column } pivot_in_clause IN ( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... } [, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ] ) unpivot_clause ： UNPIVOT ( { column } pivot_for_clause unpivot_in_clause ) unpivot_in_clause： IN ( { column | ( column [, column... ]) } )CREATE TABLE unisql_simple_query_employee ( employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50), manager_id INT, salary NUMERIC ); INSERT INTO unisql_simple_query_employee VALUES(1, 'DEL', 'FA BEN', 1, 1500); INSERT INTO unisql_simple_query_employee VALUES(2, 'AXEL', 'BELL', 1, 1000); INSERT INTO unisql_simple_query_employee VALUES(3, 'CRIS', 'RACHAR', 1, 1000); INSERT INTO unisql_simple_query_employee VALUES(4, 'BlAIR', 'WALDORF', 1, 800); -- 转换前Oracle SQL： -- 查询表中部分列 SELECT first_name, last_name, salary FROM unisql_simple_query_employee; -- 查询表中所有列 SELECT * FROM unisql_simple_query_employee; -- 对表中的列进行数学运算 SELECT salary+100 FROM unisql_simple_query_employee; -- 给表中的列取别名 SELECT salary*12 AS year_salary FROM unisql_simple_query_employee; -- 将表中的数据去重 SELECT DISTINCT manager_id FROM unisql_simple_query_employee; -- 使用 CASE WHEN 语句查询表中的数据 SELECT salary, CASE WHEN salary >= 1200 then 'high' WHEN salary >= 1000 THEN 'middle' ELSE 'low' END AS type FROM unisql_simple_query_employee; -- 转换后TDSQL-MySQL： SELECT `first_name`,`last_name`,`salary` FROM `unisql_simple_query_employee`; SELECT * FROM `unisql_simple_query_employee`; SELECT `salary`+100 FROM `unisql_simple_query_employee`; SELECT `salary`*12 AS `year_salary` FROM `unisql_simple_query_employee`; SELECT DISTINCT `manager_id` FROM `unisql_simple_query_employee`; SELECT `salary`,CASE WHEN `salary`>=1200 THEN 'high' WHEN `salary`>=1000 THEN 'middle' ELSE 'low' END AS `type` FROM `unisql_simple_query_employee`;
5.3.1.1.1. 分区表查询
-- 创建表 CREATE TABLE unisql_partition ( unisql_id NUMBER, unisql_name VARCHAR2(50), unisql_salary NUMBER(10,2), unisql_hire_date DATE, CONSTRAINT unisql_partition_pk PRIMARY KEY (unisql_id, unisql_hire_date) ) PARTITION BY RANGE (unisql_hire_date) ( PARTITION unisql_partition_p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')), PARTITION unisql_partition_p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) ); -- 插入数据 INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (1, 'John', 5000, TO_DATE('2023-01-01','YYYY-MM-DD')); INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (2, 'Mary', 6000, TO_DATE( '2023-02-15','YYYY-MM-DD')); INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (3, 'Tom', 7000, TO_DATE( '2023-03-20','YYYY-MM-DD')); INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (4, 'Alice', 8000, TO_DATE('2023-04-10','YYYY-MM-DD')); INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (5, 'Bob', 9000, TO_DATE('2023-05-01','YYYY-MM-DD')); INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (6, 'Bill', 1000, TO_DATE('2024-05-02','YYYY-MM-DD')); -- 转换前Oracle SQL： SELECT unisql_id,unisql_name,unisql_salary, unisql_hire_date FROM unisql_partition PARTITION (unisql_partition_p1); UNISQL_ID|UNISQL_NAME|UNISQL_SALARY|UNISQL_HIRE_DATE | ---------+-----------+-------------+-----------------------+ 1|John | 5000|2023-01-01 00:00:00.000| 2|Mary | 6000|2023-02-15 00:00:00.000| 3|Tom | 7000|2023-03-20 00:00:00.000| 4|Alice | 8000|2023-04-10 00:00:00.000| 5|Bob | 9000|2023-05-01 00:00:00.000| -- 转换后TDSQL-MySQL： SELECT `unisql_id`,`unisql_name`,`unisql_salary`,`unisql_hire_date` FROM `unisql_partition` PARTITION(`unisql_partition_p1`); unisql_id|unisql_name|unisql_salary|unisql_hire_date | ---------+-----------+-------------+-----------------------+ 1|John | 5000.00|2023-01-01 00:00:00.000| 2|Mary | 6000.00|2023-02-15 00:00:00.000| 3|Tom | 7000.00|2023-03-20 00:00:00.000| 4|Alice | 8000.00|2023-04-10 00:00:00.000| 5|Bob | 9000.00|2023-05-01 00:00:00.000|
5.3.1.1.2. pivot行转列
-- 创建表 CREATE TABLE unisql_pivot(id int , name varchar(64), score NUMBER , subject varchar2(64)); -- 插入数据 INSERT INTO unisql_pivot VALUES(1,'张三',70,'CHINESE'); INSERT INTO unisql_pivot VALUES(1,'张三',90,'MATH'); INSERT INTO unisql_pivot VALUES(1,'张三',95,'ENGLISH'); INSERT INTO unisql_pivot VALUES(2,'李四',75,'CHINESE'); INSERT INTO unisql_pivot VALUES(2,'李四',85,'MATH'); INSERT INTO unisql_pivot VALUES(2,'李四',90,'ENGLISH'); INSERT INTO unisql_pivot VALUES(3,'王五',90,'CHINESE'); INSERT INTO unisql_pivot VALUES(3,'王五',90,'MATH'); INSERT INTO unisql_pivot VALUES(3,'王五',90,'ENGLISH'); -- 转换前Oracle SQL： SELECT id, name, chinese_score, match_score, english_score FROM unisql_pivot pivot( min(score) FOR subject IN('CHINESE' AS chinese_score, 'MATH' AS match_score, 'ENGLISH' AS english_score) ) WHERE id IN (1, 2, 3) ORDER BY id; ID|NAME|CHINESE_SCORE|MATCH_SCORE|ENGLISH_SCORE| --+----+-------------+-----------+-------------+ 1|张三 | 70| 90| 95| 2|李四 | 75| 85| 90| 3|王五 | 90| 90| 90| -- 转换后TDSQL-MySQL： SELECT `id`, `name`, `chinese_score`, `match_score`, `english_score` FROM (SELECT `id`,`name`, min(CASE WHEN `subject`='CHINESE' THEN `score` END) AS `chinese_score`, min(CASE WHEN `subject`='MATH' THEN `score` END) AS `match_score`, min(CASE WHEN `subject`='ENGLISH' THEN `score` END) AS `english_score` FROM `unisql_pivot` WHERE `id` IN (1,2,3) GROUP BY `id`,`name` ORDER BY `id`) AS `uni_sub`; id|name|chinese_score|match_score|english_score| --+----+-------------+-----------+-------------+ 1|张三 | 70| 90| 95| 2|李四 | 75| 85| 90| 3|王五 | 90| 90| 90|
5.3.1.1.3. unpivot列转行
注意
统一SQL当前对unpivot转换使用限制如下：
1. 只支持在单表查询语句中使用，参考如下：
SELECT id,name,score AS sc,subject AS su FROM unisql_unpivot UNPIVOT(score FOR subject IN(chinese, math, english)) WHERE id IN (1, 2, 3,4,5) ORDER BY id;
2. 只支持单字段的unpivot,如上(score for subject)
3. 统一SQL会将unpivot in中的字段转换为大写，如上(chinese-->CHINESE, math-->MATH, english-->ENGLISH)
4. 不支持join，with, 子查询，group by, having，rownum
5. 不支持unpivot和unpivot for字段上使用函数，表达式
6. 不支持unpivot in语句中的字段使用AS
7. 分页查询存在offset时必须有fetch
8. 注意oracle和tdmysql字符集，排序规则等底层实现存在差异，排序字段需要保证是唯一序，否则可能导致两个库执行结果获取结果顺序不一致。
参考示例
-- 前置准备SQL：
CREATE TABLE unisql_unpivot(id INT,name VARCHAR(64),chinese NUMBER,math INT,english INT);
INSERT INTO unisql_unpivot VALUES(1,'张三',70,90,95);
INSERT INTO unisql_unpivot VALUES(2,'李四',75,85,90);
INSERT INTO unisql_unpivot VALUES(3,'张三',90,90,90);
drop table unisql_unpivot;
-- 转换前Oracle SQL：
SELECT id,name,score,subject FROM unisql_unpivot UNPIVOT(score FOR subject IN(chinese, math, english)) WHERE id IN (1, 2, 3) ORDER BY id;
ID|NAME|SCORE|SUBJECT|
--+----+-----+-------+
1|张三 | 70|CHINESE|
1|张三 | 90|MATH |
1|张三 | 95|ENGLISH|
2|李四 | 75|CHINESE|
2|李四 | 85|MATH |
2|李四 | 90|ENGLISH|
3|张三 | 90|CHINESE|
3|张三 | 90|MATH |
3|张三 | 90|ENGLISH|
-- 转换后TDSQL-MySQL
SELECT `id`,`name`,`chinese` AS `score`,'CHINESE' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) UNION ALL SELECT `id`,`name`,`math` AS `score`,'MATH' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) UNION ALL SELECT `id`,`name`,`english` AS `score`,'ENGLISH' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) ORDER BY `id`
id|name|score|subject|
--+----+-----+-------+
1|张三 | 70|CHINESE|
1|张三 | 95|ENGLISH|
1|张三 | 90|MATH |
2|李四 | 85|MATH |
2|李四 | 75|CHINESE|
2|李四 | 90|ENGLISH|
3|张三 | 90|MATH |
3|张三 | 90|CHINESE|
3|张三 | 90|ENGLISH|
-- 转换前Oracle SQL：
SELECT id,name,score,subject FROM unisql_unpivot UNPIVOT(score FOR subject IN(chinese, math, english)) WHERE id IN (1, 2, 3) ORDER BY id,name,score,subject OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;
ID|NAME|SCORE|SUBJECT|
--+----+-----+-------+
1|张三 | 90|MATH |
1|张三 | 95|ENGLISH|
-- 转换后TDSQL-MySQL
SELECT `id`,`name`,`chinese` AS `score`,'CHINESE' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) UNION ALL SELECT `id`,`name`,`math` AS `score`,'MATH' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) UNION ALL SELECT `id`,`name`,`english` AS `score`,'ENGLISH' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) ORDER BY `id`,`name`,`score`,`subject` LIMIT 1,2
id|name|score|subject|
--+----+-----+-------+
1|张三 | 90|MATH |
1|张三 | 95|ENGLISH|
5.3.1.2. 复合查询
使用集合运算符 UNION、UNION ALL、INTERSECT 和 MINUS 来组合多个查询，即复合查询。所有集合运算符都具有相同的优先级。
5.3.1.2.1. UNION 唯一并集
-- 创建表 CREATE TABLE unisql_collect_1( id INT, name VARCHAR(32), chinese NUMBER, math NUMBER, english NUMBER); CREATE TABLE unisql_collect_2( id INT, name VARCHAR(32), chinese NUMBER, math NUMBER, english NUMBER); -- 插入数据 INSERT INTO unisql_collect_1 VALUES(1,'张三',70,90,95); INSERT INTO unisql_collect_1 VALUES(2,'李四',75,85,90); INSERT INTO unisql_collect_1 VALUES(3,'王五',90,90,90); INSERT INTO unisql_collect_1 VALUES(4,'秦六',65,65,60); INSERT INTO unisql_collect_2 VALUES(1,'张三',70,90,95); INSERT INTO unisql_collect_2 VALUES(2,'李四',75,85,90); INSERT INTO unisql_collect_2 VALUES(4,'Bob',78,88,65); INSERT INTO unisql_collect_2 VALUES(5,'Li',90,93,93); -- 转换前Oracle SQL： SELECT * FROM unisql_collect_1 UNION SELECT * FROM unisql_collect_2; ID|NAME|CHINESE|MATH|ENGLISH| --+----+-------+----+-------+ 1|张三 | 70| 90| 95| 2|李四 | 75| 85| 90| 3|王五 | 90| 90| 90| 4|Bob | 78| 88| 65| 4|秦六 | 65| 65| 60| 5|Li | 90| 93| 93| -- 转换后TDSQL-MySQL： SELECT * FROM `unisql_collect_1` UNION SELECT * FROM `unisql_collect_2`; id|name|chinese|math|english| --+----+-------+----+-------+ 1|张三 | 70| 90| 95| 2|李四 | 75| 85| 90| 3|王五 | 90| 90| 90| 4|秦六 | 65| 65| 60| 4|Bob | 78| 88| 65| 5|Li | 90| 93| 93|
5.3.1.2.2. UNION ALL 并集
-- 转换前Oracle SQL： SELECT * FROM unisql_collect_1 UNION ALL SELECT * FROM unisql_collect_2; ID|NAME|CHINESE|MATH|ENGLISH| --+----+-------+----+-------+ 1|张三 | 70| 90| 95| 2|李四 | 75| 85| 90| 3|王五 | 90| 90| 90| 4|秦六 | 65| 65| 60| 1|张三 | 70| 90| 95| 2|李四 | 75| 85| 90| 4|Bob | 78| 88| 65| 5|Li | 90| 93| 93| -- 转换后TDSQL-MySQL： SELECT * FROM `unisql_collect_1` UNION ALL SELECT * FROM `unisql_collect_2` id|name|chinese|math|english| --+----+-------+----+-------+ 1|张三 | 70| 90| 95| 2|李四 | 75| 85| 90| 3|王五 | 90| 90| 90| 4|秦六 | 65| 65| 60| 1|张三 | 70| 90| 95| 2|李四 | 75| 85| 90| 4|Bob | 78| 88| 65| 5|Li | 90| 93| 93|
5.3.1.2.3. INTERSECT 交集
暂时不支持
5.3.1.2.4. MINUS 差集
暂时不支持
5.3.1.3. 联接查询
联接（Join）是将两个或多个表、视图的结合在一起的查询。
5.3.1.3.1. 等值联接
等值联接是包含等值运算符的联接。在进行等值联接时将指定列满足等值条件的行进行组合输出。
示例
-- 创建 unisql_orders 表 CREATE TABLE unisql_orders ( order_id NUMBER, order_date DATE, customer_id NUMBER, amount NUMBER ); -- 创建 unisql_customers 表 CREATE TABLE unisql_customers ( customer_id NUMBER, customer_name VARCHAR2(100), city VARCHAR2(100) ); -- 插入示例数据 INSERT INTO unisql_orders (order_id, order_date, customer_id, amount) VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 1000); INSERT INTO unisql_orders (order_id, order_date, customer_id, amount) VALUES (2, TO_DATE('2022-02-01', 'YYYY-MM-DD'), 1002, 1500); INSERT INTO unisql_customers (customer_id, customer_name, city) VALUES (1001, 'John Smith', 'New York'); INSERT INTO unisql_customers (customer_id, customer_name, city) VALUES (1002, 'Jane Doe', 'Los Angeles'); -- 转换前Oracle SQL： SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount FROM unisql_orders o JOIN unisql_customers c ON o.customer_id = c.customer_id; ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT| --------+-----------------------+-------------+-----------+------+ 1|2022-01-01 00:00:00.000|John Smith |New York | 1000| 2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500| -- 转换后TDSQL-MySQL： SELECT `o`.`order_id`,`o`.`order_date`,`c`.`customer_name`,`c`.`city`,`o`.`amount` FROM `unisql_orders` AS `o` INNER JOIN `unisql_customers` AS `c` ON `o`.`customer_id`=`c`.`customer_id` order_id|order_date |customer_name|city |amount| --------+-----------------------+-------------+-----------+------+ 1|2022-01-01 00:00:00.000|John Smith |New York | 1000| 2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
5.3.1.3.2. 自联接
自联接是表与其自身的联接。该表在 FROM 子句中出现两次，后跟表别名，这些别名限定联接条件中的列名。
示例
-- 创建 employees 表 CREATE TABLE employees ( employee_id NUMBER, employee_name VARCHAR2(100), manager_id NUMBER ); -- 插入示例数据 INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1001, 'John Smith', 1003); INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1002, 'Jane Doe', 1003); INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1003, 'Mike Johnson', NULL); -- 转换前Oracle SQL： SELECT e.employee_name AS employee, m.employee_name AS manager FROM employees e, employees m WHERE e.manager_id = m.employee_id; EMPLOYEE MANAGER ----------------------- John Smith Mike Johnson Jane Doe Mike Johnson -- 转换后TDSQL-MySQL： SELECT `e`.`employee_name` AS `employee`,`m`.`employee_name` AS `manager` FROM `employees` AS `e`, `employees` AS `m` WHERE `e`.`manager_id`=`m`.`employee_id` employee |manager | ----------+------------+ John Smith|Mike Johnson| Jane Doe |Mike Johnson|
5.3.1.3.3. 笛卡尔积
如果联接查询中的两个表没有联接条件，返回其笛卡尔乘积，使用第一个表的每一行与另一表每一个行进行组合输出。笛卡尔乘积会生成许多行，但是大多是无用数据。例如，两个都有 100 行的表的笛卡尔积有 10,000 行，所以除非特别需要笛卡尔乘积，建议查询中始终包括一个联接条件以避免返回笛卡尔乘积。
示例
-- 创建 colors 表 CREATE TABLE unisql_colors ( color_id NUMBER, color_name VARCHAR2(100) ); -- 创建 sizes 表 CREATE TABLE unisql_sizes ( size_id NUMBER, size_name VARCHAR2(100) ); -- 插入示例数据 INSERT INTO unisql_colors (color_id, color_name) VALUES (1, 'Red'); INSERT INTO unisql_colors (color_id, color_name) VALUES (2, 'Blue'); INSERT INTO unisql_sizes (size_id, size_name) VALUES (1, 'Small'); INSERT INTO unisql_sizes (size_id, size_name) VALUES (2, 'Medium'); -- 转换前Oracle SQL： SELECT c.color_id, c.color_name, s.size_id, s.size_name FROM unisql_colors c, unisql_sizes s; COLOR_ID|COLOR_NAME|SIZE_ID|SIZE_NAME| --------+----------+-------+---------+ 1|Red | 1|Small | 1|Red | 2|Medium | 2|Blue | 1|Small | 2|Blue | 2|Medium | -- 转换后TDSQL-MySQL： SELECT `c`.`color_id`,`c`.`color_name`,`s`.`size_id`,`s`.`size_name` FROM `unisql_colors` AS `c`, `unisql_sizes` AS `s` color_id|color_name|size_id|size_name| --------+----------+-------+---------+ 1|Red | 1|Small | 2|Blue | 1|Small | 1|Red | 2|Medium | 2|Blue | 2|Medium |
5.3.1.3.4. INNER JOIN 内联接查询
内联接基于联接条件将两张表（例如 A 和 B）的列组合在一起，产生新的结果存放在表中。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足联接条件的组合。当满足联接条件时，表 A 和 B 中匹配的行会按列组合（并排组合）成结果集中的一行。联接产生的结果集等于首先对两张表做笛卡尔积，将表 A 中的每一行和表 B 中的每一行组合，然后返回满足联接条件的记录。
示例
-- 创建 unisql_orders 表 CREATE TABLE unisql_orders ( order_id NUMBER, order_date DATE, customer_id NUMBER, amount NUMBER ); -- 创建 unisql_customers 表 CREATE TABLE unisql_customers ( customer_id NUMBER, customer_name VARCHAR2(100), city VARCHAR2(100) ); -- 插入示例数据 INSERT INTO unisql_orders (order_id, order_date, customer_id, amount) VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 1000); INSERT INTO unisql_orders (order_id, order_date, customer_id, amount) VALUES (2, TO_DATE('2022-02-01', 'YYYY-MM-DD'), 1002, 1500); INSERT INTO unisql_orders (order_id, order_date, customer_id, amount) VALUES (3, TO_DATE('2022-04-03', 'YYYY-MM-DD'), 1003, 1600); INSERT INTO unisql_customers (customer_id, customer_name, city) VALUES (1001, 'John Smith', 'New York'); INSERT INTO unisql_customers (customer_id, customer_name, city) VALUES (1002, 'Jane Doe', 'Los Angeles'); INSERT INTO unisql_customers (customer_id, customer_name, city) VALUES (1004, 'Grace Miller', 'San Francisco'); -- 转换前Oracle SQL： SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount FROM unisql_orders o INNER JOIN unisql_customers c ON o.customer_id = c.customer_id; ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT| --------+-----------------------+-------------+-----------+------+ 1|2022-01-01 00:00:00.000|John Smith |New York | 1000| 2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500| -- 转换后TDSQL-MySQL： SELECT `o`.`order_id`,`o`.`order_date`,`c`.`customer_name`,`c`.`city`,`o`.`amount` FROM `unisql_orders` AS `o` INNER JOIN `unisql_customers` AS `c` ON `o`.`customer_id`=`c`.`customer_id` order_id|order_date |customer_name|city |amount| --------+-----------------------+-------------+-----------+------+ 1|2022-01-01 00:00:00.000|John Smith |New York | 1000| 2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
5.3.1.3.5. LEFT JOIN 左外联接查询
左联接（LEFT [OUTER] JOIN）中左表行未在右表匹配到行时，在右表自动填充 NULL。
示例
-- 转换前Oracle SQL： SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount FROM unisql_orders o LEFT JOIN unisql_customers c ON o.customer_id = c.customer_id; ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT| --------+-----------------------+-------------+-----------+------+ 1|2022-01-01 00:00:00.000|John Smith |New York | 1000| 2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500| 3|2022-04-03 00:00:00.000| | | 1600| -- 转换后TDSQL-MySQL： SELECT `o`.`order_id`,`o`.`order_date`,`c`.`customer_name`,`c`.`city`,`o`.`amount` FROM `unisql_orders` AS `o` LEFT JOIN `unisql_customers` AS `c` ON `o`.`customer_id`=`c`.`customer_id`; order_id|order_date |customer_name|city |amount| --------+-----------------------+-------------+-----------+------+ 1|2022-01-01 00:00:00.000|John Smith |New York | 1000| 2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500| 3|2022-04-03 00:00:00.000| | | 1600|
5.3.1.3.6. RIGHT JOIN 右外联接查询
右联接（RIGHT [OUTER] JOIN）中右表行未在左表匹配到行时，在左表自动填充 NULL。
示例
-- 转换前Oracle SQL： SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount FROM unisql_orders o RIGHT JOIN unisql_customers c ON o.customer_id = c.customer_id; order_id|order_date |customer_name|city |amount| --------+-----------------------+-------------+-------------+------+ 1|2022-01-01 00:00:00.000|John Smith |New York | 1000| 2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500| | |Grace Miller |San Francisco| | -- 转换后TDSQL-MySQL： SELECT `o`.`order_id`,`o`.`order_date`,`c`.`customer_name`,`c`.`city`,`o`.`amount` FROM `unisql_orders` AS `o` RIGHT JOIN `unisql_customers` AS `c` ON `o`.`customer_id`=`c`.`customer_id`; order_id|order_date |customer_name|city |amount| --------+-----------------------+-------------+-------------+------+ 1|2022-01-01 00:00:00.000|John Smith |New York | 1000| 2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500| | |Grace Miller |San Francisco| |
5.3.1.3.7. FULL JOIN 全外联接查询
全联接（FULL [OUTER] JOIN）左表或者右表未在其它表匹配到行时均会填充NULL。
暂不支持
5.3.1.3.8. 外联接符号（+）
对于左联接，在 WHERE 子句的联接条件中对右表的所有列应用外联接符号（+）；对于右联接，在 WHERE 子句的联接条件中对左表的所有列应用外联接符号（+）。
示例
-- (+)左外连接 CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC'); CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b'); -- 转换前Oracle SQL： SELECT UNISQL_TABLE1.*, UNISQL_TABLE2.* FROM UNISQL_TABLE1,UNISQL_TABLE2 WHERE UNISQL_TABLE1.c1 = UNISQL_TABLE2.c1(+); C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+ 1|A |AA| 1|a |b | 2|B |BB| | | | 3|C |CC| | | | -- 转换后TDSQL-MySQL： SELECT `UNISQL_TABLE1`.*, `UNISQL_TABLE2`.* FROM `UNISQL_TABLE2` RIGHT JOIN `UNISQL_TABLE1` ON `UNISQL_TABLE1`.`c1`=`UNISQL_TABLE2`.`c1` WHERE 1=1 C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+ 1|A |AA| 1|a |b | 2|B |BB| | | | 3|C |CC| | | | -- (+)左外连接多个条件 CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC'); CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b'); -- 转换前Oracle SQL： SELECT UNISQL_TABLE1.*, UNISQL_TABLE2.* FROM UNISQL_TABLE1, UNISQL_TABLE2 WHERE UNISQL_TABLE1.c1 = UNISQL_TABLE2.c1(+) AND UNISQL_TABLE1.c2 = UNISQL_TABLE2.c2(+); C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+ 2|B |BB| | | | 1|A |AA| | | | 3|C |CC| | | | -- 转换后TDSQL-MySQL： select `UNISQL_TABLE1` .*, `UNISQL_TABLE2` .* from `UNISQL_TABLE2` right join `UNISQL_TABLE1` on `UNISQL_TABLE1`.`c1` = `UNISQL_TABLE2`.`c1` and `UNISQL_TABLE1`.`c2` = `UNISQL_TABLE2`.`c2` where 1 = 1 and 1 = 1; C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+ 1|A |AA| 1|a |b | 2|B |BB| | | | 3|C |CC| | | | -- (+)右外连接 CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC'); CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b'); -- 转换前Oracle SQL： SELECT * FROM UNISQL_TABLE1,UNISQL_TABLE2 WHERE UNISQL_TABLE1.c1(+) = UNISQL_TABLE2.c1; C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+ 1|A |AA| 1|a |b | -- 转换后TDSQL-MySQL： select * from `UNISQL_TABLE1` right join `UNISQL_TABLE2` on `UNISQL_TABLE1`.`c1` = `UNISQL_TABLE2`.`c1` where 1 = 1 C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+ 1|A |AA| 1|a |b | -- (+)右外连接多个条件 CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC'); CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b'); -- 转换前Oracle SQL： SELECT * FROM UNISQL_TABLE1,UNISQL_TABLE2 WHERE UNISQL_TABLE1.c1(+) = UNISQL_TABLE2.c1 AND UNISQL_TABLE1.c2(+) = UNISQL_TABLE2.c2; C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+ | | | 1|a |b | -- (+)外连接在子查询中 CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC'); CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b'); CREATE TABLE UNISQL_TABLE3(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE3 (C1, C2, C3) VALUES ('1', 'C', 'D'); -- 转换前Oracle SQL： SELECT * FROM ( SELECT t1.c1, t2.c2, t3.c3 FROM UNISQL_TABLE1 t1, UNISQL_TABLE2 t2, UNISQL_TABLE3 t3 WHERE t1.c1 = t2.c1(+) AND t1.c1 = t3.c1(+) ); C1|C2|C3| --+--+--+ 1|a |D | 2| | | 3| | | -- 转换后TDSQL-MySQL： select * from ( select `t1`.`c1`, `t2`.`c2`, `t3`.`c3` from `UNISQL_TABLE3` as `t3` right join (`UNISQL_TABLE2` as `t2` right join `UNISQL_TABLE1` as `t1` on `t1`.`c1` = `t2`.`c1`) on `t1`.`c1` = `t3`.`c1` where 1 = 1 and 1 = 1) as `uni_sub`; c1|c2|c3| --+--+--+ 1|a |D | 2| | | 3| | | -- (+)外连接加笛卡尔积 CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB'); INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC'); CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b'); CREATE TABLE UNISQL_TABLE3(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50)); INSERT INTO UNISQL_TABLE3 (C1, C2, C3) VALUES ('1', 'C', 'D'); -- 转换前Oracle SQL： SELECT t1.*, t2.*, t3.* FROM UNISQL_TABLE1 t1, UNISQL_TABLE2 t2, UNISQL_TABLE3 t3 WHERE t1.c1(+) = t2.c1 AND t1.c1 = t3.c1; C1|C2|C3|C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+--+--+--+ 1|A |AA| 1|a |b | 1|C |D | -- 转换后TDSQL-MySQL： select `t1` .*, `t2` .*, `t3` .* from (`UNISQL_TABLE1` as `t1` right join `UNISQL_TABLE2` as `t2` on `t1`.`c1` = `t2`.`c1`) join `UNISQL_TABLE3` as `t3` where 1 = 1 and `t1`.`c1` = `t3`.`c1`; C1|C2|C3|C1|C2|C3|C1|C2|C3| --+--+--+--+--+--+--+--+--+ 1|A |AA| 1|a |b | 1|C |D |
5.3.1.4. WITH CLAUSE
如果查询语句中有多个相同的子查询，可以把相同的子查询放在 WITH CLAUSE 中作为公共表达式，在主体查询中直接引用即可。
语法
with_clause_select: with_clause simple_select with_clause: WITH query_table_name [opt_column_alias_name_list] AS ( select_clause ) select_clause: simple_select | select_clause_set opt_column_alias_name_list: (column_name_list) column_name_list: column_name [,column_name...]
示例
CREATE TABLE unisql_tbl1(col1 INT,col2 INT,col3 INT); INSERT INTO unisql_tbl1 VALUES(1,1,1); INSERT INTO unisql_tbl1 VALUES(2,2,2); INSERT INTO unisql_tbl1 VALUES(3,3,3); /*直接使用表 unisql_tbl1 的列名作为 u_tbl1 的列名*/ -- 转换前Oracle SQL： WITH u_tbl1 AS(SELECT * FROM unisql_tbl1) SELECT * FROM u_tbl1; COL1|COL2|COL3| ----+----+----+ 1| 1| 1| 2| 2| 2| 3| 3| 3| -- 转换后TDSQL-MySQL： SELECT * FROM (SELECT * FROM `unisql_tbl1`) AS `u_tbl1`; col1|col2|col3| ----+----+----+ 1| 1| 1| 2| 2| 2| 3| 3| 3| /*直接给 w_tbl1 命名列名*/ -- 转换前Oracle SQL： WITH w_tbl1(c1,c2,c3) AS(SELECT * FROM unisql_tbl1) SELECT * FROM w_tbl1; C1|C2|C3| --+--+--+ 1| 1| 1| 2| 2| 2| 3| 3| 3| -- 转换后TDSQL-MySQL： SELECT * FROM (SELECT * FROM `unisql_tbl1`) AS `w_tbl1` col1|col2|col3| ----+----+----+ 1| 1| 1| 2| 2| 2| 3| 3| 3|
5.3.1.5. 子查询
子查询指的是 SELECT 查询语句中嵌套了另一个或者多个 SELECT 语句，可以返回单行结果、多行结果或不返回结果。SELECT 语句的 FROM 子句中的子查询也称为内联视图。可以在嵌入式视图中嵌套任意数量的子查询。SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。
子查询可以分为相关子查询和非相关子查询。相关子查询指该子查询的执行依赖了外部查询的变量，这种子查询通常会执行多次。非相关子查询指该子查询的执行不依赖外部查询的变量，这种子查询一般只需要计算一次。对于非相关子查询与部分相关子查询，可以通过改写进行子查询消除，实现嵌套子查询的展开。
语法
SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list FROM { table_reference | join_clause | ( join_clause ) } [ , { table_reference | join_clause | (join_clause) } ] [ where_clause ] [ hierarchical_query_clause ] [ group_by_clause ] | subquery { UNION [ALL] | INTERSECT | MINUS } subquery [ { UNION [ALL] | INTERSECT | MINUS } subquery ] | ( subquery ) [ order_by_clause ]
示例
CREATE TABLE unisql_table_a(PK INT, name VARCHAR(25)); INSERT INTO unisql_table_a VALUES(1,'福克斯'); INSERT INTO unisql_table_a VALUES(2,'警察'); INSERT INTO unisql_table_a VALUES(3,'的士'); INSERT INTO unisql_table_a VALUES(4,'林肯'); INSERT INTO unisql_table_a VALUES(5,'亚利桑那州'); INSERT INTO unisql_table_a VALUES(6,'华盛顿'); INSERT INTO unisql_table_a VALUES(7,'戴尔'); INSERT INTO unisql_table_a VALUES(10,'朗讯'); CREATE TABLE unisql_table_b(PK INT, name VARCHAR(25)); INSERT INTO unisql_table_b VALUES(1,'福克斯'); INSERT INTO unisql_table_b VALUES(2,'警察'); INSERT INTO unisql_table_b VALUES(3,'的士'); INSERT INTO unisql_table_b VALUES(6,'华盛顿'); INSERT INTO unisql_table_b VALUES(7,'戴尔'); INSERT INTO unisql_table_b VALUES(8,'微软'); INSERT INTO unisql_table_b VALUES(9,'苹果'); INSERT INTO unisql_table_b VALUES(11,'苏格兰威士忌'); -- 没有依赖关系的子查询 -- 转换前Oracle SQL： SELECT * FROM unisql_table_a T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b T2); PK|NAME| --+----+ 1|福克斯 | 2|警察 | 3|的士 | 6|华盛顿 | 7|戴尔 | -- 转换后TDSQL-MySQL select * from `unisql_table_a` as `T1` where `T1`.`PK` in ( select * from ( select `T2`.`PK` from `unisql_table_b` as `T2`) as `unisub`); PK|name| --+----+ 1|福克斯 | 2|警察 | 3|的士 | 6|华盛顿 | 7|戴尔 |
5.3.1.6. DUAL 表查询
DUAL 表有一个列 DUMMY，定义为 VARCHAR2(1)，并包含一个值为 X 的行。在使用 SELECT 语句计算常量表达式时，可以从 DUAL 表中选择数据。
-- 转换前Oracle SQL： SELECT 1, 2 + 1, NULL, '' FROM dual; 1|2+1|NULL|''| -+---+----+--+ 1| 3| | | -- 转换后TDSQL-MySQL SELECT 1,2+1,NULL,'' FROM dual; 1|2+1|NULL|| -+---+----++ 1| 3| ||
5.3.1.7. AS作为别名
通常AS在数据库中作为保留关键字，一般不建议作为字段的别名，但是有时候开发人员在编写SQL语句时，存在使用AS作为别名的情况。 统一SQL支持AS作为别名时到目标库的转换。
参考示例：
-- 转换前Oracle SQL：
SELECT
0 AS,
1 as,
2 "as",
3 a,
4 AS b,
count(*) AS
FROM
dual;
0AS|1AS|as|A|B|COUNT(*)AS|
---+---+--+-+-+----------+
0| 1| 2|3|4| 1|
-- 转换后TDSQL-MySQL
select
0 as `AS`,
1 as `as`,
2 as `as`,
3 as `a`,
4 as `b`,
count(1) as `AS`
from
dual;
AS|as|as|a|b|AS|
--+--+--+-+-+--+
0| 1| 2|3|4| 1|
5.3.2. 伪列
伪列（Pseudocolumn）的行为与表中的列相同，但并未存储具体数值。因此，伪列只具备读属性，不可以对伪列进行插入、更新、删除的等行为。本节主要介绍 统一SQL 所支持的伪列及使用说明。
当前支持如下伪列类型：
5.3.2.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 约束的条件中。
序列值迁移到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|
5.3.2.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;
5.3.2.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 |
5.3.3. 运算符
运算符一般用于连接运算数或参数等单个数据项并返回结果。从语法上讲，运算符出现在操作数之前、操作数之后或两个操作数之间均可。本节主要介绍 统一SQL 所支持的常见运算符及使用说明。
当前支持常见运算符类型：
5.3.3.1. 串联运算符
串联运算符 || 用于连接字符串和 CLOB 数据类型的数据。使用串联运算符连接两个字符串后会得到另一个字符串。
-- 转换前Oracle SQL: select '成交价格高于前一日收盘价;成交价格：' || 2 * 1000.00 || '元，收盘价：' || '' || ' '|| 'end'|| '' FROM DUAL; '成交价格高于前一日收盘价;成交价格：'||2*1000.00||'元，收盘价：'||''||''||'END'||''| ------------------------------------------------------------+ 成交价格高于前一日收盘价;成交价格：2000元，收盘价： end | -- 转换后TDSQL-MySQL： SELECT concat(concat(concat(concat(concat(concat(ifnull('成交价格高于前一日收盘价;成交价格：', ''), ifnull(2*1000.00, '')), ifnull('元，收盘价：', '')), ifnull('', '')), ifnull(' ', '')), ifnull('end', '')), ifnull('', '')) FROM DUAL; concat(concat(concat(concat(concat(concat(ifnull('成交价格高于前一日收盘价;成交价格：', ''), ifnull(2*1000.00, '')), ifnull('元，收盘价：', '')), ifnull('', '')), ifnull(' ', '')), ifnull('end', '')), ifnull('', ''))| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 成交价格高于前一日收盘价;成交价格：2000.00元，收盘价： end |
警告
本方案中串联运算符会转化成concat函数，转化的结果是字符串，字符串去和其他数据类型进行运算、比较时，因为源库和目标库的隐式转化不同，出现转出的SQL无法在目标库中执行的情况。
5.3.4. DML
5.3.4.1. 插入单条记录
指定所有字段
-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 转换前Oracle SQL：
INSERT INTO unisql_special_orders(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);
-- 转换后TDSQL-MySQL：
INSERT INTO `unisql_special_orders` (`order_`,`customer_`,`order_total`,`sales_rep_id`,`credit_limit`,`cust_email`) VALUES (1,1,1,'a1',1,1);
-- 查询表数据
SELECT * FROM unisql_special_orders;
指定某些字段，不指的有默认值的插入时带上默认值。
-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');
-- 转换后TDSQL-MySQL：
INSERT INTO `unisql_special_orders` (`sales_rep_id`) VALUES ('a1');
-- 查询表数据
SELECT * FROM unisql_special_orders;
不指定字段，则默认为所有字段与建表时一致。
-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);
-- 转换后TDSQL-MySQL：
INSERT INTO `unisql_special_orders` VALUES (3,1,1,'a1',1,1);
-- 查询表数据
SELECT * FROM unisql_special_orders;
5.3.4.2. 从另外一个表取数据进行批量插入
-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 插入数据
INSERT INTO unisql_special_orders(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);
-- 从另外一个表读取数据进行数据插入
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders;
-- 转换后TDSQL-MySQL：
INSERT INTO `unisql_special_orders` SELECT `order_`,`customer_`,`order_total`,`sales_rep_id`,`credit_limit`,`cust_email` FROM `unisql_special_orders`;
-- 查询表数据
SELECT * FROM unisql_special_orders;
5.3.4.3. 批量插入
-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 转换前Oracle SQL:
INSERT ALL
INTO unisql_special_orders VALUES(1,1,1,'a1',1,1)
INTO unisql_special_orders VALUES(2,2,2,'a2',2,2)
INTO unisql_special_orders VALUES(3,3,3,'a3',3,3)
INTO unisql_special_orders VALUES(4,4,4,'a4',4,4)
INTO unisql_special_orders VALUES(5,5,5,'a5',5,5)
SELECT * FROM dual;
-- 转换后TDSQL-MySQL：
INSERT INTO `unisql_special_orders` VALUES (1,1,1,'a1',1,1),(2,2,2,'a2',2,2),(3,3,3,'a3',3,3),(4,4,4,'a4',4,4),(5,5,5,'a5',5,5);
-- 查询表数据
SELECT * FROM unisql_special_orders;
order_|customer_|order_total|sales_rep_id|credit_limit|cust_email|
------+---------+-----------+------------+------------+----------+
1| 1| 1|a1 | 1| 1|
2| 2| 2|a2 | 2| 2|
3| 3| 3|a3 | 3| 3|
4| 4| 4|a4 | 4| 4|
5| 5| 5|a5 | 5| 5|
5.3.4.4. 多表插入
暂不支持
5.3.4.5. WITH临时表查询插入
-- 创建表
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 插入数据
INSERT INTO unisql_special_orders VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders VALUES(2,2,2,'a2',2,2);
INSERT INTO unisql_special_orders VALUES(3,3,3,'a3',3,3);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id)
WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders)
SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders t LEFT JOIN tmp y ON t.order_ = y.order_;
-- 转换后TDSQL-MySQL：
INSERT
INTO
`unisql_special_orders` (`order_`,
`customer_`,
`order_total`,
`sales_rep_id`)
SELECT
`t`.`order_`,
`t`.`customer_`,
`y`.`order_total`,
`y`.`sales_rep_id`
FROM
`unisql_special_orders` AS `t`
LEFT JOIN (
SELECT
`order_`,
`customer_`,
`order_total`,
`sales_rep_id`
FROM
`unisql_special_orders`) AS `y` ON
`t`.`order_`=`y`.`order_`;
SELECT * FROM unisql_special_orders;
5.3.4.6. 合并插入
# ON 关联条件必须是主键或者唯一键
-- 创建表
CREATE TABLE unisql_people_source (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
title VARCHAR2(10) NOT NULL
);
CREATE TABLE unisql_people_target (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
title VARCHAR2(10) NOT NULL
);
-- 插入数据
INSERT INTO unisql_people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO unisql_people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO unisql_people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO unisql_people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO unisql_people_source VALUES (4, 'Dave', 'Brown', 'Mr');
-- 转换前Oracle SQL：
MERGE INTO unisql_people_target pt
USING unisql_people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);
SELECT * FROM unisql_people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
1|John |Smith |Mr |
2|Alice |Jones |Mrs. |
3|Jane |Doe |Miss |
4|Dave |Brown |Mr |
-- 转换后TDSQL-MySQL：
INSERT
INTO
`unisql_people_target` (`person_id`,
`first_name`,
`last_name`,
`title`)
SELECT
`ps`.`person_id`,
`ps`.`first_name`,
`ps`.`last_name`,
`ps`.`title`
FROM
`unisql_people_source` AS `ps` ON
DUPLICATE KEY
UPDATE
`first_name`=`ps`.`first_name`,
`last_name`=`ps`.`last_name`,
`title`=`ps`.`title`;
person_id|first_name|last_name|title|
---------+----------+---------+-----+
1|John |Smith |Mr |
2|Alice |Jones |Mrs. |
3|Jane |Doe |Miss |
4|Dave |Brown |Mr |
5.3.4.7. 更新
-- 转换前Oracle SQL:
UPDATE unisql_people_target SET FIRST_NAME = 'Bob', LAST_NAME = 'Matthew' WHERE person_id=1;
-- 转换后TDSQL-MySQL：
UPDATE `unisql_people_target` SET `FIRST_NAME`='Bob', `LAST_NAME`='Matthew' WHERE `person_id`=1;
5.3.4.8. 更新带表别名
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;
-- 转换后TDSQL-MySQL：
UPDATE `unisql_people_target` AS `u` SET `u`.`FIRST_NAME`='Ashley', `u`.`LAST_NAME`='Jessica' WHERE `u`.`person_id`=2;
-- 查询表数据
select * from unisql_people_target;
5.3.4.9. 更新带子查询
暂不支持
5.3.4.10. 更新带函数
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = UPPER(u.FIRST_NAME),u.LAST_NAME = LOWER(u.LAST_NAME) WHERE u.person_id=3;
-- 转换后TDSQL-MySQL：
UPDATE `unisql_people_target` AS `u` SET `u`.`FIRST_NAME`=UPPER(`u`.`FIRST_NAME`), `u`.`LAST_NAME`=LOWER(`u`.`LAST_NAME`) WHERE `u`.`person_id`=3
5.3.4.11. 条件删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';
-- 转换后TDSQL-MySQL：
DELETE `u` FROM `unisql_people_target` AS `u` WHERE `u`.`PERSON_ID`=1 AND `FIRST_NAME`='Bob';
-- 查询表数据
select * from unisql_people_target;
5.3.4.12. 条件子查询删除
暂不支持
5.3.4.13. exists函数删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_source
WHERE EXISTS(SELECT 1 FROM unisql_people_target WHERE unisql_people_target.PERSON_ID = unisql_people_source.PERSON_ID);
-- 转换后TDSQL-MySQL：
DELETE FROM `unisql_people_source` WHERE EXISTS (SELECT 1 FROM `unisql_people_target` WHERE `unisql_people_target`.`PERSON_ID`=`unisql_people_source`.`PERSON_ID`);
-- 查询表数据
select * from unisql_people_source;
5.3.5. DDL
本节主要介绍统一SQL支持原生Oracle数据库中的SQL的DDL操作。
使用场景中一些注意点示例：
-- 在建表时使用比如"user","USER","rowid","value","random"等单词作为字段时；
-- 在Oracle数据库中以下的建表语句虽然可以正常执行,但是不建议如此使用,表字段名称尽量根据业务特性来定
DROP TABLE unisql_key_test;
CREATE TABLE unisql_key_test(
"user" varchar(10),
"USER" varchar(10),
"SYSDATE" varchar(10),
"rowid" varchar(10),
value varchar(10),
"value" varchar(10),
random varchar(10),
"random" varchar(10),
currval varchar(10),
"currval" varchar(10),
nextval varchar(10),
"nextval" varchar(10)
);
-- 统一SQL虽然可以对上述SQL进行转换,但是由于目标库特性支持度不同,可能会在执行时报错。
-- 比如在Oracle2TDSQL-MySQL中value和"value"会被视为重复定义。
5.3.5.1. 清空表
语法
TRUNCATE [TABLE] [schema.]table_name;
- 描述
- 该语句用于完全清空指定表，但是保留表结构，包括表中定义的分区信息。从逻辑上说，该语句与用于删除所有行的 DELETE FROM 语句相同。执行 TRUNCATE 语句，必须具有表的删除和创建权限。
示例
CREATE TABLE unisql_truncate_test(id int, name varchar(10));
INSERT INTO unisql_truncate_test values(1,'aa');
INSERT INTO unisql_truncate_test values(2,'bb');
INSERT INTO unisql_truncate_test values(3,'cc');
-- 转换前Oracle SQL:
TRUNCATE TABLE unisql_truncate_test;
-- 转换后TDSQL-MySQL：
TRUNCATE TABLE `unisql_truncate_test`
-- 查询表数据
SELECT * FROM unisql_truncate_test;
5.3.5.2. 创建索引
语法
CREATE [UNIQUE | BITMAP] INDEX index_name
ON table_name (index_col_name,...)
- 描述
- 该语句用来创建索引。索引是创建在表上的，对数据库表中一列或多列的值进行排序的一种结构。其作用主要在于提高查询的速度，降低数据库系统的性能开销。
示例
--创建表
CREATE TABLE unisql_index_test(col1 int,col2 int,col3 int,col4 int,col5 varchar(10));
-- 转换前Oracle SQL:
-- 使用示例
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE UNIQUE INDEX unisql_index_test_idx ON unisql_index_test(col1);
CREATE BITMAP INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX test.unisql_index_test_idx ON unisql_index_test(col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1 asc);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1 desc);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1,col2);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) LOGGING;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) NOLOGGING;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) GLOBAL;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) USABLE;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) UNUSABLE;
-- 转换后TDSQL-MySQL：
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE UNIQUE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1` DESC);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`, `col2`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
CREATE INDEX `unisql_index_test_idx` ON `unisql_index_test` (`col1`);
-- 创建表，LOCAL索引需要创建在分区表上
CREATE TABLE unisql_local_test_orders (
order_id int,
order_num int,
customer varchar(50)
)
PARTITION BY RANGE (order_num)
(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
-- 转换前Oracle SQL:
-- 使用示例
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders(order_num) LOCAL;
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders(order_num) tablespace test LOCAL;
-- 转换后TDSQL-MySQL：
CREATE INDEX `unisql_local_test_orders_idx` ON `unisql_local_test_orders` (`order_num`);
CREATE INDEX `unisql_local_test_orders_idx` ON `unisql_local_test_orders` (`order_num`);
5.3.5.3. 删除索引
暂不支持
5.3.5.4. 复制表
语法
CREATE [GLOBAL TEMPORARY] TABLE table_name AS select ...;
- 描述
- 该语句用于根据AS后的select 语句创建一张新表
示例
CREATE TABLE unisql_copy_table_test(id int, name varchar(10));
INSERT INTO unisql_copy_table_test values(1,'TOM');
INSERT INTO unisql_copy_table_test values(2,'JIMMY');
INSERT INTO unisql_copy_table_test values(3,'NANCY');
-- 转换前Oracle SQL:
CREATE GLOBAL TEMPORARY TABLE unisql_copy_table_test_global AS SELECT id,name FROM unisql_copy_table_test WHERE id > 1;
CREATE GLOBAL TEMPORARY TABLE unisql_copy_table_test_global_1 AS SELECT * FROM duaL;
-- 转换后TDSQL-MySQL：
CREATE TEMPORARY TABLE `unisql_copy_table_test_global` AS SELECT `id`,`name` FROM `unisql_copy_table_test` WHERE `id`>1;
CREATE TEMPORARY TABLE `unisql_copy_table_test_global_1` AS SELECT 'X' AS `DUMMY` FROM dual;
5.3.5.5. 创建表
语法
CREATE [GLOBAL TEMPORARY] TABLE table_name
(table_definition_list) [partition_option] [on_commit_option]
CREATE [GLOBAL TEMPORARY] TABLE table_name
(table_definition_list) [partition_option] [AS] select;
table_definition_list:
table_definition [, table_definition ...]
table_definition:
column_definition
| INDEX [index_name] index_desc
| [CONSTRAINT [constraint_name]] [PRIMARY KEY|UNIQUE] (column_definition_list) [USING INDEX index_option_list]
| [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constraint_state
| [CONSTRAINT [constraint_name]] CHECK(expression) constraint_state
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[VISIBLE|INVISIBLE]
{
[DEFAULT expression]
[NULL | NOT NULL]
[CONSTRAINT [constraint_name] [PRIMARY] KEY] [UNIQUE [KEY]]
[CONSTRAINT [constraint_name] CHECK(expression)]
[CONSTRAINT [constraint_name] references_clause]
|
[GENERATED ALWAYS] AS (expression) [VIRTUAL]
[NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY] [COMMENT string]
}
references_clause:
REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]
index_desc:
(column_desc_list) [index_option_list]
column_desc_list:
column_desc [, column_desc ...]
column_desc:
column_name [ASC | DESC][NULL LAST | NULL FIRST]
partition_option:
PARTITION BY HASH(column_name_list)
[subpartition_option] hash_partition_define
| PARTITION BY RANGE (column_name_list)
[subpartition_option] (range_partition_list)
| PARTITION BY LIST (column_name_list)
[subpartition_option] (list_partition_list)
on_commit_option:
ON COMMIT DELETE ROWS
| ON COMMIT PRESERVE ROWS
- 描述
- 该语句用来在数据库中创建一张新表，DEFAULT expression可支持使用关键字函数、字符串、常数。
示例
-- 转换前Oracle SQL:
CREATE table unisql_create_table_test(
DBID_ NUMERIC(19) NOT NULL,
PROCESS_KEY VARCHAR2(128) NOT NULL,
BIZ_VERSION VARCHAR2(32) NOT NULL,
PROCESS_NAME VARCHAR2(128) NOT NULL,
PROCESS_FLAG VARCHAR2(1) NOT NULL,
PROCESS_CLASS VARCHAR2(128) NOT NULL,
IGNORE_EXIST_USER VARCHAR2(1) default 0 NOT NULL,
FORM_NAME VARCHAR2(128),
FORM_TYPE VARCHAR2(128),
FORM_URL VARCHAR2(128),
FORM_UUID VARCHAR2(32),
IMPORT_TIME TIMESTAMP,
HANDLE_TIME TIMESTAMP,
STATE_ VARCHAR2(20) default 0,
ERRORMSG CLOB,
constraint UK_IMPORT_PRO UNIQUE (PROCESS_KEY,BIZ_VERSION,PROCESS_NAME),
constraint UK_PROCESS_BIZ UNIQUE (PROCESS_KEY,BIZ_VERSION),
constraint UK_IMPORT_PRO_NAME UNIQUE (PROCESS_NAME,BIZ_VERSION),
constraint PK_IMPORT_PRO primary key (DBID_)
);
-- 转换后TDSQL-MySQL：
create table `unisql_create_table_test` (
`DBID_` decimal(19) not null,
`PROCESS_KEY` varchar(128) not null,
`BIZ_VERSION` varchar(32) not null,
`PROCESS_NAME` varchar(128) not null,
`PROCESS_FLAG` varchar(1) not null,
`PROCESS_CLASS` varchar(128) not null,
`IGNORE_EXIST_USER` varchar(1) default 0 not null,
`FORM_NAME` varchar(128),
`FORM_TYPE` varchar(128),
`FORM_URL` varchar(128),
`FORM_UUID` varchar(32),
`IMPORT_TIME` timestamp(6),
`HANDLE_TIME` timestamp(6),
`STATE_` varchar(20) default 0,
`ERRORMSG` longtext,
unique `UK_IMPORT_PRO`(`PROCESS_KEY`,
`BIZ_VERSION`,
`PROCESS_NAME`),
unique `UK_PROCESS_BIZ`(`PROCESS_KEY`,
`BIZ_VERSION`),
unique `UK_IMPORT_PRO_NAME`(`PROCESS_NAME`,
`BIZ_VERSION`),
primary key `PK_IMPORT_PRO`(`DBID_`));
-- 转换前Oracle SQL:
CREATE GLOBAL TEMPORARY TABLE unisql_create_table_on_option_test(
d_SMALLINT SMALLINT ,
d_INTEGER INTEGER,
d_INT INT ,
d_LONG LONG ,
d_NUMBER NUMBER(10,2),
d_DECIMAL DECIMAL(10,2),
d_CHAR CHAR(60),
d_CHARACTER CHARACTER(60) ,
d_VARCHAR VARCHAR(60),
d_VARCHAR2 VARCHAR2(60),
d_DATE DATE,
d_TIMESTAMP TIMESTAMP(6),
d_TIMESTAMP2 TIMESTAMP(6) WITH TIME ZONE,
d_BLOB BLOB,
d_CLOB CLOB
) ON COMMIT PRESERVE ROWS;
-- 转换后TDSQL-MySQL：
create temporary table `unisql_create_table_on_option_test` (
`d_SMALLINT` bigint,
`d_INTEGER` bigint,
`d_INT` bigint,
`d_LONG` mediumtext,
`d_NUMBER` decimal(10,
2),
`d_DECIMAL` decimal(10,
2),
`d_CHAR` char(60),
`d_CHARACTER` char(60),
`d_VARCHAR` varchar(60),
`d_VARCHAR2` varchar(60),
`d_DATE` datetime,
`d_TIMESTAMP` timestamp(6),
`d_TIMESTAMP2` timestamp(6),
`d_BLOB` longblob,
`d_CLOB` longtext);
5.3.5.6. 创建分区表
语法
CREATE [GLOBAL TEMPORARY] TABLE table_name
(table_definition_list) [partition_option]
partition_option:
PARTITION BY HASH(column_name_list)
hash_partition_define
| PARTITION BY RANGE (column_name_list)
(range_partition_list)
| PARTITION BY LIST (column_name_list)
(list_partition_list)
- 描述
- 该语句用来创建分区表，统一SQL暂不支持二级分区的转换
备注
因为TDSQL-MySQL对分区表字段类型有限制，在使用时需要提前确认分区字段类型。
- 在创建（RANGE)分区表时，分区字段类型需要是以下类型(oracle-datatye)
整数类型(int,integer)
日期类型(date)
- 在创建（HASH)分区表时，分区字段类型需要是以下类型(oracle-datatye)
整数类型(int,integer)
- 在创建（LIST)分区表时，分区字段类型需要是以下类型(oracle-datatye)
整数类型(int,integer)
日期类型(date)
字符类型(char,varchar，varchar2,nchar,nvarchar2)
示例
-- 转换前Oracle SQL:
-- 创建RANGE分区
CREATE TABLE unisql_partition_by_range_test
(
product_id NUMBER(6),
sale_time DATE,
sale_price NUMBER(10,2)
)
PARTITION BY RANGE (sale_time)
(
PARTITION q1_2023 VALUES LESS THAN (TO_DATE('2023/04/01', 'yyyy/mm/dd')),
PARTITION q2_2023 VALUES LESS THAN (TO_DATE('2023/07/01', 'yyyy/mm/dd')),
PARTITION q3_2023 VALUES LESS THAN (TO_DATE('2023/10/01', 'yyyy/mm/dd')),
PARTITION q4_2023 VALUES LESS THAN (TO_DATE('2024/01/01', 'yyyy/mm/dd'))
);
-- 转换后TDSQL-MySQL：
create table `unisql_partition_by_range_test` (
`product_id` int,
`sale_time` datetime,
`sale_price` decimal(10,
2)) partition by range COLUMNS (`sale_time`) (partition `q1_2023`
values LESS THAN ('2023/04/01'),
partition `q2_2023`
values LESS THAN ('2023/07/01'),
partition `q3_2023`
values LESS THAN ('2023/10/01'),
partition `q4_2023`
values LESS THAN ('2024/01/01'));
-- 转换前Oracle SQL:
-- 创建LIST分区
create table unisql_partition_by_list_test
(
id varchar2(15) not null,
city varchar2(20)
)
partition by list (city)
(
partition p1 values ('beijing'),
partition p2 values ('shanghai'),
partition p3 values ('changsha')
);
-- 转换后TDSQL-MySQL：
create table `unisql_partition_by_list_test` (`id` varchar(15) not null,
`city` varchar(20)) partition by LIST COLUMNS (`city`) (partition `p1`
values in ('beijing'), partition `p2`
values in ('shanghai'), partition `p3`
values in ('changsha'));
-- 转换前Oracle SQL:
-- 创建HASH分区
CREATE TABLE unisql_partition_by_hash_test (col1 INT, col2 VARCHAR(50), col3 TIMESTAMP )
PARTITION BY HASH (col1) PARTITIONS 4;
-- 转换后TDSQL-MySQL：
create table `unisql_partition_by_hash_test` (`col1` bigint,
`col2` varchar(50),
`col3` timestamp(6)) partition by HASH (`col1`) PARTITIONS 4;
5.3.5.7. 清空、删除分区表
语法
ALTER TABLE TABLE_NAME [TRUNCATE|DROP] PARTITION PARTITION_NAMES
- 描述
- 该语句用于清空或者删除分区表
示例
-- 转换前Oracle SQL:
ALTER TABLE unisql_partition_by_range_test TRUNCATE PARTITION q1_2023;
ALTER TABLE unisql_partition_by_range_test DROP PARTITION q1_2023;
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_partition_by_range_test` TRUNCATE PARTITION `q1_2023`;
ALTER TABLE `unisql_partition_by_range_test` DROP PARTITION `q1_2023`;
5.3.5.8. 新增列
语法
ALTER TABLE table_name ADD {column_definition | (column_definition_list)}
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[DEFAULT const_value] [AUTO_INCREMENT]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
- 描述
- 该语句用于新增列
示例
-- 创建表
CREATE TABLE unisql_add_column_test(col1 int);
-- 转换前Oracle SQL:
ALTER TABLE unisql_add_column_test ADD col2 NUMBER(4);
ALTER TABLE unisql_add_column_test ADD col3 varchar(10) NOT NULL ;
ALTER TABLE unisql_add_column_test ADD col4 NUMBER(4) DEFAULT 0;
ALTER TABLE unisql_add_column_test ADD (col5 NUMBER(4), col6 INT);
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_add_column_test` ADD COLUMN `col2` smallint;
ALTER TABLE `unisql_add_column_test` ADD COLUMN `col3` varchar(10) NOT NULL;
ALTER TABLE `unisql_add_column_test` ADD COLUMN `col4` smallint DEFAULT 0;
ALTER TABLE `unisql_add_column_test` ADD COLUMN (`col5` smallint, `col6` bigint);
5.3.5.9. 新增表约束
语法
ALTER TABLE table_name
ADD [CONSTRAINT [constraint_name]] UNIQUE (column_name [, column_name ]...)
| ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)
| ADD [CONSTRAINT [constraint_name]] CHECK (expr)
| ADD CONSTRAINT constraint_name FOREIGN KEY(foreign_col_name) REFERENCES
reference_tbl_name(column_name);
[disable | enable]
- 描述
- 该语句用于新增约束
示例
-- 创建表
CREATE TABLE unisql_cn_test(a int,b int,c int,d varchar2(10),e varchar2(10));
-- 测试主键或者唯一约束
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a,b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE (a,b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a) disable;
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a) enable;
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_cn_test` ADD PRIMARY KEY `cn_name_a`(`a`);
ALTER TABLE `unisql_cn_test` ADD PRIMARY KEY `cn_name_a`(`a`, `b`);
ALTER TABLE `unisql_cn_test` ADD UNIQUE `cn_name_a`(`a`);
ALTER TABLE `unisql_cn_test` ADD UNIQUE `cn_name_a`(`a`, `b`);
ALTER TABLE `unisql_cn_test` ADD PRIMARY KEY `cn_name_a`(`a`);
ALTER TABLE `unisql_cn_test` ADD PRIMARY KEY `cn_name_a`(`a`);
-- 测试reference约束
-- 创建一张关联表
CREATE TABLE unisql_cn_ref_tab(col1 int,col2 int,col3 int,col4 varchar2(10),col5 varchar2(10));
-- 添加一个外键约束(指定一个列)
ALTER TABLE unisql_cn_ref_tab ADD CONSTRAINT ref_tab_col1 PRIMARY KEY (col1);
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a) REFERENCES unisql_cn_ref_tab(col1);
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_cn_test` ADD CONSTRAINT `cn_name_a` FOREIGN KEY (`a`) REFERENCES `unisql_cn_ref_tab`(`col1`);
-- 添加一个外键约束(指定两个列)
ALTER TABLE unisql_cn_ref_tab ADD CONSTRAINT ref_tab_col1_col2 PRIMARY KEY (col1,col2);
-- 添加两个列约束
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a,b) REFERENCES unisql_cn_ref_tab(col1,col2);
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_cn_test` ADD CONSTRAINT `cn_name_a` FOREIGN KEY (`a`, `b`) REFERENCES `unisql_cn_ref_tab`(`col1`, `col2`);
5.3.5.10. 删除表约束
语法
ALTER TABLE TABLE_NAME DROP PRIMARY KEY
- 描述
- 该语句用于删除表约束，目前统一SQL仅支持删除主键约束的转换
示例
-- 创建表、约束
CREATE TABLE unisql_cn_test(a int,b int,c int,d varchar2(10),e varchar2(10));
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a);
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test DROP PRIMARY key;
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_cn_test` DROP PRIMARY KEY;
5.3.5.11. 修改列的属性
语法
ALTER TABLE table_name MODIFY [COLUMN] column_definition
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[DEFAULT const_value] [AUTO_INCREMENT]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
- 描述
- 该语句用于修改列属性
示例
DROP TABLE unisql_modify_column_test;
CREATE TABLE unisql_modify_column_test(a int,d number);
SELECT * FROM unisql_modify_column_test;
-- 转换前Oracle SQL:
ALTER TABLE unisql_modify_column_test MODIFY (d number(10));
ALTER TABLE unisql_modify_column_test MODIFY (d number(10,2));
ALTER TABLE unisql_modify_column_test MODIFY d number(8);
ALTER TABLE unisql_modify_column_test MODIFY (a varchar(10),d number(10));
ALTER TABLE unisql_modify_column_test MODIFY (d varchar(10));
ALTER TABLE unisql_modify_column_test MODIFY d varchar(20) DEFAULT '12' NOT NULL;
ALTER TABLE unisql_modify_column_test MODIFY (d number(10) DEFAULT '10');
ALTER TABLE unisql_modify_column_test MODIFY (d number(10) DEFAULT '10' null);
ALTER TABLE unisql_modify_column_test MODIFY (a varchar(10) DEFAULT '1',d number(10));
ALTER TABLE unisql_modify_column_test MODIFY (a varchar(10) DEFAULT '1' NOT NULL ,d number(10) DEFAULT 10);
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `d` bigint;
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `d` decimal(10,2);
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `d` int;
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `a` varchar(10), MODIFY COLUMN `d` bigint;
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `d` varchar(10);
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `d` varchar(20) DEFAULT '12' NOT NULL;
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `d` bigint DEFAULT '10';
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `d` bigint DEFAULT '10' NULL;
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `a` varchar(10) DEFAULT '1', MODIFY COLUMN `d` bigint;
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `a` varchar(10) DEFAULT '1' NOT NULL, MODIFY COLUMN `d` bigint DEFAULT 10;
5.3.5.12. 修改表名
语法
RENAME TABLE_NAME TO NEW_TABLE_NAME;
ALTER TABLE [SCHEMA.]TABLE_NAME RENAME TO NEW_TABLE_NAME
- 描述
- 该语句用于修改表名
示例
CREATE TABLE unisql_rename_table_test(id int,name varchar(10));
-- 转换前Oracle SQL:
RENAME unisql_rename_table_test TO unisql_rename_table_test_new;
ALTER TABLE unisql_rename_table_test_new RENAME TO unisql_rename_table_test;
-- 转换后TDSQL-MySQL：
RENAME TABLE `unisql_rename_table_test` TO `unisql_rename_table_test_new`;
ALTER TABLE `unisql_rename_table_test_new` RENAME AS `unisql_rename_table_test`;
5.3.5.13. 注释表名
语法
COMMENT ON TABLE
[ schema. ] table_name IS 'string';
- 描述
- 该语句用于注释表名
示例
CREATE TABLE unisql_comment_table_test(id int,name varchar(10));
-- 转换前Oracle SQL:
COMMENT ON TABLE unisql_comment_table_test IS 'unisql_comment_table_test table';
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_comment_table_test` COMMENT = 'unisql_comment_table_test table';
5.3.5.14. 创建视图
语法
CREATE [OR REPLACE] [[NO] FORCE] VIEW view_name [(column_name_list)] AS select_stmt;
column_name_list:
column_name [, column_name ...]
- 描述
- 该语句用于创建视图语句，如果指定了 OR REPLACE 子句，该语句能够替换已有的视图
示例
-- 创建表
CREATE TABLE unisql_view_test(a int,b int,c varchar(10));
-- 创建视图示例
-- 转换前Oracle SQL:
CREATE VIEW unisql_view_1 AS SELECT * FROM unisql_view_test;
CREATE VIEW unisql_view_2 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW public.unisql_view_3 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW unisql_view_4 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW unisql_view_5 AS SELECT ' ' a, ' ' b FROM dual;
CREATE VIEW unisql_view_6 AS SELECT 1 a, 2 b FROM dual;
CREATE OR REPLACE VIEW unisql_view_7 AS SELECT 1 a, 2 b FROM dual;
CREATE OR REPLACE VIEW unisql_view_8 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE FORCE VIEW unisql_view_9 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE NOFORCE VIEW unisql_view_10 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE VIEW unisql_view_11(a1,b1) AS SELECT a,b FROM unisql_view_test;
-- 转换后TDSQL-MySQL：
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_1` AS SELECT * FROM `unisql_view_test`;
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_2` AS SELECT `a`,`b` FROM `unisql_view_test`;
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `public`.`unisql_view_3` AS SELECT `a`,`b` FROM `unisql_view_test`;
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_4` AS SELECT `a`,`b` FROM `unisql_view_test`;
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_5` AS SELECT ' ' AS `a`,' ' AS `b` FROM dual;
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_6` AS SELECT 1 AS `a`,2 AS `b` FROM dual;
CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_7` AS SELECT 1 AS `a`,2 AS `b` FROM dual;
CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_8` AS SELECT 1 AS `a`,2 AS `b`,'3' AS `c` FROM dual;
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_9` AS SELECT 1 AS `a`,2 AS `b`,'3' AS `c` FROM dual;
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_10` AS SELECT 1 AS `a`,2 AS `b`,'3' AS `c` FROM dual;
CREATE ALGORITHM = UNDEFINED DEFINER = `` SQL SECURITY DEFINER VIEW `unisql_view_11` (`a1`,`b1`) AS SELECT `a`,`b` FROM `unisql_view_test`;
5.3.5.15. 创建序列
语法
CREATE SEQUENCE [ schema. ] sequence_name
{ START WITH int_value
|[ INCREMENT BY int_value ]
- 描述
- 该语句用来创建序列。序列属于数据库对象，用户可以从序列中生成唯一整数。
参数解释
字段 |
描述 |
|---|---|
schema. |
指定包含序列的 Schema。如果省略 Schema，则数据库会在用户当前所在的 Schema 中创建序列。 |
sequence_name |
要创建的序列名称。 |
START WITH int_value |
指定序列的起始值。int_value 必须小于等于 MAXVALUE，且大于等于 MINVALUE。 如果不指定，则升序时默认取值为最小值，降序时默认取值为最大值。 |
INCREMENT BY int_value |
指定序列的自增步数。int_value 不可以为 0。 如果指定为正数，则序列为升序；如果指定为负数，则序列为降序。 不指定时缺省值为 1。 |
示例
-- 创建序列示例
-- 转换前Oracle SQL:
CREATE SEQUENCE SE_NAME MAXVALUE 100000 CYCLE;
CREATE SEQUENCE SE_NAME_1 NOCYCLE;
CREATE SEQUENCE SE_NAME_2 CACHE 20;
CREATE SEQUENCE SE_NAME_3 START WITH 1 NOCACHE;
CREATE SEQUENCE SE_NAME_4 START WITH 1 ORDER;
CREATE SEQUENCE SE_NAME_5 START WITH 1 NOORDER;
CREATE SEQUENCE SE_NAME_6 START WITH 1 KEEP;
CREATE SEQUENCE SE_NAME_7 NOKEEP START WITH 1;
CREATE SEQUENCE SE_NAME_8 NOMINVALUE MAXVALUE 9223372036854775808;
CREATE SEQUENCE SE_NAME_9 MAXVALUE 9223372036854775806;
CREATE SEQUENCE SE_NAME_10 MINVALUE -9223372036854775808 NOMAXVALUE;
CREATE SEQUENCE SE_NAME_11 MINVALUE -9223372036854775809;
CREATE SEQUENCE SE_NAME_12 MINVALUE -9999999999999999999999999999;
CREATE SEQUENCE SE_NAME_13 INCREMENT BY 9223372036854775808;
CREATE SEQUENCE SE_NAME_14 INCREMENT BY -9223372036854775809;
CREATE SEQUENCE SE_NAME_15 MINVALUE -9999999999999999999999999999;
CREATE SEQUENCE SE_NAME_16 INCREMENT BY 9223372036854775808;
CREATE SEQUENCE SE_NAME_17 INCREMENT BY -9223372036854775809;
CREATE SEQUENCE SE_NAME_18 START WITH 9223372036854775808;
-- 转换后TDSQL-MySQL：
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_1',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_2',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_3',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_4',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_5',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_6',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_7',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_8',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_9',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_10',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_11',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_12',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_13',-9223372036854775806,9223372036854775807);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_14',-9223372036854775807,-9223372036854775808);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_15',0,1);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_16',-9223372036854775806,9223372036854775807);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_17',-9223372036854775807,-9223372036854775808);
INSERT INTO `unisql`.`UNISQL_SEQUENCE` (`SEQ_CODE`,`CURRENT_VAL`,`INCREMENT_VAL`) VALUES ('SE_NAME_18',9223372036854775806,1);