difference between `id int(10)` and `id bigint(10)`

Question

I am creating a table for that I am using a integer primary key field I am creating table as

CREATE TABLE `post` (
 `id`  int(100) NOT NULL AUTO_INCREMENT,
 `description` varchar(100),
   PRIMARY KEY (`id`)
 );

I read this Numeric Types. according to this we can store values in int and bigint less than 20 digits.

I would like to know about following

What is the difference between id int(10) and id bigint(10)
I have used id int(100) is there any problem.
what data type should i use for above id(for id value 21 to 100 digits).

http://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20-etcc — Abhik Chakraborty, Apr 02 '14 at 08:41
That should answer all.. int(100) does not do anything.. since it will only accept data for int range.. — Abhik Chakraborty, Apr 02 '14 at 08:55
@AbhikChakraborty Could you tell me how to store more large data in numberic data types between above 21 digits.Is it possible — xrcwrn, Apr 04 '14 at 08:57
check this thread http://stackoverflow.com/questions/7142604/what-to-do-when-you-need-integers-larger-than-20-digits-on-mysql it has ur answer !! — Abhik Chakraborty, Apr 04 '14 at 09:11

score 1 · Answer 1 · edited Apr 02 '14 at 09:17

1

There is no meaning of defining digits for int type data as int(10), int(1), int(100) all are same as int and work as same. But int and bigint are different,bigint will occupy more spaces than int.Details are given below-

•tinyint    : 1 byte, -128 to +127 / 0 to 255 (unsigned)
•smallint   : 2 bytes, -32,768 to +32,767 / 0 to 65,535 (unsigned)
•mediumint  : 3 bytes, -8,388,608 to 8,388,607 / 0 to 16,777,215 (unsigned)
•int/integer: 4 bytes, -2,147,483,648 to +2,147,483,647 / 0 to 4,294,967,295 (unsigned)
•bigint     : 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (unsigned)

Signed : can contain -ve values (it is default with integer type datatype)

unsigned : can not contain -ve values and have values from 0 to as per integer type

Note: The "unsigned" types are only available in MySQL

edited Apr 02 '14 at 09:17

Rajesh

786
6
12

answered Apr 02 '14 at 08:46

Zafar Malik

6,734
2
19
30

int(1) will store only one digit data. int(5) will store 5 digit data?? – xrcwrn Apr 02 '14 at 08:51
Even you set int(1) still you can store upto 10 digits data as int data type can stored upto 10 digits data. – Zafar Malik Apr 02 '14 at 09:00
If you really want to save space and optimize your data type then you should use tinyint where your requirement is int(1). – Zafar Malik Apr 02 '14 at 09:01
So in my table I have mentioned int(100) this will also store data up to 10 digits – xrcwrn Apr 02 '14 at 09:06
How can we store more that 20 digits value in numeric data types. or is it limited with only 20 digits – xrcwrn Apr 02 '14 at 09:08
This isn't quite true. It does have a meaning, when used with ZEROFILL – Strawberry Apr 02 '14 at 09:59
in bigint you can store value upto 18,446,744,073,709,551,615 – Zafar Malik Apr 02 '14 at 10:04
@Strawberry I am curious to know data more that bigint above 21 digits – xrcwrn Apr 04 '14 at 08:59

difference between `id int(10)` and `id bigint(10)`

1 Answers1