您可以使用以下语句(不太美观),假设您的表名为
example
:
UPDATE
example e1
SET
e1.type_a = (
SELECT
CONCAT('*', GROUP_CONCAT(DISTINCT n1.value ORDER BY n1.value SEPARATOR '*'), '*') as type_a
FROM (
SELECT
id,
CASE
WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1) = '' THEN NULL
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)
END value
FROM example e CROSS JOIN (
SELECT
a.N + b.N * 10 + 1 AS n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE
n.n <= 1 + (LENGTH(e.type_a) - LENGTH(REPLACE(e.type_a, '*', '')))
UNION
SELECT
id,
CASE
WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_b), '*', n.n), '*', -1) = '' THEN NULL
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_b), '*', n.n), '*', -1)
END value
FROM example e CROSS JOIN (
SELECT
a.N + b.N * 10 + 1 AS n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE
n.n <= 1 + (LENGTH(e.type_b) - LENGTH(REPLACE(e.type_b, '*', '')))
) n1
WHERE
n1.id = e1.id
GROUP BY
id
),
e1.type_b = ''
;
SELECT语句的演示
说明
基本上,我改编了方法,使用peterm的方法来完成拆分。我必须先通过TRIM删除外部*
。
为了允许空字符串作为列值,我添加了CASE结构来消除这些值。如果你的列有NULL值,你可以用CASE替换它们。
SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)
并且
SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)
这个结构的UNION(不包括ALL关键字)将给我们一个独特颜色值列表,用id分组和GROUP_CONCAT可以得到以*分隔的值列表。最后,我们添加一个前导和尾随
*
以满足您的要求。
对于更新,您需要修改选择语句,以便它只返回一个带有where子句的列中的一行。
请注意:如peterm所述,这将允许在值列表中最多有100个值。我不认为您会需要更多,但如果需要,那么您必须根据自己的需求调整数字生成。