mysql - 如何检索存储在同一数据库列中的多个数据？

Question

我的数据库就像

Name  |  IC   |   Item
--------------------------
lee   |  xxx  |   pear,bear
--------------------------
ron   |  xxx   |  apple,dog

我应该如何检索“Item”列中包含的 4 个值，然后将它们分开？

Answer 1

Item 中只有两个以逗号分隔的项目吗？或者它可能会有所不同？

LE:你可以使用这个SQL split comma separated rowLLE:只是玩了一下，这就是我所做的:

create table myTable(name varchar(7), ic varchar(7), item varchar(200));

insert into myTable(name,ic,item) values ('lee','xxx','pear,bear');
insert into myTable(name,ic,item) values ('ron','xxx','apple,dog');
insert into myTable(name,ic,item) values ('a','xxx','gamma');
insert into myTable(name,ic,item) values ('b','xxx','a,b,c,d');

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.item, ',', n.n), ',', -1) value
FROM myTable 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
    ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.item) - LENGTH(REPLACE(t.item, ',', '')))
ORDER BY value;