MySQL复制表3种方式

本节目标

1.掌握MySQL复制表的几种方式
复制表，即备份表，给我们在操作表的时候，留一条后路

复制表的三种方式：
1.只复制表结构
create table tableName like someTable;

2.只复制表数据
create table tableName select * from someTable;

3.复制表结构+数据
create table tableName like someTable;
insert into tableName select * from someTable;

实战环节：

0.前期准备

创建一个t1表和索引

create table t1(
	id int not null auto_increment primary key,
	name varchar(50)
);

插入两条数据

insert into t1(name) values('张三');
insert into t1(name) values('李四');

假如索引建在名字上

create index idx_name on t1(name);

结果如下：

mysql> create table t1(
    -> id int not null auto_increment primary key,
    -> name varchar(50)
    -> );
Query OK, 0 rows affected (5.73 sec)

mysql> insert into t1(name) values('张三');
Query OK, 1 row affected (0.54 sec)

mysql> insert into t1(name) values('李四');
Query OK, 1 row affected (0.26 sec)

mysql> create index idx_name on t1(name);
Query OK, 0 rows affected (2.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引

show index from t1;
mysql> show index from t1\G;
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      <