DB_Types_Models

The maximum legal display width is 255, represented by an
m in the type definition forms listed below. This is just how
a number shows and doesn't relate to it's range.

UNSIGNED, causes the full range of the type to be
expressed as positive numbers.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer.
The signed range is -128 to 127.
The unsigned range is 0 to 255.
// one byte long

BOOL, BOOLEAN

synonyms for TINYINT(1)
A value of zero is considered false.
Non-zero values are considered true:

Example

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

Note though, the values TRUE and FALSE are aliases for 1 and 0.

mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

SMALLINT[(M)][UNSIGNED][ZEROFILL]

A small integer.
The signed range is -32768 to 32767.
The unsigned range is 0 to 65535.
// two bytes

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

A medium-sized integer.
The signed range is -8388608 to 8388607.
The unsigned range is 0 to 16777215.
// three bytes

INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer.
The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.
// four bytes

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

synonym for INT

BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer.
The signed range is -9223372036854775808 to
9223372036854775807. The unsigned range is
0 to 18446744073709551615.
2 to the power 64
// eight bytes

Floating Point Types

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

where

M is the total number of digits
and D is the number of digits following the decimal point

A small (single-precision) floating-point number.
Range -3.402823466E+38 to -1.175494351E-38, 0,
and 1.175494351E-38 to 3.402823466E+38.
// theoretical limits, based on the IEEE standard.
range may be smaller depending on the platform used

UNSIGNED, if specified, disallows negative values
single precision, accurate to 7 places

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
A (double-precision) floating-point number.
Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308,
0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.
the theoretical limits, based on the IEEE standard.
double precision accurate to ~15 decimal places
UNSIGNED, if specified, disallows negative values.

Fixed Point Number Type

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
A packed “exact” fixed-point number.
M is the total number of digits (the precision)
D is the number of digits after the decimal point (the scale).
The decimal point and (for negative numbers) the “-” sign

are not counted in M.

If D is 0, values have no decimal point or fractional part.
The maximum number of digits (M) for DECIMAL is 65
(64 from 5.0.3 to 5.0.5).
The maximum number of supported decimals (D) is 30.
If D is omitted, the default is 0. If M is omitted, the default is 10.
UNSIGNED, if specified, disallows negative values.
All basic calculations (+, -, *, /) with DECIMAL columns
are done with a precision of 65 digits.
DEC is a synonym for this type

In MySQL the string types are:

CHAR
VARCHAR
BINARY
VARBINARY
BLOB
TEXT
ENUM, and
SET.

The CHAR and VARCHAR Types

Both CHAR and VARCHAR are declared with the
length indicating the maximum number of characters
to be stored.

Example

CHAR(24) // holds up to 24 characters.

The length of a CHAR column is fixed to the length that you
declare when you create the table, a value from 0 to 255.
When CHAR values are stored, they are right-padded with
spaces to fill to the specified length. On retrieval, trailing
spaces are removed.

Values in VARCHAR columns on the other hand, are variable-
length strings. The length can be specified as a value from
0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and
later versions.

// The maximum column length is subject to a row size of 65,532 bytes.

VARCHAR only stores as many characters as are needed,
plus one byte to record the length. VARCHAR doesn't pad.

// two bytes for columns that are declared with a length longer than 255
// as of MySQL 5.0.3 trailing spaces are stored and retrieved

Exceeding column maximum length, results in a value that
is truncated to fit. If not spaces, a warning is generated.

The BINARY and VARBINARY Types

BINARY and VARBINARY are as a pair similar to CHAR
and VARCHAR however they store binary rather than
non-binary strings. They have no character set associations.

The BINARY and VARBINARY data types are distinct from the



CHAR BINARY

and VARCHAR BINARY data types. In the latter
types, the BINARY attribute does not cause the column to
be treated as a binary string column.

// Instead, it causes the binary collation for the column character
// set to be used, and the column itself contains non-binary
// character strings rather than binary byte strings.

BINARY type is right padded. VARBINARY has no padding on
insert and no bytes are stripped on select.

Example

mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t SET c = 'a';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 |       0 |           1 |
+--------+---------+-------------+
1 row in set (0.09 sec)

The BLOB and TEXT Types

A BLOB is a binary large object that can hold a variable
amount of data. The four BLOB types are:

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

BLOB columns hold binary strings.

The variations differ only in maximum length.

The four text types are:

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

TEXT columns hold character strings. TEXT
columns are associated with character sets
and their values are sorted and compared based
on collation of the character set.

In most respects, you can regard a BLOB column as
a VARBINARY column that has no size limitation. Similarly,
a TEXT column may be viewed as an unlimited VARCHAR
column.

There is no trailing-space removal for BLOB or TEXT columns.

Because BLOB and TEXT values can be extremely long, you
may encounter some limitations when using them. (For
example the default value of max_sort_length is 1024).

// this variable can be increased in size

The maximum size of a BLOB or TEXT object is practically
limited by the memory that is available and the size of the
communication buffers.

The ENUM Type

"An ENUM is a string object with a value chosen from a list of
allowed values that are enumerated explicitly in the column
specification at table creation time.

An enumeration value must be a quoted string literal; it may not
be an expression, even one that evaluates to a string value. "
- MySQL documentation

You can create a table with an ENUM column as follows.

Example

CREATE TABLE sizes (
    name ENUM('small', 'medium', 'large')
);

However, following does not work:

CREATE TABLE sizes (
    c1 ENUM('small', CONCAT('med','ium'), 'large')
);

// a string literal form must be used

Each enumeration value has an index, numbered starting
at 1 and the index of a NULL value is NULL. The index
used with enums have nothing to do with indexes associated
with tables. Another limit, in MySQL an enumeration is limited
to 65,535 elements. Trailing spaces are automatically deleted
from ENUM member values in table definitions.

The SET Type

"A SET is a string object that can have zero or more values,
each of which must be chosen from a list of allowed values
specified when the table is created." - MySQL Documentation

SET column values are specified separated by commas.
Accordingly, member values should not themselves contain
commas.

Example

A column specified as SET('one' , 'two') NOT NULL can have
any of the following values:

Values

''
'one'
'two'
'one,two'

A SET can have a maximum of 64 different members. Trailing
spaces are automatically deleted from SET member values in
the table definitions.

Date and Time Types

The MySQL types in this category are:

DATETIME
DATE
TIMESTAMP
TIME
YEAR.

Each temporal type has a range of legal values, as well as a “zero”
value to represent illegal values.

DATETIME, DATE & TIMESTAMP Types

These types are related are all related in that they deal
with time values.

The DATETIME type is used when both date and time information
is needed and is displayed in 'YYYY-MM-DD HH:MM:SS' format.
The supported range is '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'.

The DATE type supplies only a date value without a time part.
The supported range is '1000-01-01' to '9999-12-31'.

You can specify DATETIME, DATE, and TIMESTAMP values
using any of a common set of formats:

'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format.
A “relaxed” syntax is allowed: Any punctuation character may be
used as the delimiter between date parts or time parts.

Example of MySQL Equivalent Forms

'98-12-31 11:30:45'
'98.12.31 11+30+45'
'98/12/31 11*30*45'
'98@12@31 11^30^45' are equivalent.

Many other forms are permitted. // see the manual

For interest, following is a microsecond selection.

Example // from MySQL manual

mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
|                                     19473 | 
+-------------------------------------------+

The Time Type

"MySQL retrieves and displays TIME values in 'HH:MM:SS' format
or 'HHH:MM:SS' format for large hours values. TIME values may
range from '-838:59:59' to '838:59:59'." A number of formats
are permitted. // see manual

The YEAR Type

YEAR is a one-byte type used for representing years
and is displayed in YYYY format. The range is 1901
to 2155. As with the other temporal types variations
in format are permitted.

// MySQL itself is / was Y2K safe!