Question:
MySql data types INTEGER, SMALLINT, DECIMAL, and NUMERIC ?
Asked by:
When should I use INTEGER, SMALLINT, DECIMAL, and NUMERIC type for column in mysql table? What is the minimum and maxiumum value of those types.
Answer by:
You should decide what number(decimal or integer) you have to store in column and how big the number will be.
Here are the length in bytes, minimum and maximum values for those datatypes.
One remark: from MySQL 5.0.3 and on a new data type is introduced named "BIT" which is equivalent to TINYINT(1).
TINYINT(1 byte)
Signed: -128 to 127
Unsigned: 0 to 255
SMALLINT( 2 bytes)
Signed: -32768 to 32767
Unsigned: 0 to 65535
MEDIUMINT ( 3 bytes)
Signed: -8388608 to 8388607
Unsigned: 0 to 16777215
INT ( 4 bytes)
Signed: -2147483648 to 2147483647
Unsigned: 0 to 4294967295
BIGINT ( 8 bytes)
Signed: -9223372036854775808 to 9223372036854775807
Unsigned: 0 to 18446744073709551615
All integer data types can be defined with an optional (non-standard) attribute "UNSIGNED". Unsigned values can be used when we want to allow only non-negative numbers in a column and we need a larger upper numeric range for this column.
When you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the specified column.
MySQL allows a non-standard syntax: FLOAT(X,Y) or REAL(X,Y) or DOUBLE PRECISION(X,Y). In this case "(X,Y)" means than values can be stored with up to X digits in total, of which Y digits may be after the decimal point.
Here are the length in bytes, minimum and maximum values for those datatypes.
One remark: from MySQL 5.0.3 and on a new data type is introduced named "BIT" which is equivalent to TINYINT(1).
TINYINT(1 byte)
Signed: -128 to 127
Unsigned: 0 to 255
SMALLINT( 2 bytes)
Signed: -32768 to 32767
Unsigned: 0 to 65535
MEDIUMINT ( 3 bytes)
Signed: -8388608 to 8388607
Unsigned: 0 to 16777215
INT ( 4 bytes)
Signed: -2147483648 to 2147483647
Unsigned: 0 to 4294967295
BIGINT ( 8 bytes)
Signed: -9223372036854775808 to 9223372036854775807
Unsigned: 0 to 18446744073709551615
All integer data types can be defined with an optional (non-standard) attribute "UNSIGNED". Unsigned values can be used when we want to allow only non-negative numbers in a column and we need a larger upper numeric range for this column.
When you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the specified column.
MySQL allows a non-standard syntax: FLOAT(X,Y) or REAL(X,Y) or DOUBLE PRECISION(X,Y). In this case "(X,Y)" means than values can be stored with up to X digits in total, of which Y digits may be after the decimal point.
Vote:
Please vote! Your opinion matters!
If you haven't found what you've looking for, post a question
