Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
847 views
in Technique[技术] by (71.8m points)

mysql - Size for storing IPv4, IPv6 addresses as a string

what should be the ideal size for storing IPv4, IPv6 addresses as a string in the MySQL database. should varchar(32) be sufficient?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Assuming textual representation in a string :

  • 15 characters for IPv4 (xxx.xxx.xxx.xxx format, 12+3 separators)
  • 45 characters for IPv6

Those are the maximum length of the string.

Alternatives to storing as string:

  • IPv4 is 32-bits, so a MySQL data type that can hold 4 bytes will do, using INT UNSIGNED is common along with INET_ATON and INET_NTOA to handle the conversion from address to number, and from number to address
SELECT INET_ATON('209.207.224.40');
        -> 3520061480

SELECT INET_NTOA(3520061480);
        -> '209.207.224.40'
  • For IPv6, unfortunately MySQL does not have a data type that is 16 bytes, however one can put the IPv6 into a canonical form, then separate them into 2 BIGINT (8 bytes), this however will use two fields.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...