Learning the different data types in MySQL

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