Column and Data Types in MySQL

There are three basic column types in MySQL: numerical types, string or text types, and date and time types. We will look at each type in turn .

Numerical Types

Numerical types are used for storing numbers . In our example, we used the types int (integer) and float (floating-point number). These represent the two subtypes of numerical types: the exact numerical types and the approximate numerical types.

Numerical types may be constrained by a display width M and, for floating-point types, a number of decimal places, D. These numbers go after the declaration; for example:

 salary decimal(10, 2) 

This has a display width of 10 with two digits after the decimal point.

You may choose to use neither parameter, the display width only, or both the display width and the number of decimal places.

Numerical types may also be followed by the keywords UNSIGNED and/or ZEROFILL .

The UNSIGNED keyword specifies that the column contains only zero or positive numbers. The ZEROFILL keyword means that the number will be displayed with leading zeroes.

The exact types are detailed in the following text.

NUMERIC or DECIMAL

These types are the same, and DECIMAL may also be abbreviated to DEC . These types are used to store exact floating-point values and are typically used to store monetary values. They have the same range as double-precision floating-point numbers.

INTEGER and Variations

This type can be abbreviated as INT . This is a standard integer, stored in 4 bytes, giving a range of 2 ³² possible values. There are also several variations on INT :

• A TINYINT is 1 byte (2 ⁸ possible values). The keywords BIT and BOOL are synonyms for TINYINT .

• A SMALLINT is 2 bytes (2 ¹⁶ possible values).

• A MEDIUMINT is 3 bytes (2 ²⁴ possible values).

• A BIGINT is 8 bytes (2 ⁶⁴ possible values).

The approximate types are detailed in the following text.

FLOAT

This is a single-precision floating-point number. It can represent a positive number between 1.18x10 ^-38 to 3.40x10 ³⁸ and a similar range of negative numbers.

DOUBLE

This is a double-precision floating-point number. Synonyms for DOUBLE are REAL and DOUBLE PRECISION . They can represent a positive number between 2.23x10 ^-308 to 1.80x10 ³⁰⁸ and a similar range of negative numbers.

String and Text Types

MySQL supports various string and text types. The basic types are CHAR , VARCHAR , TEXT , BLOB , ENUM , and SET . We will discuss each of these in turn.

CHAR

CHAR is used to store fixed-length strings. As in the employee database, CHAR is usually followed by a string length, for example CHAR(20) . If you do not specify a length, you will get a CHAR(1) . The maximum length of a CHAR is 255 characters . When CHAR s are stored, they will always be the exact length you specify. This is achieved by padding the contents of the column with spaces. These spaces are automatically stripped when the contents of a CHAR column are retrieved.

Obviously, storing a CHAR takes up more space on disk than storing an equivalent variable-length string. The trade-off is that it is faster to retrieve rows from a table in which all the columns are of fixed widths (that is, CHAR , numeric, or date ). Often, speed is more important than disk space, so you may choose to make text fields that are not going to vary a great deal anyway into CHAR as a small optimization.

Both CHAR and VARCHAR types can be preceded with the keyword NATIONAL , meaning to restrict the contents to the standard character set. This is the default in MySQL, so you need only use it for cross-platform compatibility.

CHAR and VARCHAR can both be followed by the keyword BINARY , meaning that they should be treated as case sensitive when evaluating string comparisons. The default is for strings to be compared in a case insensitive fashion.

VARCHAR

VARCHAR stores variable-length strings. You specify the width in parentheses after the type, for example, VARCHAR(10) . The range is 0 to 255.

TEXT, BLOB, and Variations

The TEXT types are used for storing longer pieces of text than you can fit in a CHAR or VARCHAR . BLOB stands for Binary Large OBject. These types are the same except that BLOB s are intended to store binary data rather than text. Comparisons on BLOB s are case sensitive, and on TEXT s, they are not. They are both variable in length, but both come in various sizes:

• TINYTEXT or TINYBLOB can hold up to 255 (that's 2 ⁸ -1) characters or bytes.

• TEXT or BLOB can hold up to 65,535 (2 ¹⁶ -1) characters or bytes (64KB).

• MEDIUMTEXT or MEDUIMBLOB can hold up to 16,777,215 (2 ²⁴ -1) characters or bytes (16MB).

• LONGTEXT or LONGBLOB can hold up to 4,294,967,295 (2 ³² -1) characters or bytes (4GB).

ENUM

This type allows you to list a set of possible values. Each row can contain one value from the enumerated set. You declare an ENUM as follows :

 gender enum('m', 'f') 

Enumerated types can also be NULL , so the possible values of gender are m , f , NULL , or error .

SET

The SET type is similar to ENUM except that rows may contain a set of values from the enumerated set.

Date and Time Types

MySQL supports various date and time types, as discussed next .

DATE

The date type stores a date. MySQL expects the date in ISO year-month-day order, avoiding trans-Atlantic arguments. Dates are displayed as YYYY-MM-DD.

TIME

This type stores a time, displayed as HH:MM:SS.

DATETIME

This is a combination of the previous types. The format is YYYY-MM-DD HH:MM:SS.

TIMESTAMP

This is a useful column type. If you do not set this column in a particular row, or set it to NULL , it will store the time that row was inserted or last changed.

When you retrieve a timestamp, it will be displayed in the DATETIME format. This has changed significantly from MySQL 4.0 to 4.1. You could previously set the display width when declaring a column as of type TIMESTAMP .

YEAR

This type stores a year. When you declare a column of this type, you can declare it as YEAR(2) or YEAR(4) to specify the number of digits. YEAR(4) is the default. YEAR(2) represents the range 1970 to 2069.