php - 使用非拉丁字符搜索 Geoname 数据库

Question

我有一份 the Geonames database存储在 MySQL 数据库中，以及一个允许用户在数据库中搜索他们所在城市的 PHP 应用程序。如果他们用英文输入城市名称，效果很好，但我希望他们能够用他们的母语进行搜索。

例如，与其让说日语的人搜索东京，不如让他们搜索东京。

Geonames 数据库包含一个 alternatenames 列，其中包含“alternatenames，逗号分隔，自动音译的 ascii 名称，来自 alternatename 表的便利属性，varchar(10000)。”

例如，Tokyo 行的 alternatenames 值为 Edo,TYO,Tochiu,Tocio,Tokija,Tokijas,Tokio,Tokió,Tokjo,Tokyo ,Toquio,Toquio - dong jing,Toquio - æ±äº¬,Tòquio,Tókýó,Tóquio,TÅkyÅ,dokyo,dong jing,dong jing dou,tokeiyw,tokkiyo,tokyo,twkyw,twqyw,ȤόÈÎùÈ¿,ȢϾкР¸Ð¾,ТоÙÙÙ',ТоÙÙÕÕÕ¸Õ¯Õ«Õ¸,×∼×ÜÜÜܧיו,توكيو,توکیÙņ,طوكÙ,ŠÜ~Ùˆ ,ÜœÜ∼ÜŸÜÜ∼,टोकà¥à¤¯à¥‹,டோகà¯à®•à®¿à®¯à¯‹,โภ•à¹€à¸à¸µà¸¢à¸§,ტáƒáƒ™áƒ∼áƒ,东京,æ±äº¬,æ±äº¬éƒ½,ë„ì¿„。

这些值并不完全包含东京，但我猜测它们包含一种已经以某种方式编码或转换的形式。所以，我假设如果我对我的搜索字符串执行相同的编码/转换，那么我将能够匹配该行。例如:

mysql_query( sprintf( "
    SELECT * FROM geoname 
    WHERE 
        MATCH( name, asciiname, alternatenames ) 
        AGAINST ( %s )  
    LIMIT 1",
    iconv( 'UTF-8', 'ASCII', '東京' )
) );

问题是我不知道该转换是什么。我已经尝试了很多 iconv()、mb_convert_string() 等的组合，但没有成功。

MySQL 表如下所示:

CREATE TABLE `geoname` (
 `geonameid` int(11) NOT NULL DEFAULT '0',
 `name` varchar(200) DEFAULT NULL,
 `asciiname` varchar(200) DEFAULT NULL,
 `alternatenames` mediumtext,
 `latitude` decimal(10,7) DEFAULT NULL,
 `longitude` decimal(10,7) DEFAULT NULL,
 `fclass` char(1) DEFAULT NULL,
 `fcode` varchar(10) DEFAULT NULL,
 `country` varchar(2) DEFAULT NULL,
 `cc2` varchar(60) DEFAULT NULL,
 `admin1` varchar(20) DEFAULT NULL,
 `admin2` varchar(80) DEFAULT NULL,
 `admin3` varchar(20) DEFAULT NULL,
 `admin4` varchar(20) DEFAULT NULL,
 `population` int(11) DEFAULT NULL,
 `elevation` int(11) DEFAULT NULL,
 `gtopo30` int(11) DEFAULT NULL,
 `timezone` varchar(40) DEFAULT NULL,
 `moddate` date DEFAULT NULL,
 PRIMARY KEY (`geonameid`),
 KEY `timezone` (`timezone`),
 FULLTEXT KEY `namesearch` (`name`,`asciiname`,`alternatenames`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

谁能指出我正确的方向？

Answer 1

当我下载 the Japan file并像这样建立一个数据库:

CREATE TABLE geonames (
    geonameid SERIAL,
    name varchar(200),
    asciiname varchar(200),
    alternatenames varchar(10000),
    latitude float,
    longitude float,
    featureclass varchar(1),
    featurecode varchar(10),
    countrycode varchar(2),
    cc2 varchar(200),
    admin1code varchar(20),
    admin2code varchar(80),
    admin3code varchar(20),
    admin4code varchar(20),
    population BIGINT,
    elevation INT,
    dem INT,
    timezone varchar(40),
    modificationdate DATE
    ) CHARSET utf8mb4;

然后我load the data像这样:

LOAD DATA INFILE '/tmp/JP.txt' INTO TABLE geonames CHARACTER SET utf8mb4;

然后像这样选择它:

SELECT alternatenames FROM geonames WHERE geonameid=1850147\G

我明白了:

*************************** 1. row ***************************
alternatenames: Edo,TYO,Tochiu,Tocio,Tokija,Tokijas,Tokio,Tokió,Tokjo,Tokyo,Toquio,Toquio - dong jing,Toquio - 東京,Tòquio,Tókýó,Tóquio,Tōkyō,dokyo,dong jing,dong jing dou,tokeiyw,tokkiyo,tokyo,twkyw,twqyw,Τόκιο,Токио,Токё,Токіо,Տոկիո,טוקיו,توكيو,توکیو,طوكيو,ܛܘܟܝܘ,ܜܘܟܝܘ,टोक्यो,டோக்கியோ,โตเกียว,ტოკიო,东京,東京,東京都,도쿄

我也可以这样搜索:

SELECT name FROM geonames WHERE alternatenames LIKE '%,東京,%';

---

这是一个很长的说法:注意我创建表时的字符集声明。我相信这就是您在创建数据库时未能做到的。