Member Avatar for martin_57

Hi, I am currently working on a banlist and I am thinking about which data type to choose for storing the IP address in the database. Will the Binary data type be ideal?

  • 5 Contributors
  • 4 Replies
  • 648 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by Vikas_30

Dani AI

Generated

Short answer: choose a compact, canonical representation so lookups, indexing and range checks are efficient. For MySQL that usually means either INT UNSIGNED + INET_ATON() for IPv4-only, or a binary column that supports both families (store the output of INET6_ATON() in VARBINARY(16) or in a normalized BINARY(16)). Keeping addresses as VARCHAR (as and @Vikas30 noted) is simplest to read but uses more space and makes comparisons slower. ’s pointer toward the INET6* functions is the right direction when you need IPv6 support.

Practical patterns you can apply immediately:

  • IPv4-only, fast and compact: store numeric ranges and test with BETWEEN.
CREATE TABLE bans_ipv4 (
  ip_start INT UNSIGNED NOT NULL,
  ip_end   INT UNSIGNED NOT NULL,
  reason   VARCHAR(255),
  created_at DATETIME,
  INDEX(ip_start, ip_end)
);

INSERT INTO bans_ipv4 (ip_start, ip_end)
VALUES (INET_ATON('192.0.2.0'), INET_ATON('192.0.2.255'));

SELECT * FROM bans_ipv4
WHERE INET_ATON('192.0.2.123') BETWEEN ip_start AND ip_end;
  • IPv6 (and mixed): store normalized 16‑byte values. Either use VARBINARY(16) to accept both 4- and 16-byte outputs, or normalize everything to 16 bytes (e.g., IPv4 → mapped IPv6) and use BINARY(16) so fixed-length indexes are efficient. For prefix bans, compute network start/end (application-side or with helper code) and store those boundaries for fast BETWEEN checks.

Operational notes and cautions: don’t rely on IP alone — NAT, proxies and dynamic pools make IP bans blunt. ’s MAC suggestion isn’t practical for web banlists (MACs aren’t visible across the Internet). Record context (first/last seen, UA, reverse-DNS, admin who issued ban, expiry) to reduce false positives. Consider pseudonymizing or truncating stored IPs if privacy rules or policies require it; that choice affects whether you can perform precise range checks.

Recommended Answers

All 4 Replies

Member Avatar for Dani

Currently I store IP addresses as Varchar.

Binary is meant for binary data such as images, executables, etc. Not text.

Member Avatar for jwenting

Store them as strings. Unless you want to do the string to numeric conversion every time that turns the familiar string representation into its numerical format that's the way to go.

But beware that an ip address isn't the best way to go. Not only is it very easy to get a new one, it's also quite likely that multiple people have the same ip address (for example internet cafes, student dorms, etc.).
MAC address is far better, and if you're having paid accounts, username...

Member Avatar for Vikas_30

varchar is easier to use, so I will suggest to use it for storing IP address.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.