Convert IPv4 in MySQL

Support & Bugs
1

Cause it’s a little bit tricky, I want to share my example for coverting an IPv4 to the ip_location blob and backward.


SELECT *,
    IF(ip1_blob = ip2_blob, true, false) AS true_blob,
    IF(ip1 = ip2, true, false) AS `true`,
    IF(ip1_hex = ip2_hex, true, false) AS true_hex
FROM (
    SELECT *,
        UNHEX (ip1_hex) AS ip1_blob
    FROM (
        SELECT *,
            CONCAT(CONV(SUBSTRING_INDEX (ip1, '.', 1),10,16),CONV(SUBSTRING_INDEX (SUBSTRING_INDEX (ip1, '.', -3), '.',1),10,16),CONV(SUBSTRING_INDEX (SUBSTRING_INDEX (ip1, '.', -2), '.',1),10,16),CONV(SUBSTRING_INDEX (ip1, '.', -1),10,16)) AS ip1_hex,
            HEX (ip2_blob) AS ip2_hex
        FROM (
            SELECT '[b]129.187.206.132[/b]' AS ip1,
                location_ip AS ip2_blob,
                CONCAT_WS('.',CONV(SUBSTRING(HEX (location_ip),1,2),16,10),CONV(SUBSTRING(HEX (location_ip),3,2),16,10),CONV(SUBSTRING(HEX (location_ip),5,2),16,10),CONV(SUBSTRING(HEX (location_ip),7,2),16,10)) AS ip2
            FROM log_visit
            WHERE [b]idvisit = 1[/b]
        ) AS a
    ) AS b
) AS c;