MySQL Datatypes are the possible types of data that are allowed to be stored for different attributes of the entities stored as tables in a database. These can be strings, numbers, time, date, year etc. Each data type allows only specific data while insertion.
You cannot define two data types for one attribute of an entity. For example mobile number field can either be numeric or alphanumeric but cannot be both. While choosing among MySQL datatypes of an attribute you must understand what kind of data you need to store, how you want to process it and how you want to order the stored data.
MySQL Datatypes
BLOB
Blob data type is used to store binary values to represent variable type of data. It may be used to store an image in binary form for an Identity card in employee database.
CHAR
A fixed length string value can be stored in a field having CHAR data type. Maximum characters allowed are 255. You have to give the size of the field. Once you define a field with specific length and if the actual number of characters stored is less than the length of the field with, rest of the empty character will go unutilized. For example if your declare field name student_name with char(30) and name stored is “James” then out of 30 characters allocated to the field only 5 will be used and 25 character spaces will go unutilized.
DATE
This MySQL datatype is used to store date values in a field. The format is YYYY-MM-DD.
- YYYY- year in four digits(e.g 2021)
- MM- month number in 2 digits (00-12)
- DD- day in 2 digits (00-31)
DATETIME
If you need to store date and time both in a field, you declare it as DATETIME. The format of the stored data will be YYYY-MM-DD HH-MM-SS.
- YYYY- year in four digits
- MM- month number in 2 digits (00-12)
- DD- day in 2 digits (00-31)
- HH- for hours in 2 digits (00-24)
- MM- Minutes in 2 digits (00-60)
- SS- seconds in two digits(00-60).
DECIMAL
If you have an entity attribute to store value in floating point or with its decimal part, use DECIMAL data type. You can use it for storing prices or other calculated values having decimal part.
ENUM
If you want that an attribute must store one value from some set of allowed values then use this data type to declare the field. If you want to store the name of the day of the week, or month name you can use ENUM.
INT
This number data type is most commonly used and it allows storing numeric values without decimal or float part. The range of values allowed is between -2147483648 and 2147483647.
SET
This string data type is used to store one or more values from a predefined set of values.
TEXT
This is another string type data type that allows up to 65535 characters. So if you need to store address, small remarks or notes TEXT data type can be used.
TIME
MySQL data type TIME can be used to store time values in HH-MM-SS format
TIMESTAMP
This data type is used to store date and time of an event in “YYYYMMDDHHMMSS” format. You can use this data type to store log information of transactions or user actions in a database.
VARCHAR
This is also called as varying character. You can store maximum 255 characters. If the stored string is less than the declared length of field, the data is packed in such a way that the empty spaces left after the value are not wasted.
YEAR
This MySQL data type is used to store year information in YYYY or YY format.
We have discussed the basic datatypes available in MySQL. Read more about MySQL datatypes here.