Temporary Table in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 13

Division 5

Forward: In this part of the series, we look at temporary tables in MySQL.

By: Chrysanthus Date Published: 7 Aug 2012

Introduction

This is part 13 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at temporary tables in MySQL.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Temporary Table and Connection
A temporary table in MySQL is a table that exists as long as the connection is on. When a connection is dropped, the temporary table ceases to exist. The user who is connected is the one to use the temporary table..

In simple terms the syntax is:

CREATE TEMPORARY TABLE tbl_name
    (create_definition,...);

You can try the following:

CREATE TEMPORARY TABLE Women
(
    WomanID INTEGER NOT NULL AUTO_INCREMENT,
    Name CHAR(40),
    Address CHAR(40),
    City CHAR(40),
    State CHAR(40),
    Country CHAR(40),
    Phone CHAR(40),

    PRIMARY KEY (WomanID ASC)
);

You can now go on to insert values into the temporary table. You can try the following:

INSERT INTO Women (Name, Address, City, State, Country, Phone) VALUES ('Mary', '50 Up Street', 'Fine York', 'California', 'U.S.A', '5555555555');

You can go on to select what you have inserted into the temporary table. You can try,

SELECT * FROM Women;

The temporary table lasts as long as the connection lasts.

Well, let us end here for this tutorial. We continue in the next part of the series.

Chrys

Broad Network

Related Articles

Temporary Table in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 13

Division 5

Introduction

Related Links

Comments