mysql ip转整数

IP地址: A.B.C.D
整数: (A * 256^3) + (B * 256^2) + (C * 256^1) + D

(192 * 256^3) + (168 * 256^2) + (1 * 256^1) + 1 = 3232235777

DELIMITER $$
CREATE FUNCTION IP2INT(ip VARCHAR(15)) RETURNS INT
BEGIN
    DECLARE a, b, c, d INT;
    SET a = SUBSTRING_INDEX(ip, '.', 1);
    SET b = SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1);
    SET c = SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -2), '.', 1);
    SET d = SUBSTRING_INDEX(ip, '.', -1);
    RETURN (a * 256 * 256 * 256) + (b * 256 * 256) + (c * 256) + d;
END$$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION INT2IP(num INT) RETURNS VARCHAR(15)
BEGIN
    DECLARE a, b, c, d INT;
    SET a = num DIV (256 * 256 * 256);
    SET num = num % (256 * 256 * 256);
    SET b = num DIV (256 * 256);
    SET num = num % (256 * 256);
    SET c = num DIV 256;
    SET d = num % 256;
    RETURN CONCAT(a, '.', b, '.', c, '.', d);
END$$
DELIMITER ;