Are you learning about database managers? In that case, you need to inform yourself about the data types in MySQL, one of the best and most used in the world. Do not miss the opportunity !.
Data types in MySQL
Every time we need to create a table that can be used to store data for an application, we must know how to identify what type of data helps us to better store everything that we need to archive. We can choose between three: numerical data, strings (alphanumeric), and dates and times.
In these fields of the MYSQL tables we have the possibility to choose between three types of content, and although it seems obvious, determine where to send our data, to what type of group the storage will belong, here we have an example to put ourselves in context: yes we need a field with which we can store the age of a person, then it would be a numeric data field.
But before I continue explaining, do you know what MySQL is? It is known as one of the most widely used open source database managers in the world. So that we can have an idea of how popular it is, we will tell you that: WordPress is the manager of different types of content, which has existed since 2003, and that around 55% to 60% of the web pages that exist, are made thanks to this, and it uses MySQL as a database, so this proves how useful it can be and the scope it has.
MySQL belongs to the Oracle Corporation company, who were in charge of buying it in 2010. This manager has multiple uses, such as: practices, performing installations, modifying web pages, reading data, among others.
This driver can be easily downloaded and has several versions depending on the Windows you are using, in the same way, it is very easy to install it.
The vast majority of database drivers are used through a programming language. Let's say that for example, the information that we have on our computers is available in the database, but when we need to view and manage it, it uses a programming language; In the case of MySQL, it is accompanied by php, which is known as a web development language, the same with which WordPress is developed.
We suppose that we can recommend, for speed, download the XAMPP tool, which is available for various versions of Windows. XAMPP comes with a series of components, among them we have:
- Apache: This would be a web server.
- PHP: The web development language.
- Fillezilla: He is in charge of mobilizing the files.
- Mercury: It is the mail server, which has the purpose of doing the tests.
- MySQL: As we mentioned earlier, it is the database server.
After installing XAMPP, you will be able to enjoy all these components, including MySQL, which you can start directly and connect to the graphical interface, that is why XAMPP is extremely useful, in addition to having the other components.
Having all this clear, we wanted to explain that within our options for types of tables to store our data, and speaking of the numerical data field, within this same we have other types, and we must know which would be the best, which would give us the opportunity to consume less physical storage space and will give us the opportunity of data that we hope to store in that field. The only way to understand these questions is the different types of data that MySQL provides us, below we will be providing that information in order to understand the most appropriate uses of each group.
We invite you to see an intensive course on data types in MySQL and everything you need to know about it, in the video below.Don't miss the opportunity to learn !:
Numerical data
The difference that we could find between one type of data and another in MySQL is simply the range of values that it can contain. Within the numerical data we need to see that we can distinguish two large branches: integers and decimals; Now, we would like to explain the types of numerical data that we can have according to the situation that is presented to us and what we need:
Numeric integers
The first thing we want to explain at this point is that the options we have to store this type of data would be ages, quantities and magnitudes without decimals. We would also like to present an example to better understand what type of data we should choose for each field:
We present TINYINT, a data type that allows us to store a maximum value of 127. So if we need to define a field for the age of our users, this is the one we could use, because the normal age range is within that number , and unless we are living in the time of the biblical Old Testament, no one biologically surpassed that number; So no, this type of data does not allow us to store 567, for example, not even 128, if the limit reaches 127.
Now, if we want to define a field for an identifier of a large market to sell thousands of different and varied things, this would change exponentially, clearly TINYINT no longer serves us, in addition to this we should know very precisely the amount of items it sells, but not only with what we currently have, but trying to make a prediction for our near future, in this way our storage system will not quickly become obsolete.
We could use something like SMALLINT that will allow us to number up to 32,000 articles, but if we change the example and move from a market to an ID field that should be used for a customer table of a telephone company with 5 million users, we could no longer have of SMALLINT, but of some other like MEDIUMINT, and we continue, in the case that our company had 200 million clients, we should use a field of type INT. The issue changes in the case of getting capricious and wanting to define a field that identifies each of the human beings that live on planet earth, then we should ask a BIGNIT field for help, since the INT type only allow up to two thousand millions of different pieces of data, and that clearly would not reach us.
We also want to confirm the existence of negative values, which we could find when wanting to save the score of a game, or the mark below zero that could mark a table, among other things.
Unsigned values
Let's look at it this way: having a negative age would make no sense at all. If there is the possibility of doubling the limit of the maximum positive value of each data eliminating the possibility that that field can store negative values, we would be doubling the positive limit of storage, and the field of type TINYINT that normally allowed to store values of 127, now will let you store values from 0 to 255.
And how do we define a field that has no sign? Through the UNSIGNED modifier we can define a numeric field. Using this we should find a column that reads Attributes and the value of UNSIGNED and this field can no longer contain negative values, thus doubling its storage capacity.
It is worth mentioning that it is important that when defining a field in the column that we would find as Length we write a number consistent with the storage capacity that we have just chosen. Continuing with the age example, if we are working with TINYNIT, we must put a three as a length, not a greater or lesser number.
Numbers with decimals
Prices, salaries, bank account amounts, among others, we have moved to numerical values with decimals and we have left whole numbers behind, and despite the fact that these data types are called "floating point" because the comma separates the part integer and the decimal part, actually between the MySQL data types, it stores them separating them with a period; from here we would have three types of data: FLOAT, DOUBLE and DECIMAL.
FLOAT will allow us to store at least the value -999.99 and at most 999.99. Take into account that the sign - does not count, but the point that separates them, that is, the decimal point, yes, that's why they would be six digits in total, although we note that two of them are decimals; But we have something called a simple precision range, which forces us to have decimal quantities between 0 and 24.
On the other hand, DOUBLE, being twice the precision, only allows the number of decimal places to be defined between 25 and 23. Using FLOAT, which is simple precision, could cause rounding problems and loss of the remaining decimal places. The one that remains to be explained is DECIMAL, which is the best for storing monetary values where less length is required but maximum accuracy, and without rounding, this type of data assigns a fixed width to the number that it will store. The maximum total digits for this type of data is 64, of which 30 is the maximum number of decimal places allowed, more than enough to store prices, wages and currencies.
Alphanumeric data
Finally we leave the category of numerical data to enter a new one. Here we will be talking about storing character strings, to explain it in a better way, and among the data types in MySQL we have the following: CHAR, VARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT , ENUM and SET, each one has its own characteristics and its own advantages depending on what data we want to store.
Date and time data
This would be our last category when it comes to data types in MYSQL. We will see that we have several options to store referred data, dates and times, seeing the difference between one and the other and their main uses, in this way we will be able to choose the appropriate type of data in each case.
DATE
This type of data in MySQL allows us to store dates where the first four digits belong to the year, the next two to the month and the last two to the day, although in Spanish-speaking countries we are used to ordering the dates first by the day, then for the month, and then for the year, for MYSQL it is completely the other way around.
It is important to know that when reading a DATE field, although it appears with dashes separating the year from the month and the month from the day, when inserting this data it allows us to do everything continuous, for example, we can see it like this: 2018-06-04 and insert it like this 20180604. The date range that DATE allows us to handle is 1000-01-01 through 9999-12-31.
Unless we have something to do with an event that happened two thousand years ago and we need to expose it, we will have no problems with this format; on the other hand, with a view to the future we have more opportunities, since with this format we almost reached the year 10,000.
Datetime
Having a field defined as DATETIME will allow us to store information not of a date, but of a moment, an instant of time, apart from the date, also its schedule, first we would have the year, then the month, then the day, then we would also have the hour, the minutes, and even the seconds, the format looks like this:
- YYYY- MM- DD HH: MM: SS
The date part has a range similar to that of the DATE type (10,000 years), that is, from 1000-01-01 to 9999-12-31. The part of the schedule would go like this: from 00:00:00 to 23:53:53. Everything complete would look like this: 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
TEAM
Here we are allowed to store hours, minutes and seconds, and yes, the previous data type did too, but with TIME we have a permitted range that goes from: -839: 59: 59 to 839: 59: 59; this would span about 35 days back and forth on a current date. This type of data is ideal for calculating elapsed times between two close moments.
TIMESTAMP
Here we have a data type that could be very similar to DATETIME but its format and range are different, although it is still useful to store a date and a time. With the field of this format, three options can be presented to us, the first is: YYYY-MM-DD HH: MM: SS, the second is: YYYY-MM-DD, and the third is simpler: YY-MM-DD.
Here we have the possibility of having a possible length of 14, 8 or 6 digits, it all depends on the information we provide. This format is neither as historical nor as futuristic as the others, since the range that this field handles only goes from 1970-01-01 to the year 2037.
In addition, as a curious fact, we can establish that its value is kept automatically updated every time a record is inserted or updated, in this way we will always keep in this field the date and time of our last update of that data, which is really ideal. to take control without having the need to program anything.
If we want to define this from the phpMyAdmin, all we have to do is select in Attributes the option that says "on update" CURRENT_TIMESTAMP, and as the default value CURRENT_TIMESTAMP. Field whose value can be updated automatically when inserting or modifying a record.
YEAR
In the event that we have to see the need to define a field as YEAR, we can store a year, both using two, as well as four digits. In the case that we do it in two digits, from 70 to 99 (having from 70 to 99 we will understand that these correspond to the range of years from 1970 to 1999, and if we have the digits from 00 to 69 then we can understand that refers to the years 2000 to 2069), in such a case of providing the four digits then we would find that the possible range would expand, then going from 1901 to 2155.
We also have an extra possibility, although unrelated to data types in MySQL, but related to dates and times. This extra possibility is to generate a timestamp value with the PHP time function (again we want to clarify that we are no longer talking about MYSQL, although it is valid to get confused because of having quite similar names).
Anyway, we could store that value in a 10-digit INT field, in this way, it will be very simple to order the values of our field (we can put the date of a news item as an example) and then we can show that date by transforming that value timestamp into something that we can make readable using PHP's own date handling functions.
I hope that with this article on data types in MySQL, we have been able to make everything we wanted to explain sufficiently clear and that you have learned how to create a database and a table according to all our information, defining their fields with total precision using them as types of data and attributes, therefore, being in the capacity, or in the conditions, to start programming properly, now having a clear idea of exactly what format we are going to need, which fits our need according to what we have to program .
We invite you to enjoy another of our articles related to programming: Polymorphism in object-oriented programming.