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 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.

The three main data types

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

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 TypeDescription
BigIntA large integer with a signed range of -9223372036854775808 to 9223372036854775807 and an unsigned range of 0 to 18446744073709551615.
BitA bit type. The range is from 1 to 64.
BooleanA synonymn for a TinyInt, it has a value of either 0 or 1.
DecimalA decimal number. The maximum number of digits before the decimal is 65 and after the decimal is 30.
DoubleA standard sized double precision number, ranging from -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308
FloatA floating point number, with ranges from -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.
IntA normal whole number, with a signed range of -2147483648 to 2147483647 and an unsigned range of 0 to 4294967295.
MediumIntA medium sized integer with a signed range of -8388608 to 8388607 and an unsigned range of 0 to 16777215.
SmallIntA small integer with a signed range of -32768 to 32767 and an unsigned range of 0 to 65535.
TinyIntA very small integer with a signed range of -128 to 127 and an unsigned range of 0 to 255

Date and Time Data Types

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 TypeDescription
DateA simple date with ranges from '1000-01-01' to '9999-12-31'.
DateTimeDate and Time combination, ranging from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'.
TimeStampA simple timestamp. The range of the timestamp is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999'
TimeThis is a simple time datatype. The range of a time datatype is from '-838:59:59.000000' to '838:59:59.000000'
YearA year. This can be done in either 2 digit or 4 digit format.

String (text) data types

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 TypeDescription
BinarySimilar to char, but with the data stored as byte strings instead of character strings.
BlobA blob column (storing binary bytes vs characters) with a max value of 65,535.
CharA character data type with a range of 0 - 255.
LongBlobA blob column with a max length of 4,294,967,295.
LongTextA text column with a max length of 4,294,967,295.
MediumBlobA blob column with a max length of 16,777,215.
MediumTextA text column with a max length of 16,777,215.
TinyBlobA blob column with a mas length of 255.
TinyTextA text column with a max length of 255.
TextA text colum with a max value of 65,535.
VarCharA character data type with variable length and a range of 0 to 65,535.
VarBinaryLike a varchar, but as above, the data stored as byte strings instead of character strings.
Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
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.
How did you find this article?
Please tell us how we can improve this article:
Email Address
Name

new! - Enter your name and email address above and we will post your feedback in the comments on this page!

Did you find this article helpful?

Post a Comment

Name:
Email Address:
Phone Number:
Comment:
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Need More Help?

Help Center Search

Current Customers

Email: support@WebHostingHub.com Ticket: Submit a Support Ticket
Call: 877-595-4HUB (4482)
757-416-6627 (Intl.)
Chat: Click To Chat Now

Ask the Community

Get help with your questions from our community of like-minded hosting users and Web Hosting Hub Staff.

Not a Customer?

Get web hosting from a company that is here to help.
}