Quick table creation in mysql

The statement writing for mysql quick table creation is not complex. The following describes the two most commonly used statements for mysql quick table creation:

 
 

  
  
1：create table t_select select * from t_old where 1 = 0;  
  
  
2：create table t_select1 like t_old;  
 
 

However, the first mysql quick table creation statement has a defect and can cancel some definitions of the original table. Some conversion of data types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns .)
Let's take a look at the example below.

 
 

  
  
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=InnoDB DEFAULT CHARSET=utf8 
  
  
 
  
  
create table t_select select * from t_old where 1 = 0;   
  
  
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   
  
  
 
 
 

In this way, the auto-increment field and table engine have changed.
If you want to maintain the same engine, add: engine innodb
For example:

 
 

  
  
create table t_select engine innodb select * from t_old where 1 = 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=InnoDB DEFAULT CHARSET=utf8   
  
  
 
 
 

In this way, the engine and auto-increment fields are not changed.

Let's look at one of the following examples to see what has changed.

 
 

  
  
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=InnoDB DEFAULT CHARSET=utf8     
  
  
 
  
  
create table t6_innodb select * from t4_innodb where 1=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=InnoDB DEFAULT CHARSET=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=InnoDB DEFAULT CHARSET=utf8    
  
  
 
 
 

Simple MySQL large table backup method

Analysis of MySQL Chinese Table creation Problems

How to add and modify fields in MySQL

MySQL authorization table usage example

Disadvantages of MySQL memory tables