InMotion Hosting Support Center

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

Date and Time data types, also known as temporal types, are relatively self-explanatory. Temporal data types can be used in equations 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 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 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.
Was this article helpful?

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!
Ask a Question

Forum Login

You are NOT logged in. You can still browse our Support Center.

To participate within our Community Support Forum:

Post a Comment

Email Address:
Phone Number:

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

0 Questions & Comments

Post a comment

Back to first comment | top

Need more Help?


Ask the Community!

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

Current Customers

Chat: Click to Chat Now E-mail:
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!