## SQLWays - MySQL Data Types

This chapter describes the data types supported by MySQL.

- CHAR, NCHAR and VARCHAR
- BIGINT,INT, INTEGER, MEDIUMINT, SMALLINT and TINYINT
- DECIMAL, DEC and NUMERIC
- FLOAT
- DOUBLE, DOUBLE PRECISION and REAL
- DATE, TIME, DATETIME, TIMESTAMP and YEAR
- TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB
- TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT
- BIT and BOOL
- ENUM and SET

### CHAR, NCHAR and VARCHAR

[NATIONAL]CHAR[(n)] [BINARY]

A fixed-length character data. The range of n is 0 to 255 characters (1 to 255 prior to MySQL Version 3.23). When n is not specified the default value is 1.

When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed. BINARY keyword is given to sort and compare CHAR values in case-sensitive manner. NATIONAL CHAR (or NCHAR) is used to define that a CHAR column should use the default CHARACTER set. This is the default in MySQL.

If the column is defined as CHAR(0) and nullable then the column will occupy only one bit and can take two values: NULL or "" (empty string).

[NATIONAL] VARCHAR(n) [BINARY]

A variable-length string. The range of n is 0 to 255 characters (1 to 255 prior to MySQL Version 4.0.2). Value is stored in database using only as many characters as it contains, plus one byte to record the length.

When the value is stored trailing spaces are removed.

BINARY keyword is given to sort and compare VARCHAR values in case-sensitive manner.

### BIGINT, INT, INTEGER, MEDIUMINT, SMALLINT and TINYINT

BIGINT [UNSIGNED]

Integer data. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. Storage size is 8 bytes.

INT [UNSIGNED]

Integer data. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. Storage size is 4 bytes.

INTEGER [UNSIGNED]

This is a synonym for INT.

MEDIUMINT [UNSIGNED]

Integer data. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215. Storage size is 3 bytes.

SMALLINT [UNSIGNED]

Integer data. The signed range is -32768 to 32767. The unsigned range is 0 to 65535. Storage size is 2 bytes.

TINYINT [UNSIGNED]

Integer data. The signed range is -128 to 127. The unsigned range is 0 to 255. Storage size is 1 byte.

### DECIMAL, DEC and NUMERIC

DECIMAL[(p[,s])] [UNSIGNED]

An unpacked floating-point number. DECIMAL is stored as a string, using one character for each digit of the value.

The p specifies the total number of the digits not including the decimal point and, the `-' sign. Prior to MySQL Version 3.23, the p argument must include the space needed for the sign and the decimal point.

The s specifies the number of digits following the decimal point.

The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of p and s.

If p is omitted, the default is 10.

If s is omitted, the default is 0, that is, values will have no decimal point or fractional part.

If UNSIGNED is specified, negative values are not allowed.

DEC[(p[,s])] [UNSIGNED]
NUMERIC[(p[,s])] [UNSIGNED]

These are synonyms for DECIMAL. DEC datatype is available since MySQL version 4.00.

### FLOAT

FLOAT[(p[,s])] [UNSIGNED]

A floating-point number. Precision p can be <=24 for a single-precision floating-point number (allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38) and between 25 and 53 for a double-precision floating-point number.

The p is the precision, or the total number of digits. The s is the scale, or the number of digits to the right of the decimal point.

FLOAT without arguments stands for a single-precision floating-point number. In MySQL Version 3.23, this is a true floating-point value. In earlier MySQL versions, FLOAT(p) always has 2 decimals. All calculations in MySQL are done with double precision.

If UNSIGNED is specified, negative values are not allowed.

### DOUBLE, DOUBLE PRECISION and REAL

DOUBLE[(p,s)] [UNSIGNED]

A normal-size (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 p is the precision, or the total number of digits. The s is the scale, or the number of digits to the right of the decimal point.

DOUBLE without arguments stands for a double-precision floating-point number.

If UNSIGNED is specified, negative values are not allowed.

DOUBLE PRECISION[(p,s)] [UNSIGNED]
REAL[(p,s)] [UNSIGNED]

These are synonyms for DOUBLE.

### DATE, TIME, DATETIME, TIMESTAMP and YEAR

DATE

The DATE data type stores the date information (year, month, day). The supported range is January 1, 1000 to December 31, 9999.

TIME

The TIME data type stores the time information (hours, minutes, seconds). The supported range is -838 hours 59 minutes 59 seconds to 838 hours 59 minutes 59 seconds.

DATETIME

The DATETIME data type stores both date and time information (year, month, day, hours, minutes, seconds). The supported range is January 1, 1000 00 hours 00 minutes 00 seconds to December 31, 9999 23 hours 59 minutes 59 seconds.

TIMESTAMP[(p)]

The TIMESTAMP data type stores the date and time information (year, month, day, hours, minutes, seconds). The difference between TIMESTAMP and DATATYPE is that TIMESTAMP column automatically changes its value to the current date and time for the row, which is updated or inserted into the table.

The range is January 1, 1970 00 hours 00 minutes 00 seconds to January 1, 2037 00 hours 00 minutes 00 seconds.

The allowed values of p is 14 (or missing), 12, 8, 6, 4 or 2 (4 or 2 are not allowed in MySQL versions prior to v4.0). The p argument (4 bytes long) affects how a TIMESTAMP column is displayed. The TIMESTAMP(p) columns where p is 8 or 14 are reported to be numbers. The other TIMESTAMP(p) columns are reported to be strings.

YEAR[(2|4)]

The YEAR type is a 1-byte type used for representing years.

A year in 2- or 4-digit format (default is 4-digit). The allowable values are 1901 to 2155, 0000 in the 4-digit year format, and 1970 to 2069 if you use the 2-digit format (70-69). (The YEAR type is not available prior to MySQL Version 3.22.)

### TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB

A binary large object that can hold a variable amount of binary data. The sorting and comparison of the values for these objects is performed in case-sensitive manner.

TINYBLOB

A binary large object column with a maximum length of 255 (2^8 - 1) characters.

BLOB

A binary large object column with a maximum length of 65535 (2^16 - 1) characters.

MEDIUMBLOB

A binary large object column with a maximum length of 16777215 (2^24 - 1) characters.

LONGBLOB

A binary large object column with a maximum length of 4294967295 (2^32 - 1) characters.

### TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT

A character large object that can hold a variable amount of case-insensitive text data.

TINYTEXT

A character large object column with a maximum length of 255 (2^8 - 1) characters.

TEXT

A character large object column with a maximum length of 65535 (2^16 - 1) characters.

MEDIUMTEXT

A character large object column with a maximum length of 16777215 (2^24 - 1) characters.

LONGTEXT

A character large object column with a maximum length of 4294967295 (2^32 - 1) characters.

### BIT and BOOL

BIT
BOOL

These are synonyms for TINYINT.

### ENUM and SET

ENUM('value1','value2',...)

A character data that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special "" error value specified when the table is created. An ENUM can have a maximum of 65535 distinct values.

SET('value1','value2',...)

A character data that can have zero or more values, which must be chosen from the list of values 'value1', 'value2', ... specified when the table is created. A SET can have a maximum of 64 members.

[Main] [Table of Contents]
Copyright © 1999-2003 Ispirer Systems. All Rights Reserved.
SQLWays™ www.ispirer.com.