Three methods for copying table data and table structure in MySQL
When will we use a copy table? For example, if I operate on a table but are afraid of accidental deletion of data, create a table with the same structure in the same database and create a backup with the same table data. If mysqldump is difficult to use, back up files such as. MYD and. myi.
I. Copy the table structure
Method 1:
View copy print?
- Mysql> Create Table A like users; // copy the table structure
- Query OK, 0 rows affected (0.50 Sec)
- Mysql> show tables;
- + ---------------- +
- | Tables_in_test |
- + ---------------- +
- | A |
- | Users |
- + ---------------- +
- 2 rows in SET (0.00 Sec)
Method 2:
View copy print?
- Mysql> Create Table B select * from users limit 0; // copy the table structure
- Query OK, 0 rows affected (0.00 Sec)
- Records: 0 duplicates: 0 Warnings: 0
- Mysql> show tables;
- + ---------------- +
- | Tables_in_test |
- + ---------------- +
- | A |
- | B |
- | Users |
- + ---------------- +
- 3 rows in SET (0.00 Sec)
Method 3:
View copy print?
- Mysql> show create table Users \ G; // displays the SQL statement used to create a table.
- * *************************** 1. row ***************************
- Table: Users
- Create Table: Create Table 'users' (// modify the table name
- 'Id' bigint (20) unsigned not null auto_increment,
- 'User _ name' varchar (60) not null default '',
- 'User _ pass' varchar (64) not null default '',
- Primary Key ('id ')
- ) Engine = InnoDB auto_increment = 5 default charset = utf8 // modify auto_increment
- 1 row in SET (0.00 Sec)
Copy the SQL statement, change the table name and atuo_increment, and execute the command.
Ii. Copy table data and Table Structure
Method 1:
View copy print?
- Mysql> Create Table C select * from users; // copy the SQL statement of the table
- Query OK, 4 rows affected (0.00 Sec)
- Records: 4 duplicates: 0 Warnings: 0
Method 2:
- Mysql> Create Table D select user_name, user_pass from users where id = 1;
- Query OK, 1 row affected (0.00 Sec)
- Records: 1 duplicates: 0 Warnings: 0
The above two methods are convenient, fast, and flexible.
Method 3:
First create an empty table, insert into new table select * from old table, or
Insert into new table (Field 1, Field 2 ,.......) Select Field 1, Field 2 ,...... From old table
This method is not very convenient and is often used in the past.