MySQL服务的默认引擎是 InnoDB
mysql> show
engines;
+------------+---------+--------------
|
Engine
| Support | Comment
+------------+---------+--------------
| EXAMPLE |
YES
| Example stora
|
CSV
|
YES
| CSV storage e
|
MyISAM
|
YES
| Default engin
| BLACKHOLE |
YES
| /dev/null sto
| MRG_MYISAM |
YES
| Collection of
| InnoDB
| DEFAULT | Supports tran
| ARCHIVE |
YES
| Archive stora
|
MEMORY
|
YES
| Hash based, s
| FEDERATED |
YES
| Federated MyS
+------------+---------+--------------
原表的结构:
mysql> show create
table cs;
+-------+-----------------------------------------------------------------------------
| Table | Create Table
+-------+-----------------------------------------------------------------------------
| cs | CREATE
TABLE `cs` (
`cs_id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'id of the
student''s course.',
`stu_id` int(8) NOT NULL,
`cs_name` varchar(50) DEFAULT 'vkebb',
PRIMARY KEY (`cs_id`),
KEY `stu_id` (`stu_id`),
CONSTRAINT `cs_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `stu`
(`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------
(1)说明
我通过下面这条SQL语句快速的建立一个备份表,注意这种做法表的存储引擎也会采用服务器默认的存储引擎而不是源表的存储引擎,此种复制方法把表的内容也一起复制过来了。
注:as
与()可以忽略,但建议使用,因为很其他的SQL产要求使用它们。
mysql> create table
cs_bak1 as
-> (select *
-> from cs)
-> ;
mysql> show create
table cs_bak1;
+---------+-------------------------------------------------------------------------
| Table | Create Table
+---------+--------------------------------------------------------------------------
| cs_bak1 | CREATE TABLE `cs_bak1` (
`cs_id` int(8) NOT NULL DEFAULT '0' COMMENT 'id of the student''s
course.',
`stu_id` int(8) NOT NULL,
`cs_name` varchar(50) DEFAULT 'vkebb'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
//服务器默认的存储引擎等
+---------+--------------------------------------------------------------------------
(2)说明
使用和cs_bak表相同的结构来创建一个新表,列名、数据类型、空指和索引也将复制,但是表的内容不会被复制。外键和专用的权限也没有被复制。
mysql> create table
cs_bak
-> like cs;
mysql> show create
table cs_bak;
+--------+---------------------------------------------------------------------------
| Table | Create Table
+--------+---------------------------------------------------------------------------
| cs_bak | CREATE TABLE `cs_bak` (
`cs_id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'id of the
student''s course.',
`stu_id` int(8) NOT NULL,
`cs_name` varchar(50) DEFAULT 'vkebb',
PRIMARY KEY (`cs_id`),
KEY `stu_id` (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------
阅读(696) | 评论(0) | 转发(0) |