返回顶部

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

迁移数据库至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
复制代码

 

posted @   九尾cat  阅读(877)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· 上周热点回顾(2.24-3.2)
点击右上角即可分享
微信分享提示

目录导航