附录F 兼容性补充参考

本附录收录 Oracle、MySQL、PostgreSQL 各兼容模式正文中未覆盖的部分功能示例。

Oracle 兼容性补充示例

Oracle 兼容功能已在 Oracle 兼容性正文中覆盖，包括数据类型、系统函数、特色 SQL 语法（含 DUAL 伪表、CTE 等，详见Oracle 特色 SQL 语法）、视图、PL/SQL 及其他兼容特性，本附录暂无额外补充。

MySQL 兼容性补充示例

日期类型

支持 DATE、DATETIME、TIMESTAMP、YEAR 等日期和时间类型。

mysql> CREATE TABLE t_date (id INT, d DATE, dt DATETIME, ts TIMESTAMP, y YEAR);
mysql> INSERT INTO t_date VALUES(1,'2024-01-01','2024-01-01 10:00:00','2024-01-01 10:00:00',2024);
mysql> SELECT * FROM t_date;
+------+------------+---------------------+---------------------+------+
| id   | d          | dt                  | ts                  | y    |
+------+------------+---------------------+---------------------+------+
|    1 | 2024-01-01 | 2024-01-01 10:00:00 | 2024-01-01 10:00:00 | 2024 |
+------+------------+---------------------+---------------------+------+

二进制类型

支持 BLOB 等二进制数据类型。

mysql> CREATE TABLE t_blob (id INT, data BLOB);
mysql> INSERT INTO t_blob VALUES(1, 'binary_data');
mysql> SELECT * FROM t_blob;
+------+-------------+
| id   | data        |
+------+-------------+
|    1 | binary_data |
+------+-------------+

自增属性（AUTO_INCREMENT）

建表时为字段指定 AUTO_INCREMENT 属性，可实现自增主键。

mysql> CREATE TABLE t_auto (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
mysql> INSERT INTO t_auto (name) VALUES('A'),('B'),('C');
mysql> SELECT * FROM t_auto;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+------+------+

LAST_INSERT_ID

获取最近一次插入操作生成的自增 ID。

mysql> CREATE TABLE test1 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
mysql> INSERT INTO test1 (name) VALUES ('Alice');
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

反引号引用

使用反引号引用表名、列名中的保留字。

mysql> CREATE TABLE \`order\` (\`id\` INT PRIMARY KEY, \`desc\` VARCHAR(50));
mysql> INSERT INTO \`order\` VALUES(1,'test');
mysql> SELECT * FROM \`order\` WHERE \`id\`=1;
+------+------+
| id   | desc |
+------+------+
|    1 | test |
+------+------+

跨库查询

同一实例中支持跨数据库查询。

mysql> CREATE DATABASE db1;
mysql> CREATE DATABASE db2;
mysql> CREATE TABLE db1.t1 (id INT, name VARCHAR(50));
mysql> CREATE TABLE db2.t1 (id INT, val VARCHAR(50));
mysql> INSERT INTO db1.t1 VALUES(1,'Alice');
mysql> INSERT INTO db2.t1 VALUES(1,'ScoreA');
mysql> SELECT * FROM db1.t1 JOIN db2.t1 ON db1.t1.id = db2.t1.id;
+------+-------+------+--------+
| id   | name  | id   | val    |
+------+-------+------+--------+
|    1 | Alice |    1 | ScoreA |
+------+-------+------+--------+
mysql> DROP DATABASE db1, db2;

FORCE INDEX

强制查询使用指定索引。

mysql> CREATE TABLE t_frc (id INT PRIMARY KEY, name VARCHAR(50), INDEX idx_name(name));
mysql> INSERT INTO t_frc VALUES(1,'Alice'),(2,'Bob');
mysql> SELECT * FROM t_frc FORCE INDEX (idx_name) WHERE name='Alice';
+------+-------+
| id   | name  |
+------+-------+
|    1 | Alice |
+------+-------+

CREATE TABLE ... AS SELECT

从查询结果直接创建表。

mysql> CREATE TABLE test_as SELECT VERSION() AS version;
mysql> SELECT * FROM test_as;
+------------+
| version    |
+------------+
| 5.7.32-log |
+------------+

CREATE TABLE ... LIKE

复制表结构创建新表。

mysql> CREATE TABLE test1 (id INT PRIMARY KEY, name VARCHAR(50));
mysql> INSERT INTO test1 VALUES(1,'John'),(2,'Jane');
mysql> CREATE TABLE test1_copy LIKE test1;
mysql> INSERT INTO test1_copy SELECT * FROM test1;
mysql> SELECT * FROM test1_copy;
+------+------+
| id   | name |
+------+------+
|    1 | John |
|    2 | Jane |
+------+------+

多表关联查询

支持多表 JOIN。

mysql> CREATE TABLE stu(id INT PRIMARY KEY, name CHAR(10), classid INT);
mysql> CREATE TABLE class(id INT PRIMARY KEY, name CHAR(10), teacher CHAR(10));
mysql> INSERT INTO stu VALUES(1,'A',1),(2,'B',1),(3,'C',2);
mysql> INSERT INTO class VALUES(1,'实验班','小红'),(2,'普通班','小蓝');
mysql> SELECT stu.id, stu.name, class.name AS class_name FROM stu INNER JOIN class ON stu.classid = class.id;
+----+------+-----------+
| id | name | class_name|
+----+------+-----------+
|  1 | A    | 实验班    |
|  2 | B    | 实验班    |
|  3 | C    | 普通班    |
+----+------+-----------+

多表 JOIN 不带 ON

支持不使用 ON 条件的多表 JOIN。

mysql> CREATE TABLE t1 (id INT, name VARCHAR(50));
mysql> CREATE TABLE t2 (id INT, val VARCHAR(50));
mysql> INSERT INTO t1 VALUES(1,'A'),(2,'B');
mysql> INSERT INTO t2 VALUES(1,'X'),(2,'Y');
mysql> SELECT * FROM t1 JOIN t2 WHERE t1.id = t2.id;
+------+------+------+------+
| id   | name | id   | val  |
+------+------+------+------+
|    1 | A    |    1 | X    |
|    2 | B    |    2 | Y    |
+------+------+------+------+

多表 UPDATE

支持多表关联更新。

mysql> CREATE TABLE author (id INT PRIMARY KEY, name VARCHAR(100), country VARCHAR(100));
mysql> CREATE TABLE book (id INT PRIMARY KEY, title VARCHAR(100), author_id INT, price DECIMAL(10,2));
mysql> INSERT INTO author VALUES(1,'Author1','USA'),(2,'Author2','UK');
mysql> INSERT INTO book VALUES(1,'Book1',1,10.00),(2,'Book2',2,15.00);
mysql> UPDATE book b INNER JOIN author a ON b.author_id = a.id
    -> SET b.price = b.price * 1.1 WHERE a.country = 'USA';
mysql> SELECT * FROM book;
+------+-------+-----------+-------+
| id   | title | author_id | price |
+------+-------+-----------+-------+
|    2 | Book2 |         2 | 15.00 |
|    1 | Book1 |         1 | 11.00 |
+------+-------+-----------+-------+

MySQL 用户变量

支持使用 SET @var = value 定义用户变量。

mysql> SET @myvar = 100;
mysql> SELECT @myvar + 1;
+------------+
| @myvar + 1 |
+------------+
|        101 |
+------------+

别名语法

支持使用 AS 或省略 AS 为查询结果指定别名。

mysql> SELECT VERSION() AS v;
+------------+
| v          |
+------------+
| 5.7.32-log |
+------------+
mysql> SELECT VERSION() v;
+------------+
| v          |
+------------+
| 5.7.32-log |
+------------+

关键字作为表名

使用反引号引用 MySQL 保留字作为表名。

mysql> CREATE TABLE \`user\` (id INT, name VARCHAR(50));
mysql> INSERT INTO \`user\` VALUES(1,'Alice');
mysql> SELECT * FROM \`user\`;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Alice |
+------+-------+
mysql> DROP TABLE \`user\`;

PostgreSQL 兼容性补充示例

表达式索引

支持在表达式上创建索引。

postgres=# CREATE TABLE halo_test (id INT, name TEXT);
postgres=# CREATE INDEX idx_expr ON halo_test (lower(name));
postgres=# INSERT INTO halo_test VALUES (1, 'Alice');
postgres=# SELECT * FROM halo_test WHERE lower(name) = 'alice';
 id | name  
----+-------
  1 | Alice
(1 行记录)
postgres=# DROP TABLE halo_test;

函数索引

支持在函数返回值上创建索引。

postgres=# CREATE TABLE halo_test (id INT, info JSONB);
postgres=# CREATE INDEX idx_func ON halo_test ((info->>'key'));
postgres=# INSERT INTO halo_test VALUES (1, '{"key": "value1"}');
postgres=# SELECT * FROM halo_test WHERE info->>'key' = 'value1';
 id |        info        
----+-------------------
  1 | {"key": "value1"}
(1 行记录)
postgres=# DROP TABLE halo_test;

Hash 索引

支持使用 Hash 算法创建索引，适用于等值查询场景。

postgres=# CREATE TABLE t_hash (id INT, name TEXT);
postgres=# CREATE INDEX idx_hash ON t_hash USING hash (id);
postgres=# INSERT INTO t_hash VALUES (1, 'test');
postgres=# SELECT * FROM t_hash WHERE id = 1;
 id | name 
----+------
  1 | test
(1 行记录)
postgres=# DROP TABLE t_hash;

组合索引

支持在多个列上创建组合索引。

postgres=# CREATE TABLE t_compound (a INT, b INT, c INT);
postgres=# CREATE INDEX idx_compound ON t_compound (a, b);
postgres=# INSERT INTO t_compound VALUES (1, 2, 3);
postgres=# SELECT * FROM t_compound WHERE a = 1 AND b = 2;
 a | b | c 
---+---+---
 1 | 2 | 3
(1 行记录)
postgres=# DROP TABLE t_compound;

唯一索引

通过唯一索引确保列值不重复。

postgres=# CREATE TABLE t_unique (id INT, email TEXT);
postgres=# CREATE UNIQUE INDEX idx_unique_email ON t_unique (email);
postgres=# INSERT INTO t_unique VALUES (1, 'test@test.com');
postgres=# SELECT * FROM t_unique;
 id |     email     
----+---------------
  1 | test@test.com
(1 行记录)
postgres=# DROP TABLE t_unique;

虚拟列（生成列）

支持使用 GENERATED ALWAYS AS 创建虚拟列。

postgres=# CREATE TABLE halo_test (
    id INT,
    price NUMERIC,
    qty INT,
    total NUMERIC GENERATED ALWAYS AS (price * qty) STORED
);
postgres=# INSERT INTO halo_test (id, price, qty) VALUES (1, 10.5, 3);
postgres=# SELECT * FROM halo_test;
 id | price | qty | total 
----+-------+-----+-------
  1 |  10.5 |   3 |  31.5
(1 行记录)
postgres=# DROP TABLE halo_test;

部分索引

只对满足条件的行建立索引。

postgres=# CREATE TABLE t_part (id INT, status TEXT, amount NUMERIC);
postgres=# CREATE INDEX idx_part ON t_part (amount) WHERE status = 'ACTIVE';
postgres=# INSERT INTO t_part VALUES (1, 'ACTIVE', 100.0), (2, 'INACTIVE', 200.0);
postgres=# SELECT * FROM t_part WHERE status = 'ACTIVE' AND amount > 50;
 id | status | amount 
----+--------+--------
  1 | ACTIVE |  100.0
(1 行记录)
postgres=# DROP TABLE t_part;

分区表分区下线

支持将分区从分区表中分离（DETACH）。

postgres=# CREATE TABLE test_range (id INT, data TEXT) PARTITION BY RANGE (id);
postgres=# CREATE TABLE test_range_y2006m02 PARTITION OF test_range FOR VALUES FROM (1) TO (100);
postgres=# INSERT INTO test_range VALUES (50, 'data1');
postgres=# SELECT * FROM test_range;
 id  | data  
-----+-------
  50 | data1
(1 行记录)
postgres=# ALTER TABLE test_range DETACH PARTITION test_range_y2006m02;
postgres=# SELECT * FROM test_range;
 id | data 
----+------
(0 行记录)
postgres=# DROP TABLE test_range, test_range_y2006m02;

全连接

FULL JOIN 返回左右表中所有的行。

postgres=# CREATE TABLE t_a (id INT, name TEXT);
postgres=# CREATE TABLE t_b (id INT, val TEXT);
postgres=# INSERT INTO t_a VALUES (1,'A'),(2,'B');
postgres=# INSERT INTO t_b VALUES (1,'AA'),(3,'CC');
postgres=# SELECT * FROM t_a FULL JOIN t_b ON t_a.id = t_b.id;
 id | name | id | val 
----+------+----+-----
  1 | A    |  1 | AA
  2 | B    |    | 
    |      |  3 | CC
(3 行记录)
postgres=# DROP TABLE t_a, t_b;

表注释

支持为表和列添加注释。

postgres=# CREATE TABLE halo_test (id INT PRIMARY KEY, name TEXT);
postgres=# COMMENT ON TABLE halo_test IS '示例表';
postgres=# COMMENT ON COLUMN halo_test.name IS '用户名称';
postgres=# SELECT obj_description('halo_test'::regclass);
 obj_description 
-----------------
 示例表
(1 行记录)
postgres=# DROP TABLE halo_test;

字段缺省值

支持为字段设置默认值。

postgres=# CREATE TABLE halo_test (
    id SERIAL,
    name TEXT DEFAULT 'unknown',
    created_at TIMESTAMP DEFAULT NOW()
);
postgres=# INSERT INTO halo_test DEFAULT VALUES;
postgres=# SELECT * FROM halo_test;
 id |  name   |         created_at         
----+---------+----------------------------
  1 | unknown | 2026-07-30 17:06:52.482023
(1 行记录)
postgres=# DROP TABLE halo_test;

表填充率（fillfactor）

设置表的填充因子，为更新操作预留空间。

postgres=# CREATE TABLE halo_test (id INT, name TEXT) WITH (fillfactor = 80);
postgres=# INSERT INTO halo_test VALUES (1, 'test');
postgres=# SELECT reloptions FROM pg_class WHERE relname = 'halo_test';
   reloptions    
-----------------
 {fillfactor=80}
(1 行记录)
postgres=# DROP TABLE halo_test;

类型转换

支持 CAST() 函数、:: 运算符和 to_char() 进行类型转换。

postgres=# SELECT CAST('123' AS INTEGER);
 int4 
------
  123
(1 行记录)

postgres=# SELECT '123'::INTEGER;
 int4 
------
  123
(1 行记录)

postgres=# SELECT to_char(1234.56, '9999.99');
 to_char  
----------
  1234.56
(1 行记录)

索引名长度规则

支持较长的索引名称。

postgres=# CREATE TABLE inx_name_len (id INT);
postgres=# CREATE INDEX a_very_long_index_name_for_testing_purpose ON inx_name_len (id);
postgres=# SELECT indexname FROM pg_indexes WHERE tablename = 'inx_name_len';
                 indexname                  
--------------------------------------------
 a_very_long_index_name_for_testing_purpose
(1 行记录)
postgres=# DROP TABLE inx_name_len;