Alter table column to bigint

What is the difference between doing …

ALTER TABLE foobar CHANGE COLUMN id id BIGINT;

versus doing …

ALTER TABLE foobar CHANGE COLUMN id id BIGINT(15);

Which is preferred?

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

M indicates the maximum display width for integer types. The maximum legal display width is 255. Display width is unrelated to the range of values a type can contain, as described in Section 10.2, “Numeric Types”.

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

artcoder, under what circumstances do you foresee having over 2 billion rows in your table?

i don’t think just changing the id to BIGINT is going to be the last of your worries…

Authorize.net transaction id is claimed to be near the point where it may exceed the range of the INT at 2147483647 in the near future. Hence the BIGINT.

Also found on the mysql doc

This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.
...
The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.
...
if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width.

Since the default width of BIGINT is 20. I think I should leave it well alone and do BIGINT instead of BIGINT(15).

what is authorize.net? who made the claim? how does this relate to your problem?

by the way, the default “width” of BIGINT is not 20

Authorize.net is the a payment gateway. Often can be used in third party shopping carts like [URL=“http://www.zencart.com/”]ZenCart. I saw the claim that its transaction id might exceed the value of INT in this [URL=“http://www.zen-cart.com/forum/showthread.php?t=105924”]thread here.

i would say that a good alternate strategy is INTEGER UNSIGNED

that will take you to 4 billion

how long has authorize.net been issuing numbers? you have about that much more time before going to some other datatype