跟所有数据库一样，OceanBase 数据库的 SQL、PL/SQL 代码中关于保留字和关键字的使用有一些推荐的最佳实践。
本文主要介绍使用保留字和关键字时的这些最佳实践。
详细说明
OceanBase 数据库的 SQL、PL/SQL 代码中关于保留字和关键字的使用的最佳实践如下。
表名和列名只能使用字母、数字和下划线，并且必须以字母开头，不得使用系统保留字和特殊字符。
SQL 语句 select 投影字段中禁止使用数据库保留字，如果必须使用数据库保留字时需要加上反引号，如：select
key。PL/SQL 代码的参数禁止使用数据库保留字，如果必须使用数据库保留字时需要加上双引号，如："mode" varchar2。
具体示例如下。
a) Oracle 模式的列名如果是 OceanBase 数据库的保留字的话（比如 ALTER），需要加上双引号""，否则会报错的。
-- Oracle 模式需要加上双引号，不能用反引号 `` 。
obclient(SYS@oracle)[SYS]> CREATE TABLE T1 (ALTER VARCHAR2(10));
ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'ALTER VARCHAR2(10))' at line 1
obclient(SYS@oracle)[SYS]> CREATE TABLE T1 (`ALTER` VARCHAR2(10));
ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '`ALTER` VARCHAR2(10))' at line 1
obclient(SYS@oracle)[SYS]> CREATE TABLE T1 ("ALTER" VARCHAR2(10));
Query OK, 0 rows affected (0.087 sec)
obclient(SYS@oracle)[SYS]> DESC T1;
+-------+--------------+------+------+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| ALTER | VARCHAR2(10) | YES | NULL | NULL | NULL |
+-------+--------------+------+------+---------+-------+
1 row in set (0.008 sec)
obclient(SYS@oracle)[SYS]> SHOW CREATE TABLE T1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| T1 | CREATE TABLE "T1" (
"ALTER" VARCHAR2(10)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)
b) MySQL 模式的列名如果是 OceanBase 数据库的保留字的话（比如 ALTER），需要加上反引号 ``，否则会报错的。
-- MySQL模式需要加上反引号 ``，不能用双引号 "" 。
MySQL [test]> create table t1 (alter varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'alter varchar(10))' at line 1
MySQL [test]> create table t1 ("alter" varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '"alter" varchar(10))' at line 1
MySQL [test]> create table t1 (`alter` varchar(10));
Query OK, 0 rows affected (0.098 sec)
MySQL [test]> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| alter | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.009 sec)
MySQL [test]> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`alter` varchar(10) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)
c) Oracle 模式 PL 的参数如果是 OceanBase 数据库的保留字的话（比如 MODE），需要加上双引号""，否则会报错的。
-- Oracle 模式需要加上双引号，不能用反引号 ``。
obclient(SYS@oracle)[SYS]> DELIMITER ;;
obclient(SYS@oracle)[SYS]> CREATE PROCEDURE sp1 (mode varchar2)
AS
BEGIN
null;
END;;
ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'mode' at line 1
obclient(SYS@oracle)[SYS]> DELIMITER ;
obclient(SYS@oracle)[SYS]> DELIMITER ;;
obclient(SYS@oracle)[SYS]> CREATE PROCEDURE sp1 (`mode` varchar2)
AS
BEGIN
null;
END;;
ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '`' at line 1
obclient(SYS@oracle)[SYS]> DELIMITER ;
obclient(SYS@oracle)[SYS]> DELIMITER ;;
obclient(SYS@oracle)[SYS]> CREATE PROCEDURE sp1 ("mode" varchar2)
AS
BEGIN
null;
END;;
Query OK, 0 rows affected (0.053 sec)
obclient(SYS@oracle)[SYS]> DELIMITER ;
obclient(SYS@oracle)[SYS]> show create procedure sp1;
+-----------+-----------------------------------------------------------+--------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| PROCEDURE | SQL_MODE | CREATE PROCEDURE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE COLLATION |
+-----------+-----------------------------------------------------------+--------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| SP1 | PIPES_AS_CONCAT,STRICT_ALL_TABLES,PAD_CHAR_TO_FULL_LENGTH | CREATE OR REPLACE PROCEDURE "SYS"."SP1"
(
"mode" IN varchar2
) IS
BEGIN
null;
END | utf8mb4 | utf8mb4_bin | utf8mb4_bin |
+-----------+-----------------------------------------------------------+--------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.005 sec)
备注：
备注
- Oracle模式预留关键字，参见： 预留关键字（Oracle 模式）。
- MySQL模式预留关键字，参见： 预留关键字（MySQL 模式）。
- PL 保留关键字，参见：PL 保留关键字。
影响租户
影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。
适用版本
OceanBase 数据库 V2.x、V3.x、V4.x 版本。
