What is the virtual data structure for in Dbeaver or Mysql?

Question

Viewing a Mysql tables by Dbeaver, I noticed a tab called Virtual. In it it is possible to create Virtual Columns, Virtual Foreign Keys, Virtual Unique Keys and Referências Virtuais.

What is the functionality of these features? How does this affect the Database? What is the advantage of using it? This affects the Mysql Database or is just another Dbeaver resource?

Answer 1

Before that it is necessary to understand that to be virtual (or not), they need to be computed columns, that is, are values calculated from other columns. This feature is not only present in the MySQL, also exists in other banks such as SQL-Server and Oracle, among others.

In a very simple way, virtual Columns are not persisted, ie its content is not stored, only calculated.

With more details, the documentation of the MySQL here: create-table-generated-Columns.html

The VIRTUAL or STORED keyword indicates how column values are stored, which has implications for column use:

VIRTUAL: Column values are not stored, but are evaluated when Rows are read, immediately after any BEFORE triggers. A virtual column takes no Storage.

Innodb Supports Secondary Indexes on virtual Columns. See Section 13.1.18.8, "Secondary Indexes and Generated Columns".

STORED: Column values are evaluated and stored when Rows are inserted or updated. A stored column does require Storage space and can be Indexed.

The default is VIRTUAL if neither keyword is specified

In free translation:

The VIRTUAL or STORED keyword indicates how the column values are stored, which has implications for the use of the column:

VIRTUAL: column values are not stored, but are evaluated when the lines are read, immediately after any Trigger BEFORE. A virtual column does not require storage.

Innodb supports secondary indices in virtual columns. See Section 13.1.18.8, "Secondary indices and generated columns".

STORED: column values are evaluated and stored when lines are inserted or updated. A stored column requires storage space and can be indexed.

Default is VIRTUAL if no keyword is specified

That is, a column with computed value, can be persisted (STORED) in the bank or not (VIRTUAL).

Let’s take a simple example:

CREATE TABLE Valores (
   valor DECIMAL(10,2),
   desconto  DECIMAL(10,2) AS (valor * 0.1) VIRTUAL,
   desconto2 DECIMAL(10,2) AS (valor * 0.2) STORED
);

INSERT INTO Valores(valor) VALUES (1000);

SELECT * FROM Valores;

The columns "desconto" and "desconto2" are computed columns, that is, they depend on the column "valor", and calculate a discount from "valor".

Understood the concept, let’s analyze the usefulness of VIRTUAL.

What is the functionality of these features?

Allow a computed column to be (STORED) or not (VIRTUAL) stored along with the other table data.

How this affects the Database?

First we should note that, using VIRTUAL/STORED when creating a computed column is optional, and therefore the default is VIRTUAL. This seems kind of obvious, since the value is calculated from another column, this could be done directly in the query, therefore, at first it does not make sense to have this value stored, so the default is VIRTUAL. So it obviously affects the bank, since it does not take up space when VIRTUAL.

What is the advantage of using it?

The advantage was mentioned above: have a calculated value that does not need to be stored, since it can always be calculated based on another column.
So why use the STORED? Imagine that for some reason you needed to index this field, this would not be possible because it does not actually exist in the table, it is VIRTUAL. Here enters the utility of STORED, ie the stored computer field can be used in an index, that is the reason of being of STORED.

This affects the Mysql Database or is just another Dbeaver resource?

As commented above, this is a feature that exists in other databases in a similar way, the DBeaver only provides the interface to facilitate its creation/maintenance.

Note that although it is present in other databases, it is important to see the specific documentation of each one. For example, while in Oracle also used VIRTUAL/STORED, on SQL-Server and in the MariaDB is VIRTUAL/PERSISTED.