MySQL is a very advanced database server that is used by different types of companies. MySQL is used for different projects and cases in order to store different types of data in different formats. MySQL supports different types of data for storage, query, update, etc.
MySQL General Data Types
MySQL database server provides a lot of different types of data and these data types can be categorized like
- Numeric Data Types
- Date and Time Data Types
- String Data Types
- Spatial Data Types
Numeric Data Types
Numeric data types are used to store numeric data like integer, floating-point which can be counted or used for calculations. MySQL provides a wide range of numeric data types to fit the user requirements while keeping the performance and space requirement less.
| Type | Length in Bytes | Minimum Value (Signed) | Maximum Value (Signed) | Minimum Value (Unsigned) | Maximum Value (Unsigned) |
|---|---|---|---|---|---|
| TINYINT | 1 | -128 | 127 | 0 | 255 |
| SMALLINT | 2 | -32768 | 32767 | 0 | 65535 |
| MEDIUMINT | 3 | -8388608 | 8388607 to | 0 | 16777215 |
| INT | 4 | -2147483648 | 2147483647 | 0 | 4294967295 |
| BIGINT | 8 | -9223372036854775808 | 92233720368 54775807 | 0 | 184467440737 09551615 |
INT is one of the most popular types where normal integer values are stored in INT type. INT can hold up to 11 digits where the range is between -2147483648 and 2147483647 if the stored value is signed. If the value is unsigned allowable range is from 0 to 4294967295.
TINYINT is used to store tiny integer values which can be up to 4 digits. If the tiny integer is signed values between -128 and 127 can be used but it not signed from 0 to 255 can be stored.
SMALLINT is used to store small integer values which can be between -32768 and 32767 for signed integer values. If not signed SMALLINT type can store from 0 to 65535.
MEDIUMINTis used to store medium-sized integers between -8388608 to 8388607 for signed medium integers. If a medium integer is not signed from 0 to 16777215 can be stored. Medium integer can store up to 9 digits.
BIGINT is used to store big integers which is between -9223372036854775808 to 9223372036854775807 for signed big integers. If not signed big integer type can store from 0 to 18446744073709551615. Big integer type can store up to 20 digits.
| Type | Length in Bytes | Minimum Value (Signed) | Maximum Value (Signed) | Minimum Value (Unsigned) | Maximum Value (Unsigned) |
|---|---|---|---|---|---|
| FLOAT | 4 | -3.402823466E+38 | -1.175494351E-38 | 1.175494351E-38 | 3.402823466E+38 |
| DOUBLE | 8 | -1.7976931348623 157E+ 308 | -2.22507385850720 14E- 308 | 0, and 2.22507385850720 14E- 308 | 1.797693134862315 7E+ 308 |
FLOAT(M,D)is used to store floating-point data types. As floating type numbers contains decimal and floating part. D is used to specify the decimal part of the number where M is total number of digits used. For example FLOAT(12,4) will store 12 digits in total and 4 of them will be used for decimal part and 8 digits will be used for the floating-point part. FLOAT can store 24 digits for decimal and floating-point in total.
DOUBLE(M,D) is used to store double-precision floating-point numbers where they can not be unsigned.M is used to define total digits or display length where D specifies the digit count of the decimal. For example, DOUBLE(18,4) will store 18 digits in total where 4 digits are used for decimal part and 14 digits will be used for the floating-point part.
DECIMAL(M,D) is used to store floating-point numbers as digits. M specifies the total numbers used and D is decimal part digits count. DECIMAL also called a fixed-point number. For example, DECIMAL(7,2) will store 7 digits in total where 5 of them used for decimal part and 2 of them will store the floating-point part. Which means from -99999.99 to 99999.99 can be stored inside it.
BIT(M) is used to store binary values. M is used to set a bit value count. BIT(5) can store 5 bit value which can be “01011”.
Date and Time Data Types
Date and Time data types are used to store the date and time information in different formats and precision.
| Types | Description | Display Format | Range |
|---|---|---|---|
| DATETIME | Use when you need values containing both date and time information. | YYYY-MM-DD HH:MM:SS | ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. |
| DATE | Use when you need only date information. | YYYY-MM-DD | ‘1000-01-01’ to ‘9999-12-31’. |
| TIMESTAMP | Values are converted from the current time zone to UTC while storing and converted back from UTC to the current time zone when retrieved. | YYYY-MM-DD HH:MM:SS | ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC |
DATE type is used to store only the date which contains the year, month and day information. The date type is formatted as YYYY-MM-DD by default but different format specifiers can be used for different presentation types as this will not affect the value. For example the November 20th, 1984 will be stores as 1984-11-20.
DATETIME is the most complex date and time format where both date and time information is stored in the same record. The DATETIME type is store as YYYY-MM-DD HH:MM:SS where YYYY is the year, MM is the month, DD is the day, HH is the hour, MM is minute and SS is second information.
TIMESTAMP is a different date and time specifier format which stores the seconds from January 1st, 1970 into the specified date and time. TIMESTAMP is also called Unix time format which is popular on Unix platforms.
TIME is the format used to store only the time information. TIME stores as HH:MM:SS where HH is the hour, MM is the minute and SS is the second part.
YEAR(M) is a date-time format where only date year information is stored. M can be 2 or 4 where year digits like 84, 1984 can be stored.YEAR can store from 70 to 69 for 2 digits like YEAR(2) and from 1901 to 2155 for 4 digits like YEAR(4).
String Data Types
String data types is another rich type MySQL data type category where different types and size of strings can be stored.
CHAR(M) is a fixed-length character storage data type that can store a maximum of 255 characters. M is used to specify the right-padded with spaces to the specified length. For example, CHAR(20) can store up to 20 characters.
VARCHAR(M) is used to created variable-length character types where a maximum of 255 characters can be stored. For example, VARCHAR(30) can store a maximum 30 characters. The character count is a must.
BLOB or TEXT datatypes are used to store big text and long strings which can be up to 65535 characters. BLOB is the short form of Binary Large Objects and used for store large amounts of binary data like image, executable file, different document formats. Data type TEXT is case sensitive which can be different text comparison operations like equal, bigger, etc. date type BLOB is binary and does not provide case sensitivity.
TINYBLOB or TINYTEXT is used store BLOB and TEXT type data which can be a maximum of 255 characters.
MEDIUMBLOG and MEDIUMTEXT are used to store BLOB and TEXT type data which can be a maximum of 16777215 characters.
LONGBLOB and LONGTEXT are used to store BLOB and TEXT type data which can be a maximum of 4294967295 characters.
ENUM is used to store enumeration data type which is data is one of the sets of variables or NULL value. For example, ENUM(“A”, “B”, “C”, “D”) are created enum values where one of them like “B” or NULL can be store in the ENUM type data field.
Spatial Data Types
OpenGIS is an open platform and consortium which created different date types to represent geographic information. In order to use and store spatial data types, some extensions should be installed for the MySQL database server.