Data types in MySQL for a SQL database

Every time we have to create a table that serves to store data from a Web application, we must test our ability to define the types of data that can more efficiently store each piece of data that we need to store.

The fields of the MySQL tables give us the possibility to choose between three large types of content:

• Numerical data
• Data to store strings of characters (alphanumeric)
• Data to store dates and times

Since it is very obvious to distinguish which of the three groups will correspond, for example, a field that saves the "age" of a person: it will be a numerical data.

But within the different types of numerical data, what will be the best option?

A whole number, but, which of the different types of integers available?

What kind of data will allow to consume less physical space of storage and will offer the possibility of storing the amount of data that is expected to be stored in that field? (two digits, or maximum three, in the case of age).

These are questions that we can only answer based on the knowledge of the different types of data that MySQL allows us. Let us, then, analyze the most appropriate uses of each of these three large groups.

Numerical data

The difference between one and another type of data is simply the range of values it can contain.

Within the numerical data, we can distinguish two large branches: integers and decimals.

Whole numeric

Let's start by knowing the options we have to store data that are whole numbers (ages, quantities, magnitudes without decimals); We have a variety of options:

Let's see an example to better understand what type of data we should choose for each field.

If we need to define a field to store the "age" of our users, it would be sufficient to assign to that field a TINYINT data type, which allows to store a maximum value of 127 (that is, even if it has three digits, we do not it will store a 999, not even a 128, only a number up to the number 127 inclusive).

As the possible age of the people is included in that range, a TINYINT is sufficient.

Now, if we want to define the data type for the id field (identifier) of the product table of a large market that sells several thousand different items, it will not be enough with a TINYINT, and we will have to know more precisely the quantity of different items that it sells (currently and the amount planned in the near future, so that our storage system is not obsolete quickly).

Assuming SMALLINT, since it will allow us to number up to more than 32,000 items (as we saw in the previous table).

In the event that the id field should be used for a customer table of a telephone company with 5 million users, we will no longer use a SMALLINT, but we should use a MEDIUMINT.

In the event that this company had 200 million customers, we should use an INT type field.

Also, if we want to define a field that identifies each of the human beings that inhabit the planet, we will have to resort to a BIGINT field, since the INT type only allows up to 2 billion different data, which would not reach us.

We see, then, that we must always consider what will be the maximum value that will be stored within a field, before choosing the most appropriate type of data.

But not only the maximum values must be considered, we must also take into account the minimum values that could be stored.

For example, to save the score of a game, which could take negative values, or the balance of a current account, or a table that includes values of temperature below zero, and similar cases.

In the previous table we have seen that each type of data has minimum negative values symmetrical to the maximum positive values that it could store.

Values without sign

Now, there is the possibility of duplicating the maximum positive value limit of each type of data, if we eliminate the possibility of storing negative values.

Think of the previous examples: age does not make sense to be negative, so if we eliminate the possibility that this field stores negative values, we would double the positive storage limit, and the field of type TINYINT that normally allowed to store values of -128 to 127, now it will store values from 0 to 255.

This can be useful for storing prices, quantities of objects or quantities that can not be negative, etc.

Observe in the table how the maximum unsigned values are duplicated and then we will learn how to configure this possibility to remove the sign from phpMyAdmin:

How do we define that a field has no sign? Using the UNSIGNED modifier we can define a numeric field:

In the Attributes column we select the value of UNSIGNED and this field can no longer contain negative values, doubling its storage capacity (in the case of this integer type, we will soon see that in the floating-point types, if it is defined as UNSIGNED does not alter the maximum allowed value).

Let's comment on the passage, that it is important that, when defining a field in the "Length" column, we write a number consistent with the storage capacity that we have just chosen.

For example, in a TINYINT for age, we will place a length of three, and not a larger or smaller number.

Numbers with decimals

Let's leave the integers and now let's analyze the numeric values with decimals.

These types of data are necessary to store prices, salaries, bank account amounts, etc. that are not integers.

We must bear in mind that although these data types are called "floating point", because the comma separator between the integer part and the decimal part, in reality MySQL stores them using a point as a separator.

In this category, we have three types of data: FLOAT, DOUBLE and DECIMAL.

The structure with which we can declare a FLOAT field involves defining two values: the total length (including decimals and the comma), and how many of these digits are the decimal part. For example:

FLOAT (6.2)

This definition will allow you to store at least the value -999.99 and at most 999.99 (the minus sign does not count, but the decimal point does, that's why there are six digits in total, and of these two are the decimals).

The number of decimals (the second number between the parentheses) must be between 0 and 24, since that is the simple precision range.

In contrast, in DOUBLE data type, being double precision, only allows the number of decimals to be defined between 25 and 53.

Because the calculations between fields in MySQL are done with double precision (the one used by DOUBLE) using FLOAT, which is of simple precision, can bring problems of rounding and loss of the remaining decimals.

Finally, DECIMAL is ideal for storing monetary values, where less length is required, but "maximum accuracy" (without rounding).

This type of data assigns a fixed width to the number that will be stored.

The maximum number of total digits for this type of data is 64, of which 30 is the maximum number of decimals allowed. More than enough to store prices, salaries and currencies.

The format in which they are defined in the phpMyAdmin is identical for the three: first the total length, then a comma and, finally, the number of decimals.

Alphanumeric data

To store alphanumeric data (character strings) in MySQL we have the following types of data:

• CHAR
• VARCHAR
• BINARY
• VARBINARY
• TINYBLOB
• TINYTEXT
• BLOB
• TEXT
• MEDIUMBLOB
• MEDIUMTEXT
• LONGBLOB
• LONGTEX
• ENUM
• SET

We will now see what their characteristics are and what are the advantages of using one or the other, depending on what data we need to store.

CHAR

Let's start with the simplest alphanumeric data type: CHAR (character, or character).

This type of data allows you to store short texts of up to 255 characters in length in characters that we define, even if we do not use it.

For example, if we define a "name" field of 14 characters as CHAR, it will reserve (and consume on disk) this space.

Therefore, it is not efficient when the length of the data to be stored in a field is unknown a priori (typically, data entered by the user in a form, such as his name, address, etc.)

In what cases to use it, then? When the content of that field will be completed by us, programmers, when adding a record and, therefore, we are sure that the length will always be the same.

Think of a form with radio buttons to choose "sex"; Regardless of what the labels visible to the user show, we could store a single M or F character (male or female) and, consequently, the width of the CHAR field could be one digit, and it would be sufficient. The same applies to codes that identify provinces, countries, civil status, etc.

VARCHAR

Completely, the data type VARCHAR (character varying, or variable characters) is useful when the length of the data is unknown, when it depends on the information that the user types in fields or text areas of a form.

The maximum allowed length was 255 characters until MySQL 5.0.3. but from this version I change to a maximum of 65,535 characters.

This type of data has the peculiarity that each record can have a different length, which will depend on its content; if in your record the field "name" (suppose it had been defined with a maximum width of 20 characters) contains only the text: "Pepe", it will consume only five characters, four for the four letters, and one more that will indicate how many letters They were used.

If then, in another record, a name of 15 characters is entered, it will consume 16 characters (always one more than the length of the text, as long as the length does not exceed 255 characters, if it does not exceed them, there will be two bytes required to indicate the length).

Therefore, it will be more efficient to store records whose values have variable lengths, since although you "spend" one or two characters per record to declare the length, this allows you to save many other characters that would not be used.

On the other hand, in the case of data of always constant length, it would be a waste to spend one character per record to store the length, and therefore it would be convenient to use CHAR in those cases.

BINARY and VARBINARY

These two data types are identical to CHAR and VARCHAR, respectively, except that they store bytes instead of characters, a very subtle difference for a basic to intermediate level of MySQL.

TEXT

Before version 5.0.3. of MySQL, this field was used "par excellence" for descriptions of products, commentaries, news texts, and any other long text.

But, from the possibility of using VARCHAR for lengths of up to 65,535 characters, it is expected that this type of field will be used less and less.

The main disadvantage of TEXT is that it can not be easily indexed (unlike VARCHAR).

Nor can a default value be assigned to a TEXT field (a default value that is automatically completed if a value has not been provided when inserting a record).

We should only use it for really long texts, such as those mentioned at the beginning of this paragraph.

BLOB

It is a field that stores information in binary format and is used when, from PHP, the contents of a binary file (typically, an image or a compressed ZIP file) are stored in the database by reading it byte by byte, and it is required to store all of its contents. content to then rebuild the file and server to the browser again, without the need to store the image or the ZIP on a disk, but its bytes are stored in a field of a table in the database.

The maximum size it stores is 65,535 bytes.

Anyway, and as we have mentioned in this example, regarding the type of data for an image, usually the "image" (its bytes, the content of the file) is not saved in the database because, a large site, the database becomes very heavy and slow; it stores only the URL that leads to the image.

In that way, to show the image simply that URL field is read and an img tag with that URL is completed, and this is enough for the browser to display the image. Then, with a VARCHAR it reaches to store the URL of an image.

The BLOB field is for directly storing "the image" (or a compressed file, or any other binary file), not its path.

TINYBLOB, MEDIUMBLOB and LONGBLOB

Similar to the BLOB, only the maximum length changes:

• TINYBLOB is 255 bytes
• MEDIUMBLOB is 16.777.215 bytes
• LONGBLOB is 4 Gb (or as much as it allows to manipulate the operating system)

ENUM

His name is the abbreviation of "enumeration". This field allows us to establish which are the possible values that can be inserted.

That is, we will create a list of allowed values, and the entry of any value outside the list will not be authorized, and only one of these data will be allowed to be chosen as the value of the field.

The values must be separated by commas and wrapped in single quotes.

The maximum of different values is 65,535

What will be stored is not the character string itself, but the index number of its position within the enumeration.

For example, if when creating the table we define a field in this way:

In this example, the category will be exclusive, we can not choose more than one, and every teacher (one per record) must have an assigned category.

SET

His name means "set". In the same way as ENUM, we must specify a list, but of up to 64 options only.

The loading of these values is identical to that of ENUM, a list of values in single quotes, separated by commas. But, unlike ENUM, we can leave it empty, without choosing any of the possible options.

And we can also choose as field value more than one of the values in the list.

For example, give us to choose a series of topics (typically with checkboxes that allow multiple selection) and then store in a single field all the options chosen.

An important detail is that each value within the string of characters can not contain commas, since the comma is the separator between one value and another.

Once this type of data is defined, we can load multiple values for that field within a single record, by pressing "Control" while clicking on each option.

This will mean, in this example of a table of students, that that student is studying both selected subjects.

If once added a record click on "Browse" to see the contents of the table, we will see that this record, which in a field contained a multiple value, includes the following:

With this we end the group of alphanumeric data types. Let's now go to the last group, that of dates and times.

Date and time data

In MySQL, we have several options to store data referring to dates and times.

Let's see the differences between one and another, and their main uses, so we can choose the appropriate type of data in each case.

DATE

The data type DATE allows us to store dates in the format: YYYY-MM-DD (the first four digits for the year, the next two digits for the month, and the last two digits for the day).

Attention:
In Spanish-speaking countries we are used to ordering the dates in Day, Month and Year, but for MySQL it is exactly the other way around.

Bear in mind that this will force us to perform some reordering maneuvers using character handling functions.

While reading a field DATE always gives us the data separated by hyphens, when inserting a data allows us to do so either in continuous number format (for example, 201512319, or using any dividing character (2015-12-31 or any another character that separates the three groups).

The range of dates that allows to handle from 1000-01-01 to 9999-12-31

That is, it will not be useful if we work with a timeline that is remote before the year 1000, (any application related to history?), But if it is useful for data from the near past and a very long future ahead, since it almost reaches the year 10,000

DATETIME

A field defined as DATETIME will allow us to store information about a moment of time, but not only the date but also its schedule, in the format:

AAAA-MM-DD HH:MM:SS

Being the part of the date of a range similar to that of the DATE type (from 1000-01-01 00:00:00 to 9999-12-31 23:59:59), and the part of the schedule, from 00:00 : 00 to 23:59:59

TIME

This type of change allows you to store hours, minutes and seconds in the format HH: MM: SS, and its allowed range goes from -839: 59: 59 to 839: 59: 59 (about 35 days backwards and forwards of the current date). This makes it ideal for calculating times between two close moments.

TIMESTAMP

A field that has the TIMESTAMP data type defined is used to store a date and a schedule, similar to DATETIME, but its format and range of values will be different.

The format of a TIMESTAMP field can vary between three options:

• AAAA-MM-DD HH:MM:SS
• AAAA-MM-DD
• AA-MM-DD

That is, the possible length can be 14, 8 or 6 digits, depending on what information we provide.

The range of dates that this field handles goes from 1970-01-01 to the year 2037.

In addition, it has the particularity that we can define that its value is automatically updated, each time a record is inserted or updated.

In this way, we will always keep in this field the date and time of the last update of that data, which is ideal to take control without programming anything.

To define this from the phpMyAdmin, we must select in Attributes the option "on update" CURRENT_TIMESTAMP, and as default value CURRENT_TIMESTAMP:

Field whose value will be updated automatically when inserting or modifying a record

YEAR

In case of defining a field as YEAR, we can store a year, both using two and four digits.

In case of doing it in two digits, the possible range will be extended from 70 to 99 (from 70 to 99 it will be understood that they correspond to the range of years between 1970 and 1999, and from 00 to 69 it will be understood that it refers to the years 2000 to 2069); in case of providing the four digits, the possible range will be extended, going from 1901 to 2155.

An extra possibility, unrelated to MySQL but related to dates and times, is to generate a timestamp value with the PHP time function (again, we're not talking about MySQL, do not get confused because of so many similar names).

At that value, we can store it in a 10-digit INT field.

In this way, it will be very simple to order the values of that field (suppose it is the date of a news item) and then we can show the date by transforming that timestamp value into something readable using PHP's own date handling functions.

Attributes of the fields

We have already seen the different types of data that can be used when defining a field in a table, but these types of data can have certain modifiers or "attributes" that can be specified when creating the field, and that give us the possibility to control more accurately what can be stored in that field, how we will store it and other details.

Although some of these attributes we have already used intuitively in passing in some of the previous examples, we will analyze more in detail below.

Null or Not Null?

Sometimes we will have the need to add records without the values of all their fields being completed, that is, leaving some fields empty (at least temporarily).

For example, in an electronic commerce system, it could be that the price, or the complete description of a product, or the number of units in deposit, or the image of the product, are not available at the moment in which, as programmers, let's start working with the database.

All these fields, we should be defined as NULL (null), so we can go adding records with the basic data of the products (your name, code, etc.) Although still the people of the commercial area have not defined the price, neither the marketing area has finished the descriptions, nor the designers have uploaded the photos (this division of tasks in large companies is typical, and it must be kept in mind, because it affects the declaration of fields in our tables).

If we define those fields that are not essential to fill in as NULL (simply by marking the check box at the height of the NULL column, in the phpMyAdmin), the field is prepared so that, if not provided a value, it remains empty but still allows us to complete the insertion of a complete record.

By default, if we do not check any box, all fields are NOT NULL, that is, it is mandatory to enter a value in each field in order to load a new record in the table.

Predetermined value (default)

Many times, we need to speed up the loading of data using a default value (default).

For example, let's think about an order system, where, when the order arrives in the database, its status is "received", without the need for the system to send any value, just by adding the record, that record should contain in the field "status" the value of "received".

This is a typical default or default case.

In phpMyAdmin, we can specify that a field has a default value in three possible ways:

Writing the value by hand (as in the case of "received") in which case we must choose the first of the options in the "Default" column, the one that says "As it was defined:", and we must write the value in the Existing text field just below that menu:

1. We can define that the default value is NULL, that is, if a value is not provided, the NULL value remains as the value of that field
2. And, finally, we can define for a field of type TIMESTAMP that the current value of TIMESTAMP (Current_Timestamp) is inserted as the default value, something we have seen in detail when referring to this type of data

In both cases, we must leave the lower text box empty.

It is important to note that you can not give a default value to a field of type TEXT or BLOB (and all its variants).

Binary management

Defining a text field CHAR or VARCHAR as BINARY (binary) only affects the ordering of the data: instead of being case-insensitive, a BINARY field will be ordered taking into account this difference, so that, equal to letter, first the data containing that letter in capital letters will appear first.

It is defined from the phpMyAdmin by choosing BINARY in the Attributes menu:

Indices

The objective of creating an index is to keep the records organized by those fields that are frequently used in searches, in order to speed up the response times.

An index is nothing more than a "parallel" table, which stores the same data as the original table, but instead of being ordered by insertion order or by the primary key of the table, the index is sorted by the field that we choose to index.

For example, if there was a search engine by news title, in the news table we would assign an index to the "title" field, and the news would be ordered from "a" to "z" by its title.

The search will be done first in the index, quickly finding the searched title, since they are all sorted and, as a result, the index will return to the MySQL program the identifier (id) of the record in the original table, so that MySQL goes directly to Search for that record with the rest of the data, without losing time.

All this, of course, completely invisible to us.

To indicate that we want to create an index ordered by a certain field, to the field to be indexed, we must specify, within the Index attribute, of the selection menu that appears on its right, the Index option

As we can see, within the Index menu other options appear: Primary, Unique and Fulltext. Let's see what each one of these variants consists of.

PRIMARY Key and Auto_Increment

Always, in the whole table, one of the fields (by convention, the first, and also by convention usually called id - by "identifier" -), must be defined as primary key or Primary Key.

This will prevent repeated values from being inserted and their value being left null.

Usually, it is specified that the field chosen for the primary key is numeric, of integer type (in any of its variants, depending on the number of elements to be identified) and assigned another typical attribute, which is Auto_Increment, that is, not we care about giving value to that field: when adding a record, MySQL takes care of increasing the value of the primary key of the last aggregate record by one, and assigning it to the new record.

This field does not usually have any relationship with the contents of the table, its objective is simply to identify each record in a unique, unrepeatable way.

We can define a single field as a primary key, or two or more combined fields.

If you have defined two or more fields so that together they form the unique value of a primary key, we will say that it is a "combined" or "composite" primary key.

UNIQUE

If we specify that the value of a field is Unique, we will be forcing that its value can not be repeated in more than one record, but that does not mean that the field will be considered the primary key of each record.

This is useful for a field that stores, for example, number of identity documents, the email box used to identify a user's access, a username, or any other data that we should not allow to be repeated.

Attempts to add a new record that contains a value already existing in that field will be rejected.

FULLTEXT

If in a TEXT type field we create a FULLTEXT type index, MySQL will examine the content of this field word by word, storing each word in a cell of a matrix, allowing searches of words contained within the field, and no longer a simple one Search of total coincidence of the value of the field, which are much faster but do not work in the case of search within, for example, the body of a news, where the user wants to find news that mention a certain word.

Words of less than four characters and common words such as articles, etc. are ignored. which are considered irrelevant to a search, as well as differences between uppercase and miniscule.

In addition, if the searched word is found in more than 50% of the records, it will not return results, since it is considered irrelevant due to "excess" of appearance (we must refine the search in this case).

With this, we end this fairly comprehensive review.

We have already learned to create a database and a table, defining their fields with total precision, using data types and attributes, therefore, we are in a position to start programming everything necessary for our PHP pages to connect with a database and can send or request data.