When creating a table in a database, it should have both a name and a data type. A column’s data type defines the values the column holds, such as integer, money, binary, character, date, and time. Therefore, it is a developer’s task to determine which data types will be stored in each column while creating databases and tables.
In simple terms, data types are guidelines that aid SQL in understanding what type of data is required inside a column. It is also efficient in identifying how SQL interacts with the stored data.
A point to note is that data types might contain different names in different databases, and in cases where the names are the same, other aspects and details such as size will differ. Therefore, it is recommended that you always refer to documentation whenever you encounter similar cases.
The following characteristics can identify data types in MySQL:
- The values of data types that can be indexed and those that cannot be indexed
- The type of values they represent
- The space they occupy, whether the values are of variable length or fixed length
- How MySQL compares to the different values of specific data types
Before we dive in and cover MySQL data types, it is essential to learn and understand the conventions used by the data type descriptions as highlighted below:
- (M): For integer types, it indicates the maximum width that the data type can display.
: It denotes the total number of digits that can be stored for fixed-point types and floating-point types.
: For string types, it shows the maximum length
Note: M’s maximum permissible value depends on the data type
• (D): Only applies to fixed-point types and floating-pointing types. It indicates the scale (the number of digits that follow the decimal point). The maximum possible value is 10, while it shouldn’t be more significant than M-2
• The square brackets ([and]) show the definition type optional parts.
• fsp: this convention applies to timestamp, DateTime, and time types. It represents the fractional seconds precision (the number of digits that follow the decimal point for fractional seconds). The given fsp value must range from 0-6. Value 0 signifies that there are no fractional parts present in the given value. However, in cases where the value is omitted, then the precision is denoted as being 0.
In MySQL, there are three main categories of data types that contain subcategories. The primary data types are:
- String data types
- Date and Time data types.
- Numeric data types
There are other data types supported by MySQL, such as Spatial data types and JSON data types.
This article shall comprehensively cover all the data types mentioned above. Hence to get a clear understanding of data types, stick to this article.
String Data Types
The string data types are primarily used to hold binary data and plain text such as images and files. In addition, MYSQL has the ability to compare and search string values based on the matching pattern, such as regular expressions and operators.
Below is a detailed illustration of all the string data types that MySQL supports:
CHAR (size): This is the fixed length of a string. It can either contain letters, special characters, or numbers. The parameter size denotes the length of the column in characters, and it can range from 0-255. The default size is 1.
VARCHAR (size): This is the variable length of a string. It contains either numbers, special characters, or letters. The parameter size shows the column’s maximum length in characters, and it can range from 0-65535.
BINARY (size): These are equal to CHAR (), only storing binary byte strings. The parameter size specifies the length of the column in bytes. The default is 1
VARBINARY (size): This is equal to VARCHAR (), only that it stores binary byte strings. The parameter size specifies the maximum length of the column in bytes.
TINYTEXT: Holds strings that contain a maximum length of 255 characters.
TEXT (size): Holds strings that contain a maximum length of 65,535 bytes.
BLOB (size): For Binary Large Objects (BLOBs). They hold up to 65,535 data bytes.
TINYBLOB: For Binary Large Objects (BLOBs). It contains a maxim length of 255 bytes.
LONGLOB: For Binary Large Objects (BLOBs). They hold up to 4,294,967,295 data bytes.
LONGTEXT: Holds strings that contain a maximum length of 4,294,967,295 characters.
MEDIUMTEXT: Holds strings that contain a maximum length of 16,777,215 characters.
MEDIUMBLOB: For Binary Large Objects (BLOBs). They hold up to 16,777,215 data bytes.
SET (val1, val2, val3, …): This is a string object that contains more than one value (strings that contain 0 or more values). They are chosen from a list of possible values just like ENUM. However, in a SET list, you can only list up to 64 values.
ENUM (val1, val2, val3, …): This is a string object that can only contain one value chosen from a list of all possible values. In an ENUM list, you can list up to 65535 values. If a value not in the list is inserted, then the value inserted will be blank. Also, it is essential to note that the values are sorted depending on the order the user entered them.
Date and Time data types
The date and time data types specify the temporal values like DateTime, timestamp, year, time, and date. Each of the mentioned temporal types has values that are inclusive of zero. Whenever an invalid value is inserted, MySQL cannot represent it. Therefore, a zero is opted for.
Below is a comprehensive illustration of the date and time data types supported by MySQL:
DATE: The standard date format is years, months, and days respectively (YYYY-MM-DD), and the supported range is ‘1000-01-01’ to ‘9999-12-31’.
DATETIME (fsp): This is the combination of both date and time. The standard format, in this case, is years, months, days, hours, minutes, and seconds respectively (YYYY-MM-DD hh:mm: ss)
Note: Adding a DEFAULT and ON UPDATE in a column is essential in starting automatic initialization, and it updates the current time and date.
TIMESTAMP (fsp): Since the Unix epoch, timestamp values are stores as the number of seconds such as (‘1970-01-01 00;00;00’ UTC). The standard format is years, months, days, hours, minutes, and seconds respectively (YYYY-MM-DD hh:mm: ss) while the supported range is between ‘(‘1970-01-01 00;00;01’ UTC to (‘2038-01-09 03;14;07’ UTC. The DEFAULT_CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP are vital for automatically initializing and updating the current date and time.
TIME (fsp): The standard supported time format is hours, minutes, seconds, respectively (hh:mm: ss), and the supported range is ‘-838:59:59’ to ‘838:59:59’.
YEAR: A year is represented in a four-digit format—the values allowed in the four-digit format range from 1902 to 2155 and 0000.
Note: The latest version of MySQL (8.0) does not support the two-digit format year.
Numeric Data Types
The numeric data types include all the exact numeric data types such as integer, decimal, and numeric. It also contains the approximate numeric data types such as float, double, double precision, and real. Numeric data types store bit values since they support the BIT data types. Usually, numeric data types in MySQL are divided into two categories: Signed data types and Unsigned data types; however, this is an exception to bit data types.
Below is a detailed illustration containing all the numeric data types supported by MySQL and their description:
BIT (size): This is a bit-value type whereby the number of bits per value is denoted in terms of size. The parameter size has the ability to hold values from 1 to 64, and its default value for size is 1.
TINYINT (size): This is a very small integer whose signed range ranges from -128 to 127 while its unsigned range ranges from 0 to 255. The parameter size denotes the maximum width to be displayed, which is about 255.
BOOLEAN: It is equal to a BOOL
BOOL: In a BOOL, the nonzero values are considered to being true. At the same time, the Zero values are considered to being false.
INT (size): This is a medium integer whose signed range ranges from -2147483648 to 2147483647, while the unsigned range ranges from 0 to 4294967295. The parameter size specifies the maximum width to be displayed, which is about 255.
MEDIUMINT (size): This is also a medium integer whose signed range ranges from -32768 to 32767 while its unsigned range ranges from 0 to 65535. The parameter size specifies the maximum width to be displayed, which is about 255.
SMALLINT (size): This is a small integer whose signed range is between -32768 to 32767, while the unsigned range is between 0 to 16777215. The size parameter, in this case, is used to specify the maximum display width, whose range is about 255.
FLOAT (size, d): It is a floating-point number whose total number of digits is denoted in size. The d parameter helps specify the number of digits after the decimal point.
Note: this parameter has been deprecated in MySQL version 8.0.17. Therefore, it won’t be reproduced in future versions of MySQL.
INTEGER (size): This is equivalent to an INT (size).
FLOAT(p): It is a floating-point number. The P parameter is used to determine whether a FLOAT or a DOUBLE will be used in a resulting data type. When the P-value ranges from 0 to 24, the data is known as a FLOAT (). Whereas when the P-value ranges from 25 to 53, then the data type change to a DOUBLE ().
DEC (size, d): This is an equivalent of a DECIMAL (size, d)
DOUBLE (size, d): This denotes a standard size floating-point number whose total number of digits is given in size. The d parameter helps specify the number of digits after the decimal point.
DECIMAL (size, d): It is an exact fixed-point number whose total number of digits is specified in terms of size. The d parameter specifies the number digits after the decimal point. The maximum size number is 65, while the d maximum number is 30. Therefore, the default value for d is 0, while the default value for size is 10.
Note: all the numeric types contain extra options; ZEROFILL and UNSIGNED. If the UNSIGNED option is added, then MySQL will disallow the negative values in the column. On the other hand, if the ZEROFILL option is added, MySQL will automatically add the UNSIGNED attribute to the said column.
Other Data Types
Boolean data type
The smallest integer type TINYINT (1), is used to represent Boolean values in MySQL since MySQL does not contain an in-built BOOL or BOOLEAN data type. Therefore, when working with BOOLs and BOOLEANs, you should equate them to the TINYINT (1).
Spatial Data type
MySQL provides support for several spatial data types that have various kinds of geographical and geometrical values, as indicated below:
GEOMETRY: This is an aggregate or point that can hold the spatial value of any type as long as they have a location.
POLYGON: This is a planar surface represented by a multisided geometry. It can either be defined by zero or only one exterior and more interior boundaries.
MULTILINESTRING: This is a multi-curve geometry that contains a collection of LINESTRING values.
MULTIPOLYGON: This is a multi-surface object represented by a collection of several polygon elements, and it is a two-dimensional geometry
POINT: This is a point or a pair that contains the X and Y coordinates. It can be said to be a point in a geometry that represents a single location.
GEOMETRYCOLLECTION: This is a collection of GEOMETRY values
LINESTRING: This is a curve that contains one or more-point values. In cases where a line string contains only two points, then it means it represents a line.
MULTIPOINT: This is a collection of POINT values whereby the point cannot be ordered or connected in any way.
JSON data type
MYSQL has supported the native JSON data type since the inception of version 5.7.8, which allowed storage and management of JSON docs much quickly and effectively. In addition, the native JSON data type is responsible for providing optimal storage format and automatic validation of JSON documents.
Conclusion
This article has comprehensively covered all the aspects regarding MySQL data types that will aid you in understanding which data types should be used and how they should be used. We believe the article will also help improve your knowledge of MySQL.
