作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在使用 MySQL 5.6,试验新功能,如 INET6_ATON
和 IS_IPV6
。当脚本读取IPV4
时,它完美地将数据插入到表中。但是当涉及到 IPv6
时,其中一行 (ipTo)
失败,即使 INET6_ATON
单独工作也是如此。
我的表格:4 个字段
`geoIPID` INT NOT NULL AUTO_INCREMENT ,
`IPFrom` VARBINARY(16) NOT NULL ,
`IPTo` VARBINARY(16) NOT NULL ,
`countries_countryID` INT NOT NULL
将文本文件加载到表中的脚本:
LOAD DATA LOCAL INFILE '/Users/Invictus/Documents/htdocs/overkill/etcs/IPV6.csv'
INTO TABLE `overkill`.`geoIP`
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@IPFrom, @IPTo, @dummy, @dummy, @countryAbbreviation, @dummy)
SET IPFrom = IF(IS_IPV4(@IPFrom),
INET_ATON(@IPFrom),
INET6_ATON(@IPFrom)),
IPTo = IF(IS_IPV4(@IPTo),
INET_ATON(@IPTo),
INET6_ATON(@IPTo)),
countries_countryID =
(
SELECT IF (COUNT(*) != 0, `countries`.`countryID`, 999)
FROM `countries`
WHERE `countries`.`countryAbbreviation` = @countryAbbreviation
LIMIT 1
);
IPV4.csv 文件,如下所示:
"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia"
"1.0.1.0","1.0.3.255","16777472","16778239","CN","China"
"1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia"
IPV6.csv 文件,如下所示:
"2001:200::", "2001:200:ffff:ffff:ffff:ffff:ffff:ffff", "42540528726795050063891204319802818560", "42540528806023212578155541913346768895", "JP", "Japan"
"2001:208::", "2001:208:ffff:ffff:ffff:ffff:ffff:ffff", "42540529360620350178005905068154421248", "42540529439848512692270242661698371583", "SG", "Singapore"
"2001:218::", "2001:218:ffff:ffff:ffff:ffff:ffff:ffff", "42540530628270950406235306564857626624", "42540530707499112920499644158401576959", "JP", "Japan"
我的问题:
如果我加载 IPv6
,第二行 (ipTo)
是 NULL
。为什么?文件中的所有值都是有效的,但 MySQL 不会转换它们。
最佳答案
问题不在 INET6_ATON
中,而是在您的 IPV6.csv
文件中。与 IPV4.csv
不同,您在逗号后有额外的空格,这使得 LOAD DATA
像这样读取您的第二个字段
"2001:200:ffff:ffff:ffff:ffff:ffff:ffff"
^^ ^
这就是 INET6_ATON
返回 NULL
的原因。
解决这个问题:
FIELDS TERMINATED BY ', '
此外,您还可以简化查询,只使用 INET6_ATON
而不是在 INET_ATON
和 INET6_ATON
之间有条件地切换。后者适用于 IPV4 和 IPV6。
话虽如此,您的查询可能看起来像这样(假设您在 csv 文件中没有多余的空格)
LOAD DATA LOCAL INFILE '/Users/Invictus/Documents/htdocs/overkill/etcs/IPV6.csv'
INTO TABLE `overkill`.`geoIP`
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@ipfrom, @ipto, @dummy, @dummy, @abbr, @dummy)
SET IPFrom = INET6_ATON(@ipfrom),
IPTo = INET6_ATON(@ipto),
countries_countryID =
(
SELECT IF(COUNT(*) != 0, `countries`.`countryID`, 999)
FROM `countries`
WHERE `countries`.`countryAbbreviation` = @abbr
LIMIT 1
);
为了进行测试,我删除了多余的空格,合并了您的两个文件,并将它们加载到 ipv6
表中。这是结果
mysql> select geoIPID, HEX(ipfrom), HEX(ipto) from ipv6;+---------+----------------------------------+----------------------------------+| geoIPID | HEX(ipfrom) | HEX(ipto) |+---------+----------------------------------+----------------------------------+| 1 | 20010200000000000000000000000000 | 20010200FFFFFFFFFFFFFFFFFFFFFFFF || 2 | 20010208000000000000000000000000 | 20010208FFFFFFFFFFFFFFFFFFFFFFFF || 3 | 20010218000000000000000000000000 | 20010218FFFFFFFFFFFFFFFFFFFFFFFF || 4 | 01000000 | 010000FF || 5 | 01000100 | 010003FF || 6 | 01000400 | 010007FF |+---------+----------------------------------+----------------------------------+6 rows in set (0.00 sec)mysql> select geoIPID, INET6_NTOA(ipfrom), INET6_NTOA(ipto) from ipv6;+---------+--------------------+----------------------------------------+| geoIPID | INET6_NTOA(ipfrom) | INET6_NTOA(ipto) |+---------+--------------------+----------------------------------------+| 1 | 2001:200:: | 2001:200:ffff:ffff:ffff:ffff:ffff:ffff || 2 | 2001:208:: | 2001:208:ffff:ffff:ffff:ffff:ffff:ffff || 3 | 2001:218:: | 2001:218:ffff:ffff:ffff:ffff:ffff:ffff || 4 | 1.0.0.0 | 1.0.0.255 || 5 | 1.0.1.0 | 1.0.3.255 || 6 | 1.0.4.0 | 1.0.7.255 |+---------+--------------------+----------------------------------------+6 rows in set (0.00 sec)
关于mysql - 在 LOAD DATA LOCAL INFILE 中使用 INET6_ATON 时返回 null,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18038442/
我是一名优秀的程序员,十分优秀!