mysql快速创建空表
来源:互联网 发布:福建旅游 知乎 编辑:程序博客网 时间:2024/06/01 18:04
今天有人问我用什么方法可以创建空表?
在MYSQL中有两种方法。
1、create table select ...
2、create table like ...
第一种很多人都知道,第二种却很少人用。
第一种有个缺点
1、第一种会取消掉原来表的有些定义。
手册上是这么讲的:
Some conversion of data types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns.
不过我测试过,只会取消自增属性!(其他版本没有测试过!)
第二种就不会。
我们来看看例子:
mysql> create table t_old (id serial, content varchar(8000) not null,`desc` varchar(100) not null) engine innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> 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=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t_select select * from t_old where 1 = 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
PS:如果想要保持一样的引擎,就加上。
这样写:create table t_select engine innodb select * from t_old where 1 = 0;
mysql> show create table t_select;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_select | CREATE TABLE `t_select` (
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`content` varchar(8000) NOT NULL,
`desc` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t_like like t_old;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t_like;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create 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=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
在MYSQL中有两种方法。
1、create table select ...
2、create table like ...
第一种很多人都知道,第二种却很少人用。
第一种有个缺点
1、第一种会取消掉原来表的有些定义。
手册上是这么讲的:
Some conversion of data types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns.
不过我测试过,只会取消自增属性!(其他版本没有测试过!)
第二种就不会。
我们来看看例子:
mysql> create table t_old (id serial, content varchar(8000) not null,`desc` varchar(100) not null) engine innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> 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=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t_select select * from t_old where 1 = 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
PS:如果想要保持一样的引擎,就加上。
这样写:create table t_select engine innodb select * from t_old where 1 = 0;
mysql> show create table t_select;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_select | CREATE TABLE `t_select` (
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`content` varchar(8000) NOT NULL,
`desc` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t_like like t_old;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t_like;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create 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=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
- mysql快速创建空表
- PostgreSQL 快速创建空表TIPS
- 快速创建空二维数组
- MySQL 快速清空Magento 订单、客户、 产品 表信息
- MySql快速创建数字辅助表
- VisualStudio 快速创建空解决方案项目代码
- 怎样快速清空mysql数据库?
- mysql快速创建和已有表相同表结构的表
- mysql如何快速创建相同结构的表
- MySQL 最快速清空数据表数据的 SQL 语句
- 快速为MySQL创建大量测试数据
- MySQL使用存储实现快速创建多分表
- mysql清空数据库表
- 快速清空数据库中所有表
- truncate 快速清空数据库表
- 表空_用户创建胶
- mysql快速复制表
- 快速创建和mysql表对应的java domain实体类
- Concept-第10章翻译
- MYSQL中删除重复记录的方法
- bjam的主要选项
- mysql的EVENT体验
- 比爱,更爱你
- mysql快速创建空表
- 关于awt/swing组件中paint, update, paintComponent, repaint的经验
- Lesson one hundred thirty nine Is that you,John?
- 登录认证
- "No CurrentSessionContext configured"错误
- GUIXML 的 JavaScript Engine 更新列表 Until Apr. 14.
- linux解决中文乱码
- C初学者应该怎么走?
- 九层之台,起于垒土