我有2千个表. 表名是由: 'mulu' + isbn 组成的.想在 我想在这2千个表中选者更新时间最靠前的 10个.除了用这个方法:(select *from t1) union (select *from t2) ....union(select from t2000) order by 时间 DESC limit 10还有什么好方法不 ?
CREATE TABLE IF NOT EXISTS `mulu` ( id int(11) not null auto_increment, `isbn` int(11) NOT NULL, `juan` varchar(30) NOT NULL, `zhangjie` varchar(60) NOT NULL, `yeshu` varchar(100) NOT NULL, `contents` varchar(200) NOT NULL, `zishu` int(5) NOT NULL, `gengxinshijian` datetime NOT NULL, unique(id), KEY `isbn` (`isbn`) ) ENGINE=MRG_MyISAM UNION(t1,t2,t3) INSERT_METHOD=LAST DEFAULT CHARSET=utf8 ;表到是定义成功了. 可是 在打开时 出错了. Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
按照手册上的方法 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; 我创建了这些表. 可是在打开total 也提示Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
不知道为什么会 提示Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 这个错误
把这2000个表做成一个合并表
15.3. MERGE存储引擎
15.3.1. MERGE表的问题MERGE存储引擎,也被认识为MRG_MyISAM引擎,是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表。而且,任何或者所有的表可以用myisampack来压缩。请参阅8.2节,“myisampack — 生成压缩的只读MyISAM表”。表选项的差异,比如AVG_ROW_LENGTH, MAX_ROWS或PACK_KEYS都不重要。
id int(11) not null auto_increment,
`isbn` int(11) NOT NULL,
`juan` varchar(30) NOT NULL,
`zhangjie` varchar(60) NOT NULL,
`yeshu` varchar(100) NOT NULL,
`contents` varchar(200) NOT NULL,
`zishu` int(5) NOT NULL,
`gengxinshijian` datetime NOT NULL,
unique(id),
KEY `isbn` (`isbn`)
) ENGINE=MRG_MyISAM UNION(t1,t2,t3) INSERT_METHOD=LAST DEFAULT CHARSET=utf8 ;表到是定义成功了.
可是 在打开时 出错了.
Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
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;
我创建了这些表. 可是在打开total 也提示Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
提示Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 这个错误