Limit of 127 Points to Wrong Numeric Type

Sometimes the simple things evade us when we’re caught up in the details. Thank goodness for manuals.

It turns outs that the numeric type that you select for your database variables matters greatly – DUH!

I had an occurrence last week where a client entered values over 200 in a form, yet when the numbers were stored in the MySQL database were capped at 127. At first the error wasn’t spotted but when multiple entries were reported with the same 127, it became obvious that something was wrong.

My error was using tinyint instead of smallint as the numeric data type when setting up the MySQL table.

The MySQL Manual clearly gives us limits for each data type, as found in the following table:

Numeric Data Types in MySQL
Numeric Data Types in MySQL

I don’t think we’ll have an occasion to use BIGINT, but the other data types will definitely be of use.

When all else fails, RTFM!

Leave a Reply

Your email address will not be published. Required fields are marked *