Direct Switch to Contents:
What is MySQL Datatype?
Before going to start creating Database and use it, we need to first take an overview and understand what are the datatypes in MySQL? If you have learned some programming languages like C/C++ or Java then it will be easy for you guys to learn like Integer, Character, String, Date, Time, Double, Text, etc.. Mostly the basic concepts for Datatypes are the same in any language whether it is C++, Java, or SQL.
We need datatypes to differentiate the different types of data in the fields. So now suppose if we have two fields or columns in a Table are name and age then for the name we should have Character Acceptance Datatype and for Age should have Integer acceptance datatype.
Similarly, there are several different more fields, and columns can be in a table. So to differentiate the data and its type we need datatypes.
MySQL supports SQL various datatypes like integer, Character, String, Date, Time, Double, Text, etc.
Before going to the each data type let’s take an example-
A database name Bollywood in which we have 4 column or fields are as follow:
- Movie name
- Movie year
- Actor name
- Actor age.
Now, these four fields will have respected data like numerical, character, or string. Let us have a table to understand it better.
| Movie Name | Movie Year | Actor Name | Actor Age |
| Barfi | 2012 | Ranbir Kapoor | 37 |
| Biwi no. 1 | 1999 | Salman Khan | 54 |
| 2 States | 2014 | Arjun Kapoor | 34 |
| Fan | 2016 | Shahrukh Khan | 54 |
| Kabir Singh | 2019 | Shahid Kapoor | 39 |
| Dabangg 3 | 2019 | Salman Khan | 54 |
Here we can see that Movie Year and Actor Age are the integer type field, Actor Name is the String Type, and Movie name is the Both Numerical and Character that is Varchar, we will cover in brief one by one.
Taking An Another Example:
Have a look the sanp:
Broadly categories these datatypes into three are as follow:
- Numerical
- String
- Date and Time
Let us understand and discuss above all three MySQL datatype each one by one in briefly.
1. Numerical Data Types:
Whenever we need a number in any column or field of the table then we use this MySQL datatype. In addition to numbers can be natural, whole, decimal.
Int:
It is a normal-size integer value that can be signed and unsigned. The signed value range is from -2147483648 to 2147483647 and the unsigned value range is from 0 to 4294967295. It stores 4 bytes of storage. For instance, 1995 is an int value.
TINYINT(size):
Allows signed integers -128 to 127 and 0 to 255 unsigned integers.
It stores 1 byte of storage.
SMALLINT(size): Allows signed integers from -32768 to 32767 and 0 to 65535 unsigned integers.
It stores 2 bytes of storage.
MEDIUMINT(size):
Allows signed integers from -8388608 to 8388607 and 0 to 16777215 unsigned integers.
It stores 3 bytes of storage.
Bigint:
It is a large-size integer value that can be signed and unsigned. The signed value range is from -9223372036854775808 to 9223372036854775807 and the unsigned value range is from 0 to 18446744073709551615.
It stores 8 bytes of storage
Float(L,D):
It is a normal-sized floating-point number that can not be unsigned value. Here D is the number of decimals and L is the Display length which includes the decimal part also. By default, L is 10, and D is 2. So Decimal Part can be up to 24 places in this data type.
It keeps 4 bytes of storage.
For instance, 3.14 is a float value
Double(L,D):
It is a large-sized precision floating point number which can not be unsigned value. By default, L is 16, and D is 4. REAL is the synonym of this data type. So, Decimal Part can be up to 53 in this data type.
It stores 8 bytes of storage.
DECIMAL(size,d):
Allows storing DOUBLE as a string, so that there is a fixed decimal point. So here the size parameter is used to specifying the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal.
2. String Data Type:
In the MySQL, string datatype category, whether the field contains Character and number both. So now let us discuss its types
Char:
It is a fixed-sized length string that is between 0 and 255 characters in length. By default, it is 1.
Varchar:
It can store character as well as numbers. Length string which is between 0 and 255 characters in length. So, Here we must define the length while creating the data type. For instance, amitg881 is a varchar value.
Text-
It has a maximum length of a character is 65535 which is used to have a large amount of data that is texts and It is not case sensitive. Therefore, We don’t need to specify the length while creating a text data type.
TINYTEXT-
Allows a string with a maximum length of 255 characters.
MEDIUMTEXT:
Allows a string with a maximum length of 16,777,215 characters.
LONGTEXT:
Allows a string with a maximum length of 4,294,967,295 characters.
Blob:
It stands for Binary Large Object which is generally used to store large binary data such as Image file or audio files. And yes it is a case sensitive data type.
LONGBLOB:
Holds up to 4,294,967,295 bytes of data, and is used for Binary Large Objects.
ENUM:
It allows you to enter a list of possible values, with the maximum to be 65535 values. In addition, Just in case a value is inserted which is not present in the list, a blank value will be inserted.
SET:
Similarly, Enum this data type is the same, but SET can have up to 64 list items and can store more than one choice.
3. Date and Time:
To store date and time in the field or column we need a special type of data type. Therefore, MySQL has a different Date and Time datatype. Look at these data types follow.
DATE-
It stores the date in the format YYYY-MM-DD. Its range is between 1000-01-01 and 9999-12-31.
It stores 3 bytes of storage.
DateTime-
It stores the date and time in the format YYYY-MM-DD HH:MM:SS format. Its range is between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.
It stores 8 bytes of storage.
TimeStamp-
TimeStamp saves the date and time in the format YYYYMMDDHHMMSS. Similarly, it is the same as above in DateTime but without hyphen and colon.
It caches 4 bytes of storage.
Time-
It stores the time in the format DD HH:MM: SS.
The supported range is (-838:59:59) to (838:59:59).
It stores 3 bytes of storage.
Year-
It stores the year in the format of 2 lengths or 4 lengths.
It stores 1 byte of storage.
Year values in the range (70-99) are converted to (1970-1999), and year values in the range (00-69) are converted to (2000-2069)
Want to learn More about datatypes Click Here.
Some tips while choosing the data type:
- Use the CHAR data type to store the values that have fixed length, like country code. In addition, values that have variable lengths like names or titles use VARCHAR to save the space.
- The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
- In addition, nonbinary string data types, column length values are normally referred to as the number of characters rather than bytes. It means the maximum number of characters is less for strings that contain multibyte characters.
- Comparing floating-point values may lead to problems because they are approximate and not stored as exact values. Therefore to store the values that can be used in comparison like price, salary, etc. use the DECIMAL data type instead.
- By default, values that lie outside the TIME range but are otherwise valid are clipped to the closest endpoint of the range. For example, ‘860:00:00’ is converted to ‘838:59:59′. Invalid TIME values are converted to ’00:00:00’.
- MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
- The older version of MySQL also allows the storage of two-digit year value using YEAR(2), but it is deprecated now and as a result, its support for it is removed in MySQL 5.7.5.
Conclusion
Here A brief summary of the above we discussed MySQL data type. They are the same as any other programming language. We need datatypes to differentiate the data. MySQL supports many datatypes Numeric, string, date n time, and data types from other databases engines.
Here, we also share some tips while choosing the data type like the effective maximum length of a VARCHAR INVALID & VALID TIME values Comparison of floating points.
FAQs
Email Addresses generally have 3 parts Username, Symbol (‘@’), and domain name. We use VARCHAR because username can be anything whether its number or character. Example [email protected]
The preferred data type for image storage is BLOB that is Binary Large Object. It is a collection of binary data stored as a single entity in a DBMS. It are typically images, audio or other multimedia objects.
It is also called enumeration, is a string object whose value is chosen from a list of permitted values defined at the time of column creation. Its Characteristics are Compact data storage and numeric indexes (1, 2, 3, …) to represent string values.
Next Blog | What are DML Commands and DDL Commands in SQL?
Previous Blog | What is MySQL and how to download & install it?
