超级会员免费看
数据库互操作性：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 创建索引的独特变体操作步骤
-
创建全文搜索目录
- 确定要创建全文搜索目录的 BLOB 列所在的表。
- 使用以下语句创建全文搜索目录：
CREATE FULLTEXT INDEX index_name ON table_name (blob_column);
其中，`index_name` 是索引的名称，`table_name` 是表名，`blob_column` 是 BLOB 列名。
-
指定索引列长度
- 确定要索引的列及其长度。
- 使用以下语句创建索引：
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 语句在不同平台的差异及处理方法，我们可以更好地在多个数据库平台间进行开发和维护工作。遵循互操作性原则，能够减少代码的修改和调试成本，提高开发效率。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
