你可以使用 extss 但 youtrr 查询实际上返回零行
CREATE TABLE `table` (
`ID` INTEGER,
`detail_id` INTEGER,
`detail_value` VARCHAR(20),
`book_id` INTEGER
,
FULLTEXT idx (detail_value)
);
INSERT INTO `table`
(`ID`, `detail_id`, `detail_value`, `book_id`)
VALUES
('1', '11', 'Warszawa', '103'),
('2', '14', 'Grażyńskiego', '123'),
('3', '11', 'st.Warszawa m', '123'),
('4', '14', 'Michała Grażyńskiego', '222'),
('5', '11', 'Warszawa', '222'),
('6', '14', 'Katowicka', '103'),
('7', '9', '923', '23'),
('8', '12', '432424', '23'),
('9', '14', 'Przykładowa', '23'),
('10', '11', 'Warszawa', '23');
SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))
AND EXISTS (SELECT 1 FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('*rażyń*' IN BOOLEAN MODE))
身份证 | detail_id | 细节值 | book_id -: | --------: | :----------- | ------:
SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))身份证 | detail_id | 细节值 | book_id -: | --------: | :------------ | ------: 1 | 11 | 华沙 | 103 3 | 11 | 圣华沙 123 5 | 11 | 华沙 | 222 10 | 11 | 华沙 | 23
SELECT 1 as test FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('*rażyń*' IN BOOLEAN MODE)| 测试 | | ---: |
db<>在这里摆弄
更改第二个查询
SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))
AND EXISTS (SELECT 1 FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE))
身份证 | detail_id | 细节值 | book_id -: | --------: | :------------ | ------: 1 | 11 | 华沙 | 103 3 | 11 | 圣华沙 123 10 | 11 | 华沙 | 23
SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))身份证 | detail_id | 细节值 | book_id -: | --------: | :------------ | ------: 1 | 11 | 华沙 | 103 3 | 11 | 圣华沙 123 5 | 11 | 华沙 | 222 10 | 11 | 华沙 | 23
SELECT 1 as test FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE)| 测试 | | ---: | | 1 | | 1 |
db<>在这里摆弄
好的，因为进一步的约束都必须具有相同的 bookng _id
SELECT * FROM `table` t1
WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))
AND EXISTS (SELECT 1
FROM `table`
WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE)
AND book_id = t1.book_id)
身份证 | detail_id | 细节值 | book_id -: | --------: | :------------ | ------: 3 | 11 | 圣华沙 123 5 | 11 | 华沙 | 222
db<>在这里摆弄
更改
rażyń，grażyń现在它可以正常工作，但只有当您使用这些查询的一部分时。所以这个查询：SELECT * FROM table WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*grażyń*' IN BOOLEAN MODE)) AND (detail_id = 14 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE));不起作用，但是当您使用 ONE MATCH...AGAINST 时，它可以正常工作。加起来是行不通的。看看graintz的例子当然它可以工作dbfiddle.uk/…
好的，我现在明白了。但是有一个问题 - 我只需要得到这些结果，其中 book_id 是相同的。所以 book_number=222（和 123）包含短语
warszawa作为城市名称（detail_id=11）和短语grażyń作为街道名称的一部分。添加另一个 where 条件查看更改的答案
看起来不错，但是更多参数呢？例如
detail_id= 9 作为门牌号和detail_id= 12 作为邮政编码？我总共有 38 个参数，所以我需要通用解决方案。