Introducing several ways to replicate tables in MySQL

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;

Rendering:

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);

Rendering:

3. View all records of the t1 data table

SELECT * FROM t1;

Rendering:

4. View the index of the t1 data table

SHOW INDEX FROM t1\G;

Rendering:

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

CREATE TABLE t2 LIKE t1;

Rendering:

6. View the records of the t2 data table

SELECT * FROM t2;

Rendering:

7. View the index of the t2 data table

SHOW INDEX FROM t2\G;

Rendering:

8. View the structure of the t2 data table

SHOW CREATE TABLE t2\G;

Rendering:

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

CREATE TABLE t3 SELECT * FROM t1;

Rendering:

10. View the table structure of the t3 data table

SHOW CREATE TABLE t3\G;

Rendering:

11. View the index of the t3 data table

SHOW INDEX FROM t3;

Rendering:

12. View all records of the t3 data table

SELECT * FROM t3;

Rendering:

13. Create t4 data table (complete copy)

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

Rendering:

14. View the structure of t4 data table

SHOW CREATE TABLE t4\G;

Rendering:

15. View the index of the t4 data table

SHOW INDEX FROM t4\G;

Rendering:

16. View all records of the t4 data table

SELECT * FROM t4;

Rendering:

Note: This article is a summary of the blogger’s MySQL learning and does not support any commercial use. Please indicate the source when reprinting! If you also have a certain interest and understanding in MySQL learning, you are welcome to communicate with bloggers at any time~

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!