CREATE TABLE ... SELECT
複製資料表,index 及 key 都無法複製。要複製一個完全一樣的資料表,可使用以下步驟:
[root@kvm3 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe mariadb 10.3.11-MariaDB mycli 1.20.1 Chat: https://gitter.im/dbcli/mycli Mail: https://groups.google.com/forum/#!forum/mycli-users Home: http://mycli.net Thanks to the contributor - Johannes Hoff
mariadb root@localhost:(none)> use dyw; You are now connected to database "dyw" as user "root" Time: 0.001s
SHOW CREATE TABLE ...\G
列出原資料表完整結構。
mariadb root@localhost: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.011s
clone_animals
,除了第一行名稱不一樣,其他幾行直接複製貼上。
mariadb root@localhost: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=ut f8; Query OK, 0 rows affected Time: 0.113s
CREATE TABLE newtable LIKE oldtable
複製表格欄位。
mariadb root@localhost:dyw> create table clone1_animals like animals; Query OK, 0 rows affected Time: 0.106s
INSERT INTO ... SELECT ... FROM
從原資料表複製紀錄到新的資料表。
mariadb root@localhost:dyw> insert into clone_animals (id,name,foot) select id, name,foot from animals; Query OK, 4 rows affected Time: 0.005s
INSERT newtable SELECT * FROM oldtable
從原資料表複製紀錄到新的資料表。
mariadb root@localhost:dyw> insert clone1_animals select * from animals; Query OK, 4 rows affected Time: 0.006s