Identifiers

Differences in identifiers in M-compatible mode are as follows:

• In GaussDB, unquoted identifiers cannot start with a dollar sign ($). In MySQL unquoted identifiers can start with a dollar sign ($).
• GaussDB unquoted identifiers support case-sensitive database objects.
• GaussDB identifiers support extended characters from U+0080 to U+00FF. MySQL identifiers support extended characters from U+0080 to U+FFFF.
• As for unquoted identifier, a table that starts with a digit and ends with an e or E as the identifier cannot be created in GaussDB. For example:

  -- GaussDB reports an error indicating that this operation is not supported. MySQL supports this operation.
  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);
                          ^

• As for quoted identifiers, tables whose column names contain only digits or scientific computing cannot be directly used in GaussDB. You need to use them in quotes. This rule also applies to the dot operator (.) scenarios. For example:

  -- Create a table whose column names contain only numbers or scientific computing.
  m_db=# CREATE TABLE t1(`123` int, `1e3` int, `1e` int);
  CREATE TABLE
  
  -- Insert data into the table.
  m_db=# INSERT INTO t1 VALUES(7, 8, 9);
  INSERT 0 1
  
  -- The result is not as expected, but is the same as that in MySQL.
  m_db=# SELECT 123 FROM t1;
   ?column? 
  ----------
        123
  (1 row)
  
  -- The result is not as expected, but is the same as that in MySQL.
  m_db=# SELECT 1e3 FROM t1;
   ?column? 
  ----------
       1000
  (1 row)
  
  -- The result is not as expected and is not the same as that in MySQL.
  m_db=# SELECT 1e FROM t1;
   e 
  ---
   1
  (1 row)
  
  -- The correct way to use is as follows:
  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)
  
  -- Dot operator scenarios are not supported by GaussDB but supported by 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;
                    ^
  -- The correct way to use in dot operator scenarios is as follows:
  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

• In GaussDB, the partition name is case-sensitive when it is enclosed in double quotation marks (SQL_MODE must be set to ANSI_QUOTES) or backquotes, but in MySQL the partition name is case-insensitive.