在MySQL中将IP转换为Long [英] Converting an IP to a Long in MySQL

问题描述

我正在尝试使用ip2country表在我的网站上显示用户的国家/地区标志.

I'm trying to use the ip2country table to show the country flags of the users on my website.

我想到的最简单的事情是编写一条SQL语句，该语句将用户从会话表中取出并进行查询，以查看其各自的IP是否在一定范围内以找出其国家/地区标记.

The simplest thing I came up with is to write an SQL statement that takes the users from the session table and queries to see if their respective IP is in a certain range to figure out their country/flag.

这很简单，但也很危险，因为当有300位用户在线显示时，我从会话表中获取他们，查询他们的国家/地区以显示标志，肯定会占用大量内存.

It's simple but also dangerous, because when there are 300 users online to show and I fetch them from the session table, querying their countries to display the flags, there will surely be a big memory usage.

现在，我尝试在一个查询中执行此操作:

Now I tried this to do it in one single query:

SELECT 
  s.session_ip, 
  ipc.*
FROM 
  session AS s
    LEFT JOIN ip2country AS ipc 
    ON ipc.ip_lo <= s.session_ip AND ipc.ip_hi >= s.session_ip
WHERE 
  s.session_time  > '".( time() - 60) )."' 

现在显然上述查询是错误的，因为保存在ip2country表中的IP是整数，例如1000013824和存储在会话表中的IP是IP的字符串表示形式，例如193.169.0.0

Now it's clear that the above query is wrong because the IPs saved in the ip2country table is an integer, e.g. 1000013824, and the IPs stored in the session table are the string representations of the IPs, e.g. 193.169.0.0

我知道如何使用ip2long()在PHP中将IP转换为long，但是MySQL中是否有任何等效方法，因此我不必执行两个查询?

I know how to convert from an IP to a long in PHP with the ip2long(), but is there any equivalent method in MySQL so I don't have to do two queries?

推荐答案

SELECT INET_NTOA(1000013824)-> 59.155.0.0

SELECT INET_NTOA(1000013824) -> 59.155.0.0

SELECT INET_ATON('193.169.0.0')-> 3249078272

SELECT INET_ATON('193.169.0.0') -> 3249078272

享受:)

这篇关于在MySQL中将IP转换为Long的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！