What type of data to store ip addresses of users in SQL MySQL?

Question

It depends on what you are going to do with it and how many of these records, if you just look at it, then it is possible to have a varchar, if you filter on it, it is better to use an integer.
If there are a lot of records, then the number will also take up less space
Column where will be recorded IP, with an index of unique values.
@ gilo1212, if so then look at the supplement in my answer, these functions convert the aypishnik to a number - it is more convenient to operate on more details here expange.ru/e/…
@ Boris may run better this way - BINARY (16) INET6_ATON and INET6_NTOA and respectively - INET_NTOA () and INET6_ATON?
@ gilo1212 then the format should be uniquely numeric, not varchar.
Here the author of the answer advises using VARBINARY (16) instead of BINARY (16) on stackoverflow.com/questions/6964143/… to support IPV4 + IPV6

Community spirit ♦ one · Accepted Answer · 2016-06-23T15:08:13

IPv4 addresses are stored in the int unsigned int field as a number. Unsigned it is necessary that one sign more intermeddle.

On the hard disk takes 4 bytes. Which is less than char / varchar.

To convert from a string IP address to a number, there is the mysql function INET_ATON :

 mysql> SELECT INET_ATON('193.125.99.10'); +----------------------------+ | INET_ATON('193.125.99.10') | +----------------------------+ | 3246220042 | +----------------------------+ 1 row in set (0.00 sec)

To convert from number to address - INET_NTOA .

Int holds up to 2147483647
Int unsigned - up to 4294967295

Sampling by ranges is convenient. My source :)

Store ip data in varchar - moveton, suitable only for small projects. If you have a million visitors a day, you need to squeeze out maximum performance from the database, and use as little memory as possible. Since memory is money, processor cycles are money.

A million visitors per day may well be if you do not shop, but, say, an advertising grid, dsp, banner twisting, payment system (your own or for the bank), etc.

Millions of visitors per day - for example, your start-up starcher who has been deployed on AWS, an extra hundred (one thousand?) Bucks a month that such optimization can never save you.

Using INET_NTOA / INET_ATON is very easy, but important optimization.

When you need to refer to the database for an IP address, it is better to do inet_ntoa / inet_aton transformations before the request, in php / python / on_ what_you_tam_write to use database indices.

PS about IPV6 - https://dba.stackexchange.com/a/81402 in version 5.6 MySQL added functions to work with it

IpV6 will be more likely if only in local networks, and behind ip there will be a regular v4 ip.
In varchar (15), it still does not fit, it is necessary varchar (39) at least.
The answer, though supplemented, does not say how to store both IPv4 and IPv6 in one field, and what type this field should be.
Most likely - stackoverflow.com/a/6965026/5006740 , but I would povenchmarkil for myself.

Answer 2 · 2016-06-23T14:33:34

we store in varchar, in principle, why invent something to store an IP address?

You can of course be stored as integers and use the functions mysql INET_ATON () and INET_NTOA ().

more info here http://expange.ru/e/%D0%9A%D0%B0%D0%BA_%D1%85%D1%80%D0%B0%D0%BD%D0%B8%D1%82% D1% 8C_IP-% D0% B0% D0% B4% D1% 80% D0% B5% D1% 81_ (MySQL)

For some reason it seems to me that this is not entirely correct
So the choice will take a long time, compared with BINARY (16)

rjhdby rjhdby 12.7k four 22 45 · Answer 3 · 2016-06-23T14:44:36

4 byte fields for IPv4

And it takes less space and it is easier to operate

rjhdby

12.7k four 22 45

Is it really such a bad option? so many cons ... - 4per
@ 3per himself in shock. Well, at least commented on what exactly is not like. - rjhdby

|

What type of data to store ip addresses of users in SQL MySQL?

3 answers 3

More articles: