mysql 5.0升级到5.1 Match功能 [英] mysql 5.0 upgrade to 5.1 Match function

问题描述

我将网站的数据库从MySQL 5.0.95迁移到了MySQL 5.1.59

I migrated a website's database from MySQL 5.0.95 to MySQL 5.1.59

当我尝试进行搜索时，会得到不同的结果
例如: 当我在旧数据库上搜索"MTH"时，我得到一个结果， 但是当我在新数据库中搜索"MTH"时，我没有得到任何结果

When i try to do a search i get different results
for example: When i search for "MTH" on the old database i get one result, but when i search on the new database for "MTH" i do not get any results

但是，当我搜索会计"时，两个数据库都得到相同的结果！ 所以只有一些单词显示不同的结果，而不是全部

However when i do a search for "accountents" i DO get the SAME results with both databases!?! so only some words shows different results, not all

我将其范围缩小到mysql的"match"函数. 曾经遇到过同样问题的人可能会迁移网站吗?

I narrowed it down to the "match" function from mysql. Anyone who has had the same problem wen migrating a website perhaps?

这是我的SQL查询:

SELECT
 a.addressid,
 a.status,
 a.name,
 a.subname,
 a.subtitle,
 a.city,
 a.googlemaps,
 a.address,
 a.zipcode,
 a.pobox,
 a.poboxzipcode,
 a.phone,
 a.fax,
 a.website,
 a.intro,
 a.packageid_fk,
 a.enddate,
 DATE_FORMAT(a.enddatenew, '%d-%m-%Y') AS endingdate,
 p.name package,
 m.themeid_fk
FROM
 addresses AS a

INNER JOIN addresses_subcategories AS a_s 
 ON a_s.addressid_fk = a.addressid
LEFT  JOIN packages                AS p   
 ON p.packageid      = a.packageid_fk
INNER JOIN subcategories  AS s 
 ON s.subcategoryid  = a_s.subcategoryid_fk
INNER JOIN maincategories AS m 
 ON m.maincategoryid = s.maincategoryid_fk
WHERE
 a.status = 1
AND 
MATCH(a.name, a.subname, a.sponsorkeywords, a.extrakeywords, a.website)
AGAINST('MTH*' IN BOOLEAN MODE)
AND
a.websiteid_fk = 1

GROUP BY a.addressid
ORDER BY a.packageid_fk DESC, a.sort ASC, a.name ASC

PS.这个查询在MySQL 5.0上效果很好，但是在5.1上却没有相同的结果

PS. This very same query works great on MySQL 5.0, however on 5.1 it does not show the same results

推荐答案

似乎与ft_min_word_len变量有关.与我的网络托管商联系后，他们对其进行了更改，现在又重新输入了文字

Seems it had to do with the ft_min_word_len variable. after contacting my webhost they changed it and now it words again

这篇关于mysql 5.0升级到5.1 Match功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！