MySQL中如何实现与Oracle 11g中WITH子句查询的等价效果

语法差异替换说明 #

• Oracle的sysdate用于获取当前系统日期时间，MySQL中可用NOW()实现完全对等的功能，返回当前会话时区的日期时间值
• Oracle的to_date(字符串, 格式模板)用于将字符串转为日期类型，MySQL对等函数为STR_TO_DATE(字符串, 格式匹配符)，注意格式符对应关系：Oracle的dd/mm/yyyy匹配格式对应MySQL的%d/%m/%Y
• Oracle强制要求的from dual伪表，MySQL原生兼容该写法，也可直接省略不写，不影响执行结果
• 原语句用WITH定义公共表表达式（CTE）的写法，MySQL 8.0及以上版本已原生支持，逻辑与Oracle保持一致

MySQL 8.0+ 版本可直接运行的等价SQL #

WITH query1 AS
 (SELECT NOW() AS now,
         '020320322' AS contract,
         'Sergio' AS name,
         STR_TO_DATE('12/02/2021', '%d/%m/%Y') AS dt_test
  UNION ALL
  SELECT NOW() AS datetime,
         '020234322' AS contract,
         'John' AS name,
         STR_TO_DATE('12/12/2021', '%d/%m/%Y') AS dt_test
)
SELECT * FROM query1;

低版本MySQL（5.x及更早版本）兼容写法 #

如果使用的MySQL版本不支持CTE语法，可直接将原CTE的定义内容作为子查询嵌入FROM子句，执行结果与原Oracle语句完全一致：

SELECT * FROM (
  SELECT NOW() AS now,
         '020320322' AS contract,
         'Sergio' AS name,
         STR_TO_DATE('12/02/2021', '%d/%m/%Y') AS dt_test
  UNION ALL
  SELECT NOW() AS datetime,
         '020234322' AS contract,
         'John' AS name,
         STR_TO_DATE('12/12/2021', '%d/%m/%Y') AS dt_test
) AS query1;

结果对齐说明：上述SQL返回的字段类型、数据取值、列名规则（UNION ALL结果集采用首个SELECT的列名）与原Oracle 11g执行结果完全一致，不存在功能偏差。

内容的提问来源于stack exchange，提问作者sysOut