The Ultimate Guide to Storing IP Addresses in MySQL: Save 60% Space & Get an 8x Speed Boost!

## Background When developing applications, recording user IP addresses is a common requirement for tasks like log analysis, security auditing, or geolocation. However, how to efficiently store IP addresses in MySQL, especially when needing to support both IPv4 and IPv6, is a technical issue worth exploring. This article, brought to you by DP@lib00, provides a detailed analysis of the pros and cons of different storage schemes and offers best practices. ## Scenario 1: Storing IPv4 Addresses Only If your application scenario only requires IPv4 support, you have two main options. ### 1. String Storage (VARCHAR) The most intuitive method is to use `VARCHAR(15)` to store the IP address in its dot-decimal notation, such as '192.168.1.1'. - **Pros**: Highly readable, can be viewed directly without conversion. - **Cons**: Larger storage footprint, lower index efficiency, and complex, inefficient range queries. ### 2. Integer Storage (INT UNSIGNED) A more optimized approach is to use MySQL's `INET_ATON()` function to convert the IP address into an `INT UNSIGNED` for storage, and use the `INET_NTOA()` function to convert it back to a string format. ```sql -- Conversion: '192.168.1.1' -> 3232235777 SELECT INET_ATON('192.168.1.1'); -- Reverse conversion SELECT INET_NTOA(3232235777); ``` The conversion principle treats the four parts of the IP address as a base-256 number: `192*256³ + 168*256² + 1*256¹ + 1*256⁰ = 3232235777` ### IPv4 Storage Comparison (1 Million Records) | Storage Scheme | Data Type | Space per Record | Total Space for 1M Records (incl. index) | Advantage | |--------------------|----------------|-------------------|------------------------------------------|-----------| | **String** | `VARCHAR(15)` | 8-16 bytes | ~27 MB | Readability | | **Numeric (Rec.)** | `INT UNSIGNED` | **4 bytes (fixed)** | **~10 MB** | **~63% space saving, faster queries** | The conclusion is clear: for a pure IPv4 environment, using `INT UNSIGNED` is a win-win for both space and performance. ## Scenario 2: The Modern Solution for IPv4 & IPv6 Compatibility With the adoption of IPv6, modern applications must consider supporting it. An IPv6 address is 128 bits long, far exceeding the storage capacity of `INT` or even `BIGINT`, thus requiring a new solution. ### Recommended Solution: `BINARY(16)` + `INET6_ATON()` Since MySQL 5.6.3, the `INET6_ATON()` and `INET6_NTOA()` functions were introduced, perfectly solving the unified storage problem for IPv4 and IPv6. These functions can convert either an IPv4 or IPv6 address into a 16-byte binary string (`BINARY(16)` or `VARBINARY(16)`). **Why `BINARY(16)`?** - An IPv6 address is 128 bits, and 128 bits = 16 * 8 bits, which is exactly 16 bytes. - `INET6_ATON()` automatically maps IPv4 addresses into an IPv6-compatible format, allowing the same set of functions to handle both address types. ### Practical Code Examples 1. **Create Table** ```sql -- Recommended table structure from wiki.lib00.com CREATE TABLE user_logs_lib00 ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ip_address BINARY(16) NOT NULL COMMENT 'Stores IPv4/IPv6 addresses', log_data TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_ip (ip_address) ) ENGINE=InnoDB; ``` 2. **Insert Data** ```sql -- Insert an IPv4 address INSERT INTO user_logs_lib00 (ip_address, log_data) VALUES (INET6_ATON('192.168.1.1'), 'User login'); -- Insert an IPv6 address INSERT INTO user_logs_lib00 (ip_address, log_data) VALUES (INET6_ATON('2001:0db8:85a3::8a2e:0370:7334'), 'API access'); ``` 3. **Query Data** When querying, use `INET6_NTOA()` to convert the binary data back into a readable IP address string. ```sql SELECT id, INET6_NTOA(ip_address) AS readable_ip, log_data FROM user_logs_lib00; -- Conditional queries are equally efficient SELECT * FROM user_logs_lib00 WHERE ip_address = INET6_ATON('192.168.1.1'); ``` ### Hybrid Storage Space & Performance Comparison (1 Million Records) Assuming a 50/50 split between IPv4 and IPv6 data. | Comparison Metric | `VARCHAR(45)` | `BINARY(16)` (Recommended) | Advantage | |-------------------|---------------|----------------------------|--------------------------| | **Total Space** | ~65 MB | **~36 MB** | **~45% space saving** | | **Exact Match** | ~120 ms | **~15 ms** | **8x performance boost** | | **Range Query** | ~450 ms | **~80 ms** | **5.6x performance boost** | | **Sort/JOIN** | Slower | **4-5x faster** | **Significant speedup** | ## Decision and Summary To help you choose, here's a simple decision tree: - **Do you need to support IPv6?** - **Yes** -> Without a doubt, choose `BINARY(16)` + `INET6_ATON()` / `INET6_NTOA()`. - **No** -> - **Large dataset or high-performance requirements?** -> Choose `INT UNSIGNED` + `INET_ATON()` / `INET_NTOA()`. - **Small dataset where readability is the top priority?** -> You could consider `VARCHAR(15)`, but it's not recommended for production environments. **Final Recommendation**: In today's internet landscape, we strongly recommend adopting the `BINARY(16)` solution from the outset. It not only future-proofs your application for IPv6 compatibility but also far surpasses traditional string storage in both space efficiency and performance. This is the validated best practice from the lib00 project team.