Mysql

what is MySql signed / unsigned option?

Ananda Raj PandeyLeave a comment

images

When to use signed / unsigned option in MySql.

Mysql Says: 

All integer types can have an optional (nonstandard) attribute UNSIGNED. Unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column’s range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.

When to use UNSIGNED data type?

If your field will never stores negative values, then its best to use UNSIGNED, that will give you faster index and more range.

When to use  SIGNED data type?

If you need to store negative then use signed, but remember your number range is half now.

 

Note: Common mistake people do is to store primary number ( auto -increment ) as as signed. Which mean you are already reducing your number range even you will never use negative number as your primary key.

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer typesTINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.

Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

MySql Data type confusion – int(11) – what it is?

Ananda Raj Pandey1 Comment

images

I found many people are confused with MySql numeric size value, as people are thinking this is size of numbers that can be stored, like if tinyint(2) – means upto 2 digit numbers can be saved, which is misconception as size doesn’t have any impact on how many digit it can store. Size is only for display, and this only matters if you use the ZEROFILL option.

To make this clear,

MySql has following numeric data types:

Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

As per above chart, numeric data type like INT is always 4 bytes. So making int(5) doesn’t help on saving disk space.

Lets consider the table with following structure:

CREATE TABLE foo (
  age INT(3) ZEROFILL, 
  currency INT(6) ZEROFILL, 
  distance INT(11) ZEROFILL
);
INSERT INTO foo (age, currency, distance) VALUES (222, 555, 777);
INSERT INTO foo (age, currency, distance) VALUES(1,666666,88888888888);

SELECT * FROM foo;

+------+----------+-------------+
| age  | currency | distance    |
+------+----------+-------------+
|  222 | 000555   | 00000000777 |
|  001 | 666666   | 88888888888 |
+------+----------+-------------+

In above example, ‘000555’ has three extra zero, as  column `currency` is INT(6) zero fill, so less than six digit will be prepended by zeros.