MySQL Facebook user_id : big_int、int 还是 string？

Facebook user_id : big_int, int or string?

提问by Mark

Facebook's user id's go up to 2^32 .. which by my count it 4294967296.

Facebook 的用户 ID 上升到 2^32 .. 据我计算，它是 4294967296。

mySQL's unsigned int's range is 0 to 4294967295 (which is 1 short - or my math is wrong) and its unsigned big int's range is 0 to 18446744073709551615

mySQL 的无符号整数的范围是 0 到 4294967295（这是 1 短 - 或者我的数学是错误的），其无符号大整数的范围是 0 到 18446744073709551615

int = 4 bytes, bigint = 8 bytes

int = 4 字节，bigint = 8 字节

OR

或者

Do I store it as a string?

我是否将其存储为字符串？

varchar(10) = ? bytes

varchar(10) = ? 字节

How will it effect efficiency, I heard that mysql handle's numbers far better than strings (performance wise). So what do you guys recommend

它将如何影响效率，我听说 mysql 句柄的数字比字符串好得多（性能方面）。所以大家有什么推荐

回答by Phil Wallach

Because Facebook assigns the IDs, and not you, you mustuse BIGINTs.

因为 Facebook 分配 ID 而不是您，所以您必须使用 BIGINT。

Facebook does not assign the IDs sequentially, and I suspect they have some regime for assigning numbers.

Facebook 不会按顺序分配 ID，我怀疑他们有一些分配号码的制度。

I recently fixed exactly this bug, so it is a real problem.

我最近正好修复了这个错误，所以这是一个真正的问题。

I would make it UNSIGNED, simply because that is what it is.

我会把它设为 UNSIGNED，因为它就是这样。

I would notuse a string. That makes comparisons painful and your indexes clunkier than they need to be.

我不会使用字符串。这使比较变得痛苦，并且您的索引比它们需要的更笨拙。

回答by Jeff Conrad

You can't use INT any more. Last night I had two user ids that maxed out INT(10).

你不能再使用 INT 了。昨晚我有两个用户 ID 达到了 INT(10)。

回答by Elmer

I use a bigint to store the facebook id, because that's what it is.

我使用 bigint 来存储 facebook id，因为它就是这样。

but internally for the primary and foreign keys of the tables, i use a smallint, because it is smaller. But also because if the bigint should ever have to become a string (to find users by username instead of id), i can easily change it.

但在内部对于表的主键和外键，我使用 smallint，因为它更小。但也因为如果 bigint 必须成为一个字符串（通过用户名而不是 id 查找用户），我可以轻松更改它。

so i have a table that looks like this:

所以我有一张看起来像这样的表：

profile
- profile_key smallint primary key
- profile_name varchar
- fb_profile_id bigint

and one that looks like this

和一个看起来像这样

something_else
- profile_key smallint primary key
- something_else_key smallint primary key
- something_else_name varchar

and my queries for a singe page could be something like this:

我对单个页面的查询可能是这样的：

select profile_key, profile_name
from profile
where fb_profile_id = ?

now i take the profile_key and use it in the next query

现在我使用 profile_key 并在下一个查询中使用它

select something_else_key, something_else_name
from something_else
where profile_key = ?

the profile table almost always gets queried for almost any request anyway, so i don't consider it an extra step.

无论如何，几乎所有请求都会查询配置文件表，所以我不认为这是一个额外的步骤。

And ofcourse it is also quite ease to cache the first query for some extra performance.

当然，缓存第一个查询以获得一些额外的性能也很容易。

回答by Vivek Gounder

If you are reading this in 2015 when facebook has upgraded their API to 2.0 version. They have added a note in their documentation stating that their ids would be changed and would have an app scope. So maybe there is huge possibility later in the future that they might change all the ids to Alpha numeric.

如果您是在 2015 年 facebook 将其 API 升级到 2.0 版本时阅读本文的。他们在他们的文档中添加了一个注释，说明他们的 id 将被更改并具有应用程序范围。因此，也许将来他们可能会将所有 id 更改为字母数字的可能性很大。

https://developers.facebook.com/docs/apps/upgrading#upgrading_v2_0_user_idsSo I would suggest to keep the type to varchar and avoid any future migration pains

https://developers.facebook.com/docs/apps/upgrading#upgrading_v2_0_user_ids所以我建议将类型保留为 varchar 并避免任何未来的迁移痛苦

回答by Polaris878

Your math is a little wrong... remember that the largest number you can store in N bytes is 2^(N) - 1... not 2^(N). There are 2^N possiblenumbers, however the largest number you can store is 1 less that.

你的数学有点错误......请记住，你可以存储在 N 个字节中的最大数字是 2^(N) - 1......而不是 2^(N)。有 2^N 个可能的数字，但是您可以存储的最大数字比它少 1。

If Facebook uses an unsigned big int, then you should use that. They probably don't assign them sequentially.

如果 Facebook 使用 unsigned big int，那么您应该使用它。他们可能不会按顺序分配它们。

Yes, you could get away with a varchar... however it would be slower (but probably not as much as you are thinking).

是的，你可以摆脱 varchar ......但是它会更慢（但可能没有你想象的那么多）。

回答by dleavitt

Store them as strings.

将它们存储为字符串。

The Facebook Graph API returns ids as strings, so if you want comparisons to work without having to cast, you should use strings. IMO this trumps other considerations.

Facebook Graph API 将 ID 作为字符串返回，因此如果您希望比较无需强制转换即可工作，则应使用字符串。IMO 这胜过其他考虑。

回答by nickf

I would just stick with INT. It's easy, it's small, it works and you can always change the column to a larger size in the future if you need to.

我会坚持使用 INT。这很容易，它很小，它可以工作，如果需要，您可以在将来随时将列更改为更大的尺寸。

FYI:

供参考：

VARCHAR(n) ==> variable, up to n + 1 bytes
CHAR(n) ==> fixed, n bytes

VARCHAR(n) ==> variable, up to n + 1 bytes
CHAR(n) ==> fixed, n bytes

回答by Tarydon

Unless you expect more than 60% of the world's population to sign up, int should do?

除非你预计全球60%以上的人口会注册，否则int应该怎么做？