更新时间: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不支持可执行注释。
父主题: SQL