CODEWITHSUNDEEP
mysqlnumericunsignedsignedsqldatatypes
Keir Simmons
Keir Simmons

Reputation: 1684

When to use the different numeric data types - TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT - MySQL

I read the answers given here: What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL? , so I now know how they store the data, but I'm still not sure how to set my database up. For example, if I want a field to be either 0 or 1 (sort of binary, 0 = off, 1 = on), do I use TINYINT with a length of 1?

My main question is, what does the LENGTH setting determine? As each NUMERIC data type already has their own associated data size.

Also, what is the difference between SIGNED and UNSIGNED, and why should I choose one over the other?

Upvotes: 1

Views: 6714

Answers (3)

devkantmohan
devkantmohan

Reputation: 16

An integer variable has 32 bits to store the integer value. In signed integer the first bit is reserved to store positive or negative sign. So, a signed integer can use only 31 bits to store a value and hence its range will be −2,147,483,648 to +2,147,483,647. Suppose if your program needs to store only positive integer greater than +2,147,483,647. You need to consider the long integer that will take 8 bits that will cause the wastage of memory. Instead you can go with unsigned integer. In an unsigned integer no bit is reserved for the sign so now you have 32 bits to store the value. The only limitation with an unsigned integer is that you cannot use it to store negative values. The range of an unsigned integer of 32 bits will be 0 to 4,294,967,295. Hope it clears your concept of signed and unsigned integer.

Upvotes: 0

Mahesh Patil
Mahesh Patil

Reputation: 1551

Diffrence between SIGNED and UNSIGNED is with UNSIGNED you can store only positive numbers.

For example : about INT (Normal INTEGER) values

The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. If you are using PK auto_increment value then you should use UNSIGNED in this case.

Upvotes: 2

juergen d
juergen d

Reputation: 204746

For binary fields use BIT.

the length of numerics specifies the precision before and after the comma. See here

Upvotes: 0

Related Questions