By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
create table products (
id int auto_increment,
upc varchar(50),
upc_variation text,
primary key (id),
index (upc),
fulltext index (upc_variation)
);
insert into products (upc, upc_variation) values
('01234', '01234,12345,23456'),
('56789', '45678,34567'),
('056789', '045678,034567');
select p.*
from products p
where match (upc_variation) against ('12345 34567');
| id | upc | upc_variation |
|---|---|---|
| 1 | 01234 | 01234,12345,23456 |
| 2 | 56789 | 45678,34567 |
select p.*
from products p
where json_overlaps( -- requires 8.0.17+
'["12345","34567"]',
concat('["', replace(upc_variation, ',', '","'), '"]')
);
| id | upc | upc_variation |
|---|---|---|
| 1 | 01234 | 01234,12345,23456 |
| 2 | 56789 | 45678,34567 |
select distinct p.*
from products p
join json_table( -- requires 8.0.4+
concat('["', replace(p.upc_variation, ',', '","'), '"]'),
'$[*]' columns (upcv text path '$')
) v
where v.upcv in ('12345', '34567');
| id | upc | upc_variation |
|---|---|---|
| 1 | 01234 | 01234,12345,23456 |
| 2 | 56789 | 45678,34567 |
create table products_upc_variations (
product_id int,
upc_variation varchar(50),
primary key (product_id, upc_variation),
index (upc_variation, product_id)
);
insert into products_upc_variations (product_id, upc_variation) values
(1, '01234'),
(1, '12345'),
(1, '23456'),
(2, '45678'),
(2, '34567'),
(3, '045678'),
(3, '034567');
select p.*
from products p
join products_upc_variations v on v.product_id = p.id
where v.upc_variation in ('12345', '34567');
| id | upc | upc_variation |
|---|---|---|
| 1 | 01234 | 01234,12345,23456 |
| 2 | 56789 | 45678,34567 |
select p.*
from products p
where p.upc_variation rlike '(^|,)(12345|34567)(,|$)'
| id | upc | upc_variation |
|---|---|---|
| 1 | 01234 | 01234,12345,23456 |
| 2 | 56789 | 45678,34567 |