使用MERGE進行分表:
發現一個MERGE表一直出現Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist報錯,但是在5.0上一直是OK的,於是進行了一些實踐后發現是由於MERGE的表索引和其他子表不一致導致。添加索引之后正常了。而為什么5.0是正常的呢,我猜測是由於5.0的版本對MERGE表沒有那么嚴格的要求。因此當遇到Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist報錯的時候需要從如下幾個方面入手:1、查看是不是有一些表不是MYISAM引擎的表,因為MERGE引擎只適用於MYISAM表2、查看是不是在union的表中含有不存在的表。3、查看是不是MERGE的時候引用了不在同一個庫的表,並且該表沒有指定數據庫名字。4、比較各個表的結構(索引、引擎、列、字符集等)是否一致。
子表代碼為:存儲引擎要為:MyISAM
mysql> CREATE TABLE `test0` ( -> `uin` int(10) unsigned NOT NULL,
-> `data` text NOT NULL,
-> `modtime` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`uin`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.25 sec)
mysql> CREATE TABLE `test1` (
-> `uin` int(10) unsigned NOT NULL,
-> `data` text NOT NULL,
-> `modtime` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`uin`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE `test2` (
-> `uin` int(10) unsigned NOT NULL,
-> `data` text NOT NULL,
-> `modtime` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`uin`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `test3` (
-> `uin` int(10) unsigned NOT NULL,
-> `data` text NOT NULL,
-> `modtime` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`uin`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
MERGE表代碼為:存儲引擎要為MRG_MyISAM
mysql> CREATE TABLE `test` (
-> `uin` int(10) unsigned NOT NULL,
-> `data` text NOT NULL,
-> `modtime` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`uin`)
-> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`test0`,`test1`,`test2`,`test3`,`test4`);
Query OK, 0 rows affected (0.01 sec)
並沒有test4表
mysql> select * from test;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
mysql> insert into `test0` values(12,'sfs',23);
Query OK, 1 row affected (0.22 sec)
查詢的時候報錯:
mysql> select * from test;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
改成:
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `test` (
-> `uin` int(10) unsigned NOT NULL,
-> `data` text NOT NULL,
-> `modtime` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`uin`)
-> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`test0`,`test1`,`test2`,`test3`);
Query OK, 0 rows affected (0.01 sec)
去掉test4
mysql> select * from test;
+-----+------+---------+
| uin | data | modtime |
+-----+------+---------+
| 12 | sfs | 23 |
+-----+------+---------+
1 row in set (0.00 sec)
[root@localhost105 testdb]# ll
total 304
-rw-r----- 1 mysql mysql 8624 Aug 4 17:27 test0.frm
-rw-r----- 1 mysql mysql 20 Aug 4 17:29 test0.MYD
-rw-r----- 1 mysql mysql 2048 Aug 4 17:29 test0.MYI
-rw-r----- 1 mysql mysql 8624 Aug 4 17:27 test1.frm
-rw-r----- 1 mysql mysql 0 Aug 4 17:27 test1.MYD
-rw-r----- 1 mysql mysql 1024 Aug 4 17:27 test1.MYI
-rw-r----- 1 mysql mysql 8624 Aug 4 17:27 test2.frm
-rw-r----- 1 mysql mysql 0 Aug 4 17:27 test2.MYD
-rw-r----- 1 mysql mysql 1024 Aug 4 17:27 test2.MYI
-rw-r----- 1 mysql mysql 8624 Aug 4 17:27 test3.frm
-rw-r----- 1 mysql mysql 0 Aug 4 17:27 test3.MYD
-rw-r----- 1 mysql mysql 1024 Aug 4 17:27 test3.MYI
-rw-r----- 1 mysql mysql 8624 Aug 4 17:45 test.frm
-rw-r----- 1 mysql mysql 24 Aug 4 17:45 test.MRG
test表沒有數據文件test.MYD
實際存數據是test0,test1,test2,test3. 而test總表只是存了分表的信息,並沒有存數據。