1个表，但有两个表。像mysql表的磁盘分区之类的东西

Is there a way to have one table that is split in two, or partitioned like a hard drive, so that I can call

有没有办法让一个表分成两个，或像硬盘一样分区，以便我可以调用

SELECT * FROM email validated

要么

SELECT * FROM email pending

and get two different results. Both results not containing the other's rows.

得到两个不同的结果。两个结果都不包含其他行。

is this something. I feel like I read about mysql partitioning somewhere, a long time ago, and I was wondering if that is what this is. If not, is this possible.

这是什么东西。我觉得我很久以前就读过某个地方的mysql分区，我想知道这是不是这个。如果没有，这是否可能。

2 个解决方案

#1

MySQL supports a virtual table known as a VIEW. A VIEW is effectively a stored MySQL query that can be queried as though it were a real table.

MySQL支持称为VIEW的虚拟表。 VIEW实际上是一个存储的MySQL查询，可以查询它，就好像它是一个真正的表。

Using the example you provide, you would create a base table called email, as follows:

使用您提供的示例，您将创建一个名为email的基表，如下所示：

CREATE TABLE `email` (
            `email` VARCHAR(64) NOT NULL,
            `validated` CHAR(1) NOT NULL DEFAULT '0',
            PRIMARY KEY (`email`))
            ENGINE = MyISAM;

and then two virtual tables (VIEWS), as follows:

然后是两个虚拟表（VIEWS），如下所示：

CREATE VIEW `email_validated` AS SELECT * FROM `email` WHERE `validated`='1';
CREATE VIEW `email_pending` AS SELECT * FROM `email` WHERE `validated`='0';

You can then query both of the views as though they were actual tables.

然后，您可以查询这两个视图，就好像它们是实际的表一样。

Recognize, however, that using views contains a performance penalty in that the entire view is queried (the entire select statement for the view is executed) whenever the view is referenced. On an example as trivial as this, it won't be a big deal provided the 'validated' field is indexed in the base table. On a more complicated view, however, it may not make sense to load the entire view virtual table into memory when only trying to retrieve a few rows.

但是，要认识到，使用视图包含性能损失，因为每当引用视图时，都会查询整个视图（执行视图的整个select语句）。在一个像这样微不足道的例子中，如果在基表中索引'validated'字段，它将不会是一个大问题。但是，在更复杂的视图中，仅在尝试检索几行时将整个视图虚拟表加载到内存中可能没有意义。

Other database engines have a structure called a Materialized View, which is the same thing as a MySQL view, excepting that the materialized view exists as a realized table updated at some frequency or trigger. Any operation that can be done to a real table can be done to a materialized table, including changing indexes or even storage engines. It is completely reasonable to have a transaction history using an Archive storage engine while maintaining a roll-up summary table materialized view using a Memory storage engine. Although MySQL does not natively support Materialized Views, there are tricks to mimic the behavior of Materialized Views.

其他数据库引擎有一个称为物化视图的结构，它与MySQL视图相同，不同之处在于物化视图作为以某种频率或触发器更新的已实现表存在。可以对实际表执行的任何操作都可以对实现表执行，包括更改索引甚至存储引擎。在使用内存存储引擎维护汇总汇总表实体化视图的同时，使用归档存储引擎创建事务历史记录是完全合理的。虽然MySQL本身不支持物化视图，但有一些技巧可以模仿物化视图的行为。

#2

Well, even if that was possible using partitioning, it would be wrong, as the user eventually will validate the email, and you will have to move it to another partition, which is expensive operation.

好吧，即使可以使用分区，这也是错误的，因为用户最终将验证电子邮件，并且您将不得不将其移动到另一个分区，这是一项昂贵的操作。

You have to add column in the table, to store the value. Then, you can partition on that column, so physically recored would be at different places, even on different servers if you setup multiple servers, but that does not make sense.

您必须在表中添加列才能存储该值。然后，您可以对该列进行分区，因此如果您设置多个服务器，即使在不同的服务器上，也可以在不同的位置进行物理评估，但这没有意义。

#1