关于MYSQL中报错[Err] 1235 - This version of MySQL doesn‘t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery‘

雨点打透心脏的1/2处 2024-03-22 16:10 21阅读 0赞

> \[Err\] 1235 - This version of MySQL doesn’t yet support ‘LIMIT  
> & IN/ALL/ANY/SOME subquery’

Why?

update community_message
    set is_deleted = 1, remark = '手动删除其他社区id数据'
    where is_deleted = 0 
    and id in (
    	SELECT message_id
    	FROM community_user_message_mid
    	WHERE remark = '手动删除其他社区id数据' 
    	ORDER BY message_id desc 
    	LIMIT 0, 100
    	);

执行以上SQL：  
![在这里插入图片描述][4bf5313a713a4f6e8e2e701c2cd141f9.png]

出现此报错，是由于在某些MySQL版本中，LIMIT子句在IN子查询中确实不受支持。

`调整优化1`:  
这里，我将子查询放在一个新的子查询中，以确保外部查询只需要处理子查询的结果。这种方法可以提高SQL查询的性能。但是，实际的性能提升取决于您的数据库系统和数据量。

UPDATE community_message
    SET is_deleted = 1, remark = '手动删除其他社区id数据'
    WHERE is_deleted = 0
    AND id IN (
        SELECT message_id
        FROM (
            SELECT message_id
            FROM community_user_message_mid
            WHERE remark = '手动删除其他社区id数据'
            ORDER BY message_id DESC
            LIMIT 0, 100
        ) AS subquery
    )

`调整优化2`:  
作为替代方案，可以使用JOIN来实现相同的结果。以下是优化后的查询：

UPDATE community_message
    JOIN (
        SELECT message_id
        FROM community_user_message_mid
        WHERE remark = '手动删除其他社区id数据'
        ORDER BY message_id DESC
        LIMIT 100
    ) AS subquery ON community_message.id = subquery.message_id
    SET community_message.is_deleted = 1, community_message.remark = '手动删除其他社区id数据'
    WHERE community_message.is_deleted = 0;

在这个查询中，我们使用 JOIN 子句将 community\_message 表与子查询 subquery 结合起来，子查询从 community\_user\_message\_mid 表中选取满足特定条件的前100条记录。然后，我们根据 JOIN 的结果更新 community\_message 表。这样，您应该可以在不支持 LIMIT 和 IN 子查询的MySQL版本中运行此查询。

[4bf5313a713a4f6e8e2e701c2cd141f9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/d9a01e6689d4445e9197a2905e3bef8b.png

关于MYSQL中报错[Err] 1235 - This version of MySQL doesn‘t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery‘

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Mysql出现问题：ERROR 1235(42000 (ER_NOT_SUPPORTED_YET)): This version of MySQL doesn‘t yet support ‘解决方案

相关关于MYSQL中报错[Err] 1235 - This version of MySQL doesn‘t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery‘

相关 This version of ChromeDriver only supports Chrome version 87

相关 This version of MySQL doesn‘t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery‘

相关 mysql子查询用limit报错（ “This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ ”）

相关 MySQL嵌套查询 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery错误解决方法

相关解决1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

相关 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

相关解决mysql子查询里面无法使用LIMIT This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

相关 mysql报错：This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME 解决方法