Numeric data can be categorized as integers or decimal numbers. For integers, MySQL uses the INT data type even though there are versions to store smaller numbers, such as TINYINT, SMALLINT, or MEDIUMINT, or bigger numbers, such as BIGINT. The following table shows what the sizes of the different numeric types are, so you can choose which one to use depending on your situation:

Numeric types can be defined as signed by default or unsigned; that is, you can allow or not allow them to contain negative values. If a numeric type is defined as UNSIGNED, the range of numbers that it can take is doubled as it does not need to save space for negative numbers.

For decimal numbers we have two types: approximate values, which are faster to process but are not exact sometimes, and exact values that give you exact precision on the decimal value. For approximate values or the floating-point type, we have FLOAT and DOUBLE. For exact values or the fixed-point type we have DECIMAL.

MySQL allows you to specify the number of digits and decimal positions that the number can take. For example, to specify a number that can contains five digits and up to two of them can be decimal, we will use the FLOAT(5,2) notation. This is useful as a constraint, as you will note when we create tables with prices.

Even though there are several data types that allow you to store from single characters to big chunks of text or binary code, it is outside the scope of this chapter. In this section, we will introduce you to three types: CHAR, VARCHAR, and TEXT.

CHAR is a data type that allows you to store an exact number of characters. You need to specify how long the string will be once you define the field, and from this point on, all values for this field have to be of this length. One possible usage in our applications could be when storing the ISBN of the book as we know it is always 13 characters long.

VARCHAR or variable char is a data type that allows you to store strings up to 65,535 characters long. You do not need to specify how long they need to be, and you can insert strings of different lengths without an issue. Of course, the fact that this type is dynamic makes it slower to process compared with the previous one, but after a few times you know how long a string will always be. You could tell MySQL that even if you want to insert strings of different lengths, the maximum length will be a determined number. This will help its performance. For example, names are of different lengths, but you can safely assume that no name will be longer than 64 characters, so your field could be defined as VARCHAR(64).

Finally, TEXT is a data type for really big strings. You could use it if you want to store long comments from users, articles, and so on. As with INT, there are different versions of this data type: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Even if they are very important in almost any web application with user interaction, we will not use them in ours.

In MySQL, you can force a field to have a set of valid values. There are two types of them: ENUM, which allows exactly one of the possible predefined values, and SET, which allows any number of the predefined values.

For example, in our application, we have two types of customers: basic and premium. If we want to store our customers in a database, there is a chance that one of the fields will be the type of customer. As a customer has to be either basic or premium, a good solution would be to define the field as an enum as ENUM("basic", "premium"). In this way, we will make sure that all customers stored in our database will be of a correct type.

Although enums are quite common to use, the use of sets is less widespread. It is usually a better idea to use an extra table to define the values of the list, as you will note when we talk about foreign keys in this chapter.

Date and time types are the most complex data types in MySQL. Even though the idea is simple, there are several functions and edge cases around these types. We cannot go through all of them, so we will just explain the most common uses, which are the ones we will need for our application.

DATE stores dates—that is, a combination of day, month, and year. TIME stores times—that is, a combination of hour, minute, and second. DATETIME are data types for both date and time. For any of these data types, you can provide just a string specifying what the value is, but you need to be careful with the format that you use. Even though you can always specify the format that you are entering the data in, you can just enter the dates or times in the default format—for example, 2014-12-31 for dates, 14:34:50 for time, and 2014-12-31 14:34:50 for the date and time.

A fourth type is TIMESTAMP. This type stores an integer, which is the representation of the seconds from January 1, 1970, which is also known as the Unix timestamp. This is a very useful type as in PHP, it is really easy to get the current Unix timestamp with the now() function, and the format for this data type is always the same, so it is safer to work with it. The downside is that the range of dates that it can represent is limited as compared to other types.

There are some functions that help you manage these types. These functions extract specific parts of the whole value, return the value with a different format, add or subtract dates, and so on. Let's take a look at a short list of them:

Do not worry if you are confused on how to use any of these functions; we will use them during the rest of the book as part of our application. Also, an extensive list of all the types can be found at http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html.