Виртуальные столбцы в MySQL и варианты использования. кратко

Привет, Вы узнаете о том , что такое виртуальные столбцы, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое виртуальные столбцы, виртуальный столбец , настоятельно рекомендую прочитать все из категории MySql (Maria DB).

Введение:

• MySQL 5.7 вводит новую функцию, называемую виртуальным / сгенерированным столбцом. Он называется сгенерированным столбцом, потому что данные этого столбца вычисляются на основе предопределенного выражения или из других столбцов .

Что такое виртуальная колонка?

• В общем, виртуальные столбцы выглядят как обычные столбцы таблицы, но их значения извлекаются, а не хранятся на диске.
• Виртуальные столбцы - одна из основных функций MySQL 5.7, они могут хранить значение, полученное из одного или нескольких других полей той же таблицы, в новом поле.

Синтаксис:

Синтаксис для добавления нового виртуального столбца,

==> Изменить таблицу имя_таблицы добавить столбец имя_столбца, создаваемое всегда как виртуальное имя_столбца;

Пример :

Изменить таблицу контактов добавить столбец, который всегда создается как mydbops_test virtual / stored.

GENERATED ALWAYS - указывает, что столбец является сгенерированным столбцом.

ВИРТУАЛЬНО - значения столбцов не сохраняются, но они оцениваются при чтении строк.

STORED - значения столбцов оцениваются и сохраняются при вставке или обновлении строк.

Случаи использования:

Случай 1 (с использованием concat):

Например, у нас есть структура таблицы mydbops_lab_test, как показано ниже,

mysql> создать таблицу mydbops_lab_test
(id int (11) NOT NULL AUTO_INCREMENT первичный ключ,
имя varchar (20),
фамилия varchar (20),
full_name char (41) ВСЕГДА ГЕНЕРИРУЕТСЯ КАК (concat (имя, '', фамилия)),
email_id varchar (25)); 
 Запрос в порядке, затронуты 0 строк (0,40 сек)

mysql> desc mydbops_lab_test;
+ ----------- + ------------- + ------ + ----- + --------- + ------------------- +
| Поле | Тип | Null | Ключ | По умолчанию | Экстра |
+ ----------- + ------------- + ------ + ----- + --------- + ------------------- +
| id | int (11) | НЕТ | PRI | NULL | auto_increment |
| имя | варчар (20) | ДА | | NULL | |
| фамилия | варчар (20) | ДА | | NULL | |
| полное_имя | символ (41) | ДА | | NULL | ВИРТУАЛЬНАЯ ГЕНЕРАЦИЯ |
| email_id | варчар (25) | ДА | | NULL | |
+ ----------- + ------------- + ------ + ----- + --------- + ------------------- +
5 рядов в наборе (0,00 сек)

Нам нужно протестировать столбец с полным именем, поэтому заполните несколько строк в таблице контактов.

mysql> выберите * из mydbops_lab_test;
+ ---- + ----------- + ---------- + -------------- + ------ ----------- +
| id | имя | фамилия | полное_имя | email_id |
+ ---- + ----------- + ---------- + -------------- + ------ ----------- +
| 1 | Джон | рубин | Джон Рубин | rubin@gmail.com |
| 2 | отметка | Генри | Марк Генри | mark@gmail.com |
| 3 | Питер | Паркер | Питер Паркер | peter@yahoo.com |
| 4 | Джим | роза | Джим Роуз | jim@outlook.com |
+ ---- + ----------- + ---------- + -------------- + ------ ----------- +
4 ряда в наборе (0,00 сек)

Добавление виртуального столбца в существующую таблицу:

mysql> ALTER TABLE v_column ДОБАВИТЬ полное_имя char (41) СОЗДАВАЕТСЯ ВСЕГДА КАК (concat (firstname, '', lastname)) VIRTUAL NOT NULL;
 Запрос в порядке, затронуто 0 строк (0,35 секунды)
 Записей: 0 Дубликатов: 0 Предупреждений: 0

Случай 2 (с использованием разницы):

• В этом примере я рассчитываю сумму баланса для тестовой таблицы, поэтому я создал новую таблицу с виртуальным столбцом.

mysql> создать таблицу mydbops_lab_test_1
(id int (11) NOT NULL AUTO_INCREMENT первичный ключ,
Total_cost float (9,4),
Дорогой поплавок (9,4),
Balance_amount float (12,6) ВСЕГДА ГЕНЕРИРУЕТСЯ КАК (Total_cost - Дорого),
email_id varchar (25),
имя varchar (30));
 Запрос в порядке, затронуто 0 строк (0,34 секунды)

mysql> desc mydbops_lab_test_1;
 + ---------------- + ------------- + ------ + ----- + ----- ---- + ------------------- +
 | Поле | Тип | Null | Ключ | По умолчанию | Экстра |
 + ---------------- + ------------- + ------ + ----- + ----- ---- + ------------------- +
 | id | int (11) | НЕТ | PRI | NULL | auto_increment |
 | Total_cost | поплавок (9,4) | ДА | | NULL | |
 | Дорогое | поплавок (9,4) | ДА | | NULL | |
 | Balance_amount | Поплавок (12,6) | ДА | | NULL | ВИРТУАЛЬНАЯ ГЕНЕРАЦИЯ |
 | email_id | VARCHAR (25) | ДА | | NULL | |
 | имя | VARCHAR (30) | НЕТ | | NULL | |
 + ---------------- + ------------- + ------ + ----- + ----- ---- + ------------------- +
 6 рядов в наборе (0,00 сек)

• Я вставил несколько записей в эту таблицу (mydbops_lab_test_1). Об этом говорит сайт https://intellect.icu . Нам нужно рассчитать сумму баланса каждого человека.

mysql> выберите идентификатор, имя, Total_cost, Balance_amount из mydbops_lab_test_1, где Balance_amount <2000 заказ по Balance_amount desc;

 + ---- + -------- + ------------ + ---------------- +
 | id | имя | Total_cost | Balance_amount |
 + ---- + -------- + ------------ + ---------------- +
 | 1 | роза | 2000.0000 | 1949.109985 |
 | 5 | радж | 810.1100 | 798.209961 |
 | 4 | Кевин | 900.0000 | 579.000000 |
 | 9 | суреш | 677.1000 | 457.099976 |
 | 3 | Джим | 100.0000 | 71.000000 |
 | 8 | колокол | 41.9700 | 19.970001 |
 + ---- + -------- + ------------ + ---------------- +
 6 рядов в наборе (0,00 сек)

• Виртуальные столбцы вычисляются каждый раз, когда данные читаются, тогда как сохраненный столбец вычисляется и физически сохраняется при обновлении данных.

Индексирование виртуальных столбцов:

• В MySQL InnoDB поддерживает вторичные индексы виртуальных столбцов. Индексы других типов не поддерживаются (Full Text / GIS).
• Вторичный индекс может быть создан на основе одного или нескольких виртуальных столбцов или комбинации виртуальных столбцов и обычных столбцов или сохраненных сгенерированных столбцов. Вторичные индексы, включающие виртуальные столбцы, могут быть определены как уникальные.

Пример :

Здесь я добавил индекс для виртуального сгенерированного столбца (Balance_amount).

Синтаксис:

mysql> изменить таблицу mydbops_lab_test_1 добавить индекс idx_Balance_amount (Balance_amount);
 Запрос в порядке, затронуто 0 строк (0,33 секунды)
 Записей: 0 Дубликатов: 0 Предупреждений: 0

mysql> показать таблицу создания mydbops_lab_test_1 \ G
 ************************** 1. строка ******************** *******
 Таблица: mydbops_lab_test_1
 Создать таблицу: СОЗДАТЬ ТАБЛИЦУ `mydbops_lab_test_1` (
 `id` int (11) NOT NULL AUTO_INCREMENT,
 `Total_cost` float (9,4) ПО УМОЛЧАНИЮ NULL,
 `Дорогой` float (9,4) DEFAULT NULL,
 `Balance_amount` float (12,6) ВСЕГДА ГЕНЕРИРУЕТСЯ КАК ((` Total_cost` - `Дорого`)) ВИРТУАЛЬНО,
 `email_id` varchar (25) ПО УМОЛЧАНИЮ NULL,
 `name` varchar (30) НЕ NULL,
 ПЕРВИЧНЫЙ КЛЮЧ (`id`),
 КЛЮЧ `idx_Balance_amount` (` Balance_amount`)
 ) ДВИГАТЕЛЬ = InnoDB AUTO_INCREMENT = 11 НАБОР ДАННЫХ ПО УМОЛЧАНИЮ = latin1
 1 ряд в наборе (0,00 сек)

Преимущества:

• Виртуально сгенерированные столбцы можно использовать как способ упрощения и унификации запросов.
• Сложное условие можно определить как сгенерированный столбец и ссылаться на него из нескольких запросов в таблице, чтобы гарантировать, что все они используют точно такое же условие.
• Это может быть полезно для работы со столбцами типов, которые нельзя проиндексировать напрямую, например столбцами JSON.
• Столбец [NOT NULL] не поддерживается MariaDB, но разрешен только в 5.7.

Недостатки:

• Недостатком виртуальных столбцов является то, что значения сохраняются дважды: один раз как значение сгенерированного столбца и один раз в индексе.
• Если сгенерированный столбец проиндексирован, оптимизатор распознает выражения запроса, которые соответствуют определению столбца, и использует соответствующие индексы из столбца во время выполнения запроса.

Ключевые моменты, о которых следует помнить:

Сгенерированные выражения столбцов подчиняются некоторым правилам:

• Подзапросы, параметры, переменные, сохраненные функции и пользовательские функции не допускаются.
• Сгенерированное определение столбца может относиться к другим сгенерированным столбцам, но только к тем, которые встречаются ранее в определении таблицы.
• Столбец auto_increment нельзя использовать в качестве базового столбца в сгенерированном определении столбца.
• Таблица создания, такая как таблица назначения, сохраняет информацию сгенерированных столбцов из исходной таблицы.
• Создание таблицы с выбором целевой таблицы не сохраняет информацию о том, являются ли столбцы в выбранной из таблицы сгенерированными столбцами.

Часть оператора Select не может присвоить значения сгенерированным столбцам в целевой таблице.

Исследование, описанное в статье про виртуальные столбцы, подчеркивает ее значимость в современном мире. Надеюсь, что теперь ты понял что такое виртуальные столбцы, виртуальный столбец и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории MySql (Maria DB)