Three methods for copying table data and table structure in mysql

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:

1. Mysql> create table a like users; // copy the table structure
2. Query OK, 0 rows affected (0.50 sec)
3.  
4. Mysql> show tables;
5. + ------ +
6. | Tables_in_test |
7. + ------ +
8. | A |
9. | Users |
10. + ------ +
11. 2 rows in set (0.00 sec)

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:

1. Mysql> create table B select * from users limit 0; // copy the table structure
2. Query OK, 0 rows affected (0.00 sec)
3. Records: 0 Duplicates: 0 Warnings: 0
4.  
5. Mysql> show tables;
6. + ------ +
7. | Tables_in_test |
8. + ------ +
9. | A |
10. | B |
11. | Users |
12. + ------ +
13. 3 rows in set (0.00 sec)

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:

1. Mysql> show create table users \ G; // displays the SQL statement used to create a table.
2. * *************************** 1. row ***************************
3. Table: users
4. Create Table: create table 'users' (// modify the Table name
5. 'Id' bigint (20) unsigned not null AUTO_INCREMENT,
6. 'User _ name' varchar (60) not null default ",
7. 'User _ pass' varchar (64) not null default ",
8. Primary key ('id ')
9. ) ENGINE = InnoDB AUTO_INCREMENT = 5 default charset = utf8 // modify auto_increment
10. 1 row in set (0.00 sec)

Mysql> show create table users \ G; // display the SQL statement ******************************* 1. row *************************** Table: usersCreate Table: create table 'users' (// change 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_increment1 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:

1. Mysql> create table c select * from users; // copy the SQL statement of the table
2. Query OK, 4 rows affected (0.00 sec)
3. Records: 4 Duplicates: 0 Warnings: 0

Mysql> create table c select * from users; // copy the sqlQuery OK of the table, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0

Method 2:

1. Mysql> create table d select user_name, user_pass from users where id = 1;
2. Query OK, 1 row affected (0.00 sec)
3. Records: 1 Duplicates: 0 Warnings: 0

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.