MySQL

In this tutorial we will learn about some of the commonly used data types in MySQL.

Data types of a column tells us about the type of data stored in the column.

In this tutorial we will be discussing the following three types.

Data types under this category holds numerical data.

They hold exact integer values like 10, 0, -100 etc. and we can summaries the range and size of the data type as follows.

We can also use `UNSIGNED`

to store unsigned value like 0, 10, 20, 100 etc. No negative value.

Data Type | Size (byte) | Min value (signed) | Max value (signed) | Min value (unsigned) | Max value (unsigned) |
---|---|---|---|---|---|

TINYINT | 1 | -128 | 127 | 0 | 255 |

SMALLINT | 2 | -32768 | 32767 | 0 | 65535 |

MEDIUMINT | 3 | -8388608 | 8388607 | 0 | 16777215 |

INT | 4 | -2147483648 | 2147483647 | 0 | 4294967295 |

BIGINT | 8 | -9223372036854775808 | 9223372036854775807 | 0 | 18446744073709551615 |

We use the fixed-point data types to store exact value and preserve exact precision like geographical data or monetary data.

We use `DECIMAL`

and `NUMERIC`

data types for this.

Syntax: `DECIMAL(M,N)`

Where, M denotes the precision i.e., total number of significant digits that are stored for the value.

N denotes the scale i.e., total number of digits that can be stored after the decimal point.

Example: `salary DECIMAL(10,2)`

In the above example, precision is 10 and scale is 2 i.e., total significant digits that are stored for the value is 10 and total number of digits after the decimal point is 2. Like: 10.15 or 999.20 etc.

We use this data type to store approximate value with decimal part.

For single precision values we use `FLOAT`

which takes 4 bytes space.

For double precision values we use `DOUBLE`

which takes 8 bytes space.

Syntax: `FLOAT(M,N)`

Here M represents the total number of digits that can be stored. Whereas, N represents the total number of digits after the decimal point.

Example: `velocity FLOAT(10,4)`

This means we can store total 10 digits out of which 4 digits can be stored after the decimal point. Like: -10.1234 or 999.4321 etc.

Data types in this category are used to hold string value.

`CHAR`

and `VARCHAR`

data types are used to store string values.

`CHAR`

data type gives us a fixed width column size.

Syntax: `CHAR(N)`

Where, N is the total number of characters we can save in the column. Range of N = 0 to 255.

Size is also equal to N so, CHAR(10) means it can hold 10 characters and column size is 10 bytes.

Note! `CHAR(4)`

can hold max 4 characters. But if less characters are entered then it is right padded with white space and saved in the column. And when the value is retrieved the trailing right white spaces are removed.

`VARCHAR`

data type gives us a variable width column size.

Syntax: `VARCHAR(N)`

Where, N is the total number of characters we can save in the column. Range of N = 0 to 65535.

If N <= 255 then, size of the column VARCHAR(N) is 1+N bytes. So, value is saved as 1 byte prefix plus data bytes.

And if N > 255 then, size of the column VARCHAR(N) is 2+N bytes. So, value is saved as 2 bytes prefix plus data bytes.

So, the prefix byte tells about the length of the data saved in the VARCHAR column.

Trailing spaces in case of VARCHAR is retained when saved in the column and when retrived from the column.

Example: VARCHAR(100) if assigned a string value "Hello" will take up 6 bytes, 5 for the string "Hello" and 1 for the prefix byte.

`ENUM`

is a string object that can take value from a list of permitted values.

Syntax: `ENUM('value1', 'value2', ...)`

Max number of values: 65535.

Example: A user account can be in ACTIVE, INACTIVE and DELETED state and we can denote this using ENUM as follows `account_status ENUM('ACTIVE', 'INACTIVE', 'DELETED');`

.

`BLOB`

is a binary large object and is used to save data as byte (binary) string. There are 4 types of BLOB variation namely `TINYBLOB`

, `BLOB`

, `MEDIUMBLOB`

and `LONGBLOB`

.

`TEXT`

is used to save data as character (non-binary) string. There are 4 types of TEXT variation namely `TINYTEXT`

, `TEXT`

, `MEDIUMTEXT`

and `LONGTEXT`

.

Type | Storage size |
---|---|

TINYBLOB, TINYTEXT | L + 1 bytes, where L < 2^{8} |

BLOB, TEXT | L + 2 bytes, where L < 2^{16} |

MEDIUMBLOB, MEDIUMTEXT | L + 3 bytes, where L < 2^{24} |

LONGBLOB, LONGTEXT | L + 4 bytes, where L < 2^{32} |

Where, L represents the actual length (in bytes) of a given string value.

Example: If a column data type is BLOB then its size is L+3. THe extra 3 bytes is to store the length of the value and L is the actual length (in bytes) of a given string value.

Data types of this category is used to store date and time value.

We use the `DATE`

data type when we want to store date value.

Syntax: 'YYYY-MM-DD' where, YYYY is year 1000-9999, MM is month 1-12 and DD is date 1-31.

So, the range for `DATE`

is '1000-01-01' to '9999-12-31'.

We use the `DATETIME`

data type when we want to store date and time value.

Syntax: 'YYYY-MM-DD HH-MM-SS' where, YYYY is year 1000-9999, MM is month 1-12 and DD is date 1-31.

HH is hour 00-23, MM is minute 00-59 and SS is second 00-59.

So, the range for `DATETIME`

is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

We use the `TIMESTAMP`

data type to store value that have date and time part.

Syntax: 'YYYY-MM-DD HH-MM-SS' where, YYYY is year 1000-9999, MM is month 1-12 and DD is date 1-31.

HH is hour 00-23, MM is minute 00-59 and SS is second 00-59.

So, the range for `TIMESTAMP`

is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.