Mysql Fulltext deny similar column resul

Question

I have a set Fulltext to some columns like:

car normal | car super | car extra

And then values like

car normal | car super | car extra
normal car  super car    extra car

how i get only 1 column result, becouse when i run the query, bring all becouse all columns have car string?

For example, i search with the string : super car i expected the result super caronly, but result comes with super car, normal car, extra car

Could you provide a dbfidde example, because i search usually for rows ans in rare cases for column_names, but iun your cases in am not clear what you are searching — nbk, Nov 28 '19 at 23:09
@nbk tks for atention i edit tell me if now is more clear... — MagicHat, Nov 29 '19 at 05:38

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

If you give a full example of data, we could better understand,what you need. Also you didn't show us your sanitized query, to have a starting point

My take on your question, You have different columns and in that columns are hidden the words car normal , car super , car extra, and you only want to find the exact wording

CREATE TABLE tutorial (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
title VARCHAR(200), 
description1 TEXT,
description2 TEXT, 
description3 TEXT, 
FULLTEXT(title,description1,description2,description3)
) ENGINE=InnoDB;

✓

INSERT INTO tutorial (title,description1,description2,description3) VALUES
('ca1','blah blaha car normal blah blah', 'Lorem ipsum dolor sit amet','Lorem ipsum dolor sit amet'),
('car2','Lorem ipsum dolor sit amet','Blah blah car super BLAh BLAH','Lorem ipsum dolor sit amet'),
('car3','Lorem ipsum dolor sit amet','Lorem ipsum dolor sit amet','Blah Bla car extra Blah blah'),
('car4','Blah Blah normal car Blah blah','Lorem ipsum dolor sit amet','Lorem ipsum dolor sit amet')
;

✓

SELECT id, title
, LOCATE('car normal',description1)
, LOCATE('car normal',description2)
, LOCATE('car normal',description3)
FROM tutorial 
WHERE MATCH(title,description1,description2,description3) AGAINST ('+("car normal")' IN NATURAL LANGUAGE MODE) ;

id | title | LOCATE('car normal',description1) | LOCATE('car normal',description2) | LOCATE('car normal',description3)
-: | :---- | --------------------------------: | --------------------------------: | --------------------------------:
 1 | ca1   |                                12 |                                 0 |                                 0

db<>fiddle here

Mysql Fulltext deny similar column resul

1 Answers1