MySQL/Language/Data Types

From Wikibooks, open books for an open world
< MySQL‎ | Language
Jump to: navigation, search

VARCHAR[edit]

VARCHAR is shorthand for CHARACTER VARYING. 'n' represents the maximum column length (upto 65,535 characters). 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 or 2 bytes (1 if the length is < 255) to record the length of the string.
For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

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.

TEXT and BLOB[edit]

A BLOB or TEXT column with a maximum length of 65,535 characters. The required space is the real length of the stored data plus 2 bytes (1 byte if length is < 255). The BLOB / TEXT data is not stored in the table's datafile. This makes all operations (INSERT / UPDATE / DELETE / SELECT) involving the BLOB / TEXT data slower, but makes all other operations faster.

integer[edit]

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[edit]

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[edit]

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 2^{31}-1 usual limit (2038).

mysql> SET time_zone = '+00:00'; -- GMT
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT FROM_UNIXTIME(-1);
+-------------------+
| FROM_UNIXTIME(-1) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(0); -- "Epoch"
+---------------------+
| FROM_UNIXTIME(0)    |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

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[edit]

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.

Example:

SET("madam", "mister")    -- authorizes an empty field, "madam", "mister", "madam, mister", or "mister, madam"
 
ENUM("madam", "mister")   -- authorizes an empty field, "madam" or  "mister"