首页 > 数据库 >ERROR 658 (HY000): Proxy ERROR: Join internal error: Table 'mysql.proc' doesn'texist

ERROR 658 (HY000): Proxy ERROR: Join internal error: Table 'mysql.proc' doesn'texist

时间:2023-04-07 20:47:04浏览次数:47  
标签:shop locate categorys goods Join doesn ERROR id SELECT

ERROR 658 (HY000): Proxy ERROR: Join internal error: Table 'mysql.proc' doesn'texist

迁移数据库至TDSQL ,版本5.0到8.0,执行sql报错

现象

 

 查了资料发现mysql8.0的mysql的proc表确实淘汰 不用了

解决方法

使用其他函数替换,

JSON_CONTAINS替换为 locate, JSON_Array  >>    OR locate
SELECT
        COUNT(*)
FROM
        (
SELECT
        d.ec_goods_id AS parentGoodsId,
        d.flag,
        COUNT(*) statistics 
FROM
        ( 
        SELECT ec_goods_id,'a'  AS flag ,create_time from goods_electronic
        WHERE
                        shop_id = 1144
                        AND title LIKE concat( '%', '炭烤', '%' ) 
                        AND shelf_status = 1
                        AND (
                                JSON_CONTAINS(
                                        shop_categorys,
                                JSON_Array(1635128788))) 
                        AND parent_id IS NULL 
                        UNION
                        SELECT parent_id,'b' AS flag,create_time FROM goods_electronic 
                WHERE
                        shop_id = 1144
                        AND title LIKE concat( '%', '炭烤', '%' ) 
                        AND shelf_status = 1
                        AND (
                                JSON_CONTAINS(
                                        shop_categorys,
                                JSON_Array(1635128788))) 
                        AND parent_id IS NOT NULL 
        ) d 
GROUP BY
        d.ec_goods_id 
ORDER BY
        create_time
        )g

替换SQL

SELECT
        COUNT(*) 
FROM
        (
        SELECT
                d.ec_goods_id AS parentGoodsId,
                d.flag,
                COUNT(*) statistics 
        FROM
                (
                SELECT
                        ec_goods_id,
                        'a' AS flag,
                        create_time,
                        shop_categorys 
                FROM
                        goods_electronic 
                WHERE
                        shop_id = 116592 
                        AND title LIKE concat( '%', '上传', '%' ) 
                        AND shelf_status = 1 
                        AND (
                                locate( 16989, shop_categorys ) 
                                OR locate( 17002, shop_categorys ) 
                        OR locate( 17087, shop_categorys )) 
                        AND parent_id IS NULL UNION
                SELECT
                        parent_id,
                        'b' AS flag,
                        create_time,
                        shop_categorys 
                FROM
                        goods_electronic 
                WHERE
                        shop_id = 1144 
                        AND title LIKE concat( '%', '炭烤', '%' ) 
                        AND shelf_status = 1 
                        AND (
                                locate( 16989, shop_categorys ) 
                                OR locate( 17002, shop_categorys ) 
                        OR locate( 17087, shop_categorys )) 
                        AND parent_id IS NOT NULL 
                ) d 
        GROUP BY
                d.ec_goods_id 
        ORDER BY
        create_time 
        )g

 

标签:shop,locate,categorys,goods,Join,doesn,ERROR,id,SELECT
From: https://www.cnblogs.com/weiweirui/p/17296360.html

相关文章