更新时间:2024-11-11 GMT+08:00
分享

SQL

GaussDB数据库兼容绝大多数MySQL语法,但存在部分差异。本章节介绍GaussDB数据库当前支持的MySQL语法。

  • 部分关键字在MySQL可以做标识符但M-Compatibility不可以或存在限制,以下为表1
    表1 限制做标识符列表

    关键字类型

    关键字

    约束

    保留(可以是类型或函数)

    COLLATION、COMPACT

    除函数和变量,不可以作为其他数据库标识符。

    非保留(不能是类型或函数)

    BIT、BOOLEAN、COALESCE、DATE、NATIONAL、NCHAR、NONE、NUMBER、TEXT、TIME、TIMESTAMP、TIMESTAMPDIFF

    不可以作为函数或变量的标识符。

    保留

    ANY、ARRAY、BUCKETS、DO、END、LESS、MODIFY、OFFSET、ONLY、RETURNING、SOME、USER

    不可以作为任意数据库标识符。

  • 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)
    

相关文档