Mysql-merge table

Merge table Note: 1. The structure of each sub-table must be consistent. The structure of the master table and sub-table must be consistent. 2. The index of each sub-table will exist in the merge table, therefore, you cannot perform a unique search based on the index in the merge table. 3. The sub-table must be MyISAM engine 4AUTO_INCREMENT and will not work as expected. Create table statement createtabletab

Merge table Note: 1. The structure of each sub-table must be consistent. The structure of the master table and sub-table must be consistent. 2. The index of each sub-table will exist in the merge table, therefore, you cannot perform a unique search based on the index in the merge table. 3 sub-tables must be MyISAM engine 4 AUTO_INCREMENT and will not work as expected. Create table tab

Merge table

Note: 1. The structure of each sub-table must be consistent. The structure of the master table and sub-table must be consistent. 2. The index of each sub-table exists in the merge table, therefore, you cannot perform a unique search based on the index in the merge table. 3 sub-tables must be MyISAM engine 4 AUTO_INCREMENT and will not work as expected. Create table tablename (normal field) engine = merge insert_method = lastinsert_method: There are two values: LAST. If you execute the insert command to operate the merge table, the insert operation adds data to the last sub-table. Similarly, when inserting data, the data is added to the FIRST sub-table. Example: create table user1 (id int (10) not null auto_increment, name varchar (50), sex int (1), primary key (id) engine = myisam charset = utf8; create table user2 (id int (10) not null auto_increment, name varchar (50), sex int (10), primary key (id) engine = myisam charset = utf8; insert into user1 (name, sex) values ('zhang san', 0); insert into user2 (name, sex) values ('lisi', 1 ); mysql> select * from user1; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | Zhang San | 0 | + ---- + ------ + mysql> select * from user2; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | lisi | 1 | + ---- + ------ + create table alluser (id int (10) not null auto_increment, name varchar (50), sex int (10), index (id) type = merge union = (user1, user2) insert_method = last; mysql> select * from alluser; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | Zhang San | 0 | 1 | lisi | 1 | + ---- + ------ + ------ + mysql> insert into alluser (name, sex) values (' ', 0); mysql> select * from user1; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | Zhang San | 0 | + ---- + ------ + 1 row in set (0.00 sec) mysql> select * from user2; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | lisi | 1 | 2 | | 0 | + ---- + ------ + ------ + 2 rows in set (0.00 sec) // This data is stored in user2 because our insert_method parameter is set to lastmysql> update alluser set sex = replace (sex,) where id = 2; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | Zhang San | 0 | 1 | lisi | 1 | 2 | | 1 | + ---- + ------ +

Author: maildocgaojingru published on 17:32:27 Original article link

Read: 53 comments: 0 view comments

Original article address: mysql-merge table. Thank you for sharing it with the original author.