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.

Commenti

Sto caricando i commenti......
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.