Mysql SELECT LIKE所有字符串的组合

Question

我有一张产品表：

**ID > Product**
01 > Cable Printer Black USB
02 > Power Cable for PC
03 > Headset with microphone USB blue
04 > USB Extension Cable

我需要进行如下搜索：USB+电缆结果显示USB+电缆按任意顺序排列：(USB+电缆或电缆+USB(

结果表明：

01 > Cable Printer Black USB
04 > USB Extension Cable

我尝试过使用REGEXP，但它显示任何包含2个项的寄存器，但我需要结果中的所有项，而不是任何项。

SELECT * FROM tbproducts WHERE product REGEXP 'USB|Cable'

显示：

01 > Cable Printer Black USB
02 > Power Cable for PC
03 > Headset with microphone USB blue
04 > USB Extension Cable

Answer 1

使用[Full TEX INDEX][1]，，你会更开心

但是你可以按照

CREATE TABLE tbproducts (
`ID` INTEGER,
`Product` VARCHAR(32),
FULLTEXT (`Product`)
);
INSERT INTO tbproducts
(`ID`, `Product`)
VALUES
('01', 'Cable Printer Black USB'),
('02', 'Power Cable for PC'),
('03', 'Headset with microphone USB blue'),
('04', 'USB Extension Cable');

SELECT * FROM tbproducts WHERE Product REGEXP '(USB.*Cable|Cable.*USB)'

ID|产品-：|：----------------------1|有线打印机黑色USB4|USB延长线

SELECT * FROM tbproducts t1
WHERE EXISTS (SELECT 1 FROM tbproducts t2 WHERE Product LIKE '%USB%' AND t1.`ID` = t2.`ID`)
AND EXISTS (SELECT 1 FROM tbproducts t2 WHERE Product LIKE '%Cable%'AND t1.`ID` = t2.`ID`)

ID|产品-：|：----------------------1|有线打印机黑色USB4|USB扩展线

更新添加完整的TEXT搜索以确保完整性

SELECT * FROM tbproducts WHERE MATCH (Product) AGAINST ('+USB +Cable' IN BOOLEAN MODE);

ID|产品-：|：----------------------1|有线打印机黑色USB4|USB扩展线

db<gt；小提琴这里