创建表时使用UNION

Database-1

create table sample (
  id INT, 
  nm VARCHAR(10)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
UNION=(for tables from another databases);

So, when we do union what actually it meance? Please explain, I am getting confusing for this type of UNION.

那么,当我们联合起来实际上是什么时候呢?请解释一下,我对这种类型的UNION感到困惑。

1 个解决方案

#1

That looks close to the syntax for creating a merge table, but it has the engine type wrong. Your statement will ignore the union clause and simply create a new, empty table. In order to create merge table you need to specify ENGINE=MERGE.

这看起来接近于创建合并表的语法,但它的引擎类型错误。您的语句将忽略union子句,只需创建一个新的空表。要创建合并表,您需要指定ENGINE = MERGE。

14.3 The MERGE Storage Engine

14.3 MERGE存储引擎

The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one.

MERGE存储引擎,也称为MRG_MyISAM引擎,是可以作为一个使用的相同MyISAM表的集合。

The tables you specify in the UNION clause there, must all be identical - ie, having the same index and column specification, and they must all be in the same order in each table.

您在那里的UNION子句中指定的表必须全部相同 - 即具有相同的索引和列规范,并且它们必须在每个表中的顺序相同。

After that, can you query your merge table and access the data from all of the tables that form it.

之后,您可以查询合并表并访问构成它的所有表中的数据。

You can also insert into your merge table, which is something you cannot do with a view:

您也可以插入到合并表中,这是您无法对视图执行的操作:

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.

您可以选择指定INSERT_METHOD选项来控制如何进入MERGE表的插入。使用FIRST或LAST值分别导致在第一个或最后一个基础表中进行插入。如果未指定INSERT_METHOD选项或者使用值NO指定它,则不允许插入MERGE表,尝试这样做会导致错误。

Anyway, the doco has the rest of the information if you want to peruse more - I've never felt the need to use this type of table.

无论如何,如果你想要更多地阅读,doco还有其他的信息 - 我从来没有觉得需要使用这种类型的表。

Example:

mysql>
mysql> create table t2 (
    ->   id integer primary key auto_increment,
    ->   val char(20)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> insert into t1(val) values ('table1 a'), ('table1 b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2(val) values ('table2 a'), ('table2 b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> create table mt (
    ->   id integer primary key auto_increment,
    ->   val char(20)
    -> ) engine=merge union=(t1,t2) insert_method=last;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> select * from mt;
+----+----------+
| id | val      |
+----+----------+
|  1 | table1 a |
|  2 | table1 b |
|  1 | table2 a |
|  2 | table2 b |
+----+----------+
4 rows in set (0.00 sec)

mysql> insert into mt(val) values ('12345');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mt;
+----+----------+
| id | val      |
+----+----------+
|  1 | table1 a |
|  2 | table1 b |
|  1 | table2 a |
|  2 | table2 b |
|  3 | 12345    |
+----+----------+
5 rows in set (0.01 sec)

mysql> select * from t2;
+----+----------+
| id | val      |
+----+----------+
|  1 | table2 a |
|  2 | table2 b |
|  3 | 12345    |
+----+----------+
3 rows in set (0.00 sec)