MySQL Data Types
MySQL data type definitions in the field of your database optimization is very important.
MySQL supports many types can be divided into three categories: numeric, date / time and string (character) types.
Value Type
MySQL supports all standard SQL numeric data types.
These types include strict numeric data types (INTEGER, SMALLINT, DECIMAL and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL and DOUBLE PRECISION).
Keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.
BIT data type stores bit-field values and supports MyISAM, MEMORY, InnoDB and BDB tables.
As the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT and BIGINT. The following table shows the storage and range for each integer type required.
| Types of | size | Range (Signed) | Range (unsigned) | use |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128,127) | (0,255) | Small integer values |
| SMALLINT | 2 bytes | (768,32 -32 767) | (535 0,65) | Integer value |
| MEDIUMINT | 3 bytes | (-8388 608,8 388 607) | (0,16 777,215) | Integer value |
| INT or INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | Integer value |
| BIGINT | 8 bytes | (-9,233,372,036,854,775 808,9 223,372,036,854,775 807) | (0,18 446,744,073,709,551 615) | Maximum integer value |
| FLOAT | 4 bytes | (-3.402 823 466 E + 38,1.175 494 351 E-38), 0, (1.175 494 351 E-38,3.402 823 466 351 E + 38) | 0, (1.175 494 351 E-38,3.402 823 466 E + 38) | Single-precision floating-point values |
| DOUBLE | 8 bytes | (1.797 693 134 862 315 7 E + 308,2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E + 308) | 0, (2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E + 308) | Double-precision floating-point values |
| DECIMAL | Of DECIMAL (M, D), if M> D, M + 2 is otherwise D + 2 | It depends on the values of M and D | It depends on the values of M and D | Decimal value |
Date and Time Types
It indicates the date and time for the type of the time value of DATETIME, DATE, TIMESTAMP, TIME, and YEAR.
Each type has a range of valid values time and a "zero" value, use the "zero" value when the value of the specified unlawful MySQL can not be represented.
TIMESTAMP type has proprietary automatic update feature, which will be described later.
| Types of | size (byte) | range | format | use |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 / 9999-12-31 | YYYY-MM-DD | Date values |
| TIME | 3 | '-838: 59: 59' / '838: 59: 59' | HH: MM: SS | Time value or the duration |
| YEAR | 1 | 1901/2155 | YYYY | Year Value |
| DATETIME | 8 | 1000-01-0100: 00: 00 / 9999-12-31 23:59:59 | YYYY-MM-DD HH: MM: SS | Mixing date and time values |
| TIMESTAMP | 4 | Sometime 00/2037 Year: 1970-01-01 00:00 | YYYYMMDD HHMMSS | Mixing date and time value, a timestamp |
String type
It refers to a string type CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use these types in the query.
| Types of | size | use |
|---|---|---|
| CHAR | 0-255 bytes | Fixed-length string |
| VARCHAR | 0-65535 bytes | Variable-length strings |
| TINYBLOB | 0-255 bytes | No more than 255 characters in binary string |
| TINYTEXT | 0-255 bytes | Short text strings |
| BLOB | 0-65535 bytes | Long text data in binary form |
| TEXT | 0-65535 bytes | Long text data |
| MEDIUMBLOB | 0-16777215 bytes | Binary form of medium length text data |
| MEDIUMTEXT | 0-16777215 bytes | Medium length text data |
| LONGBLOB | 0-4294967295 bytes | Great text data in binary form |
| LONGTEXT | 0-4294967295 bytes | Great text data |
Similar CHAR and VARCHAR type, but they save and retrieve different ways. Their maximum length and whether trailing spaces are retained, it is also different. Storage or retrieval process without case conversion.
BINARY and VARBINARY type is similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes.
BLOB is a binary large object that can hold a variable amount of data. There are four BLOB types: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. They differ only accommodate the maximum length value.
There are four TEXT types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. These correspond to the four kinds of BLOB type, have the same maximum lengths and storage requirements.