mysql> SELECT IpToInteger ( '10.8.208.30' ); +-------------------------------+ | IpToInteger ( '10.8.208.30' ) | +-------------------------------+ | 168349726 | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT IntegerToIp( 168349726 ); +--------------------------+ | IntegerToIp( 168349726 ) | +--------------------------+ | 10.8.208.30 | +--------------------------+ 1 row in set (0.00 sec) mysql>
Oct 14, 2010 · Databases Administration · rhosto
MySQL Functions for Converting IP Address to an Integer and Back Again
So after my MySQL server-id idea. I was asked to come up with a function to Convert IP Address. So here it is.
use test;
DROP FUNCTION IF EXISTS IpToInteger;
DELIMITER //
CREATE FUNCTION IpToInteger ( ipAddress CHAR(15) )
RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN
DECLARE o1,o2,o3,o4 char(3);
DECLARE IpInteger INT UNSIGNED;
SET o1 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, '.', 1), LENGTH(SUBSTRING_INDEX( ipAddress, '.', 1 - 1) ) + 1), '.', '' );
SET o2 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, '.', 2), LENGTH(SUBSTRING_INDEX( ipAddress, '.', 2 - 1) ) + 1), '.', '' );
SET o3 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, '.', 3), LENGTH(SUBSTRING_INDEX( ipAddress, '.', 3 - 1) ) + 1), '.', '' );
SET o4 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, '.', 4), LENGTH(SUBSTRING_INDEX( ipAddress, '.', 4 - 1) ) + 1), '.', '' );
SELECT ( o1 << 24 ) + ( o2 << 16 ) + ( o3 << 8 ) + o4 INTO IpInteger;
RETURN IpInteger;
END//
DELIMITER ;
use test;
DROP FUNCTION IF EXISTS IntegerToIp;
DELIMITER //
CREATE FUNCTION IntegerToIp( ipInteger INT UNSIGNED )
RETURNS CHAR(16) DETERMINISTIC
BEGIN
DECLARE o1,o2,o3,o4 INT UNSIGNED;
SET o1 = ( ipInteger >> 24 );
SET o2 = ( ipInteger >> 16 << 16 )-( ipInteger >> 24 << 24) >> 16;
SET o3 = (ipInteger >> 8 << 8 ) - ( ipInteger >> 16 << 16 ) >> 8;
SET o4 = ipInteger - ( ipInteger >> 8 << 8 );
RETURN CONCAT( o1, "." , o2, "." , o3, ".", o4 );
END//
DELIMITER ;