Mysql查询如何在进行左连接时避免行为空/空字段

Question

我在 mysql 中尝试过一个查询，效果很好:

SELECT medias.id,medias.hash,medias.customid,medias.timestamp,medias.uploaded,
       medias.data_updated, mediadetails.media_id, mediadetails.city,
       mediadetails.state_province, mediadetails.postal_code 
  FROM medias 
  LEFT JOIN mediadetails ON medias.id=mediadetails.media_id  
 ORDER BY medias.data_updated DESC 
 LIMIT 5;

现在我正在尝试获取那些没有空 city/state_provice/postal_code 的行。

尝试过这个:

SELECT medias.id, medias.hash,medias.customid, medias.timestamp,medias.uploaded,
       medias.data_updated, mediadetails.media_id, mediadetails.city,
       mediadetails.state_province, mediadetails.postal_code 
  FROM medias 
  LEFT JOIN mediadetails on  medias.id=mediadetails.media_id  
 ORDER BY medias.data_updated DESC
 WHERE medias.city<>'' 
   AND medias.state_province<>'' 
   AND medias.postal_code<>'' 
 LIMIT 5;

但是出现错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where medias.city<>'' and medias.state_province<>'' and medias.postal_code<>'' l' at line 1

我可以获得有关如何操作的帮助吗？

Answer 1

空通常意味着null。
在这种情况下，不要将列值与 '' 进行比较，而是使用 IS NOT NULL。
无论如何，您可以使用 coalesce() :

SELECT 
  medias.id, medias.hash,medias.customid, medias.timestamp, 
  medias.uploaded, medias.data_updated, 
  mediadetails.media_id, mediadetails.city,
  mediadetails.state_province, mediadetails.postal_code 
FROM medias INNER JOIN mediadetails 
ON  medias.id=mediadetails.media_id  
WHERE 
  COALESCE(mediadetails.city, '') <>'' 
  AND 
  COALESCE(mediadetails.state_province, '') <>'' 
  AND
  COALESCE(mediadetails.postal_code, '') <>'' 
ORDER BY mediadetails.data_updated DESC
LIMIT 5;

尽管这在大多数情况下都有效:

WHERE 
  mediadetails.city IS NOT NULL 
  AND 
  mediadetails.state_province IS NOT NULL 
  AND
  mediadetails.postal_code IS NOT NULL