这不是为了DB
但有可能:
CREATE TABLE tab(id INT, col VARCHAR(100));
INSERT INTO tab(id, col)
VALUES (1, 'option[A]sum[A]g3et[B]'), (2, '[Cosi]sum[A]g3et[ZZZZ]');
SELECT DISTINCT *
FROM (
SELECT id, RIGHT(val, LENGTH(val) - LOCATE('[', val)) AS val
FROM
(
SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, ']', n.n), ']', -1) AS val
FROM tab t
CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 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
) n
WHERE n.n <= 1 + (LENGTH(t.col) - LENGTH(REPLACE(t.col, ']', '')))
) sub
) s
WHERE val <> ''
ORDER BY ID;
SqlFiddleDemo http://sqlfiddle.com/#!9/277447/1/0
Note:
根据col
您可能需要生成更多数字的最大长度CROSS JOIN
部分。目前最多为100。
Output:
怎么运行的:
- 生成号码表
CROSS JOIN
- 根据分割字符串
]
作为分隔符
-
RIGHT(val, LENGTH(val) - LOCATE('[', val))
删除部分直至[
- 过滤掉空记录
- 仅获取
DISTINCT
values
最里面的查询:
╔════╦══════════╗
║ id ║ val ║
╠════╬══════════╣
║ 1 ║ option[A ║
║ 1 ║ sum[A ║
║ 1 ║ g3et[B ║
║ 1 ║ ║
╚════╩══════════╝
第二个子查询:
╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║ 1 ║ A ║
║ 1 ║ A ║
║ 1 ║ B ║
║ 1 ║ ║
╚════╩═════╝
最外面的查询:
╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║ 1 ║ A ║
║ 1 ║ B ║
╚════╩═════╝
我需要每行查询的结果..不合并
所以添加简单的:
WHERE n.n <= 1 + (LENGTH(t.col) - LENGTH(REPLACE(t.col, ']', '')))
AND t.id = ?
EDIT 2:
see http://sqlfiddle.com/#!9/8ee95/1 http://sqlfiddle.com/#!9/8ee95/1您的查询部分适用于我的数据。我还将类型更改为长文本。
您想在 MySQL 中解析 JSON。正如我之前所说,在应用层解析并获取值。这个答案仅用于演示/玩具目的,并且性能非常低。
如果你仍然坚持使用 SQL 解决方案:
SELECT id, val,s.n
FROM (
SELECT id, RIGHT(val, LENGTH(val) - LOCATE('[', val)) AS val,n
FROM
(
SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, ']', n.n), ']', -1) AS val, n.n
FROM (SELECT id, REPLACE(col, '[]','') as col FROM tab) t
CROSS JOIN
(
SELECT e.N * 10000 + d.N * 1000 + c.N * 100 + a.N + b.N * 10 + 1 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
,(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) c
,(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) d
,(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) e
) n
WHERE n.n <= 1 + (LENGTH(t.col) - LENGTH(REPLACE(t.col, ']', '')))
) sub
) s
WHERE val <> ''
GROUP BY id, val
HAVING n <> MAX(n)
ORDER BY id,n;
SqlFiddleDemo http://sqlfiddle.com/#!9/8ee95/24/0
Output:
╔═════╦═════════════╦════╗
║ id ║ val ║ n ║
╠═════╬═════════════╬════╣
║ 1 ║ CE31285LV4 ║ 1 ║
║ 1 ║ D32E ║ 3 ║
║ 1 ║ GTX750 ║ 5 ║
║ 1 ║ M256S ║ 7 ║
║ 1 ║ H2X1T ║ 9 ║
║ 1 ║ FMLANE4U4 ║ 11 ║
╚═════╩═════════════╩════╝
EDIT 3:
那里到底做了什么?为什么你需要n
CROSS JOIN
整个子查询只是统计表。就这些。如果MySQL
具有生成数字序列的功能(例如generate_series
或预先填充的数字表,不需要CROSS JOIN
.
需要号码表SUBSTRING_INDEX http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring-index:
SUBSTRING_INDEX(str,delim,count)
返回字符串 str 中出现 count 次分隔符 delim 之前的子字符串。如果 count 为正数,则返回最终分隔符左侧的所有内容(从左侧算起)。如果 count 为负数,则返回最终分隔符右侧(从右侧计数)的所有内容。 SUBSTRING_INDEX() 在搜索 delim 时执行区分大小写的匹配。