Shulou(Shulou.com)05/31 Report--

This article will explain in detail the methods of copying MySQL tables for you. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Several ways to copy tables

Copy only the table structure

Create table tableName like someTable

Only table structures, including primary keys and indexes, are replicated, but table data is not replicated

Copy only table data

Create table tableName select * from someTable

Copy the general structure and all the data of the table, and do not copy primary keys, indexes, etc.

Full copy

Create table tableName like someTable

Insert into tableName select * from someTable

Complete in two steps, first copy the table structure, and then insert the data

Related free learning recommendation: mysql video tutorial

Examples

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

USE student;SHOW TABLES

Effect picture:

two。 Create a T1 data table, insert two records and set the index for the name field

CREATE TABLE T1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR (50) COMMENT 'name'); INSERT INTO T1 (name) VALUES ('Zhang San'); INSERT INTO T1 (name) VALUES ('Li Si'); CREATE INDEX idx_name ON T1 (name)

Effect picture:

3. View all records of the T1 data table

SELECT * FROM T1

Effect picture:

4. View the index of the T1 data table

SHOW INDEX FROM T1\ G

Effect picture:

5. Create T2 data table (copy table structure only)

CREATE TABLE t2 LIKE t1

Effect picture:

6. View the records of the T2 data table

SELECT * FROM T2

Effect picture:

7. View the index of the T2 data table

SHOW INDEX FROM T2\ G

Effect picture:

8. View the structure of the T2 data table

SHOW CREATE TABLE T2\ G

Effect picture:

9. Create T3 data table (copy table data only)

CREATE TABLE T3 SELECT * FROM T1

Effect picture:

10. View the table structure of the T3 data table

SHOW CREATE TABLE T3\ G

Effect picture:

11. View the index of the T3 data table

SHOW INDEX FROM t3

Effect picture:

twelve。 View all records of the T3 data table

SELECT * FROM T3

Effect picture:

13. Create T4 data table (full copy)

CREATE TABLE T4 LIKE T1: insert INTO T4 SELECT * FROM T1

Effect picture:

14. View the structure of the T4 data table

SHOW CREATE TABLE T4\ G

Effect picture:

15. View the index of the T4 data table

SHOW INDEX FROM T4\ G

Effect picture:

16. View all records of the T4 data table

SELECT * FROM T4

Effect picture:

This is the end of this article on "what are the methods of copying tables in MySQL?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.