48、数据库互操作性：UPDATE 语句、表与索引创建的跨平台指南

最新推荐文章于 2025-11-11 08:33:35 发布

wind

最新推荐文章于 2025-11-11 08:33:35 发布

阅读量69

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server数据库设计与优化精华文章标签：数据库互操作性 UPDATE语句 CREATE TABLE

本文链接：https://blog.youkuaiyun.com/wind/article/details/149562831

SQL Server数据库设计与优化精华专栏收录该内容

56 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

数据库互操作性：UPDATE 语句、表与索引创建的跨平台指南

1. 数据库中的特殊子句与游标性能

在数据库操作中，SQL Server 2005 的 PIVOT 和 UNPIVOT 子句能重现许多功能。而 MODEL 与 PIVOT 不同，它允许将值插入或更新回基表。

游标在不同数据库中的性能表现有所差异。在 SQL Server 中，游标可能对性能产生显著影响，但在 Oracle 中并非如此。这是因为 Oracle 的所有 SELECT 语句在幕后都会执行一个隐式游标，所以 Oracle 的 SELECT 语句和游标实际上是一回事。而 SQL Server 使用不同的引擎操作来执行查询和游标，因此游标可能会影响性能。

2. UPDATE 语句详解

2.1 通用 UPDATE 语句语法

UPDATE 语句用于修改表中现有记录的值，其最具互操作性的语法如下：

UPDATE {table_name | view_name} 
SET column_name = { DEFAULT | NULL | scalar_expression} [,...] 
WHERE {search_condition | CURRENT OF cursor_name}

2.2 各数据库平台的 UPDATE 语句语法比较

数据库平台	语法
SQL Server	UPDATE {table_name \| view_name \| rowset} [WITH (hint1, hint2 [,…])] SET {column_name = {DEFAULT \| NULL \| scalar_expression } \| variable_name = scalar_expression \| variable_name = column_name = scalar_expression } [,…] [FROM {table1 \| view1 \| nested_table1 \| rowset1} [,…]] [AS alias] [JOIN {table2 [,…]}] WHERE {conditions \| CURRENT OF [GLOBAL] cursor_name} [OPTION (hint1, hint2 [,…])]
DB2	UPDATE [ONLY] ( {table_name \| view_name} ) AS alias SET { {column_name = { DEFAULT \| NULL \| expression } [,…]} \| subquery statement } [WHERE {search_conditions \| CURRENT OF cursor_name}] [WITH { RR \| RS \| CS \| UR }]
MySQL	UPDATE [LOW PRIORITY] [IGNORE] table_name SET column_name = {scalar_expression} [,…] WHERE search_conditions [ORDER BY column_name1 [{ASC \| DESC}] [,…] ] [LIMIT integer]
Oracle	UPDATE [hint] [ONLY] { [schema.]{view_name \| materialized_view_name \| table_name} [@database_link] [alias] {[PARTITION (partition_name)] \| [SUBPARTITION (subpartition_name)]} \| subquery [WITH {[READ ONLY] \| [CHECK OPTION [CONSTRAINT constraint_name] ] \| [TABLE (collection_expression_name) [ ( + ) ] ] } SET {column_name [,…] = {expression [,…] \| subquery} \| VALUE [(alias)] = { value \| (subquery)} [,…] WHERE { search_conditions \| CURRENT OF cursor_name} RETURNING expression [,…] INTO variable [,…]

2.3 各数据库平台 UPDATE 语句的特殊子句及处理方式

SQL Server ：支持一些独特的子句，如 WITH 子句、使用 OPTION 子句的查询提示和二级 FROM 子句。为实现与其他数据库平台的互操作性，应从 UPDATE 语句中移除这些子句。
DB2 ：支持基本的 UPDATE 语句，并增加了事务隔离控制。DB2 中合法但 SQL Server 中不合法的子句包括 ONLY (table_name) 和 WITH {RR | RS | CS | UR}。ONLY (table_name) 子句可防止更新记录级联到目标表或视图的任何子表，SQL Server 中可用触发器实现类似功能。WITH {RR | RS | CS | UR} 子句控制 UPDATE 语句的事务隔离级别，可在 SQL Server 中使用 OPTION 子句手动控制锁定行为或使用 SET TRANSACTION ISOLATION LEVEL 命令模拟。
MySQL ：允许对基本 DELETE 语句进行一些扩展，但不支持 SQL Server 中的某些子句，如 WHERE CURRENT OF 子句。合法但在 SQL Server 中非法的子句有 LOW_PRIORITY、IGNORE 和 ORDER BY 子句 [LIMIT nbr_of_rows]。可使用 SQL Server 提示模拟这些行为，也可使用带有 SELECT TOP 语句作为 WHERE 子句条件的 UPDATE 语句模拟 ORDER BY 和 LIMIT 的行为。例如：

UPDATE inventory
SET wholesale_price = price * .85,
retail_price = price + 1
ORDER BY inventory_id
LIMIT 10;

Oracle ：在 UPDATE 语句的实现中有很多独特的行为，如从物化视图、嵌套子查询以及分区视图和表中删除数据。应避免使用的子句包括 ONLY (table_name)、PARTITION (partition_name) 等。可使用 T - SQL 过程代码在 SQL Server 中模拟其部分行为。

3. 创建数据库对象之创建表

3.1 通用 CREATE TABLE 语句语法

创建表是数据库管理员和程序员的常见操作。不同数据库平台的 CREATE TABLE 语句差异很大，主要体现在表在文件系统上的物理实现方式。创建具有互操作性的 CREATE TABLE 语句时，可使用以下语法：

CREATE [TEMPORARY] TABLE table_name
(column_name data_type attributes [,...] ) |
[CONSTRAINT constraint_type [constraint_name] 
[constraint_column [,...] ]  [,...] ]

特殊属性中最具互操作性的是 nullability（NULL 和 NOT NULL）和分配 DEFAULT 值。约束可用于创建 PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK 约束以及插入 DEFAULT 值，具体如下：
- PRIMARY KEY：声明一个或多个列，其值唯一标识表中的每条记录。
- FOREIGN KEY：将表中的一个或多个列定义为引用另一个表中的 UNIQUE 或 PRIMARY KEY 的列。
- UNIQUE：声明一列或多列的值组合必须唯一。
- CHECK：比较插入列的值是否符合特定条件。
- DEFAULT：声明如果未为列提供值，则插入 DEFAULT 值。

以下是在任何主要数据库平台上创建名为 partners 的表的示例：

CREATE TABLE partners
(prtn_id CHAR(4) NOT NULL,
prtn_name VARCHAR(40),
prtn_address1 VARCHAR(40),
prtn_address2 VARCHAR(40),
city VARCHAR(20),
state CHAR(2),
zip CHAR(5) NOT NULL,
phone CHAR(12),
sales_rep INT,
CONSTRAINT pk_prtn_id  PRIMARY KEY (prtn_id),
CONSTRAINT fk_emp_id   FOREIGN KEY (sales_rep)    
REFERENCES employee(emp_id),
CONSTRAINT unq_zip     UNIQUE (zip) );

3.2 各数据库平台 CREATE TABLE 语句的差异及处理

SQL Server ：为构建可在多个数据库平台使用的语句，应避免以下子句：IDENTITY、NOT FOR REPLICATION、ROWGUIDCOL、CLUSTERED | NONCLUSTERED、WITH FILLFACTOR = n、ON filegroup、TEXTIMAGE ON filegroup。
DB2 ：与 SQL Server 相比，支持多种附加和扩展。在与 SQL Server 互操作时，应避免或翻译以下子句：OF type、AS subquery 等。部分 DB2 子句可转换为 SQL Server 子句，如下表所示：
| DB2 子句 | SQL Server 子句 |
| — | — |
| CREATE TABLE … LIKE | SELECT…INTO |
| ORGANIZE BY (column_name) | CONSTRAINT PRIMARY KEY CLUSTERED (column_name) |
| IN tablespace | ON filegroup |
| INDEX IN tablespace | ON filegroup |
| LONG IN tablespace | ON filegroup |
MySQL ：由于其开源起源，与其他数据库平台有所不同。为确保最高级别的互操作性，应避免以下 CREATE TABLE 子句：IF NOT EXISTS、TYPE = {ISAM | MYISAM | MERGE | MRG_MYISAM} 等。部分 MySQL 子句可转换为 SQL Server 子句，如下表所示：
| DB2 子句 | SQL Server 子句 |
| — | — |
| CREATE TABLE … LIKE | SELECT…INTO |
| TYPE = HEAP | PRIMARY KEY NONCLUSTERED |
| TYPE = {BDB | InnoDB} | |
| AUTO_INCREMENT = int | IDENTITY(seed) |
| DATA DIRECTORY = path (MyISAM table type only) | ON filegroup |
| INDEX DIRECTORY = path (MyISAM table type only) | ON filegroup |
| GLOBAL TEMPORARY | TEMPORARY |
Oracle ：CREATE TABLE 语句的语法最为复杂。为确保最高级别的互操作性，应避免以下子句：AS objectype [[NOT] SUBSTITUTABLE AT ALL LEVELS]、OF XMLTYPE 等。部分 Oracle 子句可转换为 SQL Server 子句，如下表所示：
| DB2 子句 | SQL Server 子句 |
| — | — |
| ORGANIZATION INDEX | PRIMARY KEY NONCLUSTERED |
| ORGANIZATION HEAP | |
| PCTFREE | FILLFACTOR |
| TABLESPACE tablespace_name | ON filegroup |
| USING INDEX create_index_statement | CONSTRAINT PRIMARY KEY CLUSTERED |

4. 创建数据库对象之创建索引

4.1 通用 CREATE INDEX 语句语法

索引可通过提供指向表中记录位置的指针（通常是 B - 树）来加速查询处理。虽然索引很有用，但它不属于 ANSI SQL 标准。具有高度互操作性的“行业标准”CREATE INDEX 子句如下：

CREATE [UNIQUE] INDEX index_name ON table_name (column_name [, ...])

可声明索引为 UNIQUE，防止索引列包含重复值。

4.2 各数据库平台 CREATE INDEX 语句的特殊子句及处理

SQL Server ：创建与其他数据库平台互操作的索引时，应避免以下子句：[NON]CLUSTERED、DESC、WITH option、ON filegroup，同时应避免 WITH option 子句中的所有关键字。
DB2 ：编写要移植到 SQL Server 2005 的 CREATE INDEX 语句时，应避免以下子句：SPECIFICATION ONLY、[DIS]ALLOW REVERSE SCANS 等。可将 DB2 子句 CLUSTER 替换为 SQL Server 的 CLUSTERED，将 DB2 的 PCTFREE 替换为 SQL Server 的 WITH FILLFACTOR。
MySQL ：对标准 CREATE INDEX 语句有两个独特的变体。一是可使用 CREATE FULLTEXT INDEX 语句在 BLOB 列上构建全文搜索目录；二是在声明列作为索引时，可指定要索引的列的长度。

5. 各数据库平台创建索引的特殊情况总结

5.1 各平台创建索引特殊子句对比

数据库平台	需避免的子句	可转换子句
SQL Server	[NON]CLUSTERED、DESC、WITH option、ON filegroup 及 WITH option 子句所有关键字	无
DB2	SPECIFICATION ONLY、[DIS]ALLOW REVERSE SCANS、COLLECT [SAMPLED [DETAILED] STATISTICS、EXTEND USING …、INCLUDE (columnname [,…])、MINPCTUSED int	CLUSTER 替换为 CLUSTERED，PCTFREE 替换为 WITH FILLFACTOR
MySQL	无特定需避免子句，但有独特变体	无
Oracle	无特定需避免子句，但语法复杂	无

5.2 MySQL 创建索引的独特变体操作步骤

创建全文搜索目录
1. 确定要创建全文搜索目录的 BLOB 列所在的表。
2. 使用以下语句创建全文搜索目录：

CREATE FULLTEXT INDEX index_name ON table_name (blob_column);

其中，`index_name` 是索引的名称，`table_name` 是表名，`blob_column` 是 BLOB 列名。

指定索引列长度
1. 确定要索引的列及其长度。
2. 使用以下语句创建索引：

CREATE INDEX index_name ON table_name (column_name(length));

例如，要索引 `VARCHAR(300)` 列的前 100 个字符，可使用：

CREATE INDEX partial_index ON my_table (my_column(100));

6. 数据库互操作性总结与建议

6.1 互操作性关键要点总结

UPDATE 语句 ：不同数据库平台的 UPDATE 语句在语法和支持的子句上有很大差异。为实现互操作性，需移除特定平台的特殊子句，或使用通用语法。例如，SQL Server 的 WITH、OPTION 和二级 FROM 子句，在与其他平台互操作时应移除。
CREATE TABLE 语句 ：各平台在表的物理实现上差异显著。编写互操作的 CREATE TABLE 语句时，使用通用语法，并避免特定平台的特殊子句。如 SQL Server 的 IDENTITY、CLUSTERED 等子句，DB2 的 OF type、AS subquery 等子句。
CREATE INDEX 语句 ：虽然有通用的 CREATE INDEX 语法，但各平台仍有特殊子句。创建互操作的索引时，需避免特定平台的特殊子句，如 SQL Server 的 [NON]CLUSTERED、WITH option 等子句。

6.2 操作建议流程图

graph TD;
    A[开始操作] --> B{选择操作类型};
    B -->|UPDATE 语句| C{是否考虑互操作性};
    C -->|是| D[移除特定平台特殊子句];
    C -->|否| E[使用平台特定语法];
    B -->|CREATE TABLE 语句| F{是否考虑互操作性};
    F -->|是| G[使用通用语法，避免特殊子句];
    F -->|否| H[使用平台特定语法];
    B -->|CREATE INDEX 语句| I{是否考虑互操作性};
    I -->|是| J[避免特定平台特殊子句];
    I -->|否| K[使用平台特定语法];
    D --> L[执行操作];
    E --> L;
    G --> L;
    H --> L;
    J --> L;
    K --> L;
    L --> M[结束操作];

6.3 具体操作建议列表

UPDATE 语句
- 当需要在多个平台间迁移时，先编写通用的 UPDATE 语句，再根据目标平台进行微调。
- 对于特殊子句的功能，可使用通用的方法实现。如 MySQL 的 LOW_PRIORITY 和 IGNORE 子句，可使用 SQL Server 提示模拟。
CREATE TABLE 语句
- 尽量使用通用的约束和属性，如 NULL、NOT NULL 和 DEFAULT。
- 对于特定平台的特殊功能，如 SQL Server 的 CLUSTERED 索引，在互操作时需谨慎使用。
CREATE INDEX 语句
- 使用通用的 CREATE INDEX 语法，避免使用特定平台的特殊子句。
- 对于 MySQL 的独特变体，在跨平台时需考虑是否有替代方案。

通过以上对数据库互操作性的详细介绍，包括 UPDATE 语句、CREATE TABLE 语句和 CREATE INDEX 语句在不同平台的差异及处理方法，我们可以更好地在多个数据库平台间进行开发和维护工作。遵循互操作性原则，能够减少代码的修改和调试成本，提高开发效率。