MySQL存储过程自定义变量增减报错#1064排查请求

我帮你仔细检查了存储过程代码，触发#1064错误的核心原因是整个UNION查询语句没有以分号结尾，MySQL解析器无法识别这个查询的结束位置，进而误判END的语法。除此之外还有几个小的语法/逻辑问题，我一起整理出来：

错误点及修正方案 #

1. 缺少查询结束分号：两个SELECT通过UNION合并成一个完整查询，必须在整个语句（包括ORDER BY和LIMIT）结尾添加;，这是解决END附近语法错误的关键。
2. date_to参数笔误：在WHERE条件的date_to判断中，你写了ELSE date_from，这会导致date_to参数完全失效，应该改成ELSE date_to。
3. INT类型与空字符串比较：account_id是INT UNSIGNED类型，不能用account_id=''来匹配“所有账户”——INT和空字符串的比较会触发隐式转换，建议改成account_id=0（UNSIGNED类型不能为负，用0表示匹配所有账户）。
4. GROUP BY不规范：第一个SELECT中很多非聚合字段不在GROUP BY里，如果你的MySQL开启了ONLY_FULL_GROUP_BY模式，后续会报错，建议把所有SELECT中的非聚合字段都加入GROUP BY，或者确认数据库允许这种非规范写法。

修正后的完整存储过程代码 #

DELIMITER $$ 
CREATE DEFINER=`local`@`localhost` PROCEDURE `sp_select_customer_account_lesuire`(
    IN `date_from` VARCHAR(10), 
    IN `date_to` VARCHAR(10), 
    IN `account_id` INT(11) UNSIGNED, 
    IN `company_name` INT(11) UNSIGNED, 
    IN `start_row` INT(11) UNSIGNED, 
    IN `end_row` INT(11) UNSIGNED
) NO SQL 
BEGIN 
    SET @runningBalance := -199999; /*need a function for dynamic value*/ 

    SELECT 
        c.`customer_account_id` as 'id', 
        c.`received` as 'date', 
        c.`description` as 'desc', 
        '' as 'sales_type', 
        NULL as 'sales_us', 
        NULL as 'sales_rate', 
        NULL as 'sales_yen', 
        CONCAT('$ ',FORMAT(c.`usd_amount`,0)) as 'us', 
        c.`rate` as 'rate', 
        CONCAT('¥ ',FORMAT(c.`yen_amount`,0)) as 'yen', 
        (@runningBalance := @runningBalance-c.`yen_amount`) as 'balance' 
    FROM `customer_account` c 
    LEFT JOIN `customer_detail` cd ON cd.`customer_detail_id` = c.`customer_detail_id` 
    WHERE ( 
        (account_id=0 OR c.customer_account_id=account_id) 
        AND (cd.customer_detail_id=company_name) 
        AND (c.received BETWEEN 
            (case WHEN (date_from="" OR date_from="0000-00-00") THEN '1990-01-01' ELSE date_from END) 
            AND (case WHEN (date_to="" OR date_to="0000-00-00") THEN CURRENT_DATE ELSE date_to END)
        ) 
    ) 
    GROUP BY c.customer_account_id

    UNION

    SELECT 
        cs.`invoice_id`, 
        cs.`sales_date`, 
        CONCAT('INV#',cs.`invoice_no`), 
        (case WHEN cs.`sales_type`='1' THEN 'FOB' WHEN cs.`sales_type`='2' THEN 'C&F' WHEN cs.`sales_type`='3' THEN 'CIF' WHEN cs.`sales_type`='4' THEN 'Dealer' WHEN cs.`sales_type`='5' THEN 'Auction'END), 
        case WHEN cs.`currency`='usd' THEN CONCAT('$ ',FORMAT((cs.`grand_total`/cs.`exchange_rate`),0)) ELSE NULL END, 
        case WHEN cs.`exchange_rate` > 1 THEN cs.`exchange_rate` ELSE NULL END, 
        CONCAT('¥ ',FORMAT(cs.`grand_total`,0)), 
        NULL, 
        NULL, 
        NULL, 
        (@runningBalance := @runningBalance+cs.grand_total) 
    FROM `car_sales` cs 
    LEFT JOIN `customer_detail` cd ON cd.`customer_detail_id` = cs.`buyer_name` 
    WHERE ( 
        (cd.customer_detail_id=company_name) 
        AND (cs.sales_date BETWEEN 
            (case WHEN (date_from="" OR date_from="0000-00-00") THEN '1990-01-01' ELSE date_from END) 
            AND (case WHEN (date_to="" OR date_to="0000-00-00") THEN CURRENT_DATE ELSE date_to END)
        ) 
    )

    ORDER BY 2 ASC LIMIT start_row, end_row; -- 整个UNION查询结尾添加分号
END$$ 
DELIMITER ; 

另外，关于你后续用函数替代变量静态赋值的计划，等存储过程创建成功后，直接把SET @runningBalance := -199999;改成调用自定义函数即可，比如SET @runningBalance := your_custom_function();，只要函数返回数值类型就可以正常工作。

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