Pages

Wednesday, August 10, 2011

MySQL Basics: NUMERIC Data Types

MySQL supports a number of data types in several categories, my next few posts will cover the different types of data used in MySQL.

MySQL Data Types Overview:

Numeric - (reviewed in this post)
Date and Time
String (character)

Each data types will also be defined by certain characteristics:

Storage Requirements
Out of Range & Overflow

M indicates the maximum display width for integer types. For floating-point and fixed-point types, M is the total number of digits that can be stored. For string types, M is the maximum length. The maximum permissible value of M depends on the data type.

D applies to floating-point and fixed-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M–2.

Square brackets (“[” and “]”) indicate optional parts of type definitions.


If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

------------------------------

MySQL NUMERIC DATA TYPES:

BIT[(M)]:

A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
Before 5.0.3, BIT is a synonym for TINYINT.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]:

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

BOOL, BOOLEAN:

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true. The values TRUE and FALSE are merely aliases for 1 and 0, respectively.

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]:

A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]:

A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

INT[(M)] [UNSIGNED] [ZEROFILL]:

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

INTEGER[(M)] [UNSIGNED] [ZEROFILL]:

This type is a synonym for INT.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]:

A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Some things you should be aware of with respect to BIGINT columns*:

*All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]:

A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

UNSIGNED, if specified, disallows negative values.

Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision.

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]:

A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.

UNSIGNED, if specified, disallows negative values.

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

These types are synonyms for DOUBLE. Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.

FLOAT(p) [UNSIGNED] [ZEROFILL]:

A floating-point number. p represents the precision in bits, but MySQL uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT with no M or D values. If p is from 25 to 53, the data type becomes DOUBLE with no M or D values. The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.

FLOAT(p) syntax is provided for ODBC compatibility.

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

For MySQL 5.0.3 and above:

A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the “-” sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65 (64 from 5.0.3 to 5.0.5). The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

UNSIGNED, if specified, disallows negative values.

All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.

Before MySQL 5.0.3:

An unpacked fixed-point number. Behaves like a CHAR column; “unpacked” means the number is stored as a string, using one character for each digit of the value. M is the total number of digits and D is the number of digits after the decimal point. The decimal point and (for negative numbers) the “-” sign are not counted in M, although space for them is reserved. If D is 0, values have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If D is omitted, the default is 0. If M is omitted, the default is 10.

UNSIGNED, if specified, disallows negative values.

The behavior used by the server for DECIMAL columns in a table depends on the version of MySQL used to create the table. If your server is from MySQL 5.0.3 or higher, but you have DECIMAL columns in tables that were created before 5.0.3, the old behavior still applies to those columns. To convert the tables to the newer DECIMAL format, dump them with mysqldump and reload them.

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

These types are synonyms for DECIMAL. The FIXED synonym is available for compatibility with other database systems.

For more details (yes, it can get more detailed!) visit MySQL-Numeric Data Type

No comments:

Post a Comment