mysql 表merge及壓縮

• MySQL merge
• alter merge表
• 注意事項

#!/bin/sh
echo "merge iplog_xx --> iplog_merge"
echo "DROP TABLE IF EXISTS iplog_merge" | mysql -h jill fb
sql_s='CREATE TABLE iplog_merge (
id int(11) NOT NULL,
ip varchar(30) collate utf8_bin NOT NULL,
time datetime NOT NULL,
type tinyint(4) NOT NULL default '0',
ip_num int(10) unsigned default NULL,
KEY id (id),
KEY ip_id (ip,id),
KEY ipnum_id (ip_num,id)
) ENGINE=MERGE UNION ('
sql_end=') DEFAULT CHARSET=utf8 COLLATE=utf8_bin'
iplog_t=`mysql -h jill fb  --skip-column-names -e "show tables like 'iplog_2009%' "`
#echo $iplog_t | tr ' ' ','
t_name=`echo $iplog_t | tr ' ' ','`
echo "$sql_s$t_name$sql_end" 
echo "$sql_s$t_name$sql_end" | MySQL -h jill fb
echo "done"

alter merge表

vi iplog_merge.MRG

注意事項:

• 索引次序不同會出錯

MySQL> desc iplog_merge;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exists
**** 即使索引相同,次序不同,如下都會出錯 ***
CREATE TABLE `iplog_20090428` (
...
KEY `id` (`id`),
KEY `ip_id` (`ip`,`id`),
KEY `ipnum_id` (`ip_num`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE `iplog_20090429` (
...
KEY `ipnum_id` (`ip_num`,`id`),
KEY `id` (`id`),
KEY `ip_id` (`ip`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

• 壓縮和非壓縮的表在一個Merge會出錯

MySQL> select * from iplog_merge where id=200000053;
ERROR 1030 (HY000): Got error 124 from storage engine

• (ipo2008@jill:3306/fb)> select * from iplog_merge where id=245076456;
• ERROR 1030 (HY000): Got error 124 from storage engine

It is documented in the manual, "MERGE Table Problems":
* You can't use `DROP TABLE', `ALTER TABLE', `DELETE FROM' without
a `WHERE' clause, `REPAIR TABLE', `TRUNCATE TABLE', `OPTIMIZE
TABLE', or `ANALYZE TABLE' on any of the tables that are mapped
into a `MERGE' table that is "open."  If you do this, the `MERGE'
table may still refer to the original table and you will get
unexpected results. The easiest way to work around this deficIEncy
is to issue a `FLUSH TABLES' statement to ensure that no `MERGE'
tables remain "open."