If you are planning on creating a MySQL database, then you need to understand everything there is to know about the most basic functions you can perform. You also need to understand the different MySQL datatypes and how the program stores data.
In this post, we are going to introduce MySQL datatypes. MySQL database management software supports a number of data types. These MySQL datatypes have three different categories namely string, numeric, and MySQL date/time data types.
The first MySQL data type we will be looking at is the string data type.
MySQL String Data Types
As mentioned before, MySQL data types refer to the format in which MySQL stores data in the database. String data types store descriptions or alphanumeric values/characters such as binary data (images, audio files, etc.), names, and addresses.
String data types include:
CHAR And VARCHAR
These two data types store the same type of data although they store a different-length string. CHAR stores strings with fixed-lengths and a maximum character count of 255. On the other hand, VARCHAR data type stores strings with variable lengths. For the VARCHAR data type, the maximum string length is 65,535.
When using any of the two data types, you are responsible for declaring the length to indicate the maximum number of characters you will be storing. To put it more into perspective, CHAR(10) will hold no more than characters.
Another difference between the two data types, and which is the main difference, is that they both store data differently. For instance, with CHAR, regardless of a string’s length, the system allocates each string the maximum space specified and right-pads the remaining space for shorter strings.
As an example, CHAR (5) reserves 5 characters so the system will store a value like ‘abc’ as ‘abc ‘.
With the variable VARCHAR character type, values are only allocated space equal to their lengths. In terms of database performance, CHAR performs better as the database engine knows the number of characters to expect. However, VARCHAR uses less space as it leaves the extra allocate characters open when a string is shorter.
BINARY And VARBINARY
These two MySQL datatypes only differ slightly from CHAR and VARCHAR. The only difference is that BINARY and VARBINARY MySQL datatypes store binary data unlike CHAR and VARCHAR which store nonbinary strings.
In addition, the maximum allowable lengths for BINARY and VARBINARY is similar to that of CHAR and VACHAR. The difference is in that for BINARY and VARBINARY, the lengths are not in characters. Instead, they are in bytes.
MySQL TEXT Data Type And BLOB Data Type
The MySQL TEXT data type and BLOB data type are set apart as the MySQL datatypes that hold large data sets. MySQL TEXT data type stores long strings of text such as descriptions and comments. Unlike MySQL TEXT data type, the BLOB data type, on the other hand, holds a variable amount of large data such as images and audio files.
Both the BLOB and TEXT data types have other data types. The additional BLOB data types are TINYBLOB (255), MEDIUMBLOB (16,777,215), and LONGBLOG (4,294,967,295). For TEXT data types, they are TINYTEXT, LONGTEXT, and MEDIUMTEXT.
As you can already tell, the difference between these data types is the maximum lengths of the characters they can hold and storage requirements.
ENUM Data Type
This data type gives you the ability to specify a list of values that can be stored in a specific column. An example of ENUM data type would be something like:
In this case, the statement above specifies that the column can have one of two values – boy or girl. An ENUM list can take a maximum total number of 65,535 distinct values. Entering an invalid ENUM value enters an empty string.
SET Data Type
The SET data type has a function that is almost similar to that of the ENUM data type. With SET, you can specify a list of values that users can insert into a column. The difference is in the option of values that you can choose and enter into a column from the specified list. SET allows you to enter multiple comma-separated values.
Assuming you have a list like this – (‘one’, ‘two’). With ENUM, you can only enter either ‘one’ or ‘two’. With SET data type, you would specify the values (up to 64 distinct ones) using the command:
What this SET option tells the engine is that unlike ENUM, the column can take either ‘one’, ‘two’, or ‘one,two’.
NUMERIC
Like the name suggests, this MySQL data type stores values in numerals. Often, you will find data like price and salary among others stored by MySQL numeric datatypes.
Like some other data types we covered before, the numeric data type has several subtypes namely INTEGER (or INT), TINYINT, SMALLINT, MEDIUMINT, and BIGINT. INTEGER and SMALLINT are the standard integer types in SQL while as an extension to the SQL standard; MySQL also supports the three other numeric data types – TINYINT, MEDIUMINT, and BIGINT.
As you can tell from the table above, numeric data types come with an additional attribute – SIGNED and UNSIGNED. The signed attribute is the default which allows the data types range to start from a negative to a positive value.
To disallow the storage of negative values and only allow the values to start from 0 as the minimum value, you add the unsigned attribute.
DECIMAL, NUMERIC
When you want to store numeric values and maintain decimal points, you want to use the DECIMAL, NUMERIC data type. This allows you to add a decimal to your numeric values. Another name for this data type is ‘Exact Value’. When working with this data type, you are able to specify the precision and scale for the data to be stored. To specify the precision and scale respectively, you use either DECIMAL (P, S) or NUMERIC (P, S).
While specifying the precision and scale, you should keep in mind that the precision will be the maximum number of digits the column can store, inclusive of the decimals. On the other hand, the scale refers to the maximum number of digits after the decimal point. For example, cost DECIMAL (6, 2) will store a six-digit, two-decimal value so in this instance, the range will be anywhere from –9999.99 to 9999.99.
FLOAT, DOUBLE
This is MySQL datatypes’ approximation numeric value holder. The reason this data type is known as ‘floating-point’ is that the values it holds are approximated. The syntax for this data type in MySQL is FLOAT (M, D). Alternatively, you can also use DOUBLE PRECISION (M,D). In simple terms, M stands for the total number of digits that can be stored in the column while D stands for the number of digits that can be stored after the decimal.
With FLOAT, MySQL is allowed to round off values to meet the specified threshold. For instance, FLOAT (7,3) will hold a number like -9999.999. Inserting a number with more than three digits after the decimal in a FLOAT column leads to rounding off so a digit like 9999.0009 will be stored as 9999.001.
With this in mind, it is important to note that if you need to compare numbers at some point, you are better off using DECIMAL data type than FLOAT (float will create problems as it stores approximate and not the definite values).
Date And Time Data Types
The MySQL date data type and time data types are used to store data including dates and times when users create or update tables, hiring dates, and dates of birth.
MySQL DATE Data Type
There is not much to say about MySQL date data type as it is self-explanatory – it stores dates. In MySQL, the MySQL date data type stores date in the format ‘YYY-MM-DD’ for the year, month, and date respectively which is the same way data is retrieved.
The DATE data type supports a date range from ‘1000-01-01’ to ‘9999-12-31’.
TIME Data Type
The time data type stores time including time-of-day or time intervals. MySQL’s format for storing and retrieving time is ‘HH:MM:SS’ for hours, minutes, and seconds. For large hour values, MySQL also accepts time in the format ‘HHH:MM:SS’.
The range for a time supported in MySQL is ‘-838:59:59’ to ‘838:49:59’. One reason that MySQL TIME data type may be large is the fact that MySQL also stores elapsed time or time intervals between events (and these can be more than 24 hours or even negative).
DATETIME And TIMESTAMP
To store a combination of both date and time, MySQL has the DATETIME and TIMESTAMP data types. The format for these MySQL datatypes is ‘YYYY-MM-DD HH:MM:SS’. A good example of when you require to store both date and time is when a table is created or altered or when an order is dispatched.
The difference in these data types include the ranges they support. For DATETIME, the range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ while for TIMESTAMP, the range starts from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.
A point to note is that time conversion to UTC only happens for storage and is then converted to local time upon retrieval.
YEAR Type
Finally, our last data type is the YEAR type. The YEAR data type stores the year in four digits with the format YYYY. It supports a range from 1901 to 2155 and converts invalid values to 0000.
And that brings us to the end of our guide to MySQL data types.
