在MySQL和CodeIgniter中使用Match和Against [英] Using Match and Against in MySQL and CodeIgniter

问题描述

我试图在我的codeIgniter项目中实现一个搜索功能。我有一个名为product_search的表，其中使用以下命令设置全文索引：

I am trying to implement a search feature into my codeIgniter project. I have a table called product_search, where I am set a fulltext index using the following command:

ALTER TABLE `product_search` ADD FULLTEXT (`prod_title`, `prod_desc`)

然后我使用下面的codeIgniter代码： p>

I then search it using the following codeIgniter code:

$this->db->select("prod_id");
$this->db->where("MATCH (`prod_title`, `prod_desc`) AGAINST ('{$searchStr}')");
$this->db->or_where("prod_id LIKE '%{$searchStr}%'");
$this->db->or_where("prod_sku LIKE '%{$searchStr}%'");
$this->db->or_where("prod_title LIKE '%{$searchStr}%'");
$this->db->or_where("prod_desc LIKE '%{$searchStr}%'");

$query = $this->db->get("product_search");

但是，这会导致以下错误：

This, however, results in the following error:

错误号码：1064

Error Number: 1064

您的SQL语法有错误;请检查与您的MySQL服务器版本对应的手册，以获取在'AGAINST（'silk'）或 prod_id LIKE'％silk％'或 prod_sku LIKE'％silk％'或`prod'在第3行

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AGAINST ('silk') OR prod_id LIKE '%silk%' OR prod_sku LIKE '%silk%' OR `prod' at line 3

SELECT prod_id FROM（ product_search ）WHERE MATCH （ prod_title ， prod_desc ）AGAINST（'silk'）OR prod_id LIKE'％silk％'或 prod_sku LIKE'％silk％'或 prod_title LIKE'％silk％'或 prod_desc LIKE'％silk％'

SELECT prod_id FROM (product_search) WHERE MATCH (prod_title, prod_desc) AGAINST ('silk') OR prod_id LIKE '%silk%' OR prod_sku LIKE '%silk%' OR prod_title LIKE '%silk%' OR prod_desc LIKE '%silk%'

文件名：models / products_model.php

Filename: models/products_model.php

行号：430

为什么要把反引号放在MATCH周围？

Why is it putting backticks around the MATCH?

MATCH及其后面的括号，则会出现此错误讯息：

If I remove the space inbetween MATCH and the brackets following it, then I get this error message:

错误号码：1191

Error Number: 1191

无法找到与列列表匹配的FULLTEXT索引

Can't find FULLTEXT index matching the column list

SELECT prod_id FROM $ c> product_search ）WHERE MATCH（ prod_title ， prod_desc ）AGAINST ）OR prod_id LIKE'％silk％'或 prod_sku LIKE'％silk％'或 prod_title LIKE'％silk％'或 prod_desc LIKE'％silk％'

SELECT prod_id FROM (product_search) WHERE MATCH(prod_title, prod_desc) AGAINST ('silk') OR prod_id LIKE '%silk%' OR prod_sku LIKE '%silk%' OR prod_title LIKE '%silk%' OR prod_desc LIKE '%silk%'

Filename：models / products_model.php

Filename: models/products_model.php

行号：430

，我使用top命令添加全文索引。
发生了什么问题？

However, I used the top command to add a fulltext index. What is going wrong?

推荐答案

我通过添加IN BOOLEAN MODE来解决这个问题，

I solved the problem by adding IN BOOLEAN MODE to against section the statement as below:

SELECT `prod_id`
FROM (`product_search`)
WHERE MATCH (`prod_title`, `prod_desc`) AGAINST ('silk' IN BOOLEAN MODE)
OR `prod_id` LIKE '%silk%' OR `prod_sku` LIKE '%silk%'
OR `prod_title` LIKE '%silk%'
OR `prod_desc` LIKE '%silk%';

这篇关于在MySQL和CodeIgniter中使用Match和Against的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！