MySQL field type description

MySQL supports a large number of column types, which can be grouped into 3 categories: numeric types, date and time types, and string (character) types. This section first gives an overview of the available types, summarizes the storage requirements for each column type, and then provides a more detailed description of the nature of the types in each class. The overview is intended to be simplified, and more detailed instructions should take into account additional information about a specific column type, such as the allowable format for which you can specify a value.

The column types supported by MySQL are listed below. The following code letters are used in the description:

M

Indicates the maximum display size. The largest legal display size is 255.

D

Applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but it should be less than M-2.

The brackets ("[" and "]") indicate the part of the optional type modifier.

Note that if you specify a zerofill,mysql will automatically increase the unsigned property for the column.

tinyint[(M)] [UNSIGNED] [Zerofill]

A very small integer. The signed range is 128 to 127, and the unsigned range is 0 to 255.

smallint[(M)] [UNSIGNED] [Zerofill]

A small integer. The signed range is 32768 to 32767, and the unsigned range is 0 to 65535.

mediumint[(M)] [UNSIGNED] [Zerofill]

A medium sized integer. The signed range is 8388608 to 8388607, and the unsigned range is 0 to 16777215.

int[(M)] [UNSIGNED] [Zerofill]

A normal size integer. The signed range is 2147483648 to 2147483647, and the unsigned range is 0 to 4294967295.

integer[(M)] [UNSIGNED] [Zerofill]

This is a synonym for int.

bigint[(M)] [UNSIGNED] [Zerofill]

A large integer. The signed range is 9223372036854775808 to 9223372036854775807, and the unsigned range is 0 to

18446744073709551615. Note that all arithmetic operations are done with signed bigint or double values, so you should not use a signed large integer greater than 9223372036854775807 (63 bits), except for the bit function! Note that when two parameters are integer,-, +, and * will use the bigint operation! This means that if you multiply by 2 large integers (or from functions that return integers), you can get unexpected results if the result is greater than 9223372036854775807. A floating-point number, which cannot be unsigned, can be <=24 for a single precision float, and the precision of a double-precision float is between 25 and 53, these types such as float and double are described immediately below. Float (X) has the same range as float and double, but the display dimensions and decimal places are undefined. In MySQL3.23, this is a real floating-point value. In earlier versions of MySQL, FLOAT (precision) always had 2 decimal places. This syntax is provided for ODBC compatibility.

float[(m,d)] [Zerofill]

A small (single precision) floating-point number. cannot be unsigned. The allowable values are -3.402823466E+38 to -1.175494351e-38,0 and 1.175494351E-38 to 3.402823466E+38. M is the display width and d is the number of digits. A float with no parameters or a parameter with <24 represents a single precision floating-point number.

double[(m,d)] [Zerofill]

A normal size (double precision) floating-point number. cannot be unsigned. The allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. M is the width of the display and D is the decimal digit. Double or float (x) with no arguments (< = x < = 53) represents a double-precision floating-point number.

DOUBLE precision[(m,d)] [Zerofill]

real[(m,d)] [Zerofill]

These are double synonyms.

decimal[(M[,d])] [Zerofill]

An uncompressed (unpack) floating-point number. cannot be unsigned. Behaves like a char column: "Uncompressed" means that the number is stored as a string, and each digit of the value uses one character. decimal point, and for negative numbers, the "-" symbol is not evaluated in M. If D is 0, the value will not have a decimal point or fractional fraction. The maximum range of decimal values is the same as double, but for a given decimal column, the actual range can be limited by the choice of M and D. If D is omitted, it is set to 0. If M is omitted, it is set to 10. Note that in MySQL3.22, the M parameter includes a symbol and a decimal point.

NUMERIC (M,D) [Zerofill]

This is a synonym for decimal.

DATE

A date. The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '. MySQL Displays the date value in ' YYYY-MM-DD ' format, but allows you to assign the value to the Date column using a string or a number.

Datetime

A combination of dates and times. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. MySQL displays datetime values in the ' yyyy-mm-dd HH:MM:SS ' format, but allows you to assign values to datetime columns using strings or numbers.