Properly defining the fields in a table is important to the overall
optimization of your database.
A D V E R T I S E M E N T
You should use only the type and size of field
you really need to use; don't define a field as 10 characters wide if you know
you're only going to use 2 characters. These types of fields (or columns) are
also referred to as data types, after the type of data you will be
storing in those fields.
MySQL uses many different data types, broken into three categories: numeric,
date and time, and string types.
Numeric Data Types:
MySQL uses all the standard ANSI SQL numeric data types, so if you're coming
to MySQL from a different database system, these definitions will look familiar
to you. The following list shows the common numeric data types and their
descriptions.
INT - A normal-sized integer that can be signed or unsigned. If
signed, the allowable range is from -2147483648 to 2147483647. If unsigned,
the allowable range is from 0 to 4294967295. You can specify a width of up
to 11 digits.
TINYINT - A very small integer that can be signed or unsigned. If
signed, the allowable range is from -128 to 127. If unsigned, the allowable
range is from 0 to 255. You can specify a width of up to 4 digits.
SMALLINT - A small integer that can be signed or unsigned. If
signed, the allowable range is from -32768 to 32767. If unsigned, the
allowable range is from 0 to 65535. You can specify a width of up to 5
digits.
MEDIUMINT - A medium-sized integer that can be signed or
unsigned. If signed, the allowable range is from -8388608 to 8388607. If
unsigned, the allowable range is from 0 to 16777215. You can specify a width
of up to 9 digits.
BIGINT - A large integer that can be signed or unsigned. If
signed, the allowable range is from -9223372036854775808 to
9223372036854775807. If unsigned, the allowable range is from 0 to
18446744073709551615. You can specify a width of up to 11 digits.
FLOAT(M,D) - A floating-point number that cannot be unsigned. You
can define the display length (M) and the number of decimals (D). This is
not required and will default to 10,2, where 2 is the number of decimals and
10 is the total number of digits (including decimals). Decimal precision can
go to 24 places for a FLOAT.
DOUBLE(M,D) - A double precision floating-point number that
cannot be unsigned. You can define the display length (M) and the number of
decimals (D). This is not required and will default to 16,4, where 4 is the
number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL
is a synonym for DOUBLE.
DECIMAL(M,D) - An unpacked floating-point number that cannot be
unsigned. In unpacked decimals, each decimal corresponds to one byte.
Defining the display length (M) and the number of decimals (D) is required.
NUMERIC is a synonym for DECIMAL.
Date and Time Types:
The MySQL date and time datatypes are:
DATE - A date in YYYY-MM-DD format, between 1000-01-01 and
9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30.
DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS
format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example,
3:30 in the afternoon on December 30th, 1973 would be stored as 1973-12-30
15:30:00.
TIMESTAMP - A timestamp between midnight, January 1, 1970 and
sometime in 2037. This looks like the previous DATETIME format, only without
the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973
would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).
TIME - Stores the time in HH:MM:SS format.
YEAR(M) - Stores a year in 2-digit or 4-digit format. If the
length is specified as 2 (for example YEAR(2)), YEAR can be 1970 to 2069 (70
to 69). If the length is specified as 4, YEAR can be 1901 to 2155. The
default length is 4.
String Types:
Although numeric and date types are fun, most data you'll store will be in
string format. This list describes the common string datatypes in MySQL.
CHAR(M) - A fixed-length string between 1 and 255 characters in
length (for example CHAR(5)), right-padded with spaces to the specified
length when stored. Defining a length is not required, but the default is 1.
VARCHAR(M) - A variable-length string between 1 and 255
characters in length; for example VARCHAR(25). You must define a length when
creating a VARCHAR field.
BLOB or TEXT - A field with a maximum length of 65535 characters.
BLOBs are "Binary Large Objects" and are used to store large amounts of
binary data, such as images or other types of files. Fields defined as TEXT
also hold large amounts of data; the difference between the two is that
sorts and comparisons on stored data are case sensitive on BLOBs and are not
case sensitive in TEXT fields. You do not specify a length with BLOB or
TEXT.
TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum
length of 255 characters. You do not specify a length with TINYBLOB or
TINYTEXT.
MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum
length of 16777215 characters. You do not specify a length with MEDIUMBLOB
or MEDIUMTEXT.
LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum
length of 4294967295 characters. You do not specify a length with LONGBLOB
or LONGTEXT.
ENUM - An enumeration, which is a fancy term for list. When
defining an ENUM, you are creating a list of items from which the value must
be selected (or it can be NULL). For example, if you wanted your field to
contain "A" or "B" or "C", you would define your ENUM as ENUM ('A', 'B',
'C') and only those values (or NULL) could ever populate that field.
Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
Keywords:
MySQL Data Types, Mysql Tutorial, Mysql tutorial pdf, history of mysql, basic mysql, syntax use in mysql, mysql software download, learn mysql, mysql insert, mysql delete, mysql data types, mysql administrator.
