Thursday, November 10, 2016

MySQL - numeric types don't let you specify "length"

In MySQL I often see developers asking themselves, "Should I make this column an int(10) or int(11)? Should this bigint be bigint(15) or bigint(20)?" The answer is it probably doesn't matter! That number in parentheses does NOT indicate the maximum length of the number. For example, smallint(3) does not max out at 999, but rather it has a range of -32768 to -32767, just like any other smallint (which is 16 bits). The ONLY thing this parenthetical number has anything to do with is an esoteric, display-related feature called ZEROFILL.

So, in my opinion you should NEVER even bother to define a column as "int(10)" or "bigint(20)" etc., etc., but rather just define an "int" or a "bigint". The only way to express a max length is in terms of which type to use in the first place: tinyint, smallint, int, bigint. (I think that's all of them - int types anyway. :)) So save yourself the clutter of useless additional information, and save yourself the waste of brainpower thinking about max lengths that aren't even real.

About Me

DBA/Developer working with a slew of databases, but most passionate about Oracle and Postgres. Big proponent of maximizing your database investment by taking advantage of the powerful features it has to offer. Big opponent of using your database as a generic, lowest common denominator bit bucket.