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.
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 clasified 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.
Data types can be broken down into three categories, they are numeric, date and time, and string (or text) type. These major types will determine how the data is used. There are also subtypes that further refine the major data type, usually defined by maximum size for setting aside storeage space in the database.
Numeric data types can be used like any normal number. They can perform normal mathematical operations such as add, subtract, multiply, and divide. Text data 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 |
Temporal data types, more commonly known as Date and Time, are all about dates and times. These data types are normally used in where there is a need to calcuate 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 data types are are almost exclusively used for text or even 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 mas length of 255. |
| TinyText | A text column with a max length of 255. |
| Text | A text colum 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. |
| Email: | support@WebHostingHub.com | Ticket: | Submit a Support Ticket |
|---|---|---|---|
| Call: |
877-595-4HUB (4482) 757-416-6627 (Intl.) |
Chat: | Click To Chat Now |
We value your feedback!
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
new! - Enter your name and email address above and we will post your feedback in the comments on this page!