Number near by data type in mysql

Question

I read so many SO posts still I didn't get the point.

I have created a temp table to test my doubt.

create table ids(id int(1));

I used to think that if I specify int(1) , then I can store only 0-9. So I tried the following

insert into ids(id) values(100000);

It works!! Then I started my search to understand the number. In all the posts, I read that number near by data type means width. I still don't get the term width in mysql. Does they mean size? If so, size in bytes?.

And If I want to achieve what I thought, what should I change in my query? or Should I limit this from my php code?

Does this width mean same to all datatype?

INT(11) means that no matter what, you want the integer to be shown at most with 11 digits. So if you store an integer that's larger than 11 digits - it will only be displayed as if it had 11 digits, but its actual value will be stored. — 1000111, Mar 21 '16 at 06:58
@1000111 Thanks. You cleared my first basic doubt. Please let me know can I limit the length while inserting? — Gibbs, Mar 21 '16 at 06:59
No. While inserting an integer value if the value lies within the range of `INTEGER` then the actual value will be stored in database. That length property works while selecting like I said in my previous comment. @GopsAB — 1000111, Mar 21 '16 at 07:01
Look at this [**link**](http://stackoverflow.com/questions/6817479/mysql-int-meaning). It will definitely clear your concept. — 1000111, Mar 21 '16 at 07:20

Dylan Su · Answer 1 · 2016-03-21T07:05:31.260

The manual is already clear on this.

Manual:

http://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html

The display width is only present in the metadata, used for applications.

The following is a demo on this(length for id is 1(int(1)); length for id2 is 11(int)).

mysql> create table ids(id int(1), id2 int);
insert into ids(id) values(100000);
seQuery OK, 0 rows affected (0.00 sec)

mysql> insert into ids(id) values(100000);
lect iQuery OK, 1 row affected (0.00 sec)

mysql> select id, id2 from ids;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `ids`
Org_table:  `ids`
Type:       LONG
Collation:  binary (63)
Length:     1
Max_length: 6
Decimals:   0
Flags:      NUM

Field   2:  `id2`
Catalog:    `def`
Database:   `test`
Table:      `ids`
Org_table:  `ids`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM

+--------+------+
| id     | id2  |
+--------+------+
| 100000 | NULL |
+--------+------+
1 row in set (0.00 sec)

Ok. How do I achieve if it should mean length while inserting? — Gibbs, Mar 21 '16 at 06:58

score 0 · Answer 2 · answered Mar 21 '16 at 06:59

You can specify data/byte length in char/varchar but not int type as int type keep its default length/capacity in mysql.

Suppose int will be always int(11) even you specify int(2) and keep its default value 4 bytes or -2,147,483,648 to +2,147,483,647 in case of signed and 0 to 4,294,967,295 in case of unsigned.

if you want to store just max. 127 integer value then you can keep tinyint and if till 255 then tinyint unsigned.

Or you want to go beyond it then there is smallint, mediumint then int and then bigint.

Number near by data type in mysql

2 Answers2

Manual: