mysql - 合并引擎的主键不起作用？

Question

我的代码:

mysql> CREATE TABLE super (id int PRIMARY KEY NOT NULL AUTO_INCREMENT);
mysql> CREATE TABLE super2 (id int PRIMARY KEY NOT NULL AUTO_INCREMENT);

mysql> INSERT INTO super VALUES(1),(2);
mysql> INSERT INTO super2 VALUES(1),(3);

mysql> ALTER TABLE super ENGINE='MYIsam';
mysql> ALTER TABLE super2 ENGINE='MYIsam' 

然后我创建了 MERGE Engine 表:

mysql> CREATE TABLE super_merge (id int PRIMARY KEY NOT NULL AUTO_INCREMENT) E
INE='MERGE' UNION=(super,super2);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM super_merge;
+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
|  3 |
+----+ 

不会有唯一性规则吗？也许这很正常。

既然行不通，为什么要创建表字段结构呢？

当我截断表 super,super2 时，表 super_merge 也为空!

TRUNCATE TABLE super2;
mysql> SELECT * FROM super_merge;
Empty set (0.00 sec)

mysql> INSERT INTO super VALUES(1),(2);
mysql> INSERT INTO super2 VALUES(1),(3);

mysql> SELECT * FROM super_merge;
+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
|  3 |

Answer 1

documentation of the MERGE engine 中明确提到了这一点:

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.)

合并表实际上是两个表的联合 View ，因此从基础表中删除项目自然也会从合并表中删除这些项目(因为它们一开始就不在合并表中，但只是在底层表格中)。