生成查询中的位置与位置 [英] WHERE vs HAVING in generated queries

问题描述

我知道这个标题被过度使用了，但是似乎我的问题还没有得到回答. 因此，问题是这样的:

I know that this title is overused, but it seems that my kind of question is not answered yet. So, the problem is like this:

我有一个由四个表(表，行，列，值)组成的表结构，用于重新创建information_schema的行为(以某种方式). 在php中，我正在生成查询以检索数据，并且结果仍然看起来像普通表:

I have a table structure made of four tables (tables, rows, cols, values) that I use to recreate the behavior of the information_schema (in a way). In php I am generating queries to retrieve the data, and the result would still look like a normal table:

SELECT 
  (SELECT value FROM `values` WHERE `col` = "3" and row = rows.id) as "col1",
  (SELECT value FROM `values` WHERE `col` = "4" and row = rows.id) as "col2" 
FROM rows WHERE `table` = (SELECT id FROM tables WHERE name = 'table1') 
HAVING (col2 LIKE "%4%")

OR

SELECT * FROM 
(SELECT 
  (SELECT value FROM `values` WHERE `col` = "3" and row = rows.id) as "col1",
  (SELECT value FROM `values` WHERE `col` = "4" and row = rows.id) as "col2" 
FROM rows WHERE `table` = (SELECT id FROM tables WHERE name = 'table1')) d 
WHERE col2 LIKE "%4%" 

请注意，我定义结果列的部分是由php脚本生成的.我这样做的重要性不那么重要，但是我想扩展该算法，以生成更广泛使用的查询. 我们已经解决了核心问题，我必须决定是否要为查询生成where或having部分，而且我知道何时同时使用它们，问题是我的算法没有，我有为此进行一些额外的检查.但是以上两个查询是等效的，我总是可以将任何查询放在子查询中，给它一个别名，然后在新的派生表上使用where.但是我想知道我是否会遇到性能问题，或者这是否会以一种意想不到的方式再次出现在我身上.

note that the part where I define the columns of the result is generated by a php script. It is less important why I am doing this, but I want to extend this algorithm that generates the queries for a broader use. And we got to the core problem, I have to decide if I will generate a where or a having part for the query, and I know when to use them both, the problem is my algorithm doesn't and I have to make a few extra checks for this. But the two above queries are equivalent, I can always put any query in a sub-query, give it an alias, and use where on the new derived table. But I wonder if I will have problems with the performance or not, or if this will turn back on me in an unexpected way.

我知道它们都如何工作，应该在哪里更快，但这就是为什么我来这里询问.希望我能使自己理解，请原谅我的英语和冗长无用的词组转换等等.

I know how they both work, and how where is supposed to be faster, but this is why I came here to ask. Hopefully I made myself understood, please excuse my english and the long useless turns of phrases, and all.

编辑1

我已经知道两者之间的区别，这意味着，我唯一的难题是使用其他表中具有可变数字和大小的自定义列，并试图获得与使用正常创建的表相同的结果，这意味着我必须使用HAVING来过滤派生表列，同时可以选择将其包装在子查询中并在正常情况下使用，这可能会创建一个临时表，此后将对其进行过滤.这会影响大型数据库的性能吗?不幸的是，我现在无法对此进行测试，因为我负担不起用超过10亿个条目填充数据库(这将是这样的:rows表中为10亿，values表中为50亿，每一行在cols表中有5列，5行，在tables表中有1行=总共 6,000,006 个条目

I already know the difference between the two, and all that implies, my only dilemma is that using custom columns from other tables, with variable numbers and size, and trying to achieve the same result as using a normally created table implies that I must use HAVING for filtering the derived tables columns, at the same time having the option to wrap it up in a subquery and use where normally, this probably will create a temporary table that will be filtered afterwards. Will this affect performance for a large database? And unfortunately I cannot test this right now, as I do not afford to fill the database with over 1 billion entries (that will be something like this: 1 billion in rows table, 5 billions in values table, as every row have 5 columns, 5 rows in cols table and 1 row in tables table = 6,000,006 entries in total)

现在我的数据库如下:

+----+--------+-----------+------+
| id | name   | title     | dets |
+----+--------+-----------+------+
|  1 | table1 | Table One |      |
+----+--------+-----------+------+

+----+-------+------+
| id | table | name |
+----+-------+------+
|  3 |     1 | col1 |
|  4 |     1 | col2 |
+----+-------+------+
where `table` is a foreign key from table `tables` 


+----+-------+-------+
| id | table | extra |
+----+-------+-------+
|  1 |     1 |       |
|  2 |     1 |       |
+----+-------+-------+
where `table` is a foreign key from table `tables` 

+----+-----+-----+----------+
| id | row | col | value    |
+----+-----+-----+----------+
|  1 |   1 |   3 | 13       |
|  2 |   1 |   4 | 14       |
|  6 |   2 |   4 | 24       |
|  9 |   2 |   3 | asdfghjk |
+----+-----+-----+----------+
where `row` is a foreign key from table `rows` 
where `col` is a foreign key from table `cols` 

编辑2

这里的条件只是为了演示！

The conditions are there just for demonstration purposes!

编辑3

对于只有两行，似乎两者之间是有区别的，使用having的是0,0008，而使用where的是0.0014-0.0019.我想知道这是否会影响大量行和列的性能

For only two rows, it seems there is a difference between the two, the one using having is 0,0008 and the one using where is 0.0014-0.0019. I wonder if this will affect performance for large numbers of rows and columns

编辑4

两个查询的结果相同，即:

The result of the two queries is identical, and that is:

+----------+------+
| col1     | col2 |
+----------+------+
| 13       | 14   |
| asdfghjk | 24   |
+----------+------+

推荐答案

HAVING专用于GROUP BY，WHERE用于提供条件参数.另请参见位置与拥有空间

HAVING is specifically for GROUP BY, WHERE is to provide conditional parameters. See also WHERE vs HAVING

这篇关于生成查询中的位置与位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！