MySQL 5.7.21语法错误求助：#1064错误排查（涉及IFNULL）

Hey there, let's tackle that frustrating #1064 syntax error you're hitting. From the snippet you shared, there are a couple of clear issues causing the problem:

1. Missing Comma in SELECT Field List #

The most immediate syntax break is right here in your SELECT clause:

SELECT n.nid, n.title,n.created ucp.model

You're missing a comma between n.created and ucp.model. MySQL can't parse this correctly—it thinks you're trying to assign an alias ucp.model to n.created instead of selecting ucp.model as a separate field. Fix that by adding the comma:

SELECT n.nid, n.title, n.created, ucp.model

2. Incomplete Subquery & CASE Statement #

Looking at the truncated part of your query:

(ucp.list_price - IFNULL((SELECT (CASE WHEN role_price.price='' THEN ucp.sell_price ELSE role_pri...

You need to ensure:

• The CASE statement has a matching END to close it
• The subquery inside IFNULL has a closing parenthesis )
• You finish the arithmetic expression and any remaining parts of the SELECT clause

Example Corrected Snippet #

Here's a cleaned-up version of your query with the critical fixes (assuming the rest of your FROM/JOIN logic is correct):

SELECT 
    n.nid, 
    n.title, 
    n.created, 
    ucp.model, 
    ucp.list_price,
    ucp.sell_price, 
    (ucp.list_price - IFNULL(
        (SELECT CASE WHEN role_price.price = '' THEN ucp.sell_price ELSE role_price.price END FROM role_price WHERE /* add your matching condition here */),
        0 -- or a default value if the subquery returns NULL
    )) AS price_difference
-- Don't forget your FROM/JOIN clauses here!
FROM your_table n
JOIN ucp_table ucp ON /* your join condition */

Quick Additional Checks #

• Make sure all tables you're referencing (like n, ucp, role_price) are included in your FROM or JOIN clauses
• Verify that any string comparisons (like role_price.price='') make sense—if price is a numeric type, comparing to an empty string might cause unexpected behavior
• Double-check that all parentheses are properly balanced (a common culprit for #1064 errors)

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