Introduction To MySQL Data Types

Data types are the most important part in creating columns in tables. It define which type of value a column can contain and how long this value can be. Data type varies from column to column. Let’s get started:

What is Data Type?

A data type is a particular kind of an item that you store in a variable, whether it is returned from a function or you have defined it in your code. Data types are of different types, including numbers, strings, dates and characters.

MySQL also define different data types which you can get benefit from while creating columns of your table. Columns in a table can be of different types, it can be a number, string, you can store your whole image in bytes, or can define a column specifically for date and time. MySQL datatypes can be divided into different categories. I will define three main, mostly used categories in this blog:

1. Number Type
2. String Type
3. JSON Type

Number Type

MySQL uses all the numeric data types that you use mostly in your programming. Following are the list of commonly used numeric data types along with their short description.

1. INT: It stores real numbers which can be signed or unsigned. Its range is -2147483648 to 2147483647.
2. FLOAT(M,D): It stores floating-point numbers which can’t be unsigned. You can define the display length (M) and the number of decimals (D). Default value for M and D is 10 and 2. Decimal precision can go to 24 places
3. DOUBLE(M,D): It stores double floating-point numbers which can’t be unsigned. You can define the display length (M) and the number of decimals (D). Default value for M and D is 16 and 4. Decimal precision can go to 53 places

String Type

Most of your data will be stored in string formats. For this, MySQL has provided the string data type. You can choose from them according to your needs. Commonly used string types are:

1. Char(M): It is a fixed length string whose range is between 1-255. Its default value is 1.
2. VARCHAR(M): It is variable length string whose range is between 1 – 255. Its value must be defined when creating a VARCHAR column.
3. TEXT: It stores large amount of data. You don’t have to define a length for this when defining it. Its maximum length is 65535.
4. BLOB: These are Binary large objects, it stores the large amount of binary data such images. You don’t have to define a length for this when defining it. Its maximum length is 65535.

JSON Type

In MySQL 5.7.7 they have introduced a new datatype JSON. It used to store data which is in the form of JSON. Before this you can store your JSON format data in TEXT or VARCHAR fields but you have to write your own verification function before storing it on your database. However, after you have chooses this datatype for column it automatically validates that the data which is being entered is in JSON format or not. Its maximum length is 1073741824.

Conclusion:

Datatypes are the most necessary part of your database. It shows what the values of your columns really are. For example, if you are saving the driving license of a person you can use VARCHAR and can set a limit of of 17 with hyphens, and a limit of 14 if you are not using hyphens. By this way your database will automatically generate error if the limit crosses.

Optimized MySQL Hosting with combined powers of Nginx, Apache web servers, PHP, and Varnish HTTP Accelrator along with Memcached and Redis cache, Cloudways ensures that load time of your website improves by 100% on the Cloudways Platform.