Geek asks and answers

Mysql german emphasizes insensitive full-text search

Here is an example of a hotel:

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;

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

And now I want to achieve the same search match for the phrases: “robberies” and “Graubunden”

It is simple using MySql built into sorting in a regular search as follows:

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

This is great for robbery and graubunden and as a result I get the correct result, but the problem is when we do a full MySQL text search

What is 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 does not return any result. Any ideas where the dog is buried?

+3
source share
1

CHARACTER SETS , , .

latin1 <(t22 > ). , SHOW CREATE TABLE.

FULLTEXT , COERCIBILITY 0, , , .

CHARACTER SET 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;
+3

More articles:


All Articles