更新时间:2026-10-21 GMT+08:00
分享

标识符

GaussDB数据库中的标识符存在以下差异:

  • GaussDB无引号标识符中不支持以美元符号($)开头,MySQL无引号标识符中支持。
  • GaussDB无引号标识符中的支持大小写敏感的数据库对象。
  • GaussDB标识符支持U+0080~U+00FF扩展字符,MySQL标识符支持U+0080~U+FFFF的扩展字符。
  • 无引号标识符中,GaussDB不支持创建以数字开头包含一个e或E结尾作为标识符的表,例如:
    -- GaussDB报错不支持,MySQL支持
    m_db=# CREATE TABLE 23e(c1 int);
    ERROR:  syntax error at or near "23"
    LINE 1: CREATE TABLE 23e(c1 int);
                         ^
    m_db=# CREATE TABLE t1(23E int);
    ERROR:  syntax error at or near "23"
    LINE 1: CREATE TABLE t1(23E int);
                            ^
  • 有引号标识符中,GaussDB对于创建了列名为纯数字或科学计算法的表,不支持直接使用,需要在引号中使用;对于点操作符(.)场景,列名为纯数字或科学计算法的表也需要在引号中使用。例如:
    -- 创建列名为纯数字或科学计算法的表
    m_db=# CREATE TABLE t1(`123` int, `1e3` int, `1e` int);
    CREATE TABLE
    
    -- 向表中插入数据
    m_db=# INSERT INTO t1 VALUES(7, 8, 9);
    INSERT 0 1
    
    -- 结果非预期,但与MySQL结果一致
    m_db=# SELECT 123 FROM t1;
     ?column? 
    ----------
          123
    (1 row)
    
    -- 结果非预期,但与MySQL结果一致
    m_db=# SELECT 1e3 FROM t1;
     ?column? 
    ----------
         1000
    (1 row)
    
    -- 结果非预期,并且与MySQL结果不一致
    m_db=# SELECT 1e FROM t1;
     e 
    ---
     1
    (1 row)
    
    -- 正确用法
    m_db=# SELECT `123` FROM t1;
     123 
    -----
       7
    (1 row)
    
    m_db=# SELECT `1e3` FROM t1;
     1e3 
    -----
       8
    (1 row)
    
    m_db=# SELECT `1e` FROM t1;
     1e 
    ----
      9
    (1 row)
    
    -- 点操作符的场景,GaussDB不支持,MySQL支持
    m_db=# SELECT t1.123 FROM t1;
    ERROR:  syntax error at or near ".123"
    LINE 1: SELECT t1.123 FROM t1;
                     ^
    m_db=# SELECT t1.1e3 FROM t1;
    ERROR:  syntax error at or near "1e3"
    LINE 1: SELECT t1.1e3 FROM t1;
                      ^
    m_db=# SELECT t1.1e FROM t1;
    ERROR:  syntax error at or near "1"
    LINE 1: SELECT t1.1e FROM t1;
                      ^
    -- 点操作符的场景,正确用法:
    m_db=# SELECT t1.`123` FROM t1;
     123 
    -----
       7
    (1 row)
    
    m_db=# SELECT t1.`1e3` FROM t1;
     1e3 
    -----
       8
    (1 row)
    
    m_db=# SELECT t1.`1e` FROM t1;
     1e 
    ----
      9
    (1 row)
    
    m_db=# DROP TABLE t1;
    DROP TABLE
  • GaussDB不支持空反引号``作为别名,ANSI_QUOTES模式下同样不支持空双引号""作为别名。CREATE TABLE/VIEW AS场景下,如果存在''作为别名,在sql_mode设置为ANSI_QUOTES时解析为双引号"",不设置ANSI_QUOTES时解析为反引号``,CREATE TABLE AS会直接报错,CREATE VIEW AS可能会报错,即使创建时没有报错,后续使用该视图时仍会导致报错。示例:
    -- GaussDB行为:
    m_db=# DROP VIEW IF EXISTS v1;
    NOTICE:  view "v1" does not exist, skipping
    DROP VIEW
    
    m_db=# CREATE VIEW v1 AS SELECT 1 AS '';
    CREATE VIEW
    
    -- 视图解析为“SELECT 1 AS ``;”,后续使用该视图时会导致报错。
    m_db=# \d+ v1
                        View "public.v1"
     Column |    Type    | Modifiers | Storage | Description 
    --------+------------+-----------+---------+-------------
            | integer(1) |           | plain   | 
    View definition:
     SELECT 1 AS ``;
    
    m_db=# DROP VIEW IF EXISTS v1;
    DROP VIEW
    
    m_db=# DROP TABLE IF EXISTS t1;
    NOTICE:  table "t1" does not exist, skipping
    DROP TABLE
    
    -- CREATE TABLE AS会直接报错。
    m_db=# CREATE TABLE t1 AS SELECT (SELECT 1 AS '');
    ERROR:  zero-length delimited identifier at or near "``"
    
    -- 未设置ANSI_QUOTES时,不支持``作为别名。
    m_db=# SELECT 1 AS ``;
    ERROR:  zero-length delimited identifier at or near "``"
    LINE 1: SELECT 1 AS ``;
    
    m_db=# SET sql_mode = 'ANSI_QUOTES';
    SET
    
    m_db=# DROP VIEW IF EXISTS v1;
    NOTICE:  view "v1" does not exist, skipping
    DROP VIEW
    
    m_db=# CREATE VIEW v1 AS SELECT 1 AS '';
    CREATE VIEW
    
    m_db=# \d+ v1
                        View "public.v1"
     Column |    Type    | Modifiers | Storage | Description 
    --------+------------+-----------+---------+-------------
            | integer(1) |           | plain   | 
    View definition:
     SELECT 1 AS "";
    
    m_db=# DROP VIEW IF EXISTS v1;
    DROP VIEW
    
    m_db=# DROP TABLE IF EXISTS t1;
    NOTICE:  table "t1" does not exist, skipping
    DROP TABLE
    
    -- CREATE TABLE AS会直接报错。
    m_db=# CREATE TABLE t1 AS SELECT (SELECT 1 AS '');
    ERROR:  zero-length delimited identifier at or near """"
    
    -- 设置ANSI_QUOTES时,不支持""和``作为别名。
    m_db=# SELECT 1 AS "";
    ERROR:  zero-length delimited identifier at or near """"
    LINE 1: SELECT 1 AS "";
    
    m_db=# SELECT 1 AS ``;
    ERROR:  zero-length delimited identifier at or near "``"
    LINE 1: SELECT 1 AS ``;
    
    -- MySQL行为:
    mysql> SELECT 1 AS ``;
    +---+
    |   |
    +---+
    | 1 |
    +---+
    1 row in set (0.00 sec)
    
    mysql> DROP TABLE IF EXISTS t1;
    Query OK, 0 rows affected (0.04 sec)
    
    -- 正常执行。
    mysql> CREATE TABLE t1 AS SELECT (SELECT 1 AS '');
    Query OK, 1 row affected (0.07 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> DROP TABLE IF EXISTS t1;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> SET SESSION sql_mode = CONCAT(@@sql_mode, ',ANSI_QUOTES');
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT 1 AS "";
    +---+
    |   |
    +---+
    | 1 |
    +---+
    1 row in set (0.00 sec)
    
    -- 正常执行。
    mysql> CREATE TABLE t1 AS SELECT (SELECT 1 AS '');
    Query OK, 1 row affected (0.06 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> DROP TABLE IF EXISTS t1;
    Query OK, 0 rows affected (0.04 sec)
  • GaussDB分区名使用双引号(需要设置SQL_MODE包含ANSI_QUOTES)或反引号时区分大小写,MySQL不区分。
  • MySQL标识符长度限制为64字符,而GaussDB标识符长度限制为63字节。超过标识符的长度限制后,MySQL报错,GaussDB会对标识符截断并告警。
  • GaussDB不支持可执行注释。

相关文档