47、数据库操作：跨平台的 SQL 语句使用指南

数据库操作：跨平台的 SQL 语句使用指南

1. 平台特性与标识符规则

不同数据库平台在标识符使用和模式寻址等方面存在差异，具体如下表所示：
| 特性 | DB2 | MySQL | Oracle | SQL Server |
| — | — | — | — | — |
| 标识符是否可保留 | 是 | 否，除非作为带引号的标识符 | 否，除非作为带引号的标识符 | 否，除非作为带引号的标识符 |
| 模式寻址 | Schema.object | Database.object | Schema.object | Server.database.schema.object |
| 标识符是否必须唯一 | 是 | 是 | 是 | 是 |
| 其他规则 | 无 | 不能仅包含数字 | 数据库链接限制为 128 字节，且不能是带引号的标识符 | 无 |

使用带引号的标识符可以打破或至少绕过许多规则。带引号的标识符是用特殊分隔符（通常是双引号）封装的对象名称，SQL Server 2000 和 SQL Server 2005 也常用方括号（[ ]）作为分隔符。在命名对象和选择标识符时，应遵循以下三条经验法则：
- 避免在标识符中使用任何类型的保留字。
- 避免在标识符中使用特定的国家字符。
- 避免在标识符中使用关键字，即可能在产品未来版本中成为保留字的单词。

2. 基本 SQL 语句的跨平台使用

2.1 DELETE 语句

DELETE 语句用于从指定的表中删除记录，最安全且可互操作的形式如下：

DELETE FROM table_name
[WHERE search_condition]

以下是各平台 DELETE 语句语法的比较：
| 数据库平台 | 语法 |
| — | — |
| SQL Server | DELETE [TOP number [PERCENT]] [FROM] table_name [[AS] alias] [FROM table_source [,…]] [ [{INNER | CROSS | [{LEFT | RIGHT | FULL] OUTER}] JOIN joined_table ON condition][,…] [WHERE search_condition | WHERE CURRENT OF [GLOBAL] cursor_name ] [OPTION (query_hint[,…n])] |
| DB2 | DELETE FROM { table_name | ONLY (table_name) } [AS alias] [WHERE {search_conditions | CURRENT OF cursor_name}] [WITH { RR | RS | CS | UR }] |
| MySQL | DELETE [LOW_PRIORITY] [QUICK] [table_name[. ] [,…] ] {FROM table_name[. ] [,…] | [USING table_name[.*] [,…] ] } [WHERE search_condition] [ORDER BY clause] [LIMIT nbr_of_rows] |
| Oracle | DELETE [FROM] {table_name | ONLY (table_name)} [alias] [{PARTITION (partition_name) | SUBPARTITION (subpartition_name)}] | (subquery [WITH {READ ONLY | CHECH OPTION [CONSTRAINT constraint_name]}] ) | TABLE (collection_expression ) [ (+) ] } [WHERE search_condition] [RETURNING expression [,…] INTO variable [,…] ] |

以下是各平台 DELETE 语句的特点：
- SQL Server ：允许使用第二个 FROM 子句进行 JOIN 操作，但部分子句无法移植到其他平台，如 TOP number [PERCENT] 、 FROM table_source [,...] 等。例如：

-- 可互操作的 DELETE 语句
DELETE sales
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE type = 'computer')

-- SQL Server 特有的 DELETE 语句
DELETE s
FROM sales AS s
INNER JOIN titles AS t ON s.title_id = t.title_id
AND type = 'computer'

• DB2 ：支持基本的 DELETE 语句，并增加了事务隔离控制。 ONLY (table_name) 子句可限制目标表或视图的子表中的级联删除， WITH {RR | RS | CS | UR} 子句控制事务隔离级别。
• MySQL ：支持多个扩展，如 USING table_name[.*] [,...] 、 ORDER BY 子句和 LIMIT 子句，但不支持 WHERE CURRENT OF 子句。MySQL 还允许同时从多个表中删除数据，并且在 AUTOCOMMIT 模式下，会用 TRUNCATE 语句替代无 WHERE 子句的 DELETE 语句以提高速度。
• Oracle ：在 DELETE 语句的实现中有很多独特的行为，如从物化视图、嵌套子查询、分区视图和表中删除数据。建议在 SQL Server 和 Oracle 之间迁移时，使用基本的 DELETE 语句。

2.2 INSERT 语句

INSERT 语句用于向表或视图中插入一行或多行数据，基本且最可互操作的形式如下：

INSERT INTO {table_name | view_name} [(column1 [,...] )]
{VALUES (value1 [,...]) | SELECT statement }

各平台 INSERT 语句语法比较如下：
| 数据库平台 | 语法 |
| — | — |
| SQL Server | INSERT [INTO] table_name [(column1 [,…]) {[DEFAULT] VALUES | VALUES (variable1 [,…]) | SELECT_statement | EXEC[UTE] proc_name { [[@param =] value {[OUTPUT]} [,…] ] } |
| DB2 | INSERT INTO table_name [(column1 [,…] )] {VALUES | VALUES (value1 [,…]) | SELECT_statement } [ WITH { RR | RS | CS | UR } ] |
| MySQL | INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] [[database_name.]owner.] table_name [(column1 [,…])] {VALUES (value1 [,…]) | SELECT_statement | SET column1=value1, column2=value2 [,…]} |
| Oracle | 标准 INSERT 语句：INSERT [INTO] {table_name [ [SUB]PARTITION (prtn_name)] | (subquery) [WITH {READ ONLY | CHECK OPTION [CONSTRAINT constr_name] }] | TABLE (collection) [ (+) ] } [alias] [(column1 [,…])] {VALUES (value1[,…]) [RETURNING expression1 [,…] INTO variable1 [,…]] | subquery [WITH {READ ONLY | CHECK OPTION [CONSTRAINT constr_name]} } 条件 INSERT 语句：INSERT {[ALL | FIRST]} WHEN condition THEN 标准 INSERT 语句 ELSE 标准 INSERT 语句 |

各平台 INSERT 语句的特点：
- SQL Server ：支持将存储过程和扩展过程的结果直接插入目标表，但 EXEC[UTE] proc_name { [[@param =] value {[OUTPUT]} [,...] ] } 子句无法移植到其他平台。
- DB2 ：支持基本的 INSERT 语句，并增加了事务隔离设置， WITH {RR | RS | CS | UR} 子句控制事务隔离级别。DB2 严格执行规则，如插入的值必须与列的数据类型兼容。
- MySQL ：支持多个不兼容的 INSERT 语法选项，如 LOW_PRIORITY | DELAYED 、 IGNORE 和 SET column1 = value1 [,...] 。MySQL 在处理数据类型不匹配和过大插入时会进行修剪。
- Oracle ：允许同时向多个表插入数据、条件插入以及向给定的表、视图、分区、子分区或对象表插入数据。

2.3 SELECT 语句

SELECT 语句用于从数据库的一个或多个表中检索行、列和派生值，基本且最可互操作的形式如下：

SELECT [{ALL | DISTINCT}] select_item [[AS] alias] [,...]
FROM {table_name  [[AS] alias] | view_name [[AS] alias]} [,...]
[ [ {INNER | FULL | LEFT [OUTER] | RIGHT [OUTER]} ] JOIN join_condition ]
[WHERE [NOT] search_condition] [ {AND | OR } [ NOT] search_condition [...] ]
[GROUP BY group_by_columns  [HAVING search_condition] ]
[ORDER BY {order_expression [ASC | DESC]} [,...] ]

各平台 SELECT 语句语法比较如下：
| 数据库平台 | 语法 |
| — | — |
| SQL Server | SELECT {[ALL | DISTINCT] | [TOP number [PERCENT] [WITH TIES]]} select_item [AS alias] [INTO new_table_name ] [FROM {[rowset_function | table1 [,…]} [AS alias]] [ [join type]JOIN table2 {[ON join_condition] ] [WHERE search_condition ] [GROUP BY {grouping_column [,…]| ALL}] [ WITH { CUBE | ROLLUP } ] [HAVING search_condition ] [ORDER BY order_by_expression [ ASC | DESC ] ] [COMPUTE {aggregation (expression)} [,…] [BY expression [,…] ] ] [FOR {BROWSE | XML { RAW | AUTO | EXPLICIT} [, XMLDATA][, ELEMENTS][, BINARY base64] ] [OPTION ( [,…]) ] |
| DB2 | SELECT { [ALL | DISTINCT] } select_item [AS alias] [,…] [INTO host_variable [,…] ] FROM [ONLY | OUTER] {table_name | view_name | TABLE ( {function_name | subquery} ) [AS alias] [,…] } [ [join type] JOIN table2 ] [WHERE search_condition] [ {AND | OR | NOT} search_condition [,…] ] [GROUP BY group_by_expression [HAVING search_condition] ] [ORDER BY order_expression [ASC | DESC] ] [FETCH FIRST {n} {ROW | ROWS} ONLY] |
| MySQL | INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] [[database_name.]owner.] table_name [(column1 [,…])] {VALUES (value1 [,…]) | SELECT_statement | SET column1=value1, column2=value2 [,…]} |
| Oracle | 标准 INSERT 语句：INSERT [INTO] {table_name [ [SUB]PARTITION (prtn_name)] | (subquery) [WITH {READ ONLY | CHECK OPTION [CONSTRAINT constr_name] }] | TABLE (collection) [ (+) ] } [alias] [(column1 [,…])] {VALUES (value1[,…]) [RETURNING expression1 [,…] INTO variable1 [,…]] | subquery [WITH {READ ONLY | CHECK OPTION [CONSTRAINT constr_name]} } 条件 INSERT 语句：INSERT {[ALL | FIRST]} WHEN condition THEN 标准 INSERT 语句 ELSE 标准 INSERT 语句 |

各平台 SELECT 语句的特点：
- SQL Server ：支持 ANSI SELECT 语句的大多数基本元素，但部分子句如 [TOP number [PERCENT] [WITH TIES]] 、 [INTO new_table_name ] 等无法移植到其他平台。
- DB2 ：支持基本 SELECT 语句的所有典型子句，并支持 GROUP BY 子句的一个子句 concatenated GROUPING SETS ，还增加了 INTO host_variable [,...] 和 FETCH FIRST {n} {ROW | ROWS} ONLY 子句。
- MySQL ：支持多个不兼容的子句，如 [STRAIGHT_JOIN][ {SQL_SMALL_RESULT | SQL_BIG_RESULT} ][SQL_BUFFER_RESULT] 、 [HIGH_PRIORITY] 等。MySQL 还支持 CROSS JOIN 、 STRAIGHT_JOIN 和 NATURAL JOIN 等 JOIN 语法。
- Oracle ：允许对 SELECT 语句进行大量扩展，如 WITH query_name AS (subquery) [,...] 、 INTO {variable [,...] | record} 等。Oracle 还支持闪回查询和分层查询，但 SQL Server 不支持这些功能。

通过了解各平台的特点和差异，可以更好地编写跨平台的 SQL 语句。在实际应用中，应尽量使用可互操作的语法，避免使用特定平台的特性，以提高代码的可移植性。

数据库操作：跨平台的 SQL 语句使用指南

3. 各平台 SQL 语句差异总结

为了更清晰地了解各平台 SQL 语句的差异，我们对上述内容进行总结，如下表所示：
| 数据库平台 | DELETE 语句特点 | INSERT 语句特点 | SELECT 语句特点 |
| — | — | — | — |
| SQL Server | 允许第二个 FROM 子句进行 JOIN 操作，部分子句不可移植 | 支持将存储过程结果插入目标表，部分子句不可移植 | 支持 ANSI 基本元素，部分子句不可移植 |
| DB2 | 支持基本语句，增加事务隔离控制 | 支持基本语句，增加事务隔离设置 | 支持典型子句，有独特 GROUP BY 子句和其他扩展 |
| MySQL | 支持多个扩展，不支持部分 SQL Server 子句 | 支持多个不兼容语法选项，处理数据类型有特点 | 支持多个不兼容子句，有独特 JOIN 语法 |
| Oracle | 有很多独特删除行为，建议用基本语句 | 允许多表、条件插入等 | 允许大量扩展，支持闪回和分层查询 |

以下是各平台操作的流程图：

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px

    A([开始操作]):::startend --> B{选择数据库平台}:::process
    B --> C(SQL Server):::process
    B --> D(DB2):::process
    B --> E(MySQL):::process
    B --> F(Oracle):::process

    C --> C1(使用可互操作语法，注意不可移植子句):::process
    D --> D1(使用基本语句，利用事务隔离特性):::process
    E --> E1(利用扩展语法，注意数据类型处理):::process
    F --> F1(使用基本语句或处理独特扩展):::process

    C1 --> G([结束操作]):::startend
    D1 --> G
    E1 --> G
    F1 --> G

4. 跨平台 SQL 语句编写建议

在编写跨平台的 SQL 语句时，我们可以遵循以下建议：
- 使用可互操作的语法 ：尽量使用各平台都支持的基本语法，如基本的 DELETE、INSERT 和 SELECT 语句形式。例如，在删除数据时，使用 DELETE FROM table_name [WHERE search_condition] 这种通用形式。
- 避免特定平台特性 ：对于各平台特有的子句和功能，除非必要，否则尽量避免使用。例如，SQL Server 的 TOP number [PERCENT] 子句和 Oracle 的闪回查询功能。
- 考虑数据类型兼容性 ：不同平台在数据类型处理上可能存在差异，如 MySQL 在处理数据类型不匹配时会进行修剪。在编写 SQL 语句时，要确保插入和查询的数据类型在各平台上都能正常工作。
- 测试和验证 ：在将 SQL 代码部署到不同平台之前，一定要进行充分的测试和验证，确保代码在各平台上都能正常运行。

5. 示例代码及分析

以下是一个综合示例，展示如何编写跨平台的 SQL 语句：

-- 可互操作的 DELETE 语句
DELETE FROM sales
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE type = 'computer')

-- 可互操作的 INSERT 语句
INSERT INTO sales (title_id, quantity)
VALUES (123, 10)

-- 可互操作的 SELECT 语句
SELECT title_id, quantity
FROM sales
WHERE quantity > 5

分析：
- DELETE 语句 ：使用了子查询来指定删除条件，这种方式在各平台上都能正常工作。
- INSERT 语句 ：使用了基本的插入语法，指定了表名和列名，并插入了具体的值。
- SELECT 语句 ：使用了基本的查询语法，指定了要查询的列和查询条件。

6. 总结

通过本文的介绍，我们了解了不同数据库平台在标识符规则、基本 SQL 语句（DELETE、INSERT、SELECT）上的特点和差异。在编写跨平台的 SQL 语句时，我们应该遵循以下原则：
1. 了解各平台的特性和差异，避免使用特定平台的不兼容语法。
2. 尽量使用可互操作的语法，提高代码的可移植性。
3. 在实际应用中，对代码进行充分的测试和验证，确保在各平台上都能正常工作。

通过合理运用这些原则，我们可以编写出更加健壮、可移植的 SQL 代码，从而更好地应对不同数据库平台的需求。

希望本文能帮助你在跨平台的数据库开发中更加得心应手，如果你有任何疑问或建议，欢迎留言讨论。