MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Database Structure / Tables / Question No: 205

What is temporary table and how to create it in MySQL?

Answer No: 205

Each storage engine in MySQL implements tables with a particular set of characteristics. One characteristic held in common by all storage engines is that by default they create tables that exist until they are removed with DROP TABLE. This behavior may be changed by using CREATE TEMPORARY TABLE rather than CREATE TABLE.

How to create a temporary table, take an example:

CREATE TEMPORARY TABLE tbl_name
(
    id         INT NOT NULL,
    first_name CHAR(30) NOT NULL,
    last_name  CHAR(30) NOT NULL,
    dob        DATE NOT NULL
);

A TEMPORARY table differs from a non- TEMPORARY table in the following ways:

  • It's visible only to the client that created it and may be used only by that client. This means that different clients can create TEMPORARY tables that have the same name and no conflict occurs.
  • A TEMPORARY table exists only for the duration of the connection in which it was created. The server drops a TEMPORARY table automatically when the client connection ends if the client has not already dropped it. This is convenient because you need not remember to remove the table yourself.
  • A TEMPORARY table may have the same name as a non-TEMPORARY table. The non- TEMPORARY table becomes hidden to the client that created the TEMPORARY table as long as the TEMPORARY table exists.
  • A TEMPORARY table can be renamed only with ALTER TABLE. You cannot use RENAME TABLE.

A table created with TEMPORARY is not the same thing as a MEMORY table. A MEMORY table is temporary in the sense that its contents are lost if you restart the server, but the table definition continues to exist in its database. A TEMPORARY table exists only while the client that created it remains connected, and then disappears completely. Given that a server restart necessarily involves termination of all client connections, it also results in removal of all TEMPORARY tables. Another difference is that a MEMORY table is available to any client that has permission to access it, not just to the client that created it.

Related MySQL FAQs to the Above FAQ

How-to-create-table-in-MySQL How to create table in MySQL?

How-to-create-table-based-on-existing-tables How to create table based on existing tables?

How-to-describe-a-table-in-MySQL How to describe a table in MySQL?

How-to-renaming-a-table-in-MySQL How to renaming a table in MySQL?

How-to-specify-multiple-alterations-for-a-table How to specify multiple alterations for a table?

How-to-get-table-metadata How to get table metadata?

How-to-add-new-column-into-an-existing-table-in-MySQL How to add new column into an existing table in MySQL?

How-to-add-a-new-AUTO_INCREMENT-field-to-an-existing-table How to add a new AUTO_INCREMENT field to an existing table?

How-to-drop-a-column-from-the-existing-table How to drop a column from the existing table?

How-to-drop-all-existing-tables-in-one-go How to drop all existing tables in one go?

How-to-get-columns-of-an-existing-table-in-MySQL How to get columns of an existing table in MySQL?

How-to-change-data-type-of-an-existing-column How to change data type of an existing column?

How-to-delete-table-records How to delete table records?

How-to-set-AUTO_INCREMENT-value-other-than-1-in-MySQL How to set AUTO_INCREMENT value other than 1 in MySQL?

How-to-get-copy-of-table-structure-in-MySQL-using-query How to get copy of table structure in MySQL using query?

How-to-get-constraints-or-keys-of-the-existing-table How to get constraints or keys of the existing table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.