How to Store an IP Address in MySQL: INT UNSIGNED vs VARBINARY(16)

An IP address is just a fixed number of bytes (4 for IPv4, 16 for IPv6), so the right MySQL column stores those bytes, not their dotted-decimal text. For an IPv4-only system, store the address in INT UNSIGNED (4 bytes) and convert with INET_ATON() on the way in and INET_NTOA() on the way out. For anything that has to hold IPv6, or both families in one column, store the raw bytes in VARBINARY(16) and convert with INET6_ATON() and INET6_NTOA(). Reach for VARCHAR(45) only when you genuinely need human-readable values and will never run a range or subnet query. Below is the comparison table, the conversion functions, a worked schema, and how to do CIDR matching.

Short answer: INT UNSIGNED for IPv4-only (4 bytes, INET_ATON/INET_NTOA); VARBINARY(16) for IPv6 or mixed (16 bytes, INET6_ATON/INET6_NTOA). Both make sorting and subnet BETWEEN queries correct, which the string form quietly breaks. Skip VARCHAR(45) as a default: it is bigger, slower to compare, and treats 10.0.0.2 and 10.0.0.10 as out of order.

Why not just store the string?

The obvious move is VARCHAR(15) for IPv4 (255.255.255.255 is 15 characters) or VARCHAR(45) to cover the longest IPv6 text form. It works, it reads cleanly at the SQL prompt, and it is almost always the wrong default. Three reasons:

• Size. 203.0.113.7 is 11 characters, so 11 bytes plus a length byte in VARCHAR. The same address is 4 bytes in INT UNSIGNED. On a table that logs every request, that gap multiplies into real disk and buffer-pool cost, and the index gets bigger in lockstep.
• Comparison and sorting. Strings sort lexically, so 10.0.0.2 sorts after 10.0.0.10 because '2' is greater than '1'. An ORDER BY ip on a string column produces nonsense ordering. The numeric and binary forms sort in true address order for free.
• Range and subnet queries. This is the one that actually bites. To ask "is this address inside 192.168.1.0/24?" you compare it against the network's first and last address. That is a numeric BETWEEN, and it only works if the column holds the numeric value. On a string column there is no correct range expression at all.

The string column has exactly one honest use: a display field you read by eye and never compute against. If you need that and the math, store the bytes and convert on display, or keep a second generated column. Do not lead with the string.

The conversion functions

MySQL ships the conversions, so you never have to parse an address by hand:

• INET_ATON('203.0.113.7') turns an IPv4 dotted-quad string into its INT UNSIGNED value (the "A to N", ASCII to number).
• INET_NTOA(3405803783) turns that integer back into the dotted-quad string.
• INET6_ATON('2001:db8::1') turns an IPv6 (or IPv4) string into its raw binary form: 16 bytes for IPv6, 4 bytes for IPv4.
• INET6_NTOA(...) turns the binary back into the canonical text form.

INET6_ATON() and INET6_NTOA() were added in MySQL 5.6.3 (2012), so any currently supported server has them. The key detail is that INET6_ATON() also accepts an IPv4 string and returns its 4 raw bytes, which is exactly why VARBINARY(16) is the single-column answer when you must hold both families: one column, one pair of functions, both address types. Everything in this article works identically in MariaDB; the four INET* functions, INT UNSIGNED, and VARBINARY(16) behave the same there as in MySQL.

Storage comparison table

INT UNSIGNED is the tightest fit for IPv4: its range is 0 to 4294967295, which is exactly the 32-bit IPv4 address space, no wasted bits. VARBINARY(16) costs four times the bytes but buys you IPv6 and the ability to keep both families in one place. VARCHAR(45) is the largest and the only one that cannot do range math.

Method 1: INT UNSIGNED for IPv4-only

If your system will only ever see IPv4, INT UNSIGNED is the canonical choice. Four bytes, full address range, and the conversions are one function each.

CREATE TABLE ipv4_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip INT UNSIGNED NOT NULL,
  INDEX idx_ip (ip)
) ENGINE=InnoDB;

INSERT INTO ipv4_log (ip) VALUES (INET_ATON('203.0.113.7'));

SELECT INET_NTOA(ip) AS ip FROM ipv4_log;
-- 203.0.113.7

Note the column is INT UNSIGNED, not plain INT. A signed INT tops out at 2147483647, so any address from 128.0.0.0 upward overflows it. The UNSIGNED flag is not optional here; without it, half the address space stores garbage.

For a lookup by address, convert the parameter the same way so you compare integers to integers:

SELECT * FROM ipv4_log WHERE ip = INET_ATON(?);

Method 2: VARBINARY(16) for IPv6 (or both)

The moment IPv6 is in scope, switch to VARBINARY(16). It holds the 16 raw bytes of an IPv6 address, and because INET6_ATON() returns 4 bytes for an IPv4 string, the same column transparently stores IPv4 too. One column for both families.

CREATE TABLE ip_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip VARBINARY(16) NOT NULL,
  INDEX idx_ip (ip)
) ENGINE=InnoDB;

INSERT INTO ip_log (ip) VALUES (INET6_ATON('2001:db8::1'));
INSERT INTO ip_log (ip) VALUES (INET6_ATON('203.0.113.7'));

SELECT INET6_NTOA(ip) AS ip FROM ip_log;
-- 2001:db8::1
-- 203.0.113.7

INET6_NTOA() gives back the canonical text form, collapsing the longest zero-run in an IPv6 address to :: and lowercasing the hex, so what you read back is normalized regardless of how the address was written on insert. Lookups follow the same convert-the-parameter pattern:

SELECT * FROM ip_log WHERE ip = INET6_ATON(?);

One thing to keep in mind when you mix families: an IPv4 address stored via INET6_ATON() is 4 bytes, and the same address has a separate IPv4-mapped IPv6 form (::ffff:203.0.113.7) that is 16 bytes. Those are two different stored values, so pick one normalization for your application and apply it consistently before insert, or every IPv4 client risks landing in the table twice.

A worked schema: access_log

Here is a realistic table: an access log keyed by an auto-increment primary key, storing the client IP in VARBINARY(16) so it survives the move to IPv6, with the address indexed for per-IP queries.

CREATE TABLE access_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip          VARBINARY(16) NOT NULL,
  path        VARCHAR(2048) NOT NULL,
  status      SMALLINT UNSIGNED NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ip_created (ip, created_at)
) ENGINE=InnoDB;

-- log a request (the app passes the client IP string)
INSERT INTO access_log (ip, path, status)
VALUES (INET6_ATON('2001:db8::1'), '/login', 200);

-- read back, address rendered for humans
SELECT INET6_NTOA(ip) AS ip, path, status, created_at
FROM access_log
WHERE ip = INET6_ATON('2001:db8::1')
ORDER BY created_at DESC;

The composite idx_ip_created index is deliberate: "what did this address do, most recent first" is the query an access log exists to answer, and indexing (ip, created_at) serves both the equality on ip and the ordering on created_at from one B-tree. If the system were IPv4-only, the column would be INT UNSIGNED and the functions INET_ATON/INET_NTOA; everything else is identical.

Subnet and CIDR matching

This is where storing the bytes pays off. A CIDR block like 192.168.1.0/24 is a contiguous range of addresses from the network address (192.168.1.0) to the broadcast address (192.168.1.255). Because the column holds the numeric or binary value, "is this address in that block" is a plain BETWEEN against the two boundaries:

-- IPv4, INT UNSIGNED column: everything in 192.168.1.0/24
SELECT INET_NTOA(ip) AS ip
FROM ipv4_log
WHERE ip BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');

The same logic works on the binary form. Byte strings compare lexicographically byte-by-byte, which for big-endian network-order addresses is exactly numeric order, so BETWEEN on the network and broadcast addresses of the block selects the subnet:

-- IPv6/binary column: everything in 2001:db8::/32
SELECT INET6_NTOA(ip) AS ip
FROM ip_log
WHERE ip BETWEEN INET6_ATON('2001:db8::')
            AND INET6_ATON('2001:db8:ffff:ffff:ffff:ffff:ffff:ffff');

Both queries are sargable: the optimizer can use idx_ip to scan just the matching range of the index rather than the whole table. The reason they stay sargable is that the conversion sits on the literals, not the column. Wrap the indexed column instead (WHERE INET6_NTOA(ip) BETWEEN ...) and you force a full scan, because the index is on the stored bytes and the function output is not. Convert the boundaries you are comparing against, never the column. On a string column none of this is expressible, which is the single strongest argument against VARCHAR for addresses you ever filter by subnet.

Converting in the application instead

You do not have to lean on the MySQL functions; you can pack the address into bytes in your application and store that, which keeps the conversion off the database and lets the same logic run in code paths that never touch SQL. Every mainstream language has it built in:

• PHP: inet_pton('2001:db8::1') returns the packed bytes (4 or 16) to store in the VARBINARY column; inet_ntop($bytes) converts back to the string.
• Python: the standard-library ipaddress module parses and normalizes addresses, and ipaddress.ip_address(s).packed gives the raw bytes; socket.inet_pton/inet_ntop are the lower-level pair.
• Node.js: the built-in net module validates with net.isIP(), and packages like ip6 or ipaddr.js handle packing to and from the 16-byte buffer for the VARBINARY column.

Whichever side does the conversion, the stored bytes are identical, so you can mix a MySQL-side INET6_ATON() insert with an application-side inet_ntop() read without any mismatch. Pick one place to normalize (especially the IPv4-versus-IPv4-mapped question above) and keep it consistent.

What to do next

• For the exact byte sizes and ranges of INT, VARBINARY, and every other column type, see MySQL data types and sizes.
• For the conversion functions and DDL on one page, the MySQL cheat sheet lists INET_ATON, INET_NTOA, and the IPv6 pair alongside the rest of the common functions.
• On Postgres the picture is different: a dedicated inet/cidr type does the byte packing and subnet matching for you. See storing IP addresses in PostgreSQL.

FAQ

See also

• Storing IP addresses in PostgreSQL covers the dedicated inet/cidr types that pack the bytes and do subnet matching natively, the Postgres counterpart to this approach.
• The MySQL data types and sizes reference lists the exact byte cost and range of INT, VARBINARY, and every other column type.
• Keep the MySQL cheat sheet handy for the INET_ATON/INET_NTOA pair and the rest of the common DDL and functions on one page.
• Spinning up a throwaway server to test the schema? Running MySQL in Docker gets you a disposable instance in one command.
• For another fixed-width binary identifier, storing a UUID in MySQL walks through the same BINARY versus text tradeoff.
• Need to change an existing string IP column to the binary form? Changing a MySQL column type covers the safe migration path.