What's the best column type for Google+ User ID?

Google+ has very long User IDs

104560124403688998123

(21 characters), which is not possible to input into BIGINT fiel开发者_如何学God (not unsigned)

What column type would you use for such IDs ?

I do not think that varchar is good idea

if the length of the google id is predictable, use a static char(length), add an index on it and create an (internal) integer primary key. Inside of your application you map the data to the integer primary key. So if someone searches by google id, you lookup the integer primary key for this google id and do the rest of your queries with the integer primary key.

So the schema looks like:

Mapping Table:

id (integer) | google_id (char(length)) 

So if you create another table like comments etc, use the primary key id, if you want to lookup all comments for a certain googleid, get the internal id first and then join the comments. This way you have only one query criteria on a static, indexed char field with a predictable length, all other joins and queries will use the integer key.

I would use the same as I do with Facebook: varchar.

I cannot find official recommendation from google but i use of decimal 21,0 works correctly.

in python on appengine I save data with ndb.StringProperty and validate with a regex \d{15} hence giving a little margin. it would be really great to know what it the REAL regex to validate this field

I would recommend a VARCHAR(255) (a variable length string up to 255 characters). The reason is because the id could be a ASCII string that is up to 255 chars long, see reference below.

Google OpenID Connect
"Authenticating the user involves obtaining an ID token and validating it. ID tokens are a standardized feature of OpenID Connect designed for use in sharing identity assertions on the Internet."
OpenID Connect Core 1.0 incorporating errata set 1
sub: (is the google id)
"REQUIRED. Subject Identifier. A locally unique and never reassigned identifier within the Issuer for the End-User, which is intended to be consumed by the Client, e.g., 24400320 or AItOawmwtWwcT0k51BayewNvutrJUqsvl6qs7A4. It MUST NOT exceed 255 ASCII characters in length. The sub value is a case sensitive string."