How Can I Create an Exact Copy of a MySQL Table, Including Structure, Indices, and Data?

Creating an Exact Copy of a MySQL Table: Structure, Indices, and Data

When working with MySQL databases, it is often necessary to create an exact copy of an existing table. This involves replicating not only the table structure but also its data and indices. This article provides a solution to this challenge.

The first approach mentioned is using the CREATE TABLE statement with the SELECT * FROM clause. While this method successfully copies the data and table structure, it fails to replicate the indices.

Alternatively, the CREATE TABLE statement with the LIKE clause captures the table structure and indices but excludes the data.

To create an exact copy of a table with both the structure and data, including indices and triggers, the following two queries should be used:

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;

If only the table structure and data need to be duplicated, the following query can be used:

CREATE TABLE new_table AS SELECT * FROM old_table;

By following these steps, developers can effortlessly create exact copies of MySQL tables, ensuring that all the essential elements, including structure, data, and indices, are accurately replicated.

The above is the detailed content of How Can I Create an Exact Copy of a MySQL Table, Including Structure, Indices, and Data?. For more information, please follow other related articles on the PHP Chinese website!