779

分享到

MYSQL & DB2临时表

mysql 数据库 2023-10-06 07:10:13 779人浏览薄情痞子

摘要

Mysql 建立临时表 CREATE TEMPORARY TABLE 创建临时表临时表只在当前连接可见，当关闭连接时，mysql会自动删除表并释放所有空间如果链接到Mysql使用持久性连接，那么只

Mysql 建立临时表

CREATE TEMPORARY TABLE 创建临时表
临时表只在当前连接可见，当关闭连接时，mysql会自动删除表并释放所有空间
如果链接到Mysql使用持久性连接，那么只有在关闭客户端程序时，才会销毁临时表，当然也可以手动销毁。

创建临时表

CREATE TEMPORARY TABLE tablename

创建临时表的语法和 CREATE TABLE tablename 是一样的，只是多了一个 TEMPORARY 关键字

CREATE TEMPORARY TABLE IF NOT EXISTS `tbl_language_tmp`(   `id` INT UNSIGNED AUTO_INCREMENT,   `name` VARCHAR(64) NOT NULL,   `url` VARCHAR(128) NOT NULL,   `founded_at` DATE,   PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

基于已有表格创建临时表
也可以基于其它表格的结构和数据来创建临时表。例如基于admin表格的结构和所有数据来创建临时表：

CREATE TEMPORARY TABLE tmp_admin      select * from admin;

创建临时表时，只需要admin表的结构，不要数据：

CREATE TEMPORARY TABLE TMP_ADMIN SELECT * FROM ADMIN LIMIT 0;

从admin中查询数据并且插入到临时表中：

insert into tmp_admin (id, admin_name, age)       select id, admin_name,age from admin;

使用

SHOW TABLES

命令并不能查看到临时表，但是他是存在的。
插入数据

INSERT INTO `tbl_language_tmp` (`name`,`url`,`founded_at`) VALUES ('python','https://www.twle.cn','1991-2-20');

查询数据

SELECT * FROM tbl_language_tmp;

一些特性

（Https://www.maoyingdong.com/mysql_temporary_tables/
MySQL临时表的理解和用法）

会话结束或者数据库连接断开之后，MySQL会自动删除临时表，如果有需要也可以手动删除；
临时表只能在当前会话中访问，不同会话可以创建同名的临时表，且不会相互影响；
临时表的名称可以和已有的普通表一致（但不建议），出现这种情况的时候，优先使用临时表，这时普通表将无法访问，直到临时表被删除。
show tables语句不会显示临时表；
同一个临时表在一次查询中最多使用一次，否则会报错

select * from tmp_admin where id in (select * from tmp_admin where id >2);select * from tmp_admin join tmp_admin as t2;select * from tmp_admin uNIOn select * from tmp_admin;

报错

ERROR 1137 (HY000): Can't reopen table: 'tmp_admin'

删除临时表

DROP TEMPORARY TABLE tmp_admin（表名）;

全局临时表与会话临时表的区别

全局临时表（Global Temporary Table）和会话临时表（Session Temporary Table）是在数据库中创建临时表的两种方式，它们之间有以下区别：

作用范围：

全局临时表：全局临时表在整个数据库中可见，多个会话可以同时访问和修改全局临时表的数据。全局临时表的数据在所有会话结束后才会被清除。
会话临时表：会话临时表仅在创建它的会话中可见，其他会话无法访问该表。会话临时表的数据在会话结束后会被自动清除。

生命周期：

全局临时表：全局临时表的生命周期与数据库的生命周期相同，即全局临时表的定义会一直存在于数据库中，直到被删除或数据库关闭。
会话临时表：会话临时表的生命周期与创建它的会话的生命周期相同，即会话临时表的定义会在会话结束时自动被删除。
数据共享：

全局临时表：多个会话可以同时读取和修改全局临时表的数据，这使得全局临时表适合用于多个会话之间共享数据的场景。
会话临时表：会话临时表仅在创建它的会话中可见，其他会话无法访问该表，因此会话临时表适用于需要会话私有数据的场景。
根据具体的业务需求和数据共享的要求，可以选择全局临时表或会话临时表来创建临时表。

会话（session）是指什么

在数据库领域中，会话（session）是指用户与数据库系统进行交互的一段时间。它代表了一个用户与数据库建立的连接，通过该连接用户可以执行查询、插入、更新、删除等操作。

当用户与数据库系统建立连接时，系统会为用户创建一个会话。该会话会持续存在，直到用户断开与数据库的连接。在会话期间，用户可以发送 SQL 查询语句、执行事务、访问数据库对象等。

会话通常具有以下特点：

身份验证：在建立会话之前，用户需要进行身份验证，以确保他们有合法的权限来访问数据库。

连接：会话是通过网络或本地连接与数据库建立的连接。一旦连接建立，用户可以与数据库进行通信。

事务管理：会话可以启动、提交或回滚事务。事务用于维护数据库的一致性和完整性。

上下文环境：会话保存了用户的上下文环境，包括当前的数据库、用户权限、设置选项等。

会话变量：会话可以使用会话变量来存储和共享临时数据，这些变量只在会话期间有效。

每个会话都具有唯一的会话标识符（Session ID），用于在数据库系统中标识和管理会话。数据库系统使用会话来跟踪用户的操作和管理资源，确保并发访问的正确性和隔离性。

在多用户环境中，数据库系统能够同时处理多个会话，每个会话都有自己的执行上下文和资源。会话的概念在数据库管理和应用程序开发中非常重要，它提供了一种交互式、持久的用户与数据库系统之间的连接机制。

DB2临时表

DB2-如何建临时表、加索引、Load数据到临时表

建立临时表

DECLARE GLOBAL TEMPORARY TABLE SESSION.EXPORT    (        INSTANCE_ID VARCHAR(32),       INSTANCE_CODE VARCHAR(1000),      CLASSIFIER_ID VARCHAR(200),    PARENT_ID VARCHAR(32),     STRING_4 VARCHAR(20000),    STRING_8 VARCHAR(1000))on commit preserve rows;

如果不加最后一句on commit preserve rows 插入的数据无法保存

给临时表创建索引

CREATE INDEX SESSION.EXPORT ON SESSION.EXPORT(CLASSIFIER_ID)

使用LOAD方式插入数据

CALL SYSPROC.ADMIN_CMD('LOAD FROM (DATABASE 数据库SELECT 语句)OF CURSORINSERT INTO 插入表(字段)NONRECOVERABLE');

示例

CALL SYSPROC.ADMIN_CMD('    LOAD FROM     (DATABASE ZYRS_METADATA         select                 INSTANCE_ID,instance_code,CLASSIFIER_ID,PARENT_ID,STRING_4,STRING_8         from t_md_instance         where namespace like ''/86b2c582006e4ba5808dcd8881d45e3D%'' )     OF CURSOR     INSERT INTO     SESSION.EXPORT (INSTANCE_ID,instance_code,CLASSIFIER_ID,PARENT_ID,STRING_4,STRING_8) NONRECOVERABLE');

临时表采用压缩存储

在数据库中，临时表的存储方式通常是由数据库管理系统自动处理的，而不需要显式指定。不过，如果你希望对临时表进行压缩存储，可以考虑以下两种方法：

使用数据库管理系统的压缩功能：某些数据库管理系统（如oracle、SQL Server）提供了压缩功能，可以在创建表时启用压缩。你可以通过在创建临时表时指定压缩选项来实现。例如，在Oracle中，可以使用COMPRESS关键字来创建一个压缩的临时表，如下所示：

CREATE GLOBAL TEMPORARY TABLE your_temp_table(    column1 datatype,    column2 datatype,    ...)ON COMMIT PRESERVE ROWSCOMPRESS;

手动压缩临时表数据：如果数据库管理系统不提供自动压缩的功能，你可以在临时表使用完成后手动进行数据压缩。这可以通过创建新的表并将压缩后的数据插入其中来实现。以下是一个示例：

CREATE TABLE compressed_temp_tableASSELECT column1, column2, ...FROM your_temp_tableWHERE 1=0; -- 创建一个空表，仅保留表结构INSERT INTO compressed_temp_tableSELECT column1, column2, ...FROM your_temp_table;-- 删除原始临时表DROP TABLE your_temp_table;-- 重命名压缩后的表为原始临时表的名称ALTER TABLE compressed_temp_table RENAME TO your_temp_table;