MySQL Merge table with merge

Requirements for Merge consolidation tables

1. The merged table must be MyISAM engine

2. The structure of the table must be consistent, including index, field type, engine, and character set

Instance:

CREATE table if not exists user1 (

ID Int (one) not NULL auto_increment,

Name varchar () default NULL,

sex int (1) NOT null default 0,

Primary KEY (ID)

) engine = MyISAM default charset = UTF8 auto_increment=1;

CREATE table if not exists user2 (

ID Int (one) not NULL auto_increment,

Name varchar () default NULL,

sex int (1) NOT null default 0,

Primary KEY (ID)

) engine = MyISAM default charset = UTF8 auto_increment=1;

CREATE table if not exists alluser (

ID Int (one) not NULL auto_increment,

Name varchar () default NULL,

sex int (1) NOT null default 0,

Primary KEY (ID)

Engine = Merge union= (user1,user2) Insert_method = Last auto_increment=1;

Execute INSERT INTO AllUser (name,sex) VALUES (' Tian ', 1), with the following error:

ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of Non-myisam type or doesn ' t exist

Baidu a bit originally is the default character set is not written, modified as follows

CREATE table if not exists alluser1 (

ID Int (one) not NULL auto_increment,

Name varchar () default NULL,

sex int (1) NOT null default 0,

Primary KEY (ID)

Engine = Merge union= (user3,user4) Insert_method = last auto_increment=1 default Charset=utf8;

Execute INSERT INTO Alluser1 (name,sex) VALUES (' Tian ', 1);

Execute SELECT * from Alluser1, shown below:

+----+------+-----+
| ID | name | sex |
+----+------+-----+
| 1 | Tian | 1 |
+----+------+-----+

Execute SELECT * from User2, shown below:

+----+------+-----+
| ID | name | sex |
+----+------+-----+
| 1 | Tian | 1 |
+----+------+-----+

MySQL Merge table with merge