YouTip

Defining the type of data fields in MySQL is crucial for optimizing your database. MySQL supports multiple types, which can be broadly categorized into three classes: numeric, date/time, and string (character) types. * * * ## Numeric Types MySQL supports all standard SQL numeric data types. These include exact numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC) and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL. The BIT data type stores bit-field values and is supported for MyISAM, MEMORY, InnoDB, and BDB tables. As an extension to the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type. | Type | Size | Range (Signed) | Range (Unsigned) | Usage | | --- | --- | --- | --- | --- | | TINYINT | 1 Byte | (-128, 127) | (0, 255) | Small integer value | | SMALLINT | 2 Bytes | (-32,768, 32,767) | (0, 65,535) | Large integer value | | MEDIUMINT | 3 Bytes | (-8,388,608, 8,388,607) | (0, 16,777,215) | Large integer value | | INT or INTEGER | 4 Bytes | (-2,147,483,648, 2,147,483,647) | (0, 4,294,967,295) | Large integer value | | BIGINT | 8 Bytes | (-9,223,372,036,854,775,808, 9,223,372,036,854,775,807) | (0, 18,446,744,073,709,551,615) | Very large integer value | | FLOAT | 4 Bytes | (-3.402823466E+38, -1.175494351E-38), 0, (1.175494351E-38, 3.402823466E+38) | 0, (1.175494351E-38, 3.402823466E+38) | Single-precision floating-point value | | DOUBLE | 8 Bytes | (-1.7976931348623157E+308, -2.2250738585072014E-308), 0, (2.2250738585072014E-308, 1.7976931348623157E+308) | 0, (2.2250738585072014E-308, 1.7976931348623157E+308) | Double-precision floating-point value | | DECIMAL | For DECIMAL(M,D), if M>D, then M+2, otherwise D+2 | Depends on M and D values | Depends on M and D values | Decimal value | * * * ## Date and Time Types The date and time types for representing time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each time type has a valid range and a "zero" value, which is used when an invalid value that MySQL cannot represent is specified. The TIMESTAMP type has a special auto-update feature, which will be described later. | Type | Size (bytes) | Range | Format | Usage | | --- | --- | --- | --- | --- | | DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | Date value | | TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | Time value or duration | | YEAR | 1 | 1901/2155 | YYYY | Year value | | DATETIME | 8 | '1000-01-01 00:00:00' to '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | Mixed date and time value | | TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC, ending at the **2,147,483,647** second, Beijing time **2038-1-19 11:14:07**, Greenwich Mean Time 2038-01-19 03:14:07 | YYYY-MM-DD hh:mm:ss | Mixed date and time value, timestamp | * * * ## String Types String types refer to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in queries. | Type | Size | Usage | | --- | --- | --- | | CHAR | 0-255 bytes | Fixed-length string | | VARCHAR | 0-65,535 bytes | Variable-length string | | TINYBLOB | 0-255 bytes | Binary string up to 255 characters | | TINYTEXT | 0-255 bytes | Short text string | | BLOB | 0-65,535 bytes | Binary large object for long text data | | TEXT | 0-65,535 bytes | Long text data | | MEDIUMBLOB | 0-16,777,215 bytes | Binary large object for medium-length text data | | MEDIUMTEXT | 0-16,777,215 bytes | Medium-length text data | | LONGBLOB | 0-4,294,967,295 bytes | Binary large object for very large text data | | LONGTEXT | 0-4,294,967,295 bytes | Very large text data | **Note**: In char(n) and varchar(n), the n in parentheses represents the number of characters, not the number of bytes. For example, CHAR(30) can store 30 characters. CHAR and VARCHAR types are similar, but they are stored and retrieved differently. They also differ in their maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval. BINARY and VARBINARY are 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 they have no character set, and sorting and comparison are based on the numeric values of the bytes in the column. BLOB is a binary large object that can hold a variable amount of data. There are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the maximum length of data they can store. There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Corresponding to the 4 BLOB types, they have different maximum storage lengths, and you can choose based on your needs. * * * ## Enumeration and Set Types * **ENUM**: Enumeration type, used to store a single value, chosen from a predefined set. * **SET**: Set type, used to store multiple values, chosen from a predefined set. * * * ## Spatial Data Types GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: Used to store spatial data (geographic information, geometric shapes, etc.).