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
)
)