创建MariaDB存储过程时遭遇1064语法错误求助

你遇到的问题是因为MariaDB/MySQL的存储过程语法和你写的有好几处不兼容的地方，尤其是变量声明位置、局部变量语法和循环结构的写法，我帮你逐一修正并解释：

核心错误点分析 #

• DECLARE必须放在BEGIN块最顶部：MySQL/MariaDB要求所有局部变量的DECLARE语句必须紧跟在BEGIN之后，不能在执行CREATE TABLE等业务语句之后才声明变量，这是你触发1064错误的直接原因。
• 局部变量不能加@前缀：用DECLARE声明的是存储过程的局部变量，不需要加@（@是会话级全局变量的标识）。
• WHILE循环语法错误：正确的WHILE结构是WHILE 条件 DO ... END WHILE;，而不是用BEGIN开头。
• IF语句语法不完整：IF需要搭配THEN，且必须用END IF;闭合，同时语句末尾要加分号。
• 变量赋值方式错误：给局部变量赋值应该用SELECT ... INTO 变量的格式，而不是SELECT 变量 = ...。
• ROW_COUNT()函数替代@@ROWCOUNT：MariaDB里获取受影响行数用ROW_COUNT()函数，而非SQL Server风格的@@ROWCOUNT。
• UPDATE条件字段错误：临时表tmp_Product_List里的主键字段是PID，不是products_id，需要修正WHERE条件。

修正后的完整存储过程脚本 #

DELIMITER $$ 
USE dollar$$ 
DROP PROCEDURE IF EXISTS sp_get_products_google_feed$$ 
CREATE DEFINER=root@localhost PROCEDURE sp_get_products_google_feed() 
BEGIN 
    -- 所有DECLARE必须放在BEGIN之后的最顶部
    DECLARE LoopCounter INT DEFAULT 0; 
    DECLARE MaxId INT DEFAULT 0; 
    DECLARE GoogleCategoryid INT DEFAULT 0;
    DECLARE currentCatId INT DEFAULT 0;

    DROP TABLE IF EXISTS tmp_Product_List; 
    CREATE TEMPORARY TABLE tmp_Product_List( 
        SELECT DISTINCT 
            p.products_id AS PID, 
            p.products_model AS ID, 
            pd.products_name AS Title, 
            pd.products_description AS Description, 
            '' AS Google_product_category, 
            '' AS product_type, 
            p.products_model AS link, 
            p.products_image AS Image_link, 
            'new' AS Condition1, 
            'in stock' AS Availability, 
            p.products_price AS Price, 
            '' AS Sale_Price, 
            '' AS Sale_price_effective_date, 
            p.products_upc AS GTin, 
            p.manufacturers_id, 
            '' AS MPN, 
            '' AS Item_group_id, 
            '' AS Gender, 
            '' AS Age_group, 
            '' AS Color, 
            '' AS Size, 
            'Free' AS Shipping, 
            '' AS Shipping_Weight 
        FROM zc_products_to_categories pc, zc_products p, zc_products_description pd 
        WHERE pc.categories_id IN (
            SELECT DISTINCT mg.sub_category_id AS id FROM tbl_map_google_category_master mg WHERE mg.category_id = 1 
            UNION 
            SELECT DISTINCT mg.sub_sub_category_id AS id FROM tbl_map_google_category_master mg WHERE mg.category_id = 1 
            ORDER BY id
        ) 
        AND p.products_id = pc.products_id 
        AND p.products_id = pd.products_id 
        AND p.products_status = 1 
        ORDER BY PID
    ); 

    -- 用SELECT ... INTO给局部变量赋值
    SELECT MIN(PID), MAX(PID) INTO LoopCounter, MaxId FROM tmp_Product_List; 

    -- 正确的WHILE循环结构
    WHILE (LoopCounter IS NOT NULL AND LoopCounter <= MaxId) DO 
        -- 先获取当前产品的最大分类ID，避免重复子查询
        SELECT MAX(categories_id) INTO currentCatId FROM zc_products_to_categories WHERE products_id = LoopCounter;
        
        SELECT google_category_id INTO GoogleCategoryid 
        FROM tbl_map_google_category_master 
        WHERE category_id = currentCatId 
           OR sub_category_id = currentCatId 
           OR sub_sub_category_id = currentCatId 
        LIMIT 1; 

        -- 修正WHERE条件为PID
        UPDATE tmp_Product_List 
        SET Google_product_category = GoogleCategoryid 
        WHERE PID = LoopCounter; 

        SET LoopCounter = LoopCounter + 1; 

        -- 正确的IF判断结构，用ROW_COUNT()获取受影响行数
        IF(ROW_COUNT() = 0 ) THEN 
            SET LoopCounter = LoopCounter + 1; 
            CONTINUE; 
        END IF; 
    END WHILE; 

    SELECT * FROM tmp_Product_List; 
END$$ 
DELIMITER ;

额外优化说明 #

我还额外优化了子查询，把重复的SELECT MAX(categories_id)提取成单独的变量赋值，减少重复查询的开销，让代码更清晰。

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