PHP & MySQL: Novice to Ninja, 5th Edition (2012)

Appendix D. MySQL Column Types

When you create a table in MySQL, you must specify the data type for each column. This appendix documents all the column types that MySQL provides as of version 5.5.22 (current this writing). In this reference, many column types can accept optional parameters to further customize how data for the column is stored or displayed. First, there are the M and D parameters, which are indicated (in square brackets when optional) immediately following the column type name. The parameter M is used to specify the display size (that is, maximum number of characters) to be used by values in the column. In most cases, this will limit the range of values that may be specified in the column. M may be any integer between 1 and 255. Note that for numerical types (for example, INT), this parameter doesn’t actually restrict the range of values that may be stored. Instead, it causes spaces (or zeros in the case of a ZEROFILL column—see further on for details) to be added to the values so that they reach the desired display width when they’re displayed. Additionally, storing values longer than the specified display width can cause problems when the values are used in complex joins, and thus should be avoided whenever possible. The parameter D lets you specify how many decimal places will be stored for a floating-point value. This parameter may be set to a maximum of 30, but M should always allow for these places (that is, D should always be less than or equal to M – 2 to allow room for a zero and a decimal point). The second type of parameter is an optional column attribute. The attributes supported by the different column types are listed for each; to enable them, simply type them after the column type, separated by spaces. Here are the available column attributes and their meanings:

ZEROFILL

Values for the column always occupy their maximum display length, as the actual value is padded with zeros. This option automatically sets the UNSIGNED option as well.

UNSIGNED

The column may accept only positive numerical values (or zero). This restriction frees up more storage space for positive numbers, effectively doubling the range of positive values that may be stored in the column, and should always be set if you know that there’s no need to store negative values.

BINARY

By default, comparisons of character values in MySQL (including sorting) are case-insensitive. However, comparisons for BINARY columns are case-sensitive.

For a complete, up-to-date reference to supported SQL column types, see the MySQL Reference Manual.

Numerical Types

TINYINT[(M)]

Description:

A tiny integer value

Attributes allowed:

UNSIGNED, ZEROFILL

Range:

-128 to 127 (0 to 255 if UNSIGNED)

Storage space:

1 byte (8 bits)

SMALLINT[(M)]

Description:

A small integer value

Attributes allowed:

UNSIGNED, ZEROFILL

Range:

-32768 to 32767 (0 to 65535 if UNSIGNED)

Storage space:

2 bytes (16 bits)

MEDIUMINT[(M)]

Description:

A medium integer value

Attributes allowed:

UNSIGNED, ZEROFILL

Range:

-8588608 to 8388607 (0 to 16777215 if UNSIGNED)

Storage space:

3 bytes (24 bits)

INT[(M)]

Description:

A regular integer value

Attributes allowed:

UNSIGNED, ZEROFILL

Range:

-2147483648 to 2147483647 (0 to 4294967295 if UNSIGNED)

Storage space:

4 bytes (32 bits)

Alternative syntax:

INTEGER[(M)]

BIGINT[(M)]

Description:

A large integer value

Attributes allowed:

UNSIGNED, ZEROFILL

Range:

-9223372036854775808 to 9223372036854775807 (0 to 18446744073709551615 if UNSIGNED)

Storage space:

8 bytes (64 bits)

Notes:

MySQL performs all integer arithmetic functions in signed BIGINT format; thus, BIGINT UNSIGNED values over 9223372036854775807 (63 bits) will only work properly with bit functions (for example, bit-wise AND, OR, and NOT). Attempting integer arithmetic with larger values may produce inaccurate results due to rounding errors.

FLOAT[(M, D)] , FLOAT(precision)

Description:

A floating point number

Attributes allowed:

ZEROFILL

Range:

0 and ±1.175494351E-38 to ±3.402823466E+38

Storage space:

4 bytes (32 bits)

Notes:

precision (in bits), if specified, must be less than or equal to 24, or else a DOUBLE column will be created instead.

DOUBLE[(M, D)] , DOUBLE(precision)

Description:

A high-precision floating point number

Attributes allowed:

ZEROFILL

Range:

0 and ±2.2250738585072014-308 to ±1.7976931348623157E+308

Storage space:

8 bytes (64 bits)

Notes:

precision (in bits), if specified, must be greater than or equal to 25, or else a FLOAT column will be created instead (see earlier). precision may not be greater than 53.

Alternative syntax:

DOUBLE PRECISION[(M,D)] or REAL[(M,D)]

DECIMAL[(M [, D ])]

Description:

A floating point number stored as a character string

Attributes allowed:

ZEROFILL

Range:

As for DOUBLE, but constrained by M and D (see Notes)

Storage space:

Depends on the stored value. For a value with X digits before the decimal point and Y digits after, the storage space used is approximately (X + Y) × 4 ÷ 10 bytes.

Notes:

If D is unspecified, it defaults to 0 and numbers in this column will have no decimal point or fractional part. If M is unspecified, it defaults to 10.

Alternative syntax:

NUMERIC([ M [,D ] ])

BIT(M)

Description:

An M-bit binary value, where M can be 1 to 64. In other words, a series of M digits, each of which may be 1 or 0.

Range:

As constrained by M

Storage space:

M + 2 bytes (8 × M + 16 bits)

Notes:

Intended for storing sets of Boolean (true or false) flags. To write BIT values, use the form b'ddd…', where each digit d can be 1 (to indicate “true”) or 0 (to indicate “false”). For example, an 8-bit binary value where all the flags are true is b'11111111'.

Character Types

CHAR(M)

Description:

A fixed-length character string

Attributes allowed:

BINARY

Maximum length:

M characters

Storage space:

M bytes (8 × M bits)

Notes:

CHAR values are stored as strings of length M, even though the assigned value may be shorter. When the string is shorter than the full length of the field, spaces are added to the end of the string to bring it exactly to M characters. Trailing spaces are stripped off when the value is retrieved. CHAR columns are quicker to search than variable-length character column types such as VARCHAR, since their fixed-length nature makes the underlying database file format more regular. M may take any integer value from 0 to 255, with a CHAR(0) column able to store only two values: NULL and '' (the empty string), which occupy a single bit.

Alternative syntax:

CHARACTER(M)

VARCHAR(M)

Description:

A variable-length character string

Attributes allowed:

BINARY

Maximum length:

M characters

Storage space:

Length of stored value, plus 1 byte to store length (2 bytes if M > 255)

Notes:

As VARCHAR values occupy only the space they require, there’s usually no point specifying a maximum field length M of anything less than 255 (the maximum for MySQL versions before 5.0.3). From 5.0.3 onward, MySQL will let you specify a maximum length up to 65,535 characters; however, 65,535 also happens to be the maximum number of bytes of data that a single table row is allowed to contain, so in practice you’ll want to stick to much smaller limits, or consider a more appropriate column type like TEXT. Strings longer than the specified limit will be chopped to the maximum length when inserted. MySQL versions before 5.0.3 would strip trailing spaces from values before they were stored, but this usually unexpected (and nonstandard) behavior has been removed and values are stored as supplied in MySQL 5.0.3 and later.

Alternative syntax:

CHARACTER VARYING(M)

BINARY(M)

Description:

A fixed-length binary string

Maximum length:

M characters

Storage space:

M bytes (8 × M bits)

Notes:

Just like CHAR, except MySQL treats the values stored in this column as non-textual byte strings.

VARBINARY(M)

Description:

A variable-length binary string

Maximum length:

M characters

Storage space:

Length of stored value, plus 1 byte to store length

Notes:

Just like VARCHAR, except MySQL treats the values stored in this column as non-textual byte strings.

TINYBLOB , TINYTEXT

Description:

A short, variable-length character string

Maximum length:

255 characters

Storage space:

Length of stored value, plus 1 byte to store length

Notes:

These types are basically equivalent to VARCHAR(255) BINARY and VARCHAR(255), respectively. However, these column types do not trim trailing spaces from inserted values. The only difference between TINYBLOB and TINYTEXT is that the former performs case-sensitive comparisons and sorts, while the latter does not.

BLOB , TEXT

Description:

A variable-length character string

Maximum length:

65,535 characters (65KB)

Storage space:

Length of stored value, plus 2 bytes to store length

Notes:

The only difference between BLOB and TEXT is that the former performs case-sensitive comparisons and sorts, while the latter does not.

MEDIUMBLOB , MEDIUMTEXT

Description:

A medium, variable-length character string

Maximum length:

16,777,215 characters (16.8MB)

Storage space:

Length of stored value, plus 3 bytes to store length

Notes:

The only difference between MEDIUMBLOB and MEDIUMTEXT is that the former performs case-sensitive comparisons and sorts, while the latter does not.

LONGBLOB , LONGTEXT

Description:

A long, variable-length character string

Maximum length:

4,294,967,295 characters (4.3GB)

Storage space:

Length of stored value, plus 4 bytes to store length

Notes:

The only difference between LONGBLOB and LONGTEXT is that the former performs case-sensitive comparisons and sorts, while the latter does not.

ENUM(value1, value2, ...)

Description:

A set of values from which a single value must be chosen for each row

Maximum Length:

One value chosen from up to 65,535 possibilities

Storage space:

· 1 to 255 values: 1 byte (8 bits)

· 256 to 65,535 values: 2 bytes (16 bits)

Notes:

Values in this type of field are stored as integers that represent the element selected. 1 represents the first element, 2 the second, and so on. The special value 0 represents the empty string '', which is stored if a value is assigned that doesn’t appear in a column declaration. NOT NULL columns of this type default to the first value in the column declaration if no particular default is assigned.

SET(value1, value2, ...)

Description:

A set of values, each of which may be set or not set

Maximum length:

Up to 64 values in a given SET column

Storage space:

· 1 to 8 values: 1 byte (8 bits)

· 9 to 16 values: 2 bytes (16 bits)

· 17 to 24 values: 3 bytes (24 bits)

· 25 to 32 values: 4 bytes (32 bits)

· 33 to 64 values: 8 bytes (64 bits)

Notes:

Values in this type of field are stored as integers representing the pattern of bits for set and unset values. For example, if a set contains eight values, and in a particular row the odd values are set, the binary representation 01010101 becomes the decimal value 85. Values may therefore be assigned either as integers, or as a string of set values, separated by commas (for example, 'value1,value3,value5,value7' = 85). Searches should be performed either with the LIKE operator, or the FIND_IN_SET function.

Date/Time Types

DATE

Description:

A date

Range:

'1000-01-01' to '9999-12-31', and '0000-00-00'

Storage space:

3 bytes (24 bits)

TIME

Description:

A time

Range:

'-838:59:59' to '838:59:59'

Storage space:

3 bytes (24 bits)

DATETIME

Description:

A date and time

Range:

'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

Storage space:

8 bytes (64 bits)

YEAR

Description:

A year

Range:

1901 to 2155, and 0000

Storage space:

1 byte (8 bits)

Notes:

You can specify a year value with a four-digit number (1901 to 2155, or 0000), a four-digit string ('1901' to '2155', or '0000'), a two-digit number (70 to 99 for 1970 to 1999, 1 to 69 for 2001 to 2069, or 0 for 0), or a two-digit string ('70' to '99' for 1970 to 1999, '00' to '69' for 2000 to 2069). Note that you cannot specify the year 2000 with a two-digit number, nor the year 0 with a two-digit string. Invalid year values are always converted to 0.

TIMESTAMP[(M)]

Description:

A timestamp (date/time), in YYYYMMDDHHMMSS format

Range:

19700101000000 to some time in 2037 on current systems

Storage space:

4 bytes (32 bits)

Notes:

An INSERT or UPDATE operation on a row that contains one or more TIMESTAMP columns will automatically update the first TIMESTAMP column in the row with the current date/time. This lets you use such a column as the “last modified date/time” for the row. Assigning a value of NULLto the column will have the same effect, thereby providing a means of “touching” the date/time. You can also assign actual values as you would for any other column. Allowable values for M are 14, 12, 10, 8, 6, 4, and 2, and correspond to the display formats YYYYMMDDHHMMSS ,YYMMDDHHMMSS , YYMMDDHHMM , YYYYMMDD , YYMMDD , YYMM , and YY respectively. Odd values from 1 to 13 automatically will be bumped up to the next even number, while values of 0 or greater than 14 are changed to 14.