Tiny int store limited values in db

Question

I've created field named e2s with tinyint datatype. When I store the value of 500 it converts it into 127. Now I changed it to smallint. It stored value 500. Why? What is the difference between int, tinyint, smallint, mediumint, bigint to store values.

The difference is in the amount of bits used to store it. Tinyint uses 8 bit to store the value which maps to a range of -128 to 127 . — Christoph Diegelmann, Sep 18 '13 at 05:54
This question appears to be off-topic because it is about a failure to even consult [the documentation](http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html) — Phil, Sep 18 '13 at 05:57
@Phil you could say rtfm to a majority of qUestions around here — necromancer, Sep 18 '13 at 06:01
@user2727841 The number in parenthesis is **not** *length*. See http://stackoverflow.com/questions/8045327/is-there-a-difference-in-using-int1-vs-tinyint1-in-mysql/8045378#8045378 — Phil, Sep 18 '13 at 06:01
@Phil that's behavior worthy of an ... thanks for opening up. — necromancer, Sep 18 '13 at 06:07
Here is a table of the numeric types and their ranges: https://stackoverflow.com/a/52104781/2430549 — HoldOffHunger, Feb 21 '20 at 20:40

score 1 · Accepted Answer · answered Sep 18 '13 at 06:00

Take a look at http://dev.mysql.com/doc/refman/5.0/en/integer-types.html. TINYINT only stores 1 byte of data, thus allowing the integer range of storage from -128 to 127.

SMALLINT in other hand uses 2 bytes of storage, having a much wider range from -32768 to 32767.

Be aware of what you are going to store in TINYINT or SMALLINT columns. For instance, it's a bad idea to set an auto_increment PK column to SMALLINT datatype as you could easily overwhelm it's capacity.

Thanks. Now I've understand – user2727841 Sep 18 '13 at 06:05 — user2727841, Sep 18 '13 at 06:05

score 0 · Answer 2 · answered Sep 18 '13 at 05:59

0

Please see MySql reference on integer types:

http://dev.mysql.com/doc/refman/5.1/en/integer-types.html

It shows you the minimum and maximum values you can store.

answered Sep 18 '13 at 05:59

Grzegorz

3,207
3
20
43

Tiny int store limited values in db

2 Answers2