有没有人找到一种将 BSON ObjectId 值存储在 SQL 数据库中的有效方法? [英] Has anyone found an efficient way to store BSON ObjectId values in an SQL database?

问题描述

我们正在将一些数据从 MySQL 迁移到 Mongo，并一直在为我们的一些数据库表添加额外的字段以用于 ObjectId 迁移数据对象的值.

We are migrating some data from MySQL to Mongo and have been adding extra fields to some of our DB tables for the ObjectId value of the migrated data objects.

目前我们将它们存储为 varchar(24) latin1_general_ci ，它工作正常.

At the moment we are storing them as varchar(24) latin1_general_ci which works fine.

然而，为了有效的存储/索引，我们可能应该转换回 12 字节的二进制值.不幸的是，MySQL 似乎只有 原生整数支持最多 8 个字节.还有另一种选择 - 也许是二进制?

However, for efficient storage/indexing we probably should convert back to the 12-byte binary value. Unfortunately, MySQL only seems to have native integer support up to 8 bytes. Is there another option - binary perhaps?

更新:我正在从 MySQL 迁移，但目前只迁移某些表.所以我首先在某个表的 Mongo 中复制对象.然后我回到 MySQL 中剩下的任何其他表，这些表具有对旧 MySQL 表 ID 的外键引用，并添加一个新的引用字段来存储 Mongo 对象的 ID.然后，我将删除原始参考字段 BIGINT.

Update: I am migrating from MySQL but only certain tables at the moment. So I first make duplicates of the objects in Mongo of a certain table. Then I go back to any other tables left in MySQL that have foreign key references to that old MySQL table ID and add a new reference field that will store the Mongo object's ID. I will then remove the original reference field the BIGINT.

更新 2: 我提出这个问题的原因是，我们可能需要再过 12 个月(或永远不会)才能移动这些其他数据，因此它会对实时 Web 应用程序的性能产生影响在那之前.

Update 2: The reason I bring this up is that it may be 12 months more (or never) before we move this other data, so it will have a performance impact on the live web application until then.

推荐答案

12 字节无符号整数的范围为 0..79228162514264337593543950335(最多 29 位)；根据文档(您引用的相同 URL)， DECIMAL/NUMERIC 列将每组九个 base10 数字打包成四个字节(加起来 floor(29/9) * 4 = 3 * 4 = 12 字节(前 27 位数字)，其余 2 位数字占用一个额外字节，DECIMAL(29)DECIMAL(29)列.

The range of a 12-byte unsigned int would be 0..79228162514264337593543950335 (29 digits max); according to docs (same URL you cited), a DECIMAL/NUMERIC column packs each group of nine base10 digits into four bytes (adding up to floor(29 / 9) * 4 = 3 * 4 = 12 bytes for the first 27 digits), with the remaining 2 digits taking an additional byte, coming up to 13 bytes of storage for a DECIMAL(29) column.

或者，您可以将它们存储为 BINARY(12).

Or, you can store them as BINARY(12).

这篇关于有没有人找到一种将 BSON ObjectId 值存储在 SQL 数据库中的有效方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！