mysql merge engine_使用mysql merge engine 注意点

CREATE TABLE `t1` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`log` varchar(45) ,

PRIMARY KEY (`id`)

) ENGINE=MyISAM;

CREATE TABLE `t2`(

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`log` varchar(45) ,

PRIMARY KEY (`id`)

) ENGINE=MyISAM;

CREATE TABLE `t` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`log` varchar(45),

PRIMARY KEY (`id`)

) ENGINE=MERGE UNION=(t3, t4) INSERT_METHOD=LAST;

再比如

CREATE TABLE t1 (

a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

message CHAR(20)) ENGINE=MyISAM;

CREATE TABLE t2 (

a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

message CHAR(20)) ENGINE=MyISAM;

INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

CREATE TABLE total (

a INT NOT NULL AUTO_INCREMENT,

message CHAR(20), INDEX(a))

ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

select * from t where id=1;

只有当id在merge表是主键或唯一时才会只返回一条记录。

而在total表中，由于a不是主键，所以查询会return所有符合条件的log

select * from total where a =1;

返回结果：

a message

1 Testing

1 Testing

来自 官方文档

http://dev.mysql.com/doc/refman/5.6/en/merge-storage-engine.html

如果想在已經創建的merge表上添加新的union表。執行以下sql

ALTER TABLE total UNION=(t1,t2,t5)

分享到：

2015-08-27 14:46

浏览 993

分类:数据库

评论