mysql MERGE 错误

Question

错误代码：
Error Code : 1168
Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't existCREATE TABLE `website0001` (                                                                                       
                 `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                              
                 `siteBigType` int(11) DEFAULT NULL,                                                                                                
                 `siteBigTypeName` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,                                                               
                 `siteSmallType` int(11) DEFAULT NULL,                                                                                              
                 `siteName` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,                                                                      
                 `todaysort` varchar(50) DEFAULT '0',                                                                                              
                 `todayqushi` varchar(50) DEFAULT '0',                                                                                                   
                 `weekedsort` varchar(50) DEFAULT '0',                                                                                                 
                 `weekedqushi` varchar(50) DEFAULT '0',                                                                                                      
                 `monthedsort` varchar(50) DEFAULT '0',                                                                                                
                 `monthedqushi` varchar(50) DEFAULT '0',                                                                                                     
                 `seasonsort` varchar(50) DEFAULT NULL,                                                                                                
                 `seasonqushi` varchar(50) DEFAULT '0',                                                                                                      
                 `siteWorth` double DEFAULT '0' COMMENT '网站价值',                                                                             
                 `date` date DEFAULT NULL,                                                                                                          
                 PRIMARY KEY (`id`)                                                                                                                                                                           
               )ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC  CREATE TABLE `website0002` (                                                                                                       
                 `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                              
                 `siteBigType` int(11) DEFAULT NULL,                                                                                                
                 `siteBigTypeName` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,                                                               
                 `siteSmallType` int(11) DEFAULT NULL,                                                                                              
                 `siteName` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,                                                                      
                 `todaysort` varchar(50) DEFAULT '0',                                                                                              
                 `todayqushi` varchar(50) DEFAULT '0',                                                                                                   
                 `weekedsort` varchar(50) DEFAULT '0',                                                                                                 
                 `weekedqushi` varchar(50) DEFAULT '0',                                                                                                      
                 `monthedsort` varchar(50) DEFAULT '0',                                                                                                
                 `monthedqushi` varchar(50) DEFAULT '0',                                                                                                     
                 `seasonsort` varchar(50) DEFAULT NULL,                                                                                                 
                 `seasonqushi` varchar(50) DEFAULT '0',                                                                                                      
                 `siteWorth` double DEFAULT '0' COMMENT '网站价值',                                                                             
                 `date` date DEFAULT NULL,                                                                                                          
                 PRIMARY KEY (`id`)                                                                                                                                                                           
               )ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC  
CREATE TABLE `allwebsite` (                                                                                                       
                `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                              
                `siteBigType` int(11) DEFAULT NULL,                                                                                                
                `siteBigTypeName` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,                                                               
                `siteSmallType` int(11) DEFAULT NULL,                                                                                              
                `siteName` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,                                                                      
                `todaysort` varchar(50) DEFAULT '0',                                                                                              
                `todayqushi` varchar(50) DEFAULT '0',                                                                                                   
                `weekedsort` varchar(50) DEFAULT '0',                                                                                                 
                `weekedqushi` varchar(50) DEFAULT '0',                                                                                                      
                `monthedsort` varchar(50) DEFAULT '0',                                                                                                
                `monthedqushi` varchar(50) DEFAULT '0',                                                                                                     
                `seasonsort` varchar(50) DEFAULT NULL,                                                                                                  
                `seasonqushi` varchar(50) DEFAULT '0',                                                                                                      
                `siteWorth` double DEFAULT '0' COMMENT '网站价值',                                                                             
                `date` date DEFAULT NULL,                                                                                                             
index(`id`) , index(`seasonsort`)
               )ENGINE=MERGE UNION=(website0001,website0002) INSERT_METHOD=LAST AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC  然后执行
show index from allwebsite
就提示这个错误。

Answer 1

查看手册，merge表仅有一个frm结构定义文件和一个mrg底层表列表文件 这两个文件。也就是说merge表是没有自己的索引文件的。
你应该在每一个表中建一个index(`seasonsort`) 

Answer 2

不知道你用的那个版本，我认为你的执行没有什么问题，而且事实也是如此，这里是我执行后的情况
mysql> show index from allwebsite \G
*************************** 1. row ***************************
       Table: allwebsite
  Non_unique: 1
    Key_name: id
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: allwebsite
  Non_unique: 1
    Key_name: seasonsort
Seq_in_index: 1
 Column_name: seasonsort
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)mysql>MERGE存储引擎，也被认识为MRG_MyISAM引擎，是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。而你的2个分表都一一致的，创建merge表后有自己的frm和mrg文件，你在上面创建索引没有任何问题！这是单个表的索引mysql> show index from website0001 \G
*************************** 1. row ***************************
       Table: website0001
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)mysql>你在mrg  上建索引对原表索引没有影响