You are currently viewing archive for 04 September 2007
Category: Study
Posted by: hajime osako
A TEMPORARY table is visible only to the client that created it and may be used only by that client.
Different clients can create TEMPORARY tables that have the same name and no conflict occurs.
The server drops a TEMPORARY table automatically when the client connection ends if the client has not dropped it.
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 can't use RENAME TABLE.

* A MEMORY table is temporary in the sense that it's contents are lost if you restart the server, but the table definition continues to exists in its database.
Category: Study
Posted by: hajime osako
(A) CREATE TABLE ... SELECT ... creates a table and populates it from the result set of the SELECT statement.
(B) CREATE TABLE ... LIKE ... creates an empty table using the definition of another existing table.

The difference is (A) statement copies the column name and data type from the original table, but does not retain the column attribute information. Also the new table sues the default storage engine.

Some table attributes are not copied, even when issuing (B).
If the original table is a MyISAM table for which the DATA DIRECTORY or INDEX DIRECTORY table options are specified, those options are not copied to the new table.
Foreign Key definitions in the original table are not copied to the new table.