MySQL新手求助：LIKE语句筛选含TNY/eth0的name_cache查询错误

Hey there! Let's break down what's off with your current query and fix it up properly.

Issues in Your Original Query #

• You’re using HAVING without a GROUP BY clause. HAVING is meant to filter results after grouping data, but here you just need to filter individual rows—so WHERE is the right tool for the job.
• Your LIKE "%TNY%eth0" condition looks for records where name_cache contains both TNY and eth0 (in that specific order), but you want records that include either TNY or eth0.
• The old comma-separated table syntax works, but using explicit JOIN clauses makes your query way easier to read and maintain.

Corrected Query (Using Explicit JOINs) #

SELECT 
    data_template_data.data_source_path, 
    data_template_data.name_cache 
FROM data_local
JOIN data_template_data ON data_local.id = data_template_data.local_data_id
JOIN host ON data_local.host_id = host.id
WHERE 
    data_template_data.name_cache LIKE "%TNY%" 
    OR data_template_data.name_cache LIKE "%eth0%"
ORDER BY data_local.id;

If You Prefer the Original Table Syntax #

If you want to stick with the comma-separated table list (though JOINs are better practice), here's the fixed version:

SELECT 
    data_template_data.data_source_path, 
    data_template_data.name_cache 
FROM data_local, data_template_data, host
WHERE 
    data_local.id = data_template_data.local_data_id 
    AND data_local.host_id = host.id
    AND (data_template_data.name_cache LIKE "%TNY%" OR data_template_data.name_cache LIKE "%eth0%")
ORDER BY data_local.id;

Key Notes #

• The parentheses around the OR conditions are critical! Without them, the logic could combine incorrectly with your join conditions.
• LIKE "%TNY%" matches any name_cache value that has "TNY" anywhere in it, same for "%eth0%".
• Using WHERE instead of HAVING ensures filtering happens early in the query execution, which is more efficient.

内容的提问来源于stack exchange，提问作者Swe Zin Phyoe