mysql - 如何在 MySQL 的单列中存储 128 位数字？

Question

我正在更改一些表格以将 IP 地址存储为数字而不是字符串。这对于 IPv4 很简单，其中 32 位地址可以放入整数列中。但是，IPv6 地址是 128 位的。

MySQL documentation仅显示最多 64 位的数字类型(“bigint”)。

对于 IPv6，我应该坚持使用 char/varchar 吗？ (理想情况下，我希望对 IPv4 和 IPv6 使用同一列，所以我不想这样做)。

还有什么比使用两个 bigint 列更好的吗？我宁愿在使用地址时不必将值分成上下/64。

我正在使用 MariaDB 5.1 - 如果在更高版本的 MySQL 中有更好的解决方案，那么很高兴知道，尽管不是立即有用。

[编辑]请注意，我正在寻求最佳方法的建议 - 显然有多种方法可以做到这一点(包括现有的字符串表示)，但哪个(就性能而言)最好？ (即，如果有人已经完成了分析，那将节省我的时间，或者如果我遗漏了一些明显的东西，那也很高兴知道)。

Answer 1

我发现自己在问这个问题，并且从我阅读的所有帖子中都没有发现任何性能比较。所以这是我的尝试。

我创建了以下表格，其中填充了来自 100 个随机网络的 2,000,000 个随机 IP 地址。

CREATE TABLE ipv6_address_binary (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    addr BINARY(16) NOT NULL UNIQUE
);

CREATE TABLE ipv6_address_twobigints (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    haddr BIGINT UNSIGNED NOT NULL,
    laddr BIGINT UNSIGNED NOT NULL,
    UNIQUE uidx (haddr, laddr)
);

CREATE TABLE ipv6_address_decimal (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    addr DECIMAL(39,0) NOT NULL UNIQUE
);

然后我选择每个网络的所有 ip 地址并记录响应时间。 twobigints 表的平均响应时间约为 1 秒，而二进制表的平均响应时间约为百分之一秒。

这里是查询。

Note:

X_[HIGH/LOW] is the most/least significant 64-bits of X

when NETMASK_LOW is 0 the AND condition is omitted as it always yields true. doesn't affect performance very much.

SELECT COUNT(*) FROM ipv6_address_twobigints
WHERE haddr & NETMASK_HIGH = NETWORK_HIGH
AND laddr & NETMASK_LOW = NETWORK_LOW

SELECT COUNT(*) FROM ipv6_address_binary
WHERE addr >= NETWORK
AND addr <= BROADCAST

SELECT COUNT(*) FROM ipv6_address_decimal
WHERE addr >= NETWORK
AND addr <= BROADCAST

平均响应时间:

图表:

BINARY_InnoDB  0.0119529819489
BINARY_MyISAM  0.0139244818687
DECIMAL_InnoDB 0.017379629612
DECIMAL_MyISAM 0.0179929423332
BIGINT_InnoDB  0.782350552082
BIGINT_MyISAM  1.07809265852