MySQL中如何为CASE生成的actions字段添加LIKE查询条件

Hey there! Let's figure out how to add that LIKE condition for your actions field. I'll walk you through a couple of options, starting with the most efficient one.

Option 1: Filter Using the Original action Field (Highly Recommended) #

Since your actions field is just a transformed version of al.action ('v' → 'view', 'd' → 'download'), you can skip re-computing the CASE statement entirely and filter directly on the original field. This is way more efficient, especially with large datasets, because the database doesn't have to run the CASE logic for every row before filtering.

Here's how your modified SQL would look:

SELECT 
    al.id, 
    al.article_file_id, 
    U.firstname, 
    U.lastname, 
    al.action, 
    al.accessed_time, 
    a.file_size, 
    al.is_backup, 
    a.display_file_name, 
    fb.display_file_name, 
    CASE 
        WHEN al.action = 'v' THEN 'view' 
        WHEN al.action = 'd' THEN 'download' 
    END AS actions 
FROM rfal al 
LEFT JOIN `ru` U ON al.user_id = U.id 
LEFT JOIN raf a ON a.id = al.article_file_id 
LEFT JOIN ... -- Your remaining JOIN clauses
WHERE al.action = 'v'; -- Matches the 'view' value in your actions field

If you ever need a fuzzy match (like looking for values containing 'view'), you could adjust this to WHERE al.action LIKE 'v%'—but since your mapping is one-to-one, exact match is perfect here.

Option 2: Filter Using the Generated actions Field #

If you specifically need to use the actions alias (maybe your mapping gets more complex later), you have two ways to do this:

Suboption 2a: Repeat the CASE Statement in the WHERE Clause #

You can duplicate the CASE logic directly in your WHERE condition. It works, but it's redundant and harder to maintain if you ever update the CASE rules:

SELECT 
    al.id, 
    al.article_file_id, 
    U.firstname, 
    U.lastname, 
    al.action, 
    al.accessed_time, 
    a.file_size, 
    al.is_backup, 
    a.display_file_name, 
    fb.display_file_name, 
    CASE 
        WHEN al.action = 'v' THEN 'view' 
        WHEN al.action = 'd' THEN 'download' 
    END AS actions 
FROM rfal al 
LEFT JOIN `ru` U ON al.user_id = U.id 
LEFT JOIN raf a ON a.id = al.article_file_id 
LEFT JOIN ... -- Your remaining JOIN clauses
WHERE CASE 
        WHEN al.action = 'v' THEN 'view' 
        WHEN al.action = 'd' THEN 'download' 
    END LIKE '%view%';

Suboption 2b: Use the HAVING Clause #

The HAVING clause lets you reference aliases defined in the SELECT statement, so you don't have to repeat the CASE logic. This is cleaner, but keep in mind it runs after the SELECT, so it might be less efficient on large datasets:

SELECT 
    al.id, 
    al.article_file_id, 
    U.firstname, 
    U.lastname, 
    al.action, 
    al.accessed_time, 
    a.file_size, 
    al.is_backup, 
    a.display_file_name, 
    fb.display_file_name, 
    CASE 
        WHEN al.action = 'v' THEN 'view' 
        WHEN al.action = 'd' THEN 'download' 
    END AS actions 
FROM rfal al 
LEFT JOIN `ru` U ON al.user_id = U.id 
LEFT JOIN raf a ON a.id = al.article_file_id 
LEFT JOIN ... -- Your remaining JOIN clauses
HAVING actions LIKE '%view%';

Quick Recommendation #

Stick with Option 1 if you're just targeting the 'view' value—it's the fastest and cleanest approach. Use Option 2 only if your mapping becomes more complex and you need to filter directly on the transformed actions values.

内容的提问来源于stack exchange，提问作者Preethi