I have created the following table fruits -

CREATE TABLE `fruits` (
  `id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Then I entered the following values in table fruits -

SELECT * FROM fruits;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | apple, orange |
|  2 | apple, mango  |
|  3 | mango, kiwi   |
|  4 | mango, guava  |
|  5 | apple, banana |
+----+---------------+

Now I run the following three SQL queries -

Query 1:

SELECT id, name FROM fruits
    -> WHERE MATCH(name) AGAINST
    -> ('+apple' IN BOOLEAN MODE);
+----+---------------+
| id | name          |
+----+---------------+
|  1 | apple, orange |
|  2 | apple, mango  |
|  5 | apple, banana |
+----+---------------+

Query 2:

SELECT id, name FROM fruits
    -> WHERE MATCH(name) AGAINST
    -> ('+apple -orange' IN BOOLEAN MODE);
+----+---------------+
| id | name          |
+----+---------------+
|  2 | apple, mango  |
|  5 | apple, banana |
+----+---------------+

Query 3:

SELECT id, name FROM fruits
    -> WHERE MATCH(name) AGAINST
    -> ('+apple ~orange' IN BOOLEAN MODE);
+----+---------------+
| id | name          |
+----+---------------+
|  1 | apple, orange |
|  2 | apple, mango  |
|  5 | apple, banana |
+----+---------------+

As per MySQL developer website following is the function of ~ (tilde) operator in 'Boolean Full-Text Searches'

https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

• '+apple ~macintosh'

Find rows that contain the word “apple”, but if the row also contains the word “macintosh”, rate it lower than if row does not. This is “softer” than a search for '+apple -macintosh', for which the presence of “macintosh” causes the row not to be returned at all.

I have tried the ~ (tilde) operator in 'Query 3' but the output is certainly not what is expected. Here, the expected behavior is row with id = 1 coming at last.

P.S. I am using MySQL version - 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))