2024-04-10 mysql简单实现ip转国家code

起因

业务要根据国家来分配订阅商品

问题

客户端有些lang中带国家code，但是有些没有，想到如果没带就用ip获取所在国家

方案

本来打算直接调别人的接口查的，没想到在注册账号，准备接api时发现网站免费提供 ip段对应国家编码的json文件。本着随便省点钱的想法，就接入在自己的数据库来查

流程

1. 将ip转为可计算的数据
   使用mysql 自带函数 INET_ATON(), INET6_ATON()可以将ip字符串转为数字
2. 存储表
   ipv4数字可用bigInt存储，ipv6用varbinary 16 位存储
   表结构

CREATE TABLE `ipv4_range_country` (
  `id` int NOT NULL AUTO_INCREMENT,
  `start_ip` bigint NOT NULL,
  `end_ip` bigint NOT NULL,
  `country` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `country_name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_start_end_ip` (`start_ip`,`end_ip`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `ipv6_range_country` (
  `id` int NOT NULL AUTO_INCREMENT,
  `start_ip` varbinary(16) NOT NULL,
  `end_ip` varbinary(16) NOT NULL,
  `country` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `country_name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_start_end_ip` (`start_ip`,`end_ip`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

3. 查询
   mysql

        select
        *
        from ipv4_range_country
        where
        INET_ATON('192.168.1.1') BETWEEN `start_ip` AND `end_ip`;

        select
        *
        from ipv6_range_country
        where
        INET6_ATON('2a13:ef41:1000:') BETWEEN `start_ip` AND `end_ip`;

mybits

<select id="selectByV4" resultMap="BaseResultMap" parameterType="java.lang.String">
        select
        <include refid="Base_Column_List"/>
        from ipv4_range_country
        where INET_ATON(#{ip,jdbcType=BIGINT}) BETWEEN `start_ip` AND `end_ip`
</select>

<select id="selectByV6" resultMap="BaseResultMap" parameterType="java.lang.String">
        select
        <include refid="Base_Column_List"/>
        from ipv6_range_country
        where
        INET6_ATON(#{ip,jdbcType=VARCHAR}) BETWEEN `start_ip` AND `end_ip`
</select>

java中判断走ipv4还是v6

public ipRangeCountry getByIp(String ip) {
        InetAddress inetAddress = InetAddresses.forString(ip);
        IpRangeCountry ipRangeCountry = null;
        if (inetAddress instanceof Inet4Address) {
            ipRangeCountry = ipRangeCountryMapper.selectByV4(ip);
        } else if (inetAddress instanceof Inet6Address) {
            ipRangeCountry = ipRangeCountryMapper.selectByV6(ip);
        }
       return ipRangeCountry ;
}

有更好的方法欢迎交流。。

附录

本来打算接api的网址 ipInfo
注册登录后 在Data Downloads中找到Free IP to Country下载json.gz文件，解压
文件解析

 try {
            Files.lines(Paths.get(filename)).forEach(s -> {
                CountryData countryData = JSON.parseObject(s, CountryData.class);
                IpRangeCountry ipRangeCountry = new IpRangeCountry();
                ipRangeCountry.setCountry(countryData.getCountry());
                ipRangeCountry.setCountryName(countryData.getCountry_name());
                ipRangeCountry.setStartIp(countryData.getStart_ip());
                ipRangeCountry.setEndIp(countryData.getEnd_ip());
                ipRangeCountry.setRemark(countryData.getStart_ip() + "-" + countryData.getEnd_ip());
                ipRangeCountryService.insert(ipRangeCountry);
            });
        } catch (Exception e) {
            e.printStackTrace();
        }

    @NoArgsConstructor
    @Data
    private static class CountryData {
        private String start_ip;
        private String end_ip;
        private String country;
        private String country_name;
        private String continent;
        private String continent_name;
        private String asn;
        private String as_name;
        private String as_domain;
    }