mysql 更新 in (递归查询结果集) :data too long for column ‘xxx‘

最新推荐文章于 2024-05-17 14:37:10 发布

CXY_BOY

最新推荐文章于 2024-05-17 14:37:10 发布

阅读量224

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/CXY_BOY/article/details/130577146

版权

1.原SQl

UPDATE category 
SET wl_required = 1 
WHERE
	id IN (
	SELECT
		* 
	FROM
		(
		SELECT
			id 
		FROM
			(
			SELECT
				@ids AS _ids,
				( SELECT @ids := GROUP_CONCAT( id ) FROM category WHERE FIND_IN_SET( pid, @ids ) ) AS cids,
				@l := @l + 1 AS LEVEL 
			FROM
				category,
				( SELECT @ids := 41, @l := 0 ) b 
			WHERE
				@ids IS NOT NULL 
			) ID,
			category DATA 
		WHERE
			FIND_IN_SET( DATA.id, ID._ids ) 
			AND account_id = 0 
		ORDER BY
			id ASC 
		) c 
	)

报错:
1406 - Data too long for column ‘cids’ at row 1025
2.优化后:用FIND_IN_SET函数

UPDATE category 
SET wl_required = 1 
WHERE
	FIND_IN_SET(
		id,(
		SELECT
			GROUP_CONCAT( id ) 
		FROM
			(
			SELECT
				id
			FROM
				(
				SELECT
					@ids AS _ids,
					( SELECT @ids := GROUP_CONCAT( id ) FROM category WHERE FIND_IN_SET( pid, @ids ) ) AS cids,
					@l := @l + 1 AS LEVEL 
				FROM
					category,
					( SELECT @ids := 6, @l := 0 ) b 
				WHERE
					@ids IS NOT NULL 
				) ID,
				category DATA 
			WHERE
				FIND_IN_SET( DATA.id, ID._ids ) 
			) c 
		) 
	)