Copy table data using mysql command lines

I. How to copy the table structure 1: view the copy print? Rows; copy the table structure QueryOK, 0 rowsaffected (0.50sec) mysqlshowtables; -------------- | Tables_in_test | tables | a | users | -------------- 2 rowsinset (0.00sec) Method 2: view the replay

I. How to copy the table structure 1: view the copy print? Rows; // copy the table structure QueryOK, 0 rowsaffected (0.50sec) mysqlshowtables; -------------- | Tables_in_test | tables | a | users | -------------- 2 rowsinset (0.00sec) Method 2: view the replay

I. Copy the table structure

Method 1:

View copy print?

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

Method 2:

View copy print?

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

Method 3:

View copy print?

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)

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?

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

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

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