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.
UNSIGNED
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.
DECIMAL
NUMERIC
Syntax: DECIMAL(M,N)
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)
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.
FLOAT
For double precision values we use DOUBLE which takes 8 bytes space.
DOUBLE
Syntax: FLOAT(M,N)
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)
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
VARCHAR
CHAR data type gives us a fixed width column size.
Syntax: CHAR(N)
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.
CHAR(4)
VARCHAR data type gives us a variable width column size.
Syntax: VARCHAR(N)
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.
ENUM
Syntax: ENUM('value1', 'value2', ...)
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');.
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.
BLOB
TINYBLOB
MEDIUMBLOB
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.
TEXT
TINYTEXT
MEDIUMTEXT
LONGTEXT
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.
DATE
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.
DATETIME
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.
TIMESTAMP
So, the range for TIMESTAMP is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.