mySQL表变量和临时表的区别_表变量和临时表的比较

总结

特性

表变量

临时表

作用域

当前批处理

当前会话，嵌套存储过程，全局：所有会话

使用场景

自定义函数，存储过程，批处理

自定义函数，存储过程，批处理

创建方式

DECLARE statement only.只能通过DECLEARE语句创建

CREATE TABLE 语句

SELECT INTO 语句.

表名长度

最多128字节

最多116字节

列类型

可以使用自定义数据类型

可以使用XML集合

自定义数据类型和XML集合必须在TempDb内定义

Collation

字符串排序规则继承自当前数据库

字符串排序规则继承自TempDb数据库

索引

索引必须在表定义时建立

索引可以在表创建后建立

约束

PRIMARY KEY, UNIQUE, NULL, CHECK约束可以使用，但必须在表建立时声明

PRIMARY KEY, UNIQUE, NULL, CHECK. 约束可以使用，可以在任何时后添加，但不能有外键约束

表建立后使用DDL (索引，列)

不允许

允许.

数据插入方式

INSERT 语句 (SQL 2000: 不能使用INSERT/EXEC).

INSERT 语句, 包括 INSERT/EXEC.

SELECT INTO 语句.

Insert explicit values into identity columns (SET IDENTITY_INSERT).

不支持SET IDENTITY_INSERT语句

支持SET IDENTITY_INSERT语句

Truncate table

不允许

允许

析构方式

批处理结束后自动析构

显式调用 DROP TABLE 语句.

当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.)

事务

只会在更新表的时候有事务，持续时间比临时表短

正常的事务长度，比表变量长

存储过程重编译

否

会导致重编译

回滚

不会被回滚影响

会被回滚影响

统计数据

不创建统计数据，所以所有的估计行数都为1,所以生成执行计划会不精准

创建统计数据，通过实际的行数生成执行计划。

作为参数传入存储过程

仅仅在SQL Server2008, 并且必须预定义 user-defined table type.

不允许

显式命名对象 (索引, 约束).

不允许

允许，但是要注意多用户的问题

动态SQL

必须在动态SQL中定义表变量

可以在调用动态SQL之前定义临时表

参考：

1)INF: Frequently Asked Questions - SQL Server 2000 - Table Variables

2)T-SQL BOL (SQL 2000), table data type

3)T-SQL BOL (SQL 2008), Declare @local_variable

4)T-SQL BOL (SQL 2008), CREATE TABLE

5)Table-Valued Parameters (Database Engine)

6)Troubleshooting stored procedure recompilation

7)Local Temporary Tables and Table Variables

8)Startup stored procedure

9)Data Definition Language (DDL)

44/4<1234