Mysql德语口音在全文搜索中不敏感搜索 [英] Mysql german accents not-sensitive search in full-text searches

问题描述

我们有一个例子酒店表：

Let`s have a example hotels table:

CREATE TABLE `hotels` (
  `HotelNo` varchar(4) character set latin1 NOT NULL default '0000',
  `Hotel` varchar(80) character set latin1 NOT NULL default '',
  `City` varchar(100) character set latin1 default NULL,
  `CityFR` varchar(100) character set latin1 default NULL,
  `Region` varchar(50) character set latin1 default NULL,
  `RegionFR` varchar(100) character set latin1 default NULL,
  `Country` varchar(50) character set latin1 default NULL,
  `CountryFR` varchar(50) character set latin1 default NULL,
  `HotelText` text character set latin1,
  `HotelTextFR` text character set latin1,
  `tagsforsearch` text character set latin1,
  `tagsforsearchFR` text character set latin1,
  PRIMARY KEY  (`HotelNo`),
  FULLTEXT KEY `fulltextHotelSearch` (`HotelNo`,`Hotel`,`City`,`CityFR`,`Region`,`RegionFR`,`Country`,`CountryFR`,`HotelText`,`HotelTextFR`,`tagsforsearch`,`tagsforsearchFR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

在这个表格中，我们只有一个地区名称=Graubünden的酒店（请注意umlaut ü角色）

In this table for example we have only one hotel with Region name = "Graubünden" (please note umlaut ü character)

现在我想为短语达到相同的搜索匹配：
'graubunden'和
'graubünden'

And now I want to achieve same search match for phrases: 'graubunden' and 'graubünden'

这是简单的使用MySql内置
排序规则的常规搜索如下：

This is simple with use of MySql built in collations in regular searches as follows:

SELECT *  
FROM `hotels` 
WHERE `Region` LIKE CONVERT(_utf8 '%graubunden%' USING latin1) 
COLLATE latin1_german1_ci

这可以适用于'graubunden'和'graubünden'和
，因此我收到正确的结果，但问题是
当我们进行MySQL全文搜索

This works fine for 'graubunden' and 'graubünden' and as a result I receive proper result, but problem is when we make MySQL full text search

这个SQL语句有什么问题？：

Whats wrong with this SQL statement?:

SELECT 
 *
FROM 
 hotels 
WHERE 
 MATCH (`HotelNo`,`Hotel`,`Address`,`City`,`CityFR`,`Region`,`RegionFR`,`Country`,`CountryFR`, `HotelText`, `HotelTextFR`, `tagsforsearch`, `tagsforsearchFR`)
AGAINST( CONVERT('+graubunden' USING latin1)  COLLATE latin1_german1_ci IN BOOLEAN MODE)            
ORDER BY Country ASC, Region ASC, City ASC

这不会返回任何结果。
任何想法狗被埋葬？

This doesn`t return any result. Any ideas where the dog is buried ?

推荐答案

当您定义单个 CHARACTER SETS ，您可以覆盖在表级别上设置的排序规则。

When you define individual CHARACTER SETS for your columns, you override the collation you set default on table level.

每个列都有默认的 latin1 collat​​ion（这是 latin1_swedish_ci ）。您可以通过运行 SHOW CREATE TABLE 。

Each of your columns has default latin1 collation (which is latin1_swedish_ci). You can see it by running SHOW CREATE TABLE.

在 FULLTEXT 查询，索引列具有 CERCIBILITY 0 ，即所有全文查询都将转换为索引，反之亦然。

In FULLTEXT queries, indexed columns have COERCIBILITY of 0, that is all fulltext queries are converted to the collation used in the index, not vice versa.

您需要从列中删除 CHARACTER SET 定义或明确设置所有列到$ code> latin1_german_ci ：

You need to remove CHARACTER SET definitions from your columns or explicitly set all columns to latin1_german_ci:

CREATE TABLE `hotels` (
  `HotelNo` varchar(4) NOT NULL default '0000',
  `Hotel` varchar(80) NOT NULL default '',
  `City` varchar(100) default NULL,
  `CityFR` varchar(100) default NULL,
  `Region` varchar(50) default NULL,
  `RegionFR` varchar(100) default NULL,
  `Country` varchar(50) default NULL,
  `CountryFR` varchar(50) default NULL,
  `HotelText` text,
  `HotelTextFR` text,
  `tagsforsearch` text,
  `tagsforsearchFR` text,
  PRIMARY KEY  (`HotelNo`),
  FULLTEXT KEY `fulltextHotelSearch` (`HotelNo`,`Hotel`,`City`,`CityFR`,`Region`,`RegionFR`,`Country`,`CountryFR`,`HotelText`,`HotelTextFR`,`tagsforsearch`,`tagsforsearchFR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

INSERT
INTO    hotels (hotelText, HotelTextFR, tagsforsearch, tagsforsearchFR)
VALUES  ('text', 'text', 'graubünden', 'tags');

SELECT  *
FROM    hotels
WHERE   MATCH (`HotelNo`,`Hotel`,`City`,`CityFR`,`Region`,`RegionFR`,`Country`,`CountryFR`, `HotelText`, `HotelTextFR`, `tagsforsearch`, `tagsforsearchFR`)
AGAINST (CONVERT('+graubunden' USING latin1) COLLATE latin1_german1_ci IN BOOLEAN MODE)
ORDER BY
        Country ASC, Region ASC, City ASC;

这篇关于Mysql德语口音在全文搜索中不敏感搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！