MySQL表复制命令的区别

在MySQL（MariaDB）中，有多个复制表的命令，但我不知道它们之间的区别，所以我使用show命令进行了确认和比较。结果如下所示。

根据这个内容，CREATE TABLE…LIKE语句会复制表定义以及相关对象，而其他的CREATE TABLE…SELECT语句则会复制表和数据，这正是SQL的运行方式。

创建表…作为SELECT语句

MariaDB [iwate]> create table juve_as as select * from juve where position='MF';
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [iwate]> SHOW CREATE TABLE juve_as;
+---------+--------------------------------------------------------------------
| Table   | Create Table
+---------+--------------------------------------------------------------------
| juve_as | CREATE TABLE `juve_as` (
  `id_no` int(11) NOT NULL COMMENT 'ID',
  `no` varchar(5) NOT NULL COMMENT '背番号',
  `fir_name` varchar(25) DEFAULT NULL COMMENT '名前',
  `fam_name` varchar(25) DEFAULT NULL COMMENT '姓',
  `position` varchar(3) NOT NULL COMMENT 'ポジション'
) ENGINE=InnoDB DEFAULT CHARSET=utf8            |
+---------+--------------------------------------------------------------------
1 row in set (0.00 sec)

创建表格 (SELECT语句)

MariaDB [iwate]> create table juve2 (select * from juve where position='MF');
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [iwate]> SHOW CREATE TABLE juve2;
+-------+-------------------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------------------
| juve2 | CREATE TABLE `juve2` (
  `id_no` int(11) NOT NULL COMMENT 'ID',
  `no` varchar(5) NOT NULL COMMENT '背番号',
  `fir_name` varchar(25) DEFAULT NULL COMMENT '名前',
  `fam_name` varchar(25) DEFAULT NULL COMMENT '姓',
  `position` varchar(3) NOT NULL COMMENT 'ポジション'
) ENGINE=InnoDB DEFAULT CHARSET=utf8            |
+-------+-------------------------------------------------------------------------
1 row in set (0.00 sec)

创建表格 ･･･ 选择语句

MariaDB [iwate]> create table juve_non select * from juve where position='MF';
Query OK, 6 rows affected (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [iwate]> SHOW CREATE TABLE juve_non;
+---------+--------------------------------------------------------------------
| Table   | Create Table
+---------+--------------------------------------------------------------------
| juve_non | CREATE TABLE `juve_non` (
  `id_no` int(11) NOT NULL COMMENT 'ID',
  `no` varchar(5) NOT NULL COMMENT '背番号',
  `fir_name` varchar(25) DEFAULT NULL COMMENT '名前',
  `fam_name` varchar(25) DEFAULT NULL COMMENT '姓',
  `position` varchar(3) NOT NULL COMMENT 'ポジション'
) ENGINE=InnoDB DEFAULT CHARSET=utf8            |
+---------+--------------------------------------------------------------------
1 row in set (0.00 sec)

创建表格…像<元表>那样

MariaDB [iwate]> create table juve_like like juve;
Query OK, 0 rows affected (0.08 sec)

MariaDB [iwate]> SHOW CREATE TABLE juve_like;
+-----------+------------------------------------------------------------------
| Table     | Create Table
+-----------+------------------------------------------------------------------
| juve_like | CREATE TABLE `juve_like` (
  `id_no` int(11) NOT NULL COMMENT 'ID',
  `no` varchar(5) NOT NULL COMMENT '背番号',
  `fir_name` varchar(25) DEFAULT NULL COMMENT '名前',
  `fam_name` varchar(25) DEFAULT NULL COMMENT '姓',
  `position` varchar(3) NOT NULL COMMENT 'ポジション',
  PRIMARY KEY (`id_no`),
  KEY `ix1_juve` (`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ユベントス'
+-----------+------------------------------------------------------------------
1 row in set (0.00 sec)