Several ways to copy a table

• Copy onlytable structure

create table tableName like someTable;
Only the table structure, including primary keys and indexes, will be copied, but the table data will not be copied

• Only the table data will be copied

##create table tableName select * from someTable;

Copy the general structure of the table and all data, and will not copy the primary key, index, etc.

• Complete copy

create table tableName like someTable;

insert into tableName select * from someTable;
Complete in two steps, copy first Table structure, then insert data

Related free learning recommendations:

mysql video tutorial

Example

Connect to the database, use the student database and view all data tables

1. USE student;SHOW TABLES;

   Copy after login

2. Create the t1 data table and insert two items Record and set the index for the name field

CREATE TABLE t1( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) COMMENT '姓名');INSERT INTO t1(name) VALUES('张三');INSERT INTO t1(name) VALUES('李四');CREATE INDEX idx_name ON t1(name);

3. View all records of the t1 data table

SELECT * FROM t1;

4. View the index of the t1 data table

SHOW INDEX FROM t1\G;

5. Create the t2 data table (only copy the table structure)

CREATE TABLE t2 LIKE t1;

6. View the records of the t2 data table

SELECT * FROM t2;

7. View the index of the t2 data table

SHOW INDEX FROM t2\G;

8. View the structure of the t2 data table

SHOW CREATE TABLE t2\G;

9. Create the t3 data table ( Only copy table data)

CREATE TABLE t3 SELECT * FROM t1;

10. View the table structure of the t3 data table

SHOW CREATE TABLE t3\G;

11. View the index of the t3 data table

SHOW INDEX FROM t3;

12. View all records of the t3 data table

SELECT * FROM t3;

13. Create t4 data table (complete copy)

CREATE TABLE t4 LIKE t1;INSERT INTO t4 SELECT * FROM t1;

14. View the structure of t4 data table

SHOW CREATE TABLE t4\G;

15. View the index of the t4 data table

SHOW INDEX FROM t4\G;

16. View all records of the t4 data table

SELECT * FROM t4;

Related free learning recommendations:

mysql database(video)

The above is the detailed content of Introducing several ways to replicate tables in MySQL. For more information, please follow other related articles on the PHP Chinese website!