When you create a table in MySQL, you must specify the data type for each column. This appendix documents all of the column types that MySQL provides as of version 3.23.54a, current as of 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 (i.e. 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 (e.g. INT), this parameter does not actually restrict the range of values that may be stored. Instead, it causes spaces (or zeroes in the case of a ZEROFILL column—see below for details) to be added to the values so that they reach the desired display width when they’re displayed. Note also that the storage of 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 (i.e. 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, and are enabled by simply typing them after the column type, separated by spaces. Here are the available column attributes, and their meanings:
Values for the column always occupy their maximum display length, as the actual value is padded with zeroes. The option automatically sets the UNSIGNED option as well.
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 you won't need to store negative values.
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.