Well, that's the best way or the best way to save ip addresses in mysql, both ipv4 and ipv6 addresses
Clarifying, what I want to know is in what kind of data to save an IP address, I could keep it from the varchar type considering it as a string, then it generates a doubt if it is the best way or if there are better or more recommendable ways than work with ipv4 and ipv6
I think your question should be this from what I understand you. with what kind of data I keep the IP addresses in mysql
When we need to store IP addresses in our databases, the most convenient (and almost done by inertia) is to assign the field a VARCHAR type with a length of 15 characters. However, there is a better option that little by little I am finding myself in the main CMS and Frameworks: INT UNSIGNED.
Thus, using the MySQL command INET_ATON, the IP address would be encoded in a 4-byte string that we could retrieve by its opposite INET_NTOA
# Tomamos una direccion IP y la convertimos en una cadena de enteros:
SELECT INET_ATON('192.168.0.10') AS ipn;
# Esto se traduciría en 3232235530
# El proceso inverso:
SELECT INET_NTOA(3232235530) AS ipa;
# Nos devuelve nuestra direccion IP anterior: 192.168.0.10
MySql as mentioned above uses the function INET_ATON () to convert an ip address to an integer value, and the INET_NTOA () function to do the opposite, from a numeric value converts it into an ip address. The drawback is that it only supports ip addresses in version 4.
While to store IP addresses in version 6, you can use the INET6_ATON () function, which returns a value of VARBINARY : VARBINARY (16) for ipv6 and VARBINARY (4) for ipv4.
SELECT HEX(INET6_ATON('10.0.5.9'));
->'0A000509'
#Realizando un proceso contrario
SELECT INET6_NTOA(UNHEX('0A000509'));
->'10.0.5.9'
And for ipv6 addresses
SELECT HEX(INET6_ATON(fdfe::5a55:caff:fefa:9089));
->'FDFE0000000000005A55CAFFFEFA9089'
# y el proceso inverso
SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));
->'fdfe::5a55:caff:fefa:9089'