By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE news (
id INT NOT NULL,
title TEXT NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (id)
);
ALTER TABLE news ADD FULLTEXT (content);
Records: 0 Duplicates: 0 Warnings: 1
INSERT INTO news (id, title, content) VALUES
(1, 'best term', 'The quick brown horse'),
(2, 'another therm', 'The quick brown horse jumps over the lazy dog'),
(3, 'another color', 'The quick red horse jumps over the quick dog'),
(4, 'more than one', 'The quick brown horses jumps over the dog'),
(5, 'not what i want to get', 'quick as a mouse was the spider. The horse is brown.');
Records: 5 Duplicates: 0 Warnings: 0
SELECT * FROM news
WHERE (MATCH (content) AGAINST ('+quick +horse*' IN BOOLEAN MODE));
| id | title | content |
|---|---|---|
| 3 | another color | The quick red horse jumps over the quick dog |
| 1 | best term | The quick brown horse |
| 2 | another therm | The quick brown horse jumps over the lazy dog |
| 4 | more than one | The quick brown horses jumps over the dog |
| 5 | not what i want to get | quick as a mouse was the spider. The horse is brown. |
SELECT * FROM news
WHERE (MATCH (content) AGAINST ('"quick horse*" @3' IN BOOLEAN MODE));
| id | title | content |
|---|---|---|
| 3 | another color | The quick red horse jumps over the quick dog |
| 1 | best term | The quick brown horse |
| 2 | another therm | The quick brown horse jumps over the lazy dog |