MySQL Cookbook, 4th Edition

Chapter 6. Table Management

6.0 Introduction

This chapter covers topics that relate to creating and populating tables, including the following:

• Cloning a table

• Copying from one table to another

• Using temporary tables

• Generating unique table names

• Determining what storage engine a table uses or converting it from one storage engine to another

Many of the examples in this chapter use a table named mail containing rows that track mail message traffic between users on a set of hosts (see Recipe 5.0). To create and load this table, change location into the tables directory of the recipes distribution and run this command:

$ mysql cookbook < mail.sql

6.1 Cloning a Table

Problem

You want to create a table that has exactly the same structure as an existing table.

Solution

Use CREATE TABLE…LIKE to clone the table structure. To also copy some or all of the rows from the original table to the new one, use INSERT INTO…SELECT.

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 exceptions: 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 ...