如何在MySQL的单列中存储128位数字? [英] How to store a 128 bit number in a single column in MySQL?

问题描述

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

I'm changing some tables to store IP addresses as numbers rather than strings. This is simple with IPv4 where the 32 bit address can fit into an integer column. However, an IPv6 address is 128 bits.

MySQL文档仅显示数字类型到64位("bigint").

The MySQL documentation only shows numeric types up to 64 bits ("bigint").

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

Should I stick with char/varchar for IPv6? (Ideally I'd like to use the same column for IPv4 and IPv6, so I'd prefer not to do this).

有什么比使用两个bigint列更好的了吗?我希望每次使用地址时都不必将值分为上下/64.

Is there anything better than using two bigint columns? I would prefer not to have to break the value into upper and lower /64 whenever using the address.

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

I'm using MariaDB 5.1 - if there's a better solution in a later version of MySQL then that would be nice to know, although not helpfully immediately.

请注意，我是推荐使用最佳方法的人-很明显，有多种方法可以做到这一点(包括现有的字符串表示形式)，但是(就性能而言)哪个最好? (即，如果有人已经进行了分析，那将省去我的忙，或者如果我遗漏了明显的东西，也很高兴知道这一点.)

Note that I'm after a recommendation for the best way to do this - it's obvious that there are various ways of doing this (including the existing string representation), but which is (in terms of performance) best? (i.e. if someone has done the analysis already, that would save me doing it, or if I'm missing something obvious, that would be great to know too).

推荐答案

我发现自己在问这个问题，在我读过的所有文章中都没有发现任何性能比较.所以这是我的尝试.

I found myself asking this question and from all the posts I read never found any performance comparisons. So here's my attempt.

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

I've created the following tables, populated with 2,000,000 random ip address from 100 random networks.

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秒，而二进制表上的平均响应时间约为百分之一秒.

Then I SELECT all ip addresses for each network and record the response time. Average response time on the twobigints table is about 1 second while on the binary table it is about one-hundredth of a second.

这是查询.

注意:

X_ [HIGH/LOW]是X的最高/最低有效64位

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

当NETMASK_LOW为0时，将省略AND条件，因为它始终会产生true.不会对性能产生很大影响.

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

这篇关于如何在MySQL的单列中存储128位数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！