IP国家对照表在PHP+MySQL中的应用

最近项目中要用到IP国家对照表，于是google到一个由webhosting推出的免费的IP国家数据库：The IP to Country Database，更新速度也很快，使用起来也很方便。最新版：点击下载
在PHP使用之前，首先要把csv导入到MySQL中，用以下语句创建MySQL表，用来存放数据：

CREATE TABLE IF NOT EXISTS `ipcountry` (
  `ip_from` int(10) unsigned NOT NULL default '0',
  `ip_to` int(10) unsigned NOT NULL default '0',
  `country_code2` char(2) NOT NULL,
  `country_code3` char(3) NOT NULL,
  `country_name` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='IP国家对照表';

然后用以下语句导入csv文件数据，注意csv文件路径和表名，这里以junnan_org数据库为例：

Windows环境的MySQL导入语句

LOAD DATA INFILE "D:\ip-to-country.csv" INTO TABLE junnan_org.ipcountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'rn';

Linux环境的MySQL导入语句

LOAD DATA INFILE "/var/www/ip-to-country.csv" INTO TABLE junnan_org.ipcountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';

PHP中的查询方法就更方便了：

$conn=mysql_connect("localhost","USER","PASSWORD");
mysql_select_db('junnan_org');
$result = mysql_query("SELECT country_name FROM ipcountry WHERE ip_from< =INET_ATON('$ip') AND ip_to>=INET_ATON('$ip') LIMIT 0,1");
......