Clone Tables 複製資料表

1. 使用語法 CREATE TABLE ... SELECT 複製資料表，index 及 key 都無法複製。要複製一個完全一樣的資料表，可使用以下步驟：
2. 登入 mariadb。

   [root@kvm3 ~]# mycli -uroot -p123qwe
   Connecting to socket /var/lib/mysql/mysql.sock, owned by user mysql
   MariaDB 10.5.16
   mycli 1.26.1
   Home: http://mycli.net
   Bug tracker: https://github.com/dbcli/mycli/issues
   Thanks to the contributor - zer09
   

3. 使用資料庫 dyw。

   MariaDB root@(none):(none)> use dyw;
   You are now connected to database "dyw" as user "root"
   Time: 0.002s
   

4. 使用語法 SHOW CREATE TABLE ...\G 列出原資料表完整結構。

   MariaDB root@(none):dyw> show create table animals\G
   ***************************[ 1. row ]***************************
   Table        | animals
   Create Table | CREATE TABLE `animals` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `name` varchar(20) NOT NULL,
     `foot` tinyint(3) unsigned NOT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
   
   1 row in set
   Time: 0.050s
   

5. 產生一個新的資料表 clone_animals，除了第一行名稱不一樣，其他幾行直接複製貼上。

   MariaDB root@(none):dyw> CREATE TABLE `clone_animals` (
                         ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                         ->   `name` varchar(20) NOT NULL,
                         ->   `foot` tinyint(3) unsigned NOT NULL,
                         ->   PRIMARY KEY (`id`)
                         -> ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
   Query OK, 0 rows affected
   Time: 0.083s
   

6. 複製貼上太麻煩了，還可以使用語法 CREATE TABLE newtable LIKE oldtable 複製表格欄位。

   MariaDB root@(none):dyw> create table clone1_animals like animals;
   Query OK, 0 rows affected
   Time: 0.037s
   

7. 查詢原表格 animals 與複製的新表格 clone1_animals，欄位特徵都一樣。

   MariaDB root@(none):dyw> desc animals;
   +-------+---------------------+------+-----+---------+----------------+
   | Field | Type                | Null | Key | Default | Extra          |
   +-------+---------------------+------+-----+---------+----------------+
   | id    | int(10) unsigned    | NO   | PRI | <null>  | auto_increment |
   | name  | varchar(20)         | NO   |     | <null>  |                |
   | foot  | tinyint(3) unsigned | NO   |     | <null>  |                |
   +-------+---------------------+------+-----+---------+----------------+
   
   3 rows in set
   Time: 0.026s
   MariaDB root@(none):dyw> desc clone_animals;
   +-------+---------------------+------+-----+---------+----------------+
   | Field | Type                | Null | Key | Default | Extra          |
   +-------+---------------------+------+-----+---------+----------------+
   | id    | int(10) unsigned    | NO   | PRI | <null>  | auto_increment |
   | name  | varchar(20)         | NO   |     | <null>  |                |
   | foot  | tinyint(3) unsigned | NO   |     | <null>  |                |
   +-------+---------------------+------+-----+---------+----------------+
   
   3 rows in set
   Time: 0.025s
   MariaDB root@(none):dyw> desc clone1_animals;
   +-------+---------------------+------+-----+---------+----------------+
   | Field | Type                | Null | Key | Default | Extra          |
   +-------+---------------------+------+-----+---------+----------------+
   | id    | int(10) unsigned    | NO   | PRI | <null>  | auto_increment |
   | name  | varchar(20)         | NO   |     | <null>  |                |
   | foot  | tinyint(3) unsigned | NO   |     | <null>  |                |
   +-------+---------------------+------+-----+---------+----------------+
   
   3 rows in set
   Time: 0.019s
   

8. 查詢原表格 animals 有四筆紀錄，但複製的新表格 clone1_animals沒有任何紀錄。

   MariaDB root@(none):dyw> select * from animals;
   +----+---------+------+
   | id | name    | foot |
   +----+---------+------+
   | 1  | dog     | 4    |
   | 2  | cat     | 4    |
   | 3  | chicken | 2    |
   | 15 | bird    | 2    |
   +----+---------+------+
   
   4 rows in set
   Time: 0.026s
   MariaDB root@(none):dyw> select * from clone_animals;
   +----+------+------+
   | id | name | foot |
   +----+------+------+
   +----+------+------+
   
   0 rows in set
   Time: 0.024s
   MariaDB root@(none):dyw> select * from clone1_animals;
   +----+------+------+
   | id | name | foot |
   +----+------+------+
   +----+------+------+
   
   0 rows in set
   Time: 0.017s
   

9. 使用語法 INSERT INTO ... SELECT ... FROM 從原資料表複製紀錄到新的資料表。

   MariaDB root@(none):dyw> insert into clone_animals (id,name,foot) select id,name,
                         -> foot from animals;
   Query OK, 4 rows affected
   Time: 0.020s
   

10. 也可以簡化語法 INSERT newtable SELECT * FROM oldtable 從原資料表複製紀錄到新的資料表。

    MariaDB root@(none):dyw> insert into clone1_animals select * from animals;
    Query OK, 4 rows affected
    Time: 0.006s
    

11. 再查詢複製的新表格 clone_animals 及 clone1_animals，已有與原表格animals 完全一樣的四筆紀錄。

    MariaDB root@(none):dyw> select * from clone_animals;
    +----+---------+------+
    | id | name    | foot |
    +----+---------+------+
    | 1  | dog     | 4    |
    | 2  | cat     | 4    |
    | 3  | chicken | 2    |
    | 15 | bird    | 2    |
    +----+---------+------+
    
    4 rows in set
    Time: 0.018s
    MariaDB root@(none):dyw> select * from clone1_animals;
    +----+---------+------+
    | id | name    | foot |
    +----+---------+------+
    | 1  | dog     | 4    |
    | 2  | cat     | 4    |
    | 3  | chicken | 2    |
    | 15 | bird    | 2    |
    +----+---------+------+
    
    4 rows in set
    Time: 0.022s