VARCHAR is shorthand for CHARACTER VARYING. 'n' represents the maximum column length (upto 255 characters) char(n) is similar to varchar(n) with the only difference that char will occupy fixed length of space in the database whereas varchar will need the space to store the actual text. For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes. text
A BLOB or TEXT column with a maximum length of 65,535 characters.
integer
Specifying an n value has no effect whatsoever. Regardless of a supplied value for n, maximum (unsigned) value stored is 429 crores. If you want to add negative numbers, add the "signed" keyword next to it.
decimal
decimal(n,m) decimal(4,2) means numbers upto 99.99 (and NOT 9999.99 as you may expect) can be saved. Four digits with the last 2 reserved for decimal.
Dates
Out of the three types DATETIME, DATE, and TIMESTAMP, the DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The DATETIME type is used when you need values that contain both date and time information. The difference between DATETIME and TIMESTAMP is that the TIMESTAMP range is limited to 1970-2037 (see below).
TIME can be used to only store the time of day (HH:MM:SS), without the date. It can also be used to represent a time interval (for example: -02:00:00 for "two hours in the past"). Range: '-838:59:59' => '838:59:59'.
YEAR can be used to store the year number only.
If you manipulate dates, you have to specify the actual date, not only the time - that is, MySQL will not automagically use today as the current date. On the contrary, MySQL will even interpret the HH:MM:SS time as a YY:MM:DD value, which will probably be invalid.
The following examples show the precise date range for Unix-based timestamps, which starts at the Unix Epoch and stops just before the first new year before the 231 − 1 usual limit (2038).
mysql> SELECT FROM_UNIXTIME(2145916799); +---------------------------+ | FROM_UNIXTIME(2145916799) | +---------------------------+ | 2037-12-31 23:59:59 | +---------------------------+ 1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(2145916800); +---------------------------+ | FROM_UNIXTIME(2145916800) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec)
set and enum
A SET datatype can hold any number of strings from a predefined list of strings specified during table creation. The SET datatype is similar to the ENUM datatype in that they both work with predefined sets of strings, but where the ENUM datatype restricts you to a single member of the set of predefined strings, the SET datatype allows you to store any of the values together, from none to all of them.
