mysql能不能把两个同结构的表合并
发布网友
发布时间：2022-04-23 04:31
共3个回答
懂视网
时间：2022-05-01 21:35
merge合并表的要求
1.合并的表使用的必须是MyISAM引擎
2.表的结构必须一致，包括索引、字段类型、引擎和字符集
实例：
create table if not exists user1(
id int(11) not null auto_increment,
name varchar(50) 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(11) not null auto_increment,
name varchar(50) 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(11) not null auto_increment,
name varchar(50) default null,
sex int(1) not null default 0,
primary key (id)
)engine = merge union=(user1,user2) insert_method = last auto_increment=1;
执行insert into alluser (name,sex) values (‘tian‘,1);报错如下：
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn‘t exist
百度了一下原来是默认字符集没写，修改如下
create table if not exists alluser1(
id int(11) not null auto_increment,
name varchar(50) 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;
执行insert into alluser1 (name,sex) values (‘tian‘,1);成功
执行select * from alluser1;显示如下：
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | tian | 1 |
+----+------+-----+
执行select * from user2;显示如下：
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | tian | 1 |
+----+------+-----+
mysql用merge合并表
标签：报错 引擎 err sql isa diff efault nio sel
热心网友
时间：2022-05-01 18:43
当然可以。句法是
(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
如果只是简单地合并:
(SELECT * FROM t1 ) UNION (SELECT * FROM t2 )
热心网友
时间：2022-05-01 20:01
2个方法：
1。union来链接
2。用merge引擎的表来做这个事情
mysql> CREATE TABLE t1 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20));
mysql> CREATE TABLE t2 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20));
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
