子兮子兮

本文主要对使用原生 SQL 操作数据库时如何同时兼容以下多种数据库进行说明。

1. SQL Server: https://learn.microsoft.com/zh-cn/sql/sql-server/
2. PostgreSQL: https://www.postgresql.org/docs/current/
   • KingBaseES: https://help.kingbase.com.cn/v8/index.html
3. MySQL: https://dev.mysql.com/doc/refman/8.3/en/
   • TiDB: https://docs.pingcap.com/zh/tidb/stable
   • MariaDB: https://github.com/MariaDB
4. Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/index.html
5. 达梦 DM8: https://www.dameng.com/DM8.html
6. SQLite: https://www.sqlite.org/doclist.html

一、不同数据库中库、模式和表的支持情况说明

在不同的数据库系统中，数据库 database、模式 schema 和表 table 之间的关系可能存在一些差异。
需要注意的是，对于数据库、模式和表的命名规范和处理方式可能会有所不同。
在不同数据库中执行相关操作时，需要特别留意这些差异，以确保操作的正确性和一致性。

如上表所示，不同类型的数据库对于库 database 和模式 schema 的支持情况存在一些差异。
由于存在 schema 概念的数据库基本都支持跨 schema 操作，而有些数据库无法或很难进行跨数据库操作，
对于跨数据库操作需求，尽量使用多个 schema 而不是多个 database，以确保跨数据库操作的一致性。

在 SQL Server 数据库中开发时，有时可能会在 SQL 语句中显式指定默认模式 schema 名称，如：

SELECT * FROM dbo.table_name

为兼容多种数据库，如果使用默认模式，应该去掉模式名称，直接使用表名：

SELECT * FROM table_name

二、原生 SQL 多数据库兼容说明

（一）标识符

1. 标识符长度

在不同的数据库系统中，标识符（如表名、列名等）的长度限制可能存在差异。
一些数据库系统可能对标识符的长度有严格的限制，而另一些则可能较为宽松。
不同数据库的标识符长度限制如下所示：

由于以上数据库标识符限制长度最小的为 30 个字符，为了在不同数据库中保持一致的标识符长度，
建议在创建表和列时使用不超过 30 个字符的标识符命名，以确保在不同数据库中都能够正常使用。

因此，在编写 SQL 语句时，需要注意目标数据库系统的标识符长度限制，以确保标识符的命名不会超出限制，避免出现命名截断或错误。

2. 标识符大小写

某些数据库系统对于标识符的大小写是敏感的，在编写 SQL 语句时，应该考虑目标数据库系统的大小写敏感性，以确保使用正确的大小写命名对象。
否则，在敏感大小写的数据库系统中可能会出现无法找到对象或语法错误的问题。

例如，在 PostgreSQL 中，标识符 "tb_example" 和 "TB_EXAMPLE" 是两个不同的表，而在 SQL Server 中这两个标识符则指向同一个表：

-- DROP TABLE "tb_example";
CREATE TABLE "tb_example" (
    "id" int primary key
);
SELECT * FROM "tb_example";

-- DROP TABLE "TB_EXAMPLE";
CREATE TABLE "TB_EXAMPLE" (
    "ID" int primary key
);
SELECT * FROM "TB_EXAMPLE";

在标识符区分大小写的数据库系统中，使用双引号 "" 包裹标识符用于明确指定标识符的大小写，不加双引号的标识符被视为不区分大小写。

在没有明确指定标识符的大小写时：

• PostgreSQL 数据库都会当作小写标识符来处理；
• Oracle 数据库都会当作大写标识符来处理。

例如，未使用双引号 "" 包裹的标识符 tb_example 和 TB_EXAMPLE，
在 PostgreSQL 中都指向 "tb_example" 这张表，
而在 Oracle 中都指向 "TB_EXAMPLE" 这张表：

-- 在 PostgreSQL 中都指向 "tb_example"，在 Oracle 中都指向 "TB_EXAMPLE"
SELECT * FROM tb_example;
SELECT * FROM TB_EXAMPLE;

明确指定标识符，还可以防止标识符被识别为数据库关键字或保留字，避免因使用关键字作为标识符导致的语法错误。

下面是在各个数据库中如何明确指定标识符（例如表名、列名等）的语法示例：

（二）常用数据类型

对于同一个字段，在不同的数据库中可能需要使用不同的数据类型，如：

• 大文本类型在 SQL Server 数据库中可以使用 [n]varchar(max) 或 [n]text，而在 Oracle 数据库中则需要使用 [n]clob；
• 布尔类型在 SQL Server 数据库中使用 bit，在 Oracle 数据库中则需要使用 number[(1)]、在其他数据库中则需要使用 boolean。

1. 字符类型

符类型在不同的数据库系统中可能有不同的命名和定义方式。
一些常见的字符类型包括字符、字符串、文本等，但具体的长度限制、字符集支持和存储方式可能会因数据库系统而异。
在设计数据库表结构时，需要考虑目标数据库系统对字符类型的支持情况，以及字符数据在不同数据库系统之间的转换和兼容性。

以下是不同数据库中支持的字符类型：

以上所有数据库都支持的字符类型有 varchar 和 text，因此在原生 SQL 语句中应该尽量使用这些类型。

2. 数字类型

不同的数据库系统对于数字类型的定义和支持可能存在差异。
一些常见的数字类型包括整数、小数、浮点数等，但具体的数据范围、精度和存储方式可能因数据库系统而异。
因此，在设计数据库结构时，需要根据目标数据库系统的要求选择合适的数字类型，以确保数据的准确性和完整性。

以下是不同数据库中支持的数字类型：

以上所有数据库都支持的数字类型有 bigint, int/integer, smallint, tinyint, decimal, float, real，因此在原生
SQL 语句中应该尽量使用这些类型。

3. 布尔类型

布尔类型用于表示逻辑值，通常只有两个取值，如真（true）和假（false）。
虽然大多数数据库系统都支持布尔类型，但某些数据库系统可能使用不同的布尔类型表示真和假，如 0 和 1、true 和 false 等。
因此，在进行跨数据库操作时，需要注意目标数据库系统对布尔类型的定义和支持，以确保数据的一致性和正确性。

以下是不同数据库中支持的布尔类型：

从上表中可以看出，只有 SQL Server 和 PostgreSQL 数据库原生支持布尔类型数据，
其他数据库通过数字类型的 0 和 1 来代表布尔类型的 true 和 false，在跨数据库使用布尔类型的数据时可能需要额外的判断处理。

4. 日期时间类型

不同数据库系统对于日期时间类型的存储格式、精度和时区处理可能存在差异，一些常见的日期时间类型包括日期、时间、日期时间、时间戳等。
在设计数据库表结构时，需要考虑目标数据库系统对日期时间类型的支持情况，以及日期时间数据在不同数据库系统之间的转换和兼容性。

以下是不同数据库中支持的日期时间类型：

从上表可以看出，不同类型的数据库对日期时间类型的支持差异比较大，但 date、time、datetime 和 timestamp 是被支持最多的日期时间类型，
应该尽可能使用这几种类型。

对于在不同数据库系统中数据类型差异较大的情况，开发过程中使用 ORM（对象关系映射框架）来操作数据库比使用原生 SQL
具有更好的兼容性。

（三）SQL 语法

1. 表别名

在编写 SQL 查询语句时，使用表别名可以提高语句的可读性和性能。需要注意不同数据库系统对于表别名的语法和规范可能存在差异，
需要根据目标数据库的要求进行调整和适配。

例如，在 SQL Server 数据库中查询数据时指定别名的语法如下：

select "id" AS identifier, "data_table_name" AS table_name
from "tb_example" AS T
where T."id" = '1';

但在 Oracle 数据库中执行以上 SQL 语句时，将会报 ORA-00933: SQL 命令未正确结束 错误，
是因为 Oracle 数据库在指定表别名时不能使用 AS 关键字，而 SQL Server 数据库也支持在指定表别名时不使用 AS 关键字，
所以去掉 AS 关键字后，以下语句将同时支持在 SQL Server 和 Oracle 数据库中执行：

select "id" AS identifier, "data_table_name" AS table_name
from "tb_example" T
where T."id" = '1';

结论：

• 为 SQL 语句中的表名指定别名时不要使用 AS 关键字进行连接，以便兼容多种数据库！
• 字段名和别名之间使用 AS 关键字进行连接可以兼容多种数据库。

2. 内置函数

不同数据库系统可能支持不同的内置函数，并且函数的语法和参数也可能有所不同。在使用内置函数时，需要查阅目标数据库的文档并注意语法的兼容性。

CAST()

CAST()：
在进行数据类型转换时使用 CAST() 函数，而不是某些数据库中特有的 CONVERT 函数：

SELECT CAST(1 AS CHAR);

-- Oracle
SELECT CAST(1 AS CHAR) FROM DUAL;

COALESCE()

COALESCE()：
在字段值为 NULL 时使用另一个值代替应该使用通用的 COALESCE() 函数，而不是某些数据库中特有的 ISNULL 函数：

SELECT COALESCE(NULL, 1);

-- Oracle
SELECT COALESCE(NULL, 1) FROM DUAL;

结合使用 CAST 和 COALESCE 函数以及通用 DECIMAL 数据类型，可以实现一个兼容多种数据库的将指定字段转换为数字类型的操作：

CAST(COALESCE(column_name, '0') AS DECIMAL(38, 0))

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP：
在 SQL Server 中通常使用 GETDATE()
函数来获取当前时间，但此函数仅能在 SQL Server 中使用，可以使用通用的 CURRENT_TIMESTAMP 函数来代替 GETDATE()
函数或其他数据库中的 now() 函数：

SELECT CURRENT_TIMESTAMP;

-- Oracle
SELECT CURRENT_TIMESTAMP FROM DUAL;

3. 模糊查询

在 PostgreSQL 数据库中，使用 LIKE 关键字进行模糊查询是区分大小写的，而其他数据基本是不区分大小写的。

若需要 SQL 兼容多种数据库，为了使不同数据库中的查询效果一致，在 PostgreSQL 数据库中应该使用 ILIKE 关键字：

-- PostgreSQL：LIKE 关键字区分大小写，ILIKE 关键字不区分大小写
WHERE "column_name" iLIKE '%关键字%'

-- SQL Server、Oracle、DM8
WHERE "column_name" LIKE '%关键字%'

-- MySQL、SQLite
WHERE `column_name` LIKE '%关键字%'

4. 分页查询

对于分页查询，SQL Server 和 Oracle 数据库跟其他数据库的查询方式差异较大，且不同版本之间可能也有不同的查询方式，
不同数据库对应的分页查询语法如下所示：

SQL Server 2012 以前版本的数据库

通过 TOP 和 ROW_NUMBER 函数进行分页查询：

SELECT TOP(10) * FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) NUM, * FROM table_name) TB WHERE NUM > 0

SQL Server 2012 及以后版本的数据库

通过 ORDER BY <column> [OFFSET <offset> ROWS] FETCH NEXT <limit> ROWS ONLY 子句进行分页查询：

SELECT * FROM table_name ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Oracle 12 以前版本的数据库

通过 ROW_NUMBER() 函数进行分页查询：

SELECT * FROM (SELECT T.*, ROW_NUMBER() OVER (ORDER BY column) AS ROW_NUM FROM table_name T)
WHERE ROW_NUM BETWEEN <offset>+1 AND <offset>+<limit>

Oracle 12 及以后版本的数据库

通过 OFFSET <offset> ROWS FETCH NEXT <limit> ROWS ONLY 子句进行分页查询：

SELECT * FROM table_name ORDER BY (SELECT NULL FROM DUAL) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

PostgreSQL、MySQL 和 SQLite 等数据库

通过 LIMIT <limit> OFFSET <offset> 子句进行分页查询：

SELECT * FROM table_name LIMIT 10 OFFSET 0

[!Tip]

有些数据库在分页查询时可以不显式指定排序字段，但建议
在分页查询时一定要显式指定排序字段，以确保分页查询正确、高效执行以及查询结果的顺序一致性。

5. 拼接多个字段为一个字符串

若要拼接多个字段为一个字符串，并自动转换非字符类型字段为字符类型，可以使用 CONCAT() 函数或 || 双竖线符号进行处理：

• PostgreSQL、SQL Server、MySQL 和达梦 DM8 数据库支持使用 CONCAT() 函数拼接多个字段为一个字符串；
• PostgreSQL、SQLite 和达梦 DM8 数据库支持使用 || 双竖线符号拼接多个字段为一个字符串；
• 其中 PostgreSQL 和达梦 DM8 数据库两种方式都支持，如下所示：

使用 CONCAT() 函数

-- PostgreSQL、SQL Server、MySQL、DM8
SELECT CONCAT('Hello ', 'World!', 123);

使用 || 双竖线符号

-- PostgreSQL、DM8、SQLite
SELECT 'Hello ' || 'World!' || 123;

-- Oracle、DM8
SELECT 'Hello ' || 'World!' || 123 FROM DUAL;

另外，SQL Server 中纯字符串类型的拼接可以直接使用 + 加号进行处理。

6. 拼接指定字段的多行数据为一行

使用 STUFF 函数

SQL Server 和 DM8 可通过 STUFF 和 FOR XML PATH('') 使用指定分隔符拼接多个字段为一个字符串：

/* SQL Server */
SELECT STUFF((SELECT ',' + "name" FROM sys.tables FOR XML PATH('')), 1, 1, '') table_names
-- RESULT: spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,MSreplication_options

/* DM8 */
SELECT STUFF((SELECT ',' || "NAME" FROM SYS.SYSOBJECTS WHERE TYPE$ = 'SCH' FOR XML PATH('')), 1, 1, '') table_names
-- RESULT: CTISYS,DMHR,OTHER,PERSON,PRODUCTION,PURCHASING,RESOURCES,SALES,SYS,SYSAUDITOR,SYSDBA,SYSSSO

使用 STRING_AGG 函数

PostgreSQL 和 SQLite 数据库可通过 STRING_AGG() 函数使用指定分隔符拼接多个字段为一个字符串：

/* PostgreSQL */
SELECT STRING_AGG("table_name", ',') FROM "information_schema"."tables" WHERE "table_schema"='information_schema' AND "table_type"='BASE TABLE';
-- RESULT: sql_features,sql_implementation_info,sql_sizing

/* SQLite */
SELECT STRING_AGG("name", ',') FROM "sqlite_master" WHERE "type"='table';
-- RESULT: table_name_1,table_name_2,table_name_3

使用 LISTAGG 函数

Oracle 数据库可通过 LISTAGG() 函数使用指定分隔符拼接多个字段为一个字符串：

SELECT LISTAGG(TABLE_NAME, ',') WITHIN GROUP (ORDER BY TABLE_NAME) FROM ALL_TABLES WHERE LENGTH(TABLE_NAME) < 5 AND REGEXP_LIKE(TABLE_NAME, '^[a-zA-Z]+$');
-- RESULT: DUAL,GSM,HELP,VNCR

使用 GROUP_CONCAT 函数

MySQL 数据库可通过 GROUP_CONCAT() 函数使用指定分隔符拼接多个字段为一个字符串：

SELECT GROUP_CONCAT(`SCHEMA_NAME` ORDER BY `SCHEMA_NAME` SEPARATOR ',') FROM `information_schema`.`SCHEMATA`
-- RESULT: information_schema,mysql

未完待续...