MySQL Datatypes

bj2008_0201

Each column in a table is made of a data type. There are
    three general types of columns then, numeric types, date and
    time types, and string (character) types. In the examples
    below, I will only show the most commonly used data types.
    More information can be found at
      
http://www.mysql.com/Manual_chapter/manual_Reference.html
M - Indicates the maximum display size. The maximum legal
  display size is 255.
Numeric Types
INT[(M)]
A normal-size integer. The signed range is -2147483648 to
2147483647. The unsigned range is 0 to 4294967295.
Example:
Age INT;
This would be a valid integer if it is within the
        -2147483648 to 2147483647 values if it is unsigned, which it
        would be of course as no one is younger or older then those
numbers... obviously.
FLOAT[(M,D)]
A small (single-precision) floating-point number. Cannot be
  unsigned. Allowable values are -3.402823466E+38 to
  -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38.
Floating point numbers are made to be very precise. If I
    specified Price FLOAT(6,2) then 6 numbers would be allowed
    to the left of the decimal point, and 2 to the right. Very
    nice indeed.
Date and Time
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'.
    MySQL displays DATE values in 'YYYY-MM-DD' format, but
    allows you to assign values to DATE columns using either
  strings or numbers.
Example:
Today DATE;
TIME
A time. The range is '-838:59:59' to '838:59:59'. MySQL
        displays TIME values in 'HH:MM:SS' format, but allows you to
        assign values to TIME columns using either strings or
      numbers.
Example:
Now TIME;
String Types

CHAR(M)
CHAR's are fixed length strings that are right-padded with
    space to whatever you specify the length as in M. If you
    specify a data type as Name CHAR(60), and the entry is only
    20 characters in length, then the entry would be padded with
    40 characters worth of spaces. These spaces will be removed
    when the value is retrieved though. These entries will be
    sorted and compared by MySQL in case-insensitive fashions
    unless you give the BINARY keyword.... The maximum size is
255 characters.
Example:
Name CHAR(60);
VARCHAR(M)
These strings are made of variable lengths (as the name
        kind of suggests). All of the trailing spaces will be
        removed when the value is stored in the database. This is great for
        when you have limited hard drive space, but it can be a
        problem performance-wise. You can lose up to 50% of speed
        because of this. The explanation is that it actually loads
        the maximum length in anyway, and stores everything into
        memory. This causes the lag in performance. The maximum size
      is 255 characters.
Example:
Name VARCHAR(60);
BLOB or
      TEXT
BLOB or TEXT columns have a maximum length of 65535 (2^16 -
        1) (BIG). They can hold text, obviously of course, and are
        good at keeping large records, such as articles. The
        difference is that BLOB searches are case sensitive, while
        TEXT searches are case in-sensitive. These are used when
        your record size is between 255 and 65535 characters.
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/73684/showart_1221304.html