Data Types in MySQL
| Previous | Home | Next |
The data types in mysql is divided into few types, they are list as below:
- Numeric
- String
- Date or time
- Large Object(LOB)
Numeric data types
The following are the numeric datatypes in mysql are:
| Data Type | Description |
| BIT | Very small integer value that is equivalent to TINYINT(1). Signed values range from -128 to 127. Unsigned values range from 0 to 255. |
| TINYINT(m) | Very small integer value. Signed values range from -128 to 127. Unsigned values range from 0 to 255. |
| SMALLINT(m) | Small integer value. Signed values range from -32768 to 32767. Unsigned values range from 0 to 65535. |
| MEDIUMINT(m) | Medium integer value. Signed values range from -8388608 to 8388607. Unsigned values range from 0 to 16777215. |
| INT(m) | Standard integer value. Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295. |
| INTEGER(m) | Standard integer value. Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295. |
| BIGINT(m) | Big integer value. Signed values range from -9223372036854775808 to 9223372036854775807. Unsigned values range from 0 to 18446744073709551615. |
| DECIMAL(m,d) | Unpacked fixed point number. m defaults to 10, if not specified. d defaults to 0, if not specified. |
| DEC(m,d) | Unpacked fixed point number. m defaults to 10, if not specified. d defaults to 0, if not specified. |
| NUMERIC(m,d) | Unpacked fixed-point number. m defaults to 10, if not specified. d defaults to 0, if not specified. |
| FIXED(m,d) | Unpacked fixed-point number. m defaults to 10, if not specified.d defaults to 0, if not specified(Introduced in MySQL 4.1). |
| FLOAT(m,d) | Single precision floating point number. |
| DOUBLE(m,d) | Double precision floating point number. |
| DOUBLE PRECISION(m,d) | Double precision floating point number. |
| REAL(m,d) | Double precision floating point number. |
| FLOAT(p) | Floating point number. Where p is the precision.This is a synonym for the DOUBLE datatype. |
| BOOL | Synonym for TINYINT(1). Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE. |
| BOOLEAN | Synonym for TINYINT(1). Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE. |
String data types
The following are the string datatypes in mysql are:
| Data Type | Description |
| CHAR(size) | Maximum size of 255 characters. Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters. |
| VARCHAR(size) | Maximum size of 255 characters. Where size is the number of characters to store. Variable-length string. |
| TINYTEXT(size) | Maximum size of 255 characters. Where size is the number of characters to store. |
| TEXT(size) | Maximum size of 65,535 characters. Where size is the number of characters to store. |
| MEDIUMTEXT(size) | Maximum size of 16,777,215 characters. Where size is the number of characters to store. |
| LONGTEXT(size) | Maximum size of 4GB or 4,294,967,295 characters. Where size is the number of characters to store. |
| BINARY(size) | Maximum size of 255 characters. Where size is the number of binary characters to store. Fixed-length strings. Space padded on right to equal size characters. (Introduced in MySQL 4.1.2) |
| VARBINARY(size) | Maximum size of 255 characters. Where size is the number of characters to store. Variable-length string.(Introduced in MySQL 4.1.2) |
Date or Time data types
The following are the date or time datatypes in mysql are:
| Data Type | Description |
| DATE | Values range from '1000-01-01' to '9999-12-31'. Displayed as 'YYYY-MM-DD'. |
| DATETIME | Values range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Displayed as 'YYYY-MM-DD HH:MM:SS'. |
| TIMESTAMP(m) | Values range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. Displayed as 'YYYY-MM-DD HH:MM:SS'. |
| TIME | Values range from '-838:59:59' to '838:59:59'. Displayed as 'HH:MM:SS'. |
| YEAR[(2|4)] | Year value as 2 digits or 4 digits. Default is 4 digits. |
Large Object(LOB) data types
The following are the large object datatypes in mysql are:
| Data Type | Description |
| TINYBLOB | Maximum size of 255 bytes. |
| BLOB(size) | Maximum size of 65,535 bytes. Where size is the number of characters to store (size is optional and was introduced in MySQL 4.1) |
| MEDIUMBLOB | Maximum size of 16,777,215 bytes. |
| LONGTEXT | Maximum size of 4GB or 4,294,967,295 characters. |
| Previous | Home | Next |