MySQL->复制表[20180509]
MySQL复制表 通常复制表所采用CREATE TABLE .... SELECT 方式将资料复制，但无法将旧表中的索引，约束(除非空以外的)也复制。 完整复制MySQL数据表所需步骤： 方式一 1.使用SHOW CREATE TABLE 命令获取创建数据表的create table语句，语句会包含原表的结构，索引，存储引擎，字符集。 2.更改其中的表名称，再执行create table语句。 3.复制表的资料，使用INSERT INTO ... SELECT 语句。 方式二 CREATE TABLE <table_name> LIKE <old_table>; INSERT INTO <table_name> SELECT * FROM <old_table>; 获取创建表的SQL语句：
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄
mysql> show create table index_tab01\G *************************** 1. row *************************** Table: index_tab01 Create Table: CREATE TABLE `index_tab01` ( `id` int(11) NOT NULL DEFAULT '0', `col01` varchar(10) NOT NULL DEFAULT '', `col02` text, UNIQUE KEY `index_un` (`col01`), KEY `indx_01` (`col01`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)更改表名称，后执行创建语句：
mysql> CREATE TABLE `copy_tab01` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `col01` varchar(10) NOT NULL DEFAULT '', -> `col02` text, -> UNIQUE KEY `index_un` (`col01`), -> KEY `indx_01` (`col01`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -> ; Query OK, 0 rows affected (0.01 sec)复制表的资料
mysql> insert into copy_tab01 select * from index_tab01; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from copy_tab01; +----+--------+---------------+ | id | col01 | col02 | +----+--------+---------------+ | 0 | Name01 | This is Test! | | 0 | Name02 | This is Test! | | 0 | Name03 | This is Test! | | 0 | Name04 | This is Test! | +----+--------+---------------+ 4 rows in set (0.00 sec) mysql> select * from index_tab01; +----+--------+---------------+ | id | col01 | col02 | +----+--------+---------------+ | 0 | Name01 | This is Test! | | 0 | Name02 | This is Test! | | 0 | Name03 | This is Test! | | 0 | Name04 | This is Test! | +----+--------+---------------+ 4 rows in set (0.00 sec)方式二，复制完整的表
mysql> create table copy_tab02 like index_tab01; Query OK, 0 rows affected (0.00 sec) mysql> show create table copy_tab02\G *************************** 1. row *************************** Table: copy_tab02 Create Table: CREATE TABLE `copy_tab02` ( `id` int(11) NOT NULL DEFAULT '0', `col01` varchar(10) NOT NULL DEFAULT '', `col02` text, UNIQUE KEY `index_un` (`col01`), KEY `indx_01` (`col01`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into copy_tab02 select * from index_tab01; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from copy_tab02; +----+--------+---------------+ | id | col01 | col02 | +----+--------+---------------+ | 0 | Name01 | This is Test! | | 0 | Name02 | This is Test! | | 0 | Name03 | This is Test! | | 0 | Name04 | This is Test! | +----+--------+---------------+ 4 rows in set (0.00 sec)
更多精彩
