What type of data is to keep user addresses at SQL MySQL?



  • What type of data is to keep user addresses at SQL MySQL?



  • Address IPv4 stored in the field int unsigned as a number. Unsigned has to make one more sign.

    The hard drive takes four bikes. Which is less than char/varchar.

    To transform from a ip-dress into 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)
    

    For conversion from among to INET_NTOA

    1. Int contains up to 2147483647
    2. Int unsigned - to 4294967295

    It's easy to make choices across ranges. http://jeka.by/ask/124/mysql-ip-address/#answer-75 :

    Store ip data in varchar - moveton suitable for small projects only. If you've got a million visitors a day, you've got to squeeze out of the base and you need to use as little as possible. Because memory is money, the cycles of the processor are money.

    A million visitors a day may well be if you don't make a store, but perhaps a billboard, dsp, a banner, a payment system (its or bank) etc.

    A million visitors a day is, for example, your shooter, deployed on AWS, an extra hundred bucks a month that you can save that optimization will never hurt.

    The use of INET_NTOA/INET_ATON is very light but important optimization.

    When you need to contact the OBD for the epic, you'd better change inet_ntoa/inet_aton before the request, in php/python/whatever you've got to use the bd indexes.

    P.S. on IPV6 - https://dba.stackexchange.com/a/81402 In version 5.6 MySQL, the functions for dealing with it are added




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2