1

Besides using MySQL's DESCRIBE syntax and parsing the Type column, is there any way to programmatically determine what's the largest integer that can be stored in a given INT column and its varieties? (e.g. TINYINT, MEDIUMINT, etc.) I'd simply store a hard-coded table, except that my columns use custom lengths like TINYINT(2) and MEDIUMINT(5).

I ask because I'm thinking of writing a cron job to warn me when my various auto-incrementing integer-based ID columns approach their maximum values, so I can reduce the risk of integer overruns.

Edit

I see this was kind of a dumb question. As was pointed out to me, the custom lengths don't affect the maximum integer values that those columns can store. So if I wanted to write the script, I can hard-code the maximum integer sizes given in the MySQL manual. As to whether this script is the best way to handle things, that's debatable.

curtisdf
  • 4,130
  • 4
  • 33
  • 42
  • Why would you ever use variants like that? If you're worried about roll-overs, your datatype is *way* too small. – tadman Apr 08 '13 at 17:45
  • Programmatically? Just [read the manual](http://dev.mysql.com/doc/refman/5.0/en/integer-types.html) – Kermit Apr 08 '13 at 17:46
  • Use INT(11) and by the time you get to this number, I'm sure you would have solved this problem – Ibu Apr 08 '13 at 17:46
  • 4
    Those "custom lengths" are merely display width for `ZEROFILL` padding. They do not affect the range of values that can be stored in the column. – eggyal Apr 08 '13 at 17:46
  • @Ibu Why create the overhead? – Kermit Apr 08 '13 at 17:46
  • @FreshPrinceOfSO it won't be overhead if he is expecting big numbers – Ibu Apr 08 '13 at 17:48
  • @Ibu Then OP shouldn't be using `tinyint`. – Kermit Apr 08 '13 at 17:48
  • Wow, quick replies everyone. Thanks. I take it the answer to my question is "no", and that I'd have to parse `DESCRIBE` output or switch to using much larger `INT`-type columns. I'll have to think more about what works best for my situation. – curtisdf Apr 08 '13 at 17:53
  • Oh. Okay, thanks `eggyal` for pointing out that the custom lengths don't affect the max integer values. Learned something today! :-) – curtisdf Apr 08 '13 at 18:15

1 Answers1

1

The custom lengths DO NOT affect the max value, in the case of INTs they are merely a hint for visually outputting/displaying the value AND are ONLY used when autofill leading zeroes is being used.

Please see: https://stackoverflow.com/a/6817537/330315 and here: https://stackoverflow.com/a/5634151/330315 and here: https://stackoverflow.com/a/4432037/330315 and here: https://stackoverflow.com/a/7048597/330315 (tnx to a_horse_with_no_name for once giving me these links.)

Community
  • 1
  • 1
nl-x
  • 11,762
  • 7
  • 33
  • 61