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

问题描述

我们正在将一些数据从MySQL迁移到Mongo，并已在

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位数字)；根据docs(您引用的URL相同)的描述，DECIMAL/NUMERIC列将每组9个base10数字打包为四个字节(前27个数字总计为floor(29 / 9) * 4 = 3 * 4 = 12个字节)，其余2个数字为一个额外的字节，最多为DECIMAL(29)列存储13个字节.

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).

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