更新时间:2024-11-11 GMT+08:00
SQL
GaussDB数据库兼容绝大多数MySQL语法,但存在部分差异。本章节介绍GaussDB数据库当前支持的MySQL语法。
- 部分关键字在MySQL可以做标识符但M-Compatibility不可以或存在限制,以下为表1。
- GaussDB优化器与MySQL的优化器存在差异,由于优化器生成的执行计划的差异,可能导致GaussDB行为与MySQL行为的不一致,不影响业务数据结果。
例如以下场景,GaussDB在计算col1以及使用col1进行where比较时,均会调用cast函数,产生两条WARNING记录。
MySQL在计算col1时会调用cast函数,在进行where比较时,使用计算好的值直接进行比较,因而产生一条WARNING记录。
- GaussDB行为: m_db=# select * from (select cast('abc' as decimal) as col1) t1 where col1=0; WARNING: Truncated incorrect DECIMAL value: 'abc' WARNING: Truncated incorrect DECIMAL value: 'abc' CONTEXT: referenced column: col1 col1 ------ 0 (1 row) m_db=# explain verbose select * from (select cast('abc' as decimal) as col1) t1 where col1=0; WARNING: Truncated incorrect DECIMAL value: 'abc' WARNING: Truncated incorrect DECIMAL value: 'abc' CONTEXT: referenced column: col1 QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) Output: 0::decimal (2 rows) -- MySQL行为: mysql> select * from (select cast('abc' as decimal) as col1) t1 where col1=0; +------+ | col1 | +------+ | 0 | +------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect DECIMAL value: 'abc' | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select * from (select cast('abc' as decimal) as col1) t1 where col1=0; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 2 warnings (0.01 sec) mysql> show warnings; +---------+------+---------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DECIMAL value: 'abc' | | Note | 1003 | /* select#1 */ select '0' AS `col1` from dual where ('0' = 0) | +---------+------+---------------------------------------------------------------+ 2 rows in set (0.00 sec)