MySQL – Data Type
MySQL offers many datatypes apart from numeric and string types to store the values in the columns. These data types may be used to specify the behavior of the columns and kind of values so one can be allowed to shop in it. Data types in MySQL are associated with more than one characteristics that are as defined below:
The sort of values that will be saved and represented inside the declared columns.
The reminiscence area requirement of that data type for storage and additionally to define whether the values in that column could be of variable or fixed length.
Whether or not the column’s values for that datatype may be used for indexing.
Contrast the values of that data type with different values in MySQL.
MySQL Data Types
Mysql gives numerous statistics types which are further grouped based on the form of the values stored in that data type columns. The data types can be broadly classified into the subsequent companies:
We can discuss and examine the data types underneath each institution in the list manner a good way to comprise the name of all records kinds. Earlier than that allows us to have a brief overview of a number of the maximum used statistics sorts out them in conjunction with the variety of the values and type of content material in order to be saved in them.
|Name of datatype||Range||Kind of data stored|
|VARCHAR||(0 – 255)||String|
|CHAR||(0 – 255)||String|
|INT||(-2147483648 to 214748- 3647)||Integer|
|TINYINT||(-128 to 127)||Integer|
|MEDIUMINT||(-8388608 to 8388607)||Integer|
|BIGINT||(-9223372036854775808 to 9223372036854775807)||Integer|
|SMALLINT||(-32768 to 32767)||Integer|
|TEXT||0 – 65535||String|
|DOUBLE||(24 to 53 digits)||Decimal|
|BLOB||(0 – 65535)||String|
|DECIMAL||(24 to 53 digits)||Double value but stored in string format|
|DATETIME||YYYY-MM-DD HH:MM: SS|
|ENUM||Can store one of the defined options of values|
|SET||Can store selected values of the defined options of values|
1. Numeric Data type
We can keep numeric values of different tiers and codecs in MySQL. Specific and approximate numeric values may be saved by the use of MySQL numeric data types. Besides that, MySQL also affords the functionality to save the bit values the usage of the BIT statistics kind.
The following listing carries all of the numeric facts available in MySQL.
2. String Data Types
We are able to store the binary statistics like photographs of different media documents and even plain test inside the string statistics type values in MySQL. In addition, MySQL gives distinct functionalities for searching the string values using normal expressions, like operator or full-textual content seek that assist in using and manipulating string values.
The following list contains all of the available data types that assist to shop string values:
3. Boolean Data Type
Mysql does not offer a devoted information type for storing boolean values. However, on every occasion a BOOLEAN or BOOL named data type is created in MySQL it’s miles internally interpreted as TINYINT(1) datatype by using MySQL.
4. Date and Time Data Types
We can shop date, time in my opinion as well as togetherly in MySQL. The timestamp also can be used to shop and tune the modifications to the rows of the desk. Except that, if you want to keep only a 12 months then you can use the 12 months information type.
The following list carries all of the date and time-associated records types available in MySQL.
5. Spatial Data Type
MySQL presents us with the facility to keep the geometrical and geographical values in numerous fact types.
The subsequent list carries all of the spatial records to be had in MySQL.
- GEOMETRY COLLECTION
6. JSON Data Type
Mysql model five.7.8 and in addition comes with the assist of storing the JSON statistics kind for storing and manipulating documents of JSON without problems and efficiently. Validation of the JSON files is executed automatically when the column is said to be of JSON data type in MySQL. This data type gives optimal storage of JSON documents that may be dealt with and stored successfully.