I have made a list of MySQL data types for my own convenience. Maybe it's of use for somebody else too.
Data types
NUMERIC
BIT
Bit field type, 1 to 64 bits per value (default: 1)
TINYINT
A very small integer, between -128 to 127
BOOL, BOOLEAN
Synonyms for TINYINT(1), where 0 = false and non zero value (1 etc.) = true
SMALLINT
A small integer, between -32768 to 32767
MEDIUMINT
A medium sized integer, between -8388608 to 8388607
INT
A normal size integer, between -214748364 to 214748364
INTEGER
Synonym for INT
BIGINT
A large integer, between -9223372036854775808 to 9223372036854775808
SERIAL
Synonym for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
FLOAT
A small (single-precision) floating-point number. -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
DOUBLE
A normal-size (double-precision) floating-point number
DECIMAL
A packed "exact" fixed-point number
DEC
Synonym for DECIMAL
All integer types can have an optional (non-standard) attribute UNSIGNED. Unsigned values can be used when you want to allow only non-negative numbers in a column and you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.
Integer or floating-point data types can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.
DATE AND TIME
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows assignment of values to DATE columns using either strings or numbers.
DATETIME
A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows assignment of values to DATETIME columns using either strings or numbers.
TIMESTAMP
A timestamp. The range is '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the “zero” TIMESTAMP value.
A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value.
A TIMESTAMP value is returned as a string in the format 'YYYY-MM-DD HH:MM:SS' with a display width fixed at 19 characters. To obtain the value as a number, you should add +0 to the timestamp column.
TIME
A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows assignment of values to TIME columns using either strings or numbers.
YEAR[(2|4)]
A year in two-digit or four-digit format. The default is four-digit format. In four-digit format, the allowable values are 1901 to 2155, and 0000. In two-digit format, the allowable values are 70 to 69, representing years from 1970 to 2069. MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, which loses the part after the first non-numeric character.) To work around this problem, you can convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
STRING TYPES
The CHARACTER SET attribute specifies the character set, and the COLLATE attribute specifies a collation for the character set.
Example:
CREATE TABLE t
(
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);
CHARSET
Synonym for CHARACTER SET
Specifying the CHARACTER SET binary attribute for a character data type causes the column to be created as the corresponding binary data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB.
ASCII
Shorthand for CHARACTER SET latin1
UNICODE
Shorthand for CHARACTER SET ucs2
BINARY
Shorthand for specifying the binary collation of the column character set. In this case, sorting and comparison are based on numeric character values.
CHAR(M)
A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1. Shorthand for CHARACTER.
VARCHAR(M)
A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters. Shorthand for CHARACTER VARYING.
BINARY(M)
The BINARY type is similar to the CHAR type, but stores binary byte strings rather than non-binary character strings. M represents the column length in bytes.
VARBINARY(M)
The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes.
TINYBLOB
A BLOB column with a maximum length of 255 (28 – 1) bytes. Each TINYBLOB value is stored using a one-byte length prefix that indicates the number of bytes in the value.
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 255 (28 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TINYTEXT value is stored using a one-byte length prefix that indicates the number of bytes in the value.
BLOB(M)
A BLOB column with a maximum length of 65,535 (216 – 1) bytes. Each BLOB value is stored using a two-byte length prefix that indicates the number of bytes in the value. An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest BLOB type large enough to hold values M bytes long.
TEXT(M)
[CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 65,535 (216 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TEXT value is stored using a two-byte length prefix that indicates the number of bytes in the value.
An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.
MEDIUMBLOB
A BLOB column with a maximum length of 16,777,215 (224 – 1) bytes. Each MEDIUMBLOB value is stored using a three-byte length prefix that indicates the number of bytes in the value.
MEDIUMTEXT
[CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 16,777,215 (224 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each MEDIUMTEXT value is stored using a three-byte length prefix that indicates the number of bytes in the value.
LONGBLOB
A BLOB column with a maximum length of 4,294,967,295 or 4GB (232 – 1) bytes. The effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a four-byte length prefix that indicates the number of bytes in the value.
LONGTEXT
[CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a four-byte length prefix that indicates the number of bytes in the value.
ENUM('value1','value2',...)
[CHARACTER SET charset_name] [COLLATE collation_name]
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. An ENUM column can have a maximum of 65,535 distinct values. ENUM values are represented internally as integers.
SET('value1','value2',...)
[CHARACTER SET charset_name] [COLLATE collation_name] A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET column can have a maximum of 64 members. SET values are represented internally as integers.
DEFAULT VALUES
The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.
BLOB and TEXT columns cannot be assigned a default value.
If a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:
If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.
If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:
If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For non-transactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.
For a given table, you can use the SHOW CREATE TABLE statement to see which columns have an explicit DEFAULT clause.
For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.
For date and time types other than TIMESTAMP, the default is the appropriate “zero” value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See Section 10.3, “Date and Time Types”.
For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.
SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.
Recent Comments