mysql快速建表的语句写法并不复杂,下面就为您详细介绍两种最常用的mysql快速建表的语句:
1:create table t_select select * from t_old where1=0;
2:create table t_select1 like t_old;
但是***种mysql快速建表的语句有缺陷,他能取消原来表的有些定义。(手册上说Some conversion of data types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns. )
可以看看下面的例子
create table t_old (id serial, content varchar(8000) not null,`desc` varchar(100) not null) engine innodb;
show CREATE table t_old;
| Table | Create Table
| t_old | CREATE TABLE `t_old` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`content` varchar(8000) NOT NULL,
`desc` varchar(100) NOT NULL,
UNIQUE KEY `id` (`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
create table t_select select * from t_old where1=0;
CREATE TABLE `t_select` (
`id` bigint(20) unsigned NOT NULL default '0',
`content` varchar(8000) NOT NULL,
`desc` varchar(100) NOT NULL
)ENGINE=MyISAMDEFAULTCHARSET=utf8
这样 自增字段跟表引擎都变了
如果想要保持一样的引擎,就加上:engine innodb
如:
create table t_select engine innodb select * from t_old where1=0; create table t_like like t_old;
show CREATE table t_like;
Table | t_like | CREATE TABLE `t_like` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`content` varchar(8000) NOT NULL,
`desc` varchar(100) NOT NULL,
UNIQUE KEY `id` (`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
这样引擎跟自增字段都没有变
看下面一个一个例子,就知道有什么变化了
CREATE TABLE `t4_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a1` int(11) NOT NULL,
`a2` int(11) DEFAULT NULL,
`remark` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
KEY `a1_2_idx` (`a1`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
create table t6_innodb select * from t4_innodb where1=2;
CREATE TABLE `t6_innodb` (
`id` int(11) NOT NULL DEFAULT '0',
`a1` int(11) NOT NULL,
`a2` int(11) DEFAULT NULL,
`remark` varchar(200) NOT NULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8
create table t8_innodb like t4_innodb;
CREATE TABLE `t8_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a1` int(11) NOT NULL,
`a2` int(11) DEFAULT NULL,
`remark` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
KEY `a1_2_idx` (`a1`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
【编辑推荐】
【责任编辑:段燃 TEL:(010)68476606】
点赞 0