The
MERGE
Storage Engine
1301
To create a
MERGE
table, you must specify a
UNION=(list-of-tables)
option that indicates which
MyISAM
tables to use. You can optionally specify an
INSERT_METHOD
option to control how inserts
into the
MERGE
table take place. Use a value of
FIRST
or
LAST
to cause inserts to be made in the first
or last underlying table, respectively. If you specify no
INSERT_METHOD
option or if you specify it with a
value of
NO
, inserts into the
MERGE
table are not permitted and attempts to do so result in an error.
The following example shows how to create a
MERGE
table:
mysql>
CREATE TABLE t1 (
->
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->
message CHAR(20)) ENGINE=MyISAM;
mysql>
CREATE TABLE t2 (
->
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->
message CHAR(20)) ENGINE=MyISAM;
mysql>
INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql>
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql>
CREATE TABLE total (
->
a INT NOT NULL AUTO_INCREMENT,
->
message CHAR(20), INDEX(a))
->
ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
The older term
TYPE
is supported as a synonym for
ENGINE
for backward compatibility, but
ENGINE
is
the preferred term and
TYPE
is deprecated.
Note that column
a
is indexed as a
PRIMARY KEY
in the underlying
MyISAM
tables, but not in
the
MERGE
table. There it is indexed but not as a
PRIMARY KEY
because a
MERGE
table cannot
enforce uniqueness over the set of underlying tables. (Similarly, a column with a
UNIQUE
index in the
underlying tables should be indexed in the
MERGE
table but not as a
UNIQUE
index.)
After creating the
MERGE
table, you can use it to issue queries that operate on the group of tables as a
whole:
mysql>
SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
To remap a
MERGE
table to a different collection of
MyISAM
tables, you can use one of the following
methods:
•
DROP
the
MERGE
table and re-create it.
• Use
ALTER TABLE tbl_name UNION=(...)
to change the list of underlying tables.
Beginning with MySQL 5.0.60, it is also possible to use
ALTER TABLE ... UNION=()
(that is, with
an empty
UNION
clause) to remove all of the underlying tables.
As of MySQL 5.0.36, the underlying table definitions and indexes must conform more closely than
previously to the definition of the
MERGE
table. Conformance is checked when a table that is part of a
MERGE
table is opened, not when the
MERGE
table is created. If any table fails the conformance checks,
the operation that triggered the opening of the table fails. This means that changes to the definitions
of tables within a
MERGE
may cause a failure when the
MERGE
table is accessed. The conformance
checks applied to each table are:
• The underlying table and the
MERGE
table must have the same number of columns.
• The column order in the underlying table and the
MERGE
table must match.
• Additionally, the specification for each corresponding column in the parent
MERGE
table and the
underlying tables are compared and must satisfy these checks:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...