Mysql全文返回NULL

Question

我的mysql代码。

CREATE TABLE `videos` (
  `id` int(50) NOT NULL,
  `user_id` int(250) NOT NULL,
  `title` varchar(250) NOT NULL,
  `discription` text NOT NULL,
  `video_path` varchar(250) NOT NULL,
  `tumbnail_path` varchar(250) NOT NULL,
  `paid` int(250) NOT NULL,
  `date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `videos` (`id`, `user_id`, `title`, `discription`, `video_path`, `tumbnail_path`, `paid`, `date`) VALUES
(1, 4, 'This is a new video', '<p>This is a new video from eduladder&nbsp;in at this video we are discribing how stuffs works</p>\r\n', 'uploadvid/xIdivzexFZXzr6bng2E9mU3PNvMVq0Iz.mp4', 'uploadthump/1AT1EsgJ--6iVLxEwEFRkWa9ADqqD1BG.jpg', 0, '2018-12-10'),
(2, 4, 'New Video for testig', '<p>This is a new video for testing purpose&nbsp;only</p>\r\n', 'uploadvid/_rsIHMc2giVoWV6aRixCoEUk0gKcDhDI.mp4', 'uploadthump/zA_t-2DMusUDvg9xVPwmRAn5-59He76-.jpg', 0, '2018-12-12'),
(3, 4, 'Some New Videos', '<p>This is a record of some new videos</p>\r\n', 'uploadvid/jPzlU3xSJaZVm7EzZu_JfaXq8kAK_1Vc.mp4', 'uploadthump/M_SZodSk20ba2FsXw3X1WVq7a48S_cj3.jpg', 0, '2018-12-13'),
(4, 4, 'Old video', '<p>This is an old video</p>\r\n', 'uploadvid/yaYiDBru2c7fCcosPmrj94JhZ5waxbu8.mp4', 'uploadthump/FhRXXen99DEa0d-8w5m2FDcvFyxlZgx4.png', 0, '2018-12-13'),
(5, 4, 'Almost new video and edited', '<p>This is about almost new video and editted&nbsp;version</p>\r\n', 'uploadvid/YOVPqiFO5xUnCtFAdYzgiY2wzsCnSQ11.mp4', 'uploadthump/MO1faxOKDNESee0gG5SQZYeantzlrPYM.png', 0, '2018-12-13');
ALTER TABLE `videos` ADD FULLTEXT(`title`,`discription`);

我正在执行的查询就在这里。

SELECT * , 
MATCH (title, discription) AGAINST ('New') AS score
FROM videos
WHERE MATCH (title, discription) AGAINST ('New')
ORDER BY score
DESC LIMIT 20

这是一个 mysql fiddle https://www.db-fiddle.com/f/jUs9EABZjuBL956WtnTbqx/3

但是它没有给我任何信息，我哪里出错了，我该如何解决这个问题？

Answer 1

由于您在评论中要求它与 MySql 5.5 一起使用:

Plz see the udated code db-fiddle.com/f/jUs9EABZjuBL956WtnTbqx/3 innodb wont work it is myql version <5.6

那么这是两种不同的情况。对于 MySql 5.7，仅停用词列表适用。

但是对于 MySql 5.5，从你最新的 fiddle 来看，有两个原因:

这不起作用的第一个原因是因为您要搜索的单词出现在总行数的 50% 或更多中，因此它被视为常见单词并且不会匹配为一个结果。请参阅Mysql 5.5 FullText Search Docs :

There are three types of full-text searches:

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators, with the exception of double quote (") characters. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match.

第二个是因为默认情况下全文搜索长度设置为 4。因此您需要在 my.cnf 中更改它并添加值:

[mysqld]
ft_min_word_len = 3

能够搜索3个字符的单词。

但是由于在 db-fiddle 中我无法修改长度，这里是 modified working fiddle其中使用了“Older”一词。 50% 的行中不存在 Older，其长度为 >= 4。