MERGE

Descrizione

Lo Storage Engine MERGE, chiamato anche MRG_MyISAM, rappresenta collezioni di tabelle MyISAM identiche, che possono essere utilizzate come una sola. "Identiche" significa che tutte le tabelle devono avere le stesse definizioni per le colonne e gli indici. Non è possibile unire tabelle MyISAM le cui colonne sono posizionate in un ordine differente, o che non hanno le stesse colonne, o i cui indici sono ordinati diversamente. Tuttavia una o più tabelle MyISAM possono essere compresse con myisampack. Si veda http://dev.mysql.com/doc/refman/5.1/en/myisampack.html. Le differenze nelle opzioni delle tabelle, come AVG_ROW_LENGTH, MAX_ROWS o PACK_KEYS non costituiscono un problema.

Esempi

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 total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

In the following example, we'll create three MyISAM tables, and then a MERGE table on them. However, one of them uses a different datatype for the column b, so a SELECT will produce an error:

MariaDB [test]> CREATE TABLE t1 (
    -> a INT,
    -> b INT
    -> ) ENGINE = MyISAM;

MariaDB [test]> CREATE TABLE t2 (
    -> a INT,
    -> b INT
    -> ) ENGINE = MyISAM;

MariaDB [test]> CREATE TABLE t3 (
    -> a INT,
    -> b TINYINT
    -> ) ENGINE = MyISAM;

MariaDB [test]> CREATE TABLE t_mrg (
    -> a INT,
    -> b INT
    -> ) ENGINE = MERGE,UNION=(t1,t2,t3);

MariaDB [test]> SELECT * FROM t_mrg;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
 or of non-MyISAM type or doesn't exist

To find out what's wrong, we'll use a CHECK TABLE:

MariaDB [test]> CHECK TABLE t_mrg;
+------------+-------+----------+-----------------------------------------------
------------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text
                                                      |
+------------+-------+----------+-----------------------------------------------
------------------------------------------------------+
| test.t_mrg | check | Error    | Table 'test.t3' is differently defined or of n
on-MyISAM type or doesn't exist                       |
| test.t_mrg | check | Error    | Unable to open underlying table which is diffe
rently defined or of non-MyISAM type or doesn't exist |
| test.t_mrg | check | error    | Corrupt
                                                      |
+------------+-------+----------+-----------------------------------------------
------------------------------------------------------+

Now, we know that the problem is in t3's definition.