MySQL BETWEEN 在无符号 bigint 上

发布于 2024-09-17 23:11:34 字数 1058 浏览 10 评论 0原文

我正在开发一个具有 IPv6 模型的 Rails 应用程序。我将 IPv6 地址存储在 2 个 32 位整数和一个 64 位整数中：

+-----------------------+---------------------+------+-----+---------+----------------+
| Field                 | Type                | Null | Key | Default | Extra          |
+-----------------------+---------------------+------+-----+---------+----------------+
| global_routing_prefix | int(11) unsigned    | YES  |     | NULL    |                | 
| subnet_identifier     | int(11) unsigned    | YES  |     | NULL    |                | 
| interface_identifier  | bigint(20) unsigned | YES  |     | NULL    |                |

不幸的是，当我在某个范围内查找 IP 时，MySQL 会使用带符号的 bigint 进行所有算术运算，因此：

mysql> select 2 BETWEEN 0 AND 18446744073709551614;
+--------------------------------------+
| 2 BETWEEN 0 AND 18446744073709551614 |
+--------------------------------------+
|                                    0 | 
+--------------------------------------+

有什么解决办法吗？，或者我是否需要将 interface_identifier 分成 2 个无符号整数？

谢谢，唐纳德

原文

I'm working on a rails app that has an IPv6 model. I'm storing the IPv6 address in 2 32-bit ints and a 64-bit int:

+-----------------------+---------------------+------+-----+---------+----------------+
| Field                 | Type                | Null | Key | Default | Extra          |
+-----------------------+---------------------+------+-----+---------+----------------+
| global_routing_prefix | int(11) unsigned    | YES  |     | NULL    |                | 
| subnet_identifier     | int(11) unsigned    | YES  |     | NULL    |                | 
| interface_identifier  | bigint(20) unsigned | YES  |     | NULL    |                |

Unfortunately, when I go to find IPs in a range, MySQL does all arithmetic with signed bigints, so:

mysql> select 2 BETWEEN 0 AND 18446744073709551614;
+--------------------------------------+
| 2 BETWEEN 0 AND 18446744073709551614 |
+--------------------------------------+
|                                    0 | 
+--------------------------------------+

Is there a work around I can do, or do I need to split up by interface_identifier into 2 unsigned ints?

Thanks,
Donald

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海螺姑娘 2024-09-24 23:11:34

丑陋的解决方案：

select 2 BETWEEN 0 AND CAST(18446744073709551614 AS DECIMAL);

MontyAB 的人员正在为 MariaDB/MySQL 开发原生 IPv6 列类型。如果您可以支持他们，我们可能会更快提供此功能。 ;)

Ugly solution:

select 2 BETWEEN 0 AND CAST(18446744073709551614 AS DECIMAL);

People at MontyAB are working on native IPv6 column type for MariaDB/MySQL. If you can support them, we're likely to have this feature sooner. ;)

回复收藏 0 原文

绾颜 2024-09-24 23:11:34

不使用 BETWEEN 似乎可以工作

mysql> select 0 <= 2 and 2 <= 18446744073709551614;
+--------------------------------------+
| 0 <= 2 and 2 <= 18446744073709551614 |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

Doing it without using BETWEEN seems to work

mysql> select 0 <= 2 and 2 <= 18446744073709551614;
+--------------------------------------+
| 0 <= 2 and 2 <= 18446744073709551614 |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

回复收藏 0 原文

~没有更多了~