在mysql数据库的所有表中搜索

Question

我有一个mysql (mariadb)数据库，有许多表，所有的表都有相同的结构。

为了简单起见，让我们假设结构如下所示。

UserID - Varchar (primary)
Email - Varchar (indexed)

是否可以一起查询电子邮件字段的所有表？

编辑:我还没有完成数据库的设计，我可以把所有的数据放在一个表中。但是我担心这个大桌子会减慢操作速度，如果它崩溃了，它的恢复将是痛苦的。有什么想法？

我读过一些建议将所有数据一起转储到一个临时表中的答案，但这对我来说不是一种选择。

Mysql或PHPMyAdmin也没有用，我正在寻找的是一个SQL查询，而不是一个前端搜索技术。

Answer 1

在SQL中没有简洁的方法来表达这类事情。

SELECT a,b,c  FROM <<<all tables>>> WHERE b LIKE 'whatever%' 

如果事先知道所有的表名，就可以编写这样的查询。

SELECT a,b,c  FROM table1 WHERE b LIKE 'whatever%' 
UNION ALL
SELECT a,b,c  FROM table2 WHERE b LIKE 'whatever%' 
UNION ALL
SELECT a,b,c  FROM table3 WHERE b LIKE 'whatever%' 
UNION ALL
SELECT a,b,c  FROM table4 WHERE b LIKE 'whatever%' 
...

或者你可以创建这样的视图。

CREATE VIEW everything AS 
SELECT *  FROM table1 
UNION ALL
SELECT *  FROM table2 
UNION ALL
SELECT *  FROM table3 
UNION ALL
SELECT *  FROM table4 
...

然后使用

SELECT a,b,c  FROM everything WHERE b LIKE 'whatever%' 

如果事先不知道所有表的名称，可以从MySQL的information_schema中检索它们，并编写一个程序来创建查询，就像我的建议之一。如果你决定这样做，并需要帮助，请问另一个问题。

不幸的是，这类查询总是比只查询一个表慢得多。为什么？MySQL必须重复在每个表上运行查询的开销，而且一个索引比在不同表上使用多个索引要快。

Pro提示尝试设计数据库，以便在添加用户(或客户或其他什么)时不添加表。

编辑您可能会出于查询性能的原因而尝试使用多个表。恕我直言，请不要这样做。正确的索引几乎总是比搜索多个表提供更好的查询性能。就其价值而言，MySQL的一个“巨大”表通常至少有一亿行，它对它的能力提出了挑战。真的。只要索引正确，就有数十万行处于性能的最佳位置。这是一个很好的参考资料，其中之一。https://use-the-index-luke.com/

避免在生产中常规地创建新表的另一个原因是:维护和优化包含大量表的数据库是*xxx中的一个难题。六个月后，随着数据库的扩展，您几乎肯定需要添加索引来帮助加快一些缓慢的查询。如果您必须添加许多索引，您或您的继任者都不会喜欢它。

您还可能会尝试使用多个表，以使数据库更能抵御崩溃。恕我直言，这是行不通的。崩溃是罕见的，在可靠的硬件上，灾难性的无法恢复的崩溃几乎是罕见的。崩溃会破坏多个表。(应急能力:良好的备份)。

请记住，MySQL已经开发了超过四分之一世纪(和其他RDBMS一样)。数千年来，程序员们都在快速、灵活地开发它。你也可以利用所有的工作，因为你不能超过它。我知道这是因为我试过但失败了。

保持数据库简单。花费您的时间(您唯一不可替代的资产)，使您的应用程序优秀，这样您实际上得到了数百万用户。