When working with MySQL and its Structured Query Language (SQL), you may run across code where you need to undertand how it talks to the database. While SQL is a relatively simple language, knowing things such as data types will help you in your troubleshooting or coding. Below is a brief description of the main datatypes you will find used in your php code or the code within your Content Management Systems such as WordPress, Joomla, etc.
DataTypes used in MySQL
What is a data type?
A data type is a simple term used in programming. It is a classification of data that helps determine how it can be used within a program. For example, even though 2 is a number, if it is classified as a text data type, such as a string, then it cannot be used in a mathematical equation, whereas the same number stored in an integer column is able to be used mathematically.
The three main data types
Data types can be broken down into three types, they are numeric, date and time, and string (or text) type. These major types will determine how they can be used. There are subtypes that further refine the major data type, usually defined by maximum size for setting aside storage space in the database.
Numeric Data Types
Numeric data types can be used like any normal number. They can be added, subtracted, multiplied, and divided. It should seem obvious that text cannot be stored in a numeric data type. The subtypes are listed below with quick descriptions.
| Data Type | Description |
|---|---|
| BigInt | A large integer with a signed range of -9223372036854775808 to 9223372036854775807 and an unsigned range of 0 to 18446744073709551615. |
| Bit | A bit type. The range is from 1 to 64. |
| Boolean | A synonymn for a TinyInt, it has a value of either 0 or 1. |
| Decimal | A decimal number. The maximum number of digits before the decimal is 65 and after the decimal is 30. |
| Double | A standard sized double precision number, ranging from -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308 |
| Float | A floating point number, with ranges from -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. |
| Int | A normal whole number, with a signed range of -2147483648 to 2147483647 and an unsigned range of 0 to 4294967295. |
| MediumInt | A medium sized integer with a signed range of -8388608 to 8388607 and an unsigned range of 0 to 16777215. |
| SmallInt | A small integer with a signed range of -32768 to 32767 and an unsigned range of 0 to 65535. |
| TinyInt | A very small integer with a signed range of -128 to 127 and an unsigned range of 0 to 255 |
Date and Time Data Types
Date and Time data types, also known as temporal types, are relatively self-explanatory. Temporal data types can be used in equations to calculate time differences. Below are the different date and time datatypes used in MySQL.
| Date Type | Description |
|---|---|
| Date | A simple date with ranges from ‘1000-01-01’ to ‘9999-12-31’. |
| DateTime | Date and Time combination, ranging from ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’. |
| TimeStamp | A simple timestamp. The range of the timestamp is ‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19 03:14:07.999999’ |
| Time | This is a simple time datatype. The range of a time datatype is from ‘-838:59:59.000000’ to ‘838:59:59.000000’ |
| Year | A year. This can be done in either 2 digit or 4 digit format. |
String (text) data types
String data types are largely for text or binary date storage. Numbers stored in a text based field are not available for use in mathematical equations without converting them.
| Data Type | Description |
|---|---|
| Binary | Similar to char, but with the data stored as byte strings instead of character strings. |
| Blob | A blob column (storing binary bytes vs characters) with a max value of 65,535. |
| Char | A character data type with a range of 0 – 255. |
| LongBlob | A blob column with a max length of 4,294,967,295. |
| LongText | A text column with a max length of 4,294,967,295. |
| MediumBlob | A blob column with a max length of 16,777,215. |
| MediumText | A text column with a max length of 16,777,215. |
| TinyBlob | A blob column with a max length of 255. |
| TinyText | A text column with a max length of 255. |
| Text | A text column with a max value of 65,535. |
| VarChar | A character data type with variable length and a range of 0 to 65,535. |
| VarBinary | Like a varchar, but as above, the data stored as byte strings instead of character strings. |
