March 11, 2008

List of MySQL Field Type

MySQL Field Type
Description
bigint(length) Numeric field that stores integers from
-9223372036854775808 to 9223372036854775807.
(Adding the unsigned parameter allows storage
of 0 to 18446744073709551615.) The parameter
length limits the number of characters to
be displayed. Please refer to the manual at
http://www.mysql.com before performing
mathematical calculations involving the bigint
type.
bit Equal to tinyint(1) field. A value of zero
represents false; a non-zero value represents
true.
blob Equal to a text field, except it is case-sensitive
when sorting and comparing. Stores up to 65535
characters.
bool Equal to tinyint(1) field. A value of zero
represents false; a non-zero value represents
true.
boolean Equal to tinyint(1) field. A value of zero
represents false; a non-zero value represents
true.
char(length) Any characters can be in this field, but the field will
have a fixed length. The length parameter can be
between 0 and 255. If the length parameter is not
defined, the default value is 1. Adding the BINARY
attribute will make comparisons and sorting results
case-sensitive.
date Stores a date as yyyy-mm-dd. Allows values from
1000-01-01 to 9999-12-31.
datetime Stores date and time as yyyy-mm-dd hh:mm:ss.
Allows values from 1000-01-01 00:00:00 to 9999-12-31
23:59:59.
dec(length,dec) Equal to decimal field.

decimal
(length,dec)

Numeric field that can store decimals. The length
parameter limits the number of digits that will be
displayed, and the dec parameter limits the number
of decimal places that can be stored. The length
parameter does not count decimal points and “-” for
negative values. A price field that would store prices
up to 999.99, for example, would be defined as
decimal(5,2). If the length parameter is not specified,
the default is 10. Adding the unsigned attribute
allows only non-negative values.
double
(length,dec)
Amedium-sized floating point number that stores
values from -1.7976931348623157E+308 to
-2.2250738585072014E-308, 0, and
2.2250738585072014E-308 to 1.7976931348623157E+308.
The length parameter determines how many digits
will be displayed; the dec parameter determines how
many decimal places are displayed. (Adding the
unsigned parameter allows only positive numbers
to be stored.) Using double without parameters
specified represents a double-precision floating point
number.
double precision
(length, dec)
Equal to double.
enum(“option1”, “option2”, ...) Allows only certain values to be stored in this field,
such as true and false, or a list of states. 65,535
different options can be allowed.
fixed(length,dec Equal to decimal field.
float(length,dec) A small floating point number that stores values
from -3.402823466E+38 to -1.175494351E-38, 0, and
1.175494351E-38 to 3.402823466E+38. The length
parameter determines how many digits will be
displayed; the dec parameter determines how many
decimal places are displayed. (Adding the unsigned
parameter allows only positive numbers to be stored.)
float(precision) Equal to float(length,dec) except the length
and dec parameters are undefined. To be used with a
true floating point number. (Adding the unsigned
parameter allows only positive numbers to be stored.)
The precision parameter can be from 0 to 24 for
single-precision floating point numbers, and from
25-35 for double-precision floating point numbers.
int(length) Numeric field that stores integers from -2147483648
to +2147483647, but can be limited with the length
parameter. length limits the number of digits that can
be shown, not the value. Mathematical functions can
be performed on data in this field. Signifying the
unsigned parameter permits positive integers (and
zero) up to 4294967295.
integer(length) Equal to int.
longblob Equal to longtext except it is case-sensitive when
sorting and comparing.
longtext Allows storage of up to 4294967295 character
mediumblob Equal to mediumtext field except it is case-sensitive
when sorting and comparing.
mediumint
(length)
Numeric field that stores integers from -8388608 to
8388607. (Adding the unsigned parameter allows
storage of 0 to 16777215.) length limits the number
of digits to be displayed.
mediumtext Allows storage of up to 16777215 characters.
numeric
(length,dec)
Equal to decimal field.
real(length,dec) Equal to double field.
set(“option1”, “option2”, ...) Similar to enum field, but with set there can be none
or more than one of the available options. Set allows
up to 64 options.
smallint(length) Numeric field that stores integers from -32768 to
32767. (Adding the unsigned parameter allows
storage of 0 to 65535.) The length parameter limits
the number of characters to be displayed.
text Any character can be in this field, and the maximum
size of the data is 64K (65535 characters).
time Stores time as hh:mm:ss.
timestamp Stores date and time as yyyy-mm-dd hh:mm:ss. Useful
for automatically capturing current date and time.
tinyblob Equal to tinytext field, except it is case-sensitive
when sorting and comparing.
tinyint(length) Numeric field that stores integers from -128 to 127.
(Adding the unsigned parameter allows storage of 0
to 255.) The length parameter limits the number of
characters to be shown.
tinytext Allows storage of up to 255 characters.
varchar(length) Any character can be in this field, and the data can
vary from 0 to 255 characters. Maximum length of
field is denoted with the length parameter. Adding
the BINARY attribute causes comparisons and sorting
results to be case-sensitive.
year(length) Stores a year in 4-character format (by default). In
this format, values from 1901 to 2155, and 0000 are
acceptable. It is possible to specify a 2-year format
by signifying so with the length parameter. In this
format, the values from 70 to 69 are acceptable
(1970-2069).

Share

0 comments:

Blogger template 'PlainFish' by Ourblogtemplates.com 2008