Posted on 26 Comments

Storing an IP address in a database table

Say you have an IP address, 192.168.0.10 and want to store that in a database table. You could of course store it in a CHAR(15) and that is in fact what many people do. But you probably want to search on this field and therefore want it indexed also. So can we do better than using a 15 byte character field? We sure can.

MySQL has two built-in functions: INET_ATON() and INET_NTOA(). They are actually based on the equivalent inet_aton() and inet_ntoa() which are C library functions present on pretty much every TCP/IP capable system. Why? These two functions are used allover the place in any TCP/IP stack implementation or even application.
The INET_ATON() function converts Internet addresses from the numbers-and-dots notation into a 32-bit unsigned integer, and INET_NTOA() does the opposite. Isn’t that handy!

Let’s put it to the test:

mysql> SELECT INET_ATON('192.168.0.10') AS ipn;
+------------+
| ipn        |
+------------+
| 3232235530 |
+------------+

mysql> SELECT INET_NTOA(3232235530) AS ipa;
+--------------+
| ipa          |
+--------------+
| 192.168.0.10 |
+--------------+

So you can store an IP address in an INT UNSIGNED (4 bytes) which is of course much more efficient and faster than a CHAR(15). Naturally, you can call the function while you’re inserting, so something like this is fine also:

INSERT INTO tbl VALUES (..., INET_ATON('192.168.0.10'), ...)

In MySQL 5.0, you can even do this transformation inside a LOAD DATA INFILE command, without using temporary columns:

LOAD DATA INFILE 'filename'
INTO TABLE tbl
...
(col1, ..., @ipa1, ..., coln)
SET ipn = INET_ATON(@ipa);

So in the list of columns you assign this column to a server-side variable, and then assign the transformed value to the proper column in the SET clause. Quite elegant, really.

Posted on 26 Comments

26 thoughts on “Storing an IP address in a database table

  1. Should ATON_ATON be replaced with INET_ATON ?
    And what about ‘ipn’? Where is it used?

  2. INET_ATON() typo fixed, thanks for spotting that.

    ipn is just used to show how the short form is represented (i.e., a single 32-bit unsigned integer value). Try and see what my story means, don’t try to copy the statements exactly. They are just an example, you need to understand the concept and apply it to your situation.

  3. In PostgreSQL there is a native network address type and MAC address data type:

    http://www.postgresql.org/docs/8.1/interactive/datatype-net-types.html

    Then there are functions and operators for them:

    http://www.postgresql.org/docs/8.1/interactive/functions-net.html


    Joseph Scott
    http://joseph.randomnetworks.com/

  4. Thanks. But those types are mostly used for input validation, not efficient storage. Reading that documentation page, one sees that an IPv4 address is stored in 12. The above solution in MySQL uses 4 bytes. This is clearly more efficient in storage, less resource use for indexing, and increased performance for handling (lookups, etc).

  5. They are stored in 12 because they can store IPv6 addresses as well. This is a big deal in many cases. In addition, the indexes support all sorts of fun queries, such as “inside netblock” and things like that in a pretty efficient manner.

  6. You just saved me untold hours of angst.
    Thank you. Thank you. Thank you.

  7. IPv6 uses 128 bit addresses, equal to 16 octets.

    I do like the idea, though, of the index supporting natural queries with regard to the data type. Currently an indexed date field would not utilize the index if one selects YEAR(datefield) and the same goes to LEFT(textfield,2).

    It is possible to workaround using BETWEEN (or just > and < ) and LIKE in the two cases. But it might not seem intuitive or add to readability. YEAR(datefield) = 2007 would be more self-explaining than datefield BETWEEN '2007-01-01 00:00:00' AND '2007-12-31 23:59:59' - Peter Brodersen

  8. The latter could be internally fixed up by the optimizer, without the user having to worry about it.
    Of course it would need to detect all usable cases to deal with them appropriately.

  9. Thanks, found you via google.

  10. Hi,

    my C code goes like this…
    sprintf(query, “INSERT INTO PeerInfo(IPAddress, Port, Active, Counter, Priority) VALUES(‘%d’, ‘%d’, ‘%c’, ‘%d’, ‘%c'”, inet_aton(addr->sin_addr.s_addr), addr->sin_port, active, counter, priority);

    where IPAddress is unsigned int and Port is unsigned short.I am getting this error “conversion from `in_addr_t’ to non-scalar type `in_addr’ requested”.

    can some body correct this plz.

  11. You’ll want to use %u and cast to a uint32 or something.

  12. The Problem here is i want to store the IP address as an integer in the table.when i am trying to convert it from addr->sin_addr to integer,it is giving error.
    when i give the query like this..
    insert into PeerInfo values(inet_aton(“211.38.137.33”)); the value in the table looks like this 3542518049.

    but when i try to write the same in the query,its giving an error.

  13. Well, “it” would be your C compiler, this has nothing to do with MySQL or the MySQL client API. It’s pure C coding stuff so that’s where that problem needs to be solved, too.

    And indeed, doing it in SQL is what this original posting was about. It works fine too.

  14. Hardly as elegant as the PostgreSQL solution though.

  15. Elegant comes in many ways. I’ll concur that PostgreSQL makes it look nice from the user perspective. But if you’re only dealing with IPv4, you don’t want to use more than 4 bytes for an address. The additional space costs you memory, disk, and I/O which you may not be able to afford – depending on the application.

    I’m all for having specific data types for commonly used domains, and IPV4 and IPV6 (each individually) definitely fall into that category.

  16. if u can tell how to store ip addr range also.

  17. Store the low and high, then the server can find it using a range scan in its index.
    If you work using a mask, it may not be able to use an index, although there might be some tricks available to work around that.

  18. netmask operations are easy, they are based on bitwise operations.

    if you have ip4 address a.b.c.d as an integer abcd
    and netmask m.n.o.p as integer mnop

    the first address is written in c as (abcd & mnop) and the last as (abcd | ^ mnop )

    unlike the C version of inet_aton the mysql version transforms the result to host byte order, so betweeen can be used with sensible results

    so in mysql, to see if addr in in the subnet described by
    a.b.c.d and m.n.o.p you can say

    addr between ( abcd & mnop ) and ( abcd | ~ mnop & 0xfffffff)

    and get meaningful results.

  19. That’s exactly the same as what the C version does. In fact MySQL calls the C function, as far as I know.

    Doing the bitwise operations on the network ordered address is fine, however for searches you don’t want to do that as it would make using an index lookup for the search impossible. In the best case it might use an index scan rather than a full table scan.

  20. hi,

    i perform the client- server connection with mysql using c. in the table contains 2 feilds.(i.e) ip, data. but the ip’s & data’s does’t stored in the table. they stored as like this
    ———————-|———————-|
    | IP(feild1) | DATA(field2) |
    |———————-|——————– |
    | | |
    |inet_ntoa(client | recv_data); |
    | _addr.sin_addr)| |
    | | |
    ———————————————–

    i write the sql query as like this. mysql_query(&mysql,”INSERT INTO server(ip,data) VALUES(‘inet_ntoa(client_addr.sin_addr)’,’recv_data’)”);

    Plzzzzzzz give me a solution

  21. For storage, you need to use the aton function not the ntoa function.
    Also, quoting it feeds the server a string with “inet_ntoa(…” which is not what you want.
    You need to put the parameters properly into the query string, the above is just incorrect application code.

  22. You saved me a lot of time with your thoughtful post!
    You Rock!

  23. I’m trying to insert some data into a table named Site’
    the commands i’m trying to use are the following:

    load data infile “teste.txt”
    INTO TABLE site(dia_da_semana, mes, dia_no_mes, Hora, Ano, Tipo_de_requisicao, protocolo, IP, Usuario, Endereco, tamanho_arquivo)
    set IP = INET_ATON(IP)

    After the commands i get this error message:
    ERROR 1265 (01000): Data truncated for column ‘IP’ at row 1

    I figured that the INET_ATON part is not working by trying to insert manually an IP into the table and it worked fine, what should i do to make it work?

    Here is the result of the manual insert:
    mysql> INSERT INTO site (ip) VALUES (INET_ATON(“192.168.111.111”));
    Query OK, 1 row affected (0.02 sec)

    Really appreciate the help.
    Thanks

  24. Use another column name as intermediate?

  25. Hi,
    thanks for answering my question, i don’t think i got it right. i’m a beginner in SQL and I was making a database about reading a log with internet connections and sites that were accessed from our network. First i thought, like many did as you said in your article, to use a char(15), but then i thought that could have a better way to store an IP address and then i found your
    site.
    I tried making a new column and use it in the set part
    IE: SET IP = INET_ATOM(IPA) or
    SET IPA = INET_ATON(IP) and it resulted in the same truncated message.
    Can you please tell me the right way to do it as you to use another column name?
    Hope you don’t think i’m being lazy to try to find for myself, i did but couldn’t figure it :).
    Thanks a lot again.

  26. Hi again 🙂
    I found what i was doing wrong, forgot to use @ before the IP in the SET ip = INET_ATON(@ip);
    here is my result:

    mysql> load data infile “teste.txt” INTO TABLE site(dia_da_semana, mes, dia_no_mes, Hora, Ano, Tipo_de_requisicao, protocolo, @IP, Usuario, Endereco, tamanho_arquivo) set IP = INET_ATON(@IP);
    Query OK, 30 rows affected (0.02 sec)
    Records: 30 Deleted: 0 Skipped: 0 Warnings: 0

    really thank you for your time and information 🙂

Comments are closed.