Home > Admin > MySQL 101 : Numeric type attributes and maximum value

MySQL 101 : Numeric type attributes and maximum value

I would like to make a quick MySQL 101 about numeric types in MySQL and especially explain the difference between the numeric type attributes and the maximum value that a numeric type can store.

Someone asked me yesterday why when he inserts a value in a table, the value 127 appears instead of the value 160 that he’s just inserted.
Ok, let me explain, consider this table :

What happens if you want to insert this row in the table :

sql > insert into product values (160, 554, "iPad mini");
Query OK, 1 row affected, 1 warning (0.01 sec)
sql > show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

sql > select * from product;
+-----+----------+-----------+
| id  | quantity | label     |
+-----+----------+-----------+
| 127 | 554      | iPad mini |
+-----+----------+-----------+
1 row in set (0.00 sec)

The result of the insert command suggest that a warning was detected during the insert process.
The warning message indicates that the value we wanted to insert in the id column was too high. There isn’t any mention of the quantity column.
And when I wanted to display the contents of the table, the value 127 appears instead of the value 160 previously inserted.

  • Value 160 inserted in a tinyint(8) column => 127 was stored in the table
  • Value 554 inserted in a int(200) column => 554 was stored in the table

The two values we wanted to insert for the id and the quantity columns were more important than the value of the type attribute.
But why there wasn’t any error for the quantity column ?

Simply because that attribute is not used to determine the maximum value that a numeric type can store.
The numeric type attribute may be used only by applications, not by MySQL.
The width of this attribute is present in the metadata returned with result sets of the query.

In our example, there was a warning for the id column and the data was truncated because the maximum value that a signed tinyint can store is 127.
Take a look at this table that shows the maximum values for each numeric types :
(Source : Official MySQL documentation)

You can see that the maximum value for a signed int is much larger than the value that we wanted to insert in the quantity column.

This question comes up frequently, hope this post can help.

Congratulations, you've read this post till the end !
If you enjoyed this article, please leave a comment


You can also subscribe to RSS feed or by email to automatically receive real-time notification of new posts

Thanks for your visit on MySQL+. Cédric

Categories: Admin
  1. November 21st, 2012 at 08:25 | #1

    Hi!

    See also Roland Bouman’s post on evaluating INTEGER limits.

  2. November 21st, 2012 at 22:38 | #2

    And the solution is the STRICT_ALL_TABLES sql_mode!

    mysql> create table tinyinttest(id tinyint(1));
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into tinyinttest values(100),(200),(300);
    Query OK, 3 rows affected, 2 warnings (0.00 sec)
    Records: 3 Duplicates: 0 Warnings: 2

    mysql> select * from tinyinttest;
    +——+
    | id |
    +——+
    | 100 |
    | 127 |
    | 127 |
    +——+
    3 rows in set (0.00 sec)

    mysql> truncate table tinyinttest;
    Query OK, 0 rows affected (0.01 sec)

    mysql> set sql_mode=STRICT_ALL_TABLES;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into tinyinttest values(100),(200),(300);
    ERROR 1264 (22003): Out of range value for column ‘id’ at row 2
    mysql>

    • November 22nd, 2012 at 14:39 | #3

      Of course sql_mode can help but it remains an unknown parameter.
      And I’m not sure this is the solution, in some cases, it’s better to let MySQL insert a wrong value rather than return an error.

  3. November 27th, 2012 at 10:08 | #4

    Hi
    I m curious to know how and why “applications” uses this width attribute. Any link? And what kind of app ?
    Thanks!

  1. November 23rd, 2012 at 07:02 | #1
  2. January 7th, 2013 at 11:34 | #2