即使所有内容都应该匹配，SQL Like 或 REGEXP 也会过滤结果
这就是我所拥有的
SELECT node.nid AS nid, node.type AS node_type, product.*
FROM ml_node node
LEFT JOIN ml_content_type_product product
ON node.vid = product.vid
WHERE (node.type in('product'))
AND product.field_sockel_value REGEXP '.*'
AND product.field_artikel_value REGEXP '.*'
AND product.field_leistung_value REGEXP '.*'
AND product.field_licht_farbe_value REGEXP '.*'
AND product.field_rubrik_value REGEXP '.*'
AND product.field_artikelgruppe_value REGEXP '.*'
ORDER BY product.field_artikel_value
有了这些位置条件，我会假设这给出了与以下相同的结果：
SELECT node.nid AS nid, node.type AS node_type, product.*
FROM ml_node node
LEFT JOIN ml_content_type_product product
ON node.vid = product.vid
WHERE (node.type in('product'))
ORDER BY product.field_artikel_value
但事实并非如此。第一个返回 494 行，最后一个返回 717 行。因此缺少一堆行。当我使用这个查询时，
SELECT node.nid AS nid, node.type AS node_type, product.*
FROM ml_node node
LEFT JOIN ml_content_type_product product
ON node.vid = product.vid
WHERE (node.type in('product'))
AND product.field_artikel_value REGEXP '.*'
AND product.field_leistung_value REGEXP '.*'
AND product.field_rubrik_value REGEXP '.*'
AND product.field_artikelgruppe_value REGEXP '.*'
ORDER BY product.field_artikel_value
我也得到了 717 行。那么……是什么让这些被移除的条件如此特别呢？这些列都是长文本类型（因为 cms 就是这样做的）并且具有完全相同的属性。
顺便说一句我在使用 LIKE 而不是 REGEXP 时注意到了相同的行为。
this is what i have
SELECT node.nid AS nid, node.type AS node_type, product.*
FROM ml_node node
LEFT JOIN ml_content_type_product product
ON node.vid = product.vid
WHERE (node.type in('product'))
AND product.field_sockel_value REGEXP '.*'
AND product.field_artikel_value REGEXP '.*'
AND product.field_leistung_value REGEXP '.*'
AND product.field_licht_farbe_value REGEXP '.*'
AND product.field_rubrik_value REGEXP '.*'
AND product.field_artikelgruppe_value REGEXP '.*'
ORDER BY product.field_artikel_value
Having these where-conditions I would assume that this gives the same results as:
SELECT node.nid AS nid, node.type AS node_type, product.*
FROM ml_node node
LEFT JOIN ml_content_type_product product
ON node.vid = product.vid
WHERE (node.type in('product'))
ORDER BY product.field_artikel_value
But it does not. The first returns 494 rows and the last one gives 717. So there's missing a bunch of rows. When I use this query
SELECT node.nid AS nid, node.type AS node_type, product.*
FROM ml_node node
LEFT JOIN ml_content_type_product product
ON node.vid = product.vid
WHERE (node.type in('product'))
AND product.field_artikel_value REGEXP '.*'
AND product.field_leistung_value REGEXP '.*'
AND product.field_rubrik_value REGEXP '.*'
AND product.field_artikelgruppe_value REGEXP '.*'
ORDER BY product.field_artikel_value
I also do get 717 rows. So… what could it be that makes those removed conditions so special? The columns are all of type longtext (because the cms does so) and have the exact same attributes.
BTW i noticed the same behavior when using LIKE instead of REGEXP.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
由于您有
LEFT JOIN，您还会得到product.field_sockel_value或product.field_licht_farbe_value为NULL的行。这些行在WHERE子句中被过滤Since you have a
LEFT JOINyou also get lines whereproduct.field_sockel_valueorproduct.field_licht_farbe_valueisNULL. These line got filtered in theWHEREclause这可能是由某些行中的 NULL 值引起的。与另一个值比较时，NULL 字段的计算结果为 False。
如果您想通过第二个查询获取所有值，请将
WHERE子句中与产品表相关的表达式移动到联接条件中，这样您最终会得到：这将确保联接是仅当它满足您的其他条件时才创建，但仍会返回所有
node记录，其中type正确。This could be caused by NULL values in certain rows. A NULL field will evaluate to False when compared with another value.
If you want to get all the values back with your second query, move the product-table-related expressions in the
WHEREclause into your join criteria, so you end up with:This will ensure the join is only made when it meets your other criteria, but will still return all of your
noderecords, where thetypeis correct.