Home Education What is MySQL Datatype? Overview And Understanding

What is MySQL Datatype? Overview And Understanding

Direct Switch to Contents:

  1. What is MySQL Datatype?
  2. Some tips while choosing the data type
  3. Conclusion
  4. FAQs

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.

MySQL DATA Type

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:

  1. Movie name
  2. Movie year
  3. Actor name
  4. 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 NameMovie YearActor NameActor Age
Barfi2012Ranbir Kapoor37
Biwi no. 11999Salman Khan54
2 States2014Arjun Kapoor34
Fan2016Shahrukh Khan54
Kabir Singh2019Shahid Kapoor39
Dabangg 32019Salman Khan54

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:

MySql DataType

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.

DataType MySQL

Some tips while choosing the data type:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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’.
  6. MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
  7. 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

1. What is the data type for email addresses in MySQL?

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]

2. What data type is an image in MySQL?

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.

3. What is the use of ENUM in MySQL?

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?

RELATED ARTICLES

5 Best Laptops Under 60000 Suitable To Your Profession

Today we live in the era of the internet in which laptops and mobile phones play an important role. So if you...

5 Best Inverters to Buy This Year | How to Choose the Right Inverter

We all know inverters are useful for getting backup of electricity, but fewer people know about all features and types. If you...

Top Selling Best Budget Smartphones That You Can Buy This Year

Are you planning to buy a smartphone? Or Looking for a budget smartphones with the best features?  Almost everyone is stuck with...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

5 Best Laptops Under 60000 Suitable To Your Profession

Today we live in the era of the internet in which laptops and mobile phones play an important role. So if you...

5 Best Inverters to Buy This Year | How to Choose the Right Inverter

We all know inverters are useful for getting backup of electricity, but fewer people know about all features and types. If you...

Top Selling Best Budget Smartphones That You Can Buy This Year

Are you planning to buy a smartphone? Or Looking for a budget smartphones with the best features?  Almost everyone is stuck with...

Top 10 Network Marketing Companies in India | Future of MLM Business

Everybody knows about marketing that it is an essential part of a business. It is essential to promote our product to others...

Recent Comments