Table Management - MySQL - page 128

Discussion
To create a new table that is just like an existing table, use this statement:
CREATE TABLE new_table LIKE original_table ;
The structure of the new table is the same as that of the original table, with a few ex‐
ceptions: CREATE TABLE … LIKE does not copy foreign key definitions, and it doesn't
copy any DATA DIRECTORY or INDEX DIRECTORY table options that the table might use.
The new table is empty. If you also want the contents to be the same as the original table,
copy the rows using an INSERT INTO … SELECT statement:
INSERT INTO new_table SELECT * FROM original_table ;
To copy only part of the table, add an appropriate WHERE clause that identifies which
rows to copy. For example, these statements create a copy of the mail table named
mail2 , populated only with the rows for mail sent by barb :
CREATE TABLE mail2 LIKE mail ;
INSERT INTO mail2 SELECT * FROM mail WHERE srcuser = 'barb' ;
For more information about INSERT … SELECT , see Recipe 4.2 .
4.2. Saving a Query Result in a Table
Problem
You want to save the result from a SELECT statement to a table rather than display it.
Solution
If the table exists, retrieve rows into it using INSERT INTO … SELECT . If the table does
not exist, create it on the fly using CREATE TABLE … SELECT .
Discussion
The MySQL server normally returns the result of a SELECT statement to the client that
executed the statement. For example, when you execute a statement from within the
mysql program, the server returns the result to mysql , which in turn displays it on the
screen. It's possible to save the results of a SELECT statement in a table instead, which is
useful in several ways:
• You can easily create a complete or partial copy of a table. If you're developing an
algorithm that modifies a table, it's safer to work with a copy of a table so that you
need not worry about the consequences of mistakes. If the original table is large,
creating a partial copy can speed the development process because queries run
against it take less time.