6

I've been running a small web-based experiemnt using Facebook UIDs to verify unique users.

Recently I've discovered that UIDs can be bigger than I realised among some users, so my int-based system is now inadequate and I need to convert to bigint.

I can't risk losing the results I already have, but need to convert the table so that the index containing the uid is now bigint. Are there any particular issues changing the type of an index column, and would it be as simple as:

ALTER TABLE breadusers MODIFY userid bigint;

?

Hal Clive
  • 137
  • 1
  • 1
  • 9
  • Yes, go ahead and change to BIGINT, nothing will break. – Pentium10 Mar 20 '11 at 16:54
  • Thanks - sorry, it was a rather basic question, but when trying to research an answer I read things suggesting that changing the index type may have complicated things. I suppose may be to do with problems where changing, for example, from string to int... – Hal Clive Mar 20 '11 at 17:11
  • Also note that if you have foreign key references you probably have to drop the columns and recreate see http://stackoverflow.com/questions/9018286/mysql-alter-int-column-to-bigint-with-foreign-keys – lrkwz Feb 03 '14 at 14:14
  • **Always** make a backup before running ALTER TABLE. A simple mysqldump will do the job. – bjunix Jan 28 '15 at 11:18

2 Answers2

3

In theory this should be absolutely fine, although it the data really matters, I presume you have a recent backup anyway in case something goes awry.

That said, I'd probably be tempted to store the Facebook UID as a string (i.e.: in a VARCHAR field) and simply have a generic auto-incremented ID field. Then again, that's an answer to a different question. :-)

John Parker
  • 54,048
  • 11
  • 129
  • 129
0

For the Facebook UID part, I would suggest you to go for BIGINT(64). Here is the answer from Facebook Blog: https://developers.facebook.com/blog/post/45/

Faizan Noor
  • 846
  • 10
  • 11
  • 1
    It's not `bigint(64)`, it's `bigint(20)`. Facebook only said to use `bigint` since as it is a 64 bit integer. As for the sql syntax itself, it sould be `bigint(20)` since maximum number in `unsigned bigint` is `18,446,744,073,709,551,615` (a 20 digit numbers). Note that the `20` in `bigint(20)` is only for formatting purpose. It does not affect anything in term of data length (in byte or bit). Bigint always stored in 64 bit a.k.a 8 byte data. Similar to `long` and `unsigned long` in any programming languages. – Firanto Oct 10 '16 at 09:28