1

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?

Community
  • 1
  • 1
Keir Simmons
  • 1,634
  • 7
  • 21
  • 37
  • Does the length/ size of a field come into play if `ZEROFILL` has not been used? – Keir Simmons Jul 27 '12 at 13:21
  • I've just realised that I have been storing timestamps in `INT(99) SIGNED`, which can only hold values up to 2147483647, but timestamps are much higher than that - except my timestamps have not been truncated. Why is that? – Keir Simmons Jul 27 '12 at 13:38

3 Answers3

2

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.

Mahesh Patil
  • 1,529
  • 1
  • 11
  • 22
0

For binary fields use BIT.

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

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Can I use `BIT` for values which could either be -1,0 or 1? or is it specifically for 0,1? If I am storing monetary data (but without decimal points, i.e $1,$2,$3 etc how would I go about storing it? – Keir Simmons Jul 27 '12 at 13:01
  • `BIT(1)` can only store `1` and `0`. You can read up all your questions [here](http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html). BTW `SIGNED` is for values that can be negative and `UNSIGNED` for values that are never be negative. – juergen d Jul 27 '12 at 13:05
  • Is there a reason for using `BIT` over `BOOL`? – Keir Simmons Jul 27 '12 at 13:23
  • No, you can use `bool` as well. For `true-false` values might it be even better. – juergen d Jul 27 '12 at 13:31
0

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.

devkantmohan
  • 16
  • 4