mysql临时表插入数据方式

目录
  • mysql临时表插入数据
    • 临时表
  • mysql中临时表(TEMPORARY)
    • 语法
    • 功能
    • 检查是否存在临时表
    • 与MEMORY比较

mysql临时表插入数据

临时表

1.在数据库断开连接的时候会自动删除临时表。

 create temporary table IF NOT EXISTS client.getstucou(
  type_id int
 )Engine=InnoDB default charset utf8;

注意:在存储过程中创建了临时表的情况的话,在外面使用查询语句是找不到这张表的。

例如:

在存储过程中的sql语句中才而能找到该表。

2.临时表插入(select语句查出的数据集合)。INSERT INTO 表名 (查询到的集合)

例如:UNION ALL(将两个select语句求并集)

INSERT INTO getstucou
 WITH TEMP AS (
  SELECT
  *
  FROM
  t_shop_type
  WHERE
  parent_type_id = @StuNo
  OR type_id
  = @StuNo
  UNION ALL
  SELECT
  t.*
  FROM
  TEMP,
  t_shop_type t
  WHERE
  TEMP.type_id = t.parent_type_id
  ) SELECT
  type_id
  FROM
  TEMP;

mysql中临时表(TEMPORARY)

首先来看看官网给的有关MySQL的临时表的简介:

In MySQL, a temporary table is a special type of table that allows you to store a temporary result set, which you can reuse several times in a single session.A temporary table is very handy when it is impossible or expensive to query data that requires a single SELECT statement with the JOIN clauses. In this case, you can use a temporary table to store the immediate result and use another query to process it.

总结而来就是:

当我们需要使用难以接受的或开销很大的一个SELECT语句和JOIN子句的数据时,临时表非常方便。

语法

大致同CREATE TABLE语法,创建临时表则是CREATE TEMPORARY TABLE,增加了TEMPORARY关键字表示临时表。

1.创建

(1)创建不同于现有表的临时表

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);

实例:

//首先,创建一个新的临时表,称为credits存储客户的信用:
CREATE TEMPORARY TABLE credits(
    customerNumber INT PRIMARY KEY,
    creditLimit DEC(10,2)
);
//然后,将customers表中的行插入临时表中credits:
INSERT INTO credits(customerNumber,creditLimit)
SELECT customerNumber, creditLimit
FROM customers
WHERE creditLimit > 0;

(2)创建结构基于现有表的临时表

不能使用该CREATE TEMPORARY TABLE … LIKE语句。而是,使用以下语法:

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;

实例:

//下面的示例创建一个临时表,该表按收入存储前10名客户。临时表的结构是从以下SELECT语句派生的:
CREATE TEMPORARY TABLE top_customers
SELECT p.customerNumber, 
       c.customerName, 
       ROUND(SUM(p.amount),2) sales
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;
//从top_customers临时表中查询数据,就像从永久表中查询一样:
SELECT 
    customerNumber, 
    customerName, 
    sales
FROM
    top_customers
ORDER BY sales;

2.删除

可以通过DROP TABLE语句删除临时表,但不推荐这样,因为当临时表与永久表同名时,有可能会误删永久表,当然若你已经准备好机票,我就祝你一路顺风吧!若你未曾准备好机票,这里也给你提供中航订票电话:0086-95583 | 0086-10-95583,祝你好运!

推荐:

DROP TEMPORARY TABLE table_name;

注意:

(1)如果尝试使用该DROP TEMPORARY TABLE语句删除永久表,则会收到一条错误消息,提示您尝试删除的表是未知的。愉快地避免删表的误操作了。

(2)如果开发使用连接池或持久连接的应用程序,则不能保证在终止应用程序时自动删除临时表。因为该应用程序使用的数据库连接可能仍处于打开状态,并放置在连接池中,以便其他客户端以后再使用。因此,一个好习惯是在不再使用临时表时始终删除它们。

(3)在采用连接池的情况下,为防止多次CREATE 、 DROP TEMPORARY TABLE带来的性能瓶颈,可以使用CREATE IF NOT EXISTS + TRUNCATE TABLE 的方式来提升性能。(注意:IF NOT EXISTS是在TABLE之后,table_name之前的。)

功能

(1)通过使用CREATE TEMPORARY TABLE语句创建一个临时表。请注意,该关键字TEMPORARY已添加到CREATE和TABLE关键字之间 。

(2)当会话结束或连接终止时,MySQL自动删除临时表。当然,您可以在DROP TABLE不再使用临时表时使用该 语句显式删除该临时表。

(3)临时表仅对创建它的客户端可用并且可以访问。不同的客户端可以创建具有相同名称的临时表而不会导致错误,因为只有创建临时表的客户端才能看到它。但是,在同一会话中,两个临时表不能共享相同的名称。

(4)临时表可以与数据库中的普通表具有相同的名称。例如,如果您创建一个employees在示例数据库中命名的临时表,则现有employees表将变得不可访问。您针对该employees表发出的每个查询现在都引用该临时表 employees。当您删除employees临时表时,永久employees表可用且可访问。

但即使临时表可以与永久表具有相同的名称,也不建议使用。因为这可能导致混乱并可能导致意外的数据丢失。例如,如果与数据库服务器的连接丢失并且您自动重新连接到服务器,则无法区分临时表和永久表。然后,您可以发出一条DROP TABLE 语句删除永久表而不是临时表,这是不期望的。为避免此问题,可以使用该DROP TEMPORARY TABLE语句删除临时表。

检查是否存在临时表

MySQL不提供直接检查临时表是否存在的函数或语句。但是,我们可以创建一个存储过程来检查临时表是否存在,如下所示:

DELIMITER //
CREATE PROCEDURE check_table_exists(table_name VARCHAR(100)) 
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @err = 1;
    SET @err = 0;
    SET @table_name = table_name;
    SET @sql_query = CONCAT('SELECT 1 FROM ',@table_name);
    PREPARE stmt1 FROM @sql_query;
    IF (@err = 1) THEN
        SET @table_exists = 0;
    ELSE
        SET @table_exists = 1;
        DEALLOCATE PREPARE stmt1;
    END IF;
END //
DELIMITER ;
//在此过程中,我们尝试从临时表中选择数据。如果临时表存在,则将该@table_exists变量设置为1,否则,将其设置为0。
//该语句调用check_table_exists来检查临时表是否credits 存在:
CALL check_table_exists('credits');
SELECT @table_exists;
//根据输出判断临时表是否存在

与MEMORY比较

我们知道还有一种通过存储引擎创建临时表的方式ENGINE|TYPE = MEMORY,这种方式创建的临时表是在内存中的,效率在理论上是比TEMPORARY更高的,因为MEMORY是在内存中,TEMPORARY是同永久表在磁盘上的。

实例:

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
) ENGINE|TYPE = MEMORY;

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

(0)

相关推荐

  • mysql存储过程如何利用临时表返回结果集

    目录 首先要声明的是 测试示例 真实需求,查找出所有用建单情况 首先要声明的是 1,游标的方法会很慢在mysql中,在oracle数据库中还可以,除非没有别的方法,一般不建议在mysql中使用游标, 2,不建议在mysql中拼接sql,会使存储过程显得很臃肿,可以使用or来动态判别传入的参数是否为空 and  ( TTB.office_id=输入参数 or  输入参数 is null  or 输入参数 = '') and  ( TTB.office_id=IN_Office_id or  IN_

  • MySQL临时表的使用方法详解

    目录 1. 写在前面的话 2. 临时表的使用 2.1 创建一个只存放亚洲国家信息的临时表 2.1.1 创建临时表 2.1.2 向临时表里写数据 2.2 在查询过程中直接创建临时表 2.3 查询临时表中的数据 2.4 删除临时表 3. 以上操作的全部代码 总结 1. 写在前面的话 在开发数据库时,特别是写存储过程,遇到比较复杂的需求,使用临时表可以简化很多逻辑.曾经在一家互联网金融公司供职,公司数据组团队做数据清洗,写SQL脚本时,一个查询语句可以套到数层查询,甚至十几层.看起来几百行上千行的脚本

  • MySQL 临时表的原理以及优化方法

    目录 1 临时表 2 union临时表优化 3 group by临时表优化 1 临时表 sort buffer.内存临时表和join buffer,这三个数据结构都是用来存放语句执行过程中的中间数据,以辅助SQL语句的执行的.其中,在排序的时候用到了sort buffer,在使用join语句的时候用到了join buffer. 而使用临时表的时候,Explain的Extra字段中具有Using temporary标记.union.group by.distinct等等查询都有可能使用到临时表.

  • MySQL为什么临时表可以重名

    目录 临时表的特性 临时表的应用 为什么临时表可以重名? 临时表和主备复制 主库上不同的线程创建同名的临时表是没关系的,但是传到备库执行是怎么处理的呢? 今天我们就从这个问题说起:临时表有哪些特征,适合哪些场景? 这里,我需要先帮你厘清一个容易误解的问题:有的人可能会认为,临时表就是内存表.但是,这两个概念可是完全不同的. 内存表,指的是使用Memory引擎的表,建表语法是create table …engine=memory.**这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还

  • MySQL内部临时表的具体使用

    目录 UNION 表初始化 执行语句 UNION RESULT UNION ALL GROUP BY 内存充足 执行语句 执行过程 排序过程 ORDER BY NULL 内存不足 执行语句 优化方案 优化索引 直接排序 执行过程 对比DISTINCT 小结 参考资料 UNION UNION语义:取两个子查询结果的并集,重复的行只保留一行 表初始化 CREATE TABLE t1(id INT PRIMARY KEY, a INT, b INT, INDEX(a)); DELIMITER ;; C

  • 使用MySQL实现select into临时表的功能

    目录 MySQL select into临时表 select into 临时表 create 临时表 mysql临时表(可以将查询结果存在临时表中) 创建临时表可以将查询结果寄存 mysql把select结果保存为临时表,有2种方法 MySQL select into临时表 最近在编写sql语句时,遇到两次将数据放temp表,然后将两次的temp表进行inner join,再供后续insert数据时使用的场景. 写完后发现执行耗时较长,需要优化,于是将一条长长的sql语句拆分成一个sql脚本,用

  • 浅谈Mysql在什么情况下会使用内部临时表

    union执行 为了便于分析,使用一下sql来进行举例 CREATE TABLE t1 ( id INT PRIMARY KEY, a INT, b INT, INDEX ( a ) ); delimiter ;; CREATE PROCEDURE idata ( ) BEGIN DECLARE i INT; SET i = 1; WHILE ( i <= 1000 ) DO INSERT INTO t1 VALUES ( i, i, i ); SET i = i + 1; END WHILE;

  • mysql临时表插入数据方式

    目录 mysql临时表插入数据 临时表 mysql中临时表(TEMPORARY) 语法 功能 检查是否存在临时表 与MEMORY比较 mysql临时表插入数据 临时表 1.在数据库断开连接的时候会自动删除临时表. create temporary table IF NOT EXISTS client.getstucou( type_id int )Engine=InnoDB default charset utf8; 注意:在存储过程中创建了临时表的情况的话,在外面使用查询语句是找不到这张表的.

  • mysql大批量插入数据的4种方法示例

    前言 本文主要给大家介绍了关于mysql大批量插入数据的4种方法,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 方法一:循环插入 这个也是最普通的方式,如果数据量不是很大,可以使用,但是每次都要消耗连接数据库的资源. 大致思维如下 (我这里写伪代码,具体编写可以结合自己的业务逻辑或者框架语法编写) for($i=1;$i<=100;$i++){ $sql = 'insert...............'; //querysql } foreach($arr as $key =

  • 防止MySQL重复插入数据的三种方法

    新建表格 CREATE TABLE `person` ( `id` int NOT NULL COMMENT '主键', `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '姓名', `age` int NULL DEFAULT NULL COMMENT '年龄', `address` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin N

  • Python操控mysql批量插入数据的实现方法

    在Python中,通过pymysql模块,编写简短的脚本,即方便快捷地控制MySQL数据库 一.连接数据库 使用的函数:pymysql.connect 语法:db=pymysql.connect(host='localhost',user='root',port=3306,password='Your password',db='database_name') 参数说明:host:MySQL服务器地址                           user:用户名             

  • Mysql循环插入数据的实现

    目录 Mysql 循环插入数据 WHILE…DO方式 其他基本命令 Mysql 循环插入10000条数据 1.所以按自己想法,写个循环1W次随便插入数据测试 2.执行过程 Mysql 循环插入数据 说明:首先需要设置delimiter. delimiter的作用:告诉解释器,这段命令是否已经结束了,mysql是否可以执行了 默认情况下,delimiter是‘;’但是当我们编写procedure时,如果是默认设置,那么一遇到‘;’,mysql就要执行. WHILE…DO方式 /*循环插入*/ DE

  • 解决Mysql数据库插入数据出现问号(?)的解决办法

    首先,我用的mysql数据库是5.7.12版本. 出现的问题: 1.插入数据显示错误,插入不成功,出现:Incorrect string value: '\xCD\xF5\xD5\xBC\xBE\xA9' for column 'Sname' at row 1 2.插入中文,虽然插入成功,但是显示:?? 解决方法: 在my.ini文件中的 [mysqld] 中加入 #character-set-server=utf8 如图所示,必须在蓝圈的上方,就是说,蓝圈内的内容必须在[mysqld]的最下面

  • MySQL批量插入数据脚本

    MySQL批量插入数据脚本 #!/bin/bash i=1; MAX_INSERT_ROW_COUNT=$1; while [ $i -le $MAX_INSERT_ROW_COUNT ] do mysql -uroot -proot dbname -e "insert into tablename (name,age,createTime) values ('HELLO$i',$i % 99,NOW());" d=$(date +%M-%d\ %H\:%m\:%S) echo &qu

  • MYSQL批量插入数据的实现代码第1/3页

    @echo off cls set CLASSPATH=..\api\jogre.jar set CLASSPATH=%CLASSPATH%;. set CLASSPATH=%CLASSPATH%;classes set CLASSPATH=%CLASSPATH%;lib\dom4j.jar java org.jogre.server.JogreServer 建表 复制代码 代码如下: create database con_test; use con_test; create table te

  • SQL Server 向临时表插入数据示例

    复制代码 代码如下: INSERT INTO #DirtyOldWIPBOM SELECT TOP (100) PERCENT dbo.WIP_BOM.Model, dbo.WIP_BOM.PartNumber,WIP_BOM.WIP FROM dbo.WIP_BOM left OUTER JOIN dbo.BOM_CHINA ON LTRIM(dbo.WIP_BOM.Model) = LTRIM(dbo.BOM_CHINA.Model) AND LTRIM(dbo.WIP_BOM.PartNu

  • mysql 选择插入数据(包含不存在列)具体实现

    mysql> select 'name',id from table_b; //'name' 不在table_b表中 +------+-----------+ | name | id | +------+-----------+ | name | 123456 | | name | 1234567 | | name | 12345678 | | name | 123456789 | +------+-----------+ 4 rows in set (0.00 sec) 复制代码 代码如下:

随机推荐