Error 3106 changing the stored status is not supported for generated columns

I encountered an error when using the literal type to change columns.

use PhinxDbAdapterMysqlAdapter;
use PhinxUtilLiteral;

class ContactsGenerated2 extends PhinxMigrationAbstractMigration
{
    public function change()
    {
        $this->execute('SET unique_checks=0; SET foreign_key_checks=0;');
        $this->table('t_contacts', [
                'id' => false,
                'primary_key' => ['c_uid'],
                'engine' => 'InnoDB',
                'encoding' => 'utf8mb4',
                'collation' => 'utf8mb4_0900_ai_ci',
                'row_format' => 'DYNAMIC',
            ])
            ->changeColumn('c_fn', Literal::from("varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (`c_json`->>'$.fn') VIRTUAL"), [
                'null' => true,
            ])
            ->update();
        $this->execute('SET unique_checks=1; SET foreign_key_checks=1;');
    }
}

As long as I change only the json path c_json->>'$.to.something.else', all works well, but changing VIRTUAL to STORED or vice-versa fails with

PDOException: SQLSTATE[HY000]: General error: 3106 'Changing the STORED status' is not supported for generated columns. in /srv/varwwwhtml/glued-skeleton/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:194…

I think that when the STORED status is changed, the changed column needs to get dropped and recreated.

Perhaps you have columns in your table that work together to provide some meaningful results. During table creation, you have the option to CREATE a GENERATED COLUMN in MySQL. What is that? And, what can you use it for? Read on to learn with me and find out…

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and DB used:

• Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
• MySQL 8.0.15

Let’s look at a simple example and this CREATE TABLE statement:

Next, I will populate the table with some mock data:

Notice in the above INSERT, the email column was not part of the listed columns, nor was any data supplied in the VALUES clause for it.

Let’s check the table data now:

You can see that column email returns the definition we set during table creation, concatenating the f_name and l_name with the company email address. Hence, this is the GENERATED COLUMN.

Let’s see the underlying details for table company_emails:

The Extra column provides an important specifier or definition, VIRTUAL, which is the default should one be omitted in the definition (as was the case in the example here). However, there is also a STORED option for GENERATED COLUMN‘s.

The naming helps (me at least!) with clearing up any distinction between the two but, in order to solidify the concept, we will have an example.

Since I am not changing the column name, but only the definition, I use the MODIFY clause in this ALTER TABLE statement:

(Told you I was learning!!!)

Hmm…
Can’t do that it seems.
Why?

See this passage from the ALTER TABLE and Generated Columns official documentation for better understanding:

“Virtual generated columns cannot be altered to stored generated columns, or vice versa. To work around this, drop the column, then add it with the new definition.”

Probably a good idea to keep this in mind during the design phase. Dropping a column and reimplementing it has its drawbacks (in my mind at least) due to table locking and the fact you are essentially discarding data.

Yet, for our arbitrary example, we will proceed forward:

Let’s run another INSERT then check the data:

So what’s the difference between them?
The crux of it is:

• STORED columns take up storage space and can have indexes. (I think of them as real).
• VIRTUAL columns are just essentially generated on the fly as rows are returned in query results.

While the example presented here is mostly arbitrary, I am sure you can find a use for GENERATED COLUMN‘s in your tables… Let me know all about them in the comments below!!!

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

Explore the official MySQL 8.0 Online Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

У MySQL нет возможности напрямую индексировать документы JSON, но есть альтернатива: генерируемые столбцы.

С момента введения поддержки типа данных JSON в MySQL 5.7.8 не хватает одной вещи: способности индексировать значения JSON. Для того, чтобы обойти это ограничение, можно использовать генерируемые столбцы. Эта возможность, представленная в MySQL 5.7.5, позволяет разработчикам создавать столбцы, содержащие информацию, полученную из других столбцов, предопределенных выражений или вычислений. Генерируя столбец из значений JSON, а затем индексируя его, можно практически индексировать поле с JSON.

Набор данных в формате JSON, используемый в данной статье, можно скачать на Гитхабе. Он содержит список игроков со следующими элементами: идентификатор игрока, его имя и игры, в которые он играл (Battlefield, Crazy Tennis и Puzzler).

{
    "id":1,
    "name":"Sally",
    "games_played":{
        "Battlefield":{
            "weapon":"sniper rifle",
            "rank":"Sergeant V",
            "level":20
        },
        "Crazy Tennis":{
            "won":4,
            "lost":1
        },
        "Puzzler":{
            "time":7
        }
    }
},
…

Поле Battlefield содержит любимое оружие игрока, его текущий ранг и уровень этого ранга. Crazy Tennis включает в себя количество выигранных и проигранных игр, а Puzzler содержит время, затраченное игроком на прохождение игры. Создадим начальную таблицу:

CREATE TABLE `players` (  
    `id` INT UNSIGNED NOT NULL,
    `player_and_games` JSON NOT NULL,
    PRIMARY KEY (`id`)
);

Этот запрос создает таблицу players, состоящую из идентификатора и JSON-данных, а также устанавливает в поле id первичный ключ.

Нужно построить индекс по полю с JSON. Давайте посмотрим, что нужно добавить в команду CREATE TABLE.

Генерация столбцов

Для создания генерируемых столбцов в операторе CREATE TABLE используется следующий синтаксис:

`column_name` datatype GENERATED ALWAYS AS (expression)

Ключевыми словами здесь являются GENERATED ALWAYS и AS. Фраза GENERATED ALWAYS необязательна. Она необходима только в том случае, если вы хотите явно указать, что этот столбец таблицы — генерируемый. Необходимо, чтобы слово AS сопровождалось выражением, которое вернет значение для генерируемого столбца.

Начнем с этого:

`names_virtual` VARCHAR(20) GENERATED ALWAYS AS ...

Cоздаем столбец с именем names_virtual длиной до 20 символов, в котором будем хранить значение поля «name» из объекта JSON. Обращаться к полю «name» в JSON будем с использованием MySQL-оператора ->>, который эквивалентен написанию JSON_UNQUOTE (JSON_EXTRACT (...)). Эта конструкция вернет значение поля «name» из объекта JSON в качестве результата.

`names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name')

Этот код означает, что мы берём поле c JSON player_and_games и извлекаем значение из JSON по ключу «name» — дочернее по отношению к корню.

Как и в большинстве определений столбцов, существует ряд ограничений и параметров, которые можно применить к столбцу.

[VIRTUAL|STORED] [UNIQUE [KEY]] [[NOT] NULL] [[PRIMARY] KEY]

Уникальные для генерируемых столбцов ключевые слова VIRTUAL и STORED указывают на то, будут ли значения сохраняться в таблице.

Ключевое слово VIRTUAL используется по умолчанию. Оно означает, что значения столбца не сохраняются и не занимают место для хранения. Они вычисляются при каждом чтении строки. Если вы создаете индекс с виртуальным столбцом, значение всё же сохраняется — в индексе.

Ключевое слово STORED указывает, что значения вычисляются при записи данных в таблицу: при вставке или обновлении. В этом случае индексу не нужно сохранять значение.

Другие параметры — необязательные ограничения, которые гарантируют, что значения поля будут NULL или NOT NULL, а также добавления ограничений на индекс, например, UNIQUE или PRIMARY KEY. Для гарантии существования значения следует использовать NOT NULL при создании столбца, однако ограничения зависят от варианта использования. В примере будет использоваться NOT NULL, так как у игроков обязательно есть имя.

Запрос, создающий таблицу:

CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
);

Заполнение таблицы тестовыми данными:

INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{  
    "id": 1,  
    "name": "Sally",
    "games_played":{    
       "Battlefield": {
          "weapon": "sniper rifle",
          "rank": "Sergeant V",
          "level": 20
        },                                                                                                                          
       "Crazy Tennis": {
          "won": 4,
          "lost": 1
        },  
       "Puzzler": {
          "time": 7
        }
      }
   }'
);
...

Содержимое таблицы players на Гисте или…

SELECT * FROM `players`;

+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| id | player_and_games                                                                                                                                                                                           | names_virtual |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
|  1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}}                  | Sally         |
|  2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}}            | Thom          |
|  3 | {"id": 3, "name": "Ali", "games_played": {"Puzzler": {"time": 12}, "Battlefield": {"rank": "First Sergeant II", "level": 37, "weapon": "machine gun"}, "Crazy Tennis": {"won": 30, "lost": 21}}}           | Ali           |
|  4 | {"id": 4, "name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": {"rank": "Chief Warrant Officer Five III", "level": 73, "weapon": "pistol"}, "Crazy Tennis": {"won": 47, "lost": 2}}} | Alfred        |
|  5 | {"id": 5, "name": "Phil", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Lt. Colonel III", "level": 98, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 130, "lost": 75}}}          | Phil          |
|  6 | {"id": 6, "name": "Henry", "games_played": {"Puzzler": {"time": 17}, "Battlefield": {"rank": "Captain II", "level": 87, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 68, "lost": 149}}}             | Henry         |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+

Таблица включает столбец names_virtual, в который вставлены все имена игроков. Структура таблицы players:

SHOW COLUMNS FROM `players`;

+------------------+------------------+------+-----+---------+-------------------+
| Field            | Type             | Null | Key | Default | Extra             |
+------------------+------------------+------+-----+---------+-------------------+
| id               | int(10) unsigned | NO   | PRI | NULL    |                   |
| player_and_games | json             | NO   |     | NULL    |                   |
| names_virtual    | varchar(20)      | NO   |     | NULL    | VIRTUAL GENERATED |
+------------------+------------------+------+-----+---------+-------------------+

Поскольку мы не указали, является ли генерируемый столбец VIRTUAL или STORED, по умолчанию MySQL автоматически сделал столбец VIRTUAL. Чтобы проверить, являются ли столбцы VIRTUAL или STORED, просто запустите вышеуказанный запрос SHOW COLUMNS, и он покажет либо VIRTUAL GENERATED, либо STORED GENERATED.

Теперь, когда мы настроили таблицу и виртуальный столбец, добавим еще четыре столбца, используя операции ALTER TABLE и ADD COLUMN. Они будут содержать уровни Battlefield, выигранные и проигранные игры в теннис и время в Puzzler.

ALTER TABLE `players` ADD COLUMN `battlefield_level_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Battlefield.level') NOT NULL AFTER `names_virtual`;  
ALTER TABLE `players` ADD COLUMN `tennis_won_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".won') NOT NULL AFTER `battlefield_level_virtual`;  
ALTER TABLE `players` ADD COLUMN `tennis_lost_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".lost') NOT NULL AFTER `tennis_won_virtual`;  
ALTER TABLE `players` ADD COLUMN `times_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Puzzler.time') NOT NULL AFTER `tennis_lost_virtual`;  

Опять же, запустив запрос SHOW COLUMNS FROM players;, мы видим, что рядом с ними все столбцы указаны как VIRTUAL GENERATED. Это означает, что мы успешно настроили новые созданные VIRTUAL столбцы.

Код Гисте или…

+---------------------------+------------------+------+-----+---------+-------------------+
| Field                     | Type             | Null | Key | Default | Extra             |
+---------------------------+------------------+------+-----+---------+-------------------+
| id                        | int(10) unsigned | NO   | PRI | NULL    |                   |
| player_and_games          | json             | NO   |     | NULL    |                   |
| names_virtual             | varchar(20)      | NO   |     | NULL    | VIRTUAL GENERATED |
| battlefield_level_virtual | int(11)          | NO   |     | NULL    | VIRTUAL GENERATED |
| tennis_won_virtual        | int(11)          | NO   |     | NULL    | VIRTUAL GENERATED |
| tennis_lost_virtual       | int(11)          | NO   |     | NULL    | VIRTUAL GENERATED |
| times_virtual             | int(11)          | NO   |     | NULL    | VIRTUAL GENERATED |
+---------------------------+------------------+------+-----+---------+-------------------+

Выполнение запроса SELECT показывает нам все значения из VIRTUAL COLUMNS, которые должны выглядеть так:

Код Гисте или…

SELECT `names_virtual`, `battlefield_level_virtual`, `tennis_won_virtual`, `tennis_lost_virtual`, `times_virtual` FROM `players`;

+---------------+---------------------------+--------------------+---------------------+---------------+
| names_virtual | battlefield_level_virtual | tennis_won_virtual | tennis_lost_virtual | times_virtual |
+---------------+---------------------------+--------------------+---------------------+---------------+
| Sally         |                        20 |                  4 |                   1 |             7 |
| Thom          |                       127 |                 10 |                  30 |            25 |
| Ali           |                        37 |                 30 |                  21 |            12 |
| Alfred        |                        73 |                 47 |                   2 |            10 |
| Phil          |                        98 |                130 |                  75 |             7 |
| Henry         |                        87 |                 68 |                 149 |            17 |
+---------------+---------------------------+--------------------+---------------------+---------------+

После добавления данные и создания генерируемых столбцов, мы можем создавать индекс для каждого из них, чтобы оптимизировать поиск…

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

При установке вторичных индексов на значения генерируемых столбцов VIRTUAL значения сохраняются в индексе. Это дает преимущества: размер таблицы не увеличивается, появляется возможность использования индексов в MySQL.

Давайте сделаем простой запрос к генерируемому столбцу, чтобы увидеть, как он выглядит, прежде чем индексировать его. Изучив детали запроса при выборе names_virtual и имени «Sally», получим следующее:

EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ALL
possible_keys: NULL  
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where

Для этого запроса MySQL просматривает каждую строку, чтобы найти «Sally». Однако, можно получить совершенно другой результат, добавив индекс к столбцу:

CREATE INDEX `names_idx` ON `players`(`names_virtual`);  

Теперь, выполняя тот же запрос, получаем:

EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ref
possible_keys: names_idx  
          key: names_idx
      key_len: 22
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

Как видно, индекс в столбце ускорил запрос, просматривая только одну строку вместо шести, используя индекс names_idx. Давайте создадим индексы для остальных виртуальных столбцов, следуя тому же синтаксису, что и names_idx:

CREATE INDEX `times_idx` ON `players`(`times_virtual`);  
CREATE INDEX `won_idx` ON `players`(`tennis_won_virtual`);  
CREATE INDEX `lost_idx` ON `players`(`tennis_lost_virtual`);  
CREATE INDEX `level_idx` ON `players`(`battlefield_level_virtual`);  

Можно проверить, были ли проиндексированы все наши столбцы, запустив:
Код Гисте или…

SHOW INDEX ON `players`;

+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| players |          0 | PRIMARY   |            1 | id                        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | names_idx |            1 | names_virtual             | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | times_idx |            1 | times_virtual             | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | won_idx   |            1 | tennis_won_virtual        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | lost_idx  |            1 | tennis_lost_virtual       | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | level_idx |            1 | battlefield_level_virtual | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Теперь, когда созданы несколько индексов в генерируемых столбцах, давайте усложним поиск. В этом примере выбираются идентификаторы, имена, выигранные теннисные игры, уровень Battlefield и время Puzzler для игроков, которые имеют уровень выше 50, а также выигравших 50 теннисных игр. Все результаты будут упорядочены по возрастанию в соответствии с временем в Puzzler. Команда SQL и результаты будут выглядеть так:

SELECT `id`, `names_virtual`, `tennis_won_virtual`, `battlefield_level_virtual`, `times_virtual` FROM `players` WHERE (`battlefield_level_virtual` > 50 AND  `tennis_won_virtual` > 50) ORDER BY `times_virtual` ASC;

+----+---------------+--------------------+---------------------------+---------------+
| id | names_virtual | tennis_won_virtual | battlefield_level_virtual | times_virtual |
+----+---------------+--------------------+---------------------------+---------------+
|  5 | Phil          |                130 |                        98 |             7 |
|  6 | Henry         |                 68 |                        87 |            17 |
+----+---------------+--------------------+---------------------------+---------------+

Давайте посмотрим, как MySQL выполнял этот запрос:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: range
possible_keys: won_idx,level_idx  
          key: won_idx
      key_len: 4
          ref: NULL
         rows: 2
     filtered: 66.67
        Extra: Using where; Using filesort

При использовании индексов win_idx и level_idx MySQL приходилось обращаться к двум столбцам, чтобы вернуть желаемый результат. Если запрос должен выполнить полный просмотр таблицы с миллионом записей, это займёт очень много времени. Однако, с помощью генерируемых столбцов и их индексированием, MySQL показал очень быстрый результат и удобный способ поиска элементов в JSON-данных.

Тем не менее остается один вопрос: для чего нужны STORED генерируемые столбцц? Как их использовать и как они работают?

Хранение значений в генерируемых столбцах

Использование ключевого слова STORED при настройке генерируемого столбца обычно не предпочтительно, поскольку в основном значения в таблице сохраняются дважды: поле с JSON и в STORED столбце. Тем не менее, существует три сценария, когда в MySQL нужно использовать столбец STORED:

1. индексирование первичных ключей,
2. полнотекстовый индекс/индекс R-tree,
3. столбец, который часто выбирается.

Синтаксис добавления генерируемого STORED столбца, совпадает с созданием генерируемых столбцов VIRTUAL, за исключением того, что нужно добавить ключевое слово STORED:

`id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED NOT NULL,

Чтобы посмотреть как использовать STORED, создадим еще одну таблицу. Она будет брать id из данных JSON и хранить его в STORED столбце. Установим PRIMARY KEY для столбца id:

CREATE TABLE `players_two` (  
    `id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED NOT NULL,
    `player_and_games` JSON NOT NULL,
    `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL,
    `times_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Puzzler.time') NOT NULL,
    `tennis_won_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".won') NOT NULL,
    `tennis_lost_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".lost') NOT NULL,
    `battlefield_level_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Battlefield.level') NOT NULL, 
    PRIMARY KEY (`id`),
    INDEX `times_index` (`times_virtual`),
    INDEX `names_index` (`names_virtual`),
    INDEX `won_index` (`tennis_won_virtual`),
    INDEX `lost_index` (`tennis_lost_virtual`),
    INDEX `level_index` (`battlefield_level_virtual`)
);

Добавим тот же набор данных в player_two, за исключением того, что удалим id, который ранее добавили в операцию INSERT:

INSERT INTO `players_two` (`player_and_games`) VALUES ('{  
    "id": 1,  
    "name": "Sally",  
    "games_played":{    
...
);

После того, как данные были вставлены в таблицу, запустим SHOW COLUMNS в новой таблице, чтобы узнать, как MySQL создал столбцы. Обратите внимание, что поле id теперь — STORED GENERATED и содержит индекс PRIMARY KEY.

SHOW COLUMNS FROM `players_two`;

+---------------------------+-------------+------+-----+---------+-------------------+
| Field                     | Type        | Null | Key | Default | Extra             |
+---------------------------+-------------+------+-----+---------+-------------------+
| id                        | int(11)     | NO   | PRI | NULL    | STORED GENERATED  |
| player_and_games          | json        | NO   |     | NULL    |                   |
| names_virtual             | varchar(20) | NO   | MUL | NULL    | VIRTUAL GENERATED |
| times_virtual             | int(11)     | NO   | MUL | NULL    | VIRTUAL GENERATED |
| tennis_won_virtual        | int(11)     | NO   | MUL | NULL    | VIRTUAL GENERATED |
| tennis_lost_virtual       | int(11)     | NO   | MUL | NULL    | VIRTUAL GENERATED |
| battlefield_level_virtual | int(11)     | NO   | MUL | NULL    | VIRTUAL GENERATED |
+---------------------------+-------------+------+-----+---------+-------------------+

Замечание об использовании PRIMARY KEY с генерируемыми столбцами: MySQL не позволит создавать первичные ключи для генерируемых VIRTUAL столбцов. На самом деле, если не указать STORED в поле id, MySQL выдает следующую ошибку:

ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.  

В то же время, если не устанавливать индекс первичного ключа и попытаться вставить данные, MySQL выдает сообщение об ошибке:

ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.  

Это означает, что у таблицы нет первичного ключа. Поэтому нужно вернуться и пересоздать таблицу, либо удалить столбец id и добавить генерируемый STORED столбец с первичным ключом, например:

ALTER TABLE `players_two` ADD COLUMN `id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED PRIMARY KEY;  

Вывод

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

We have had a look at how MySQL 8 handles JSON

recently, but with all those JSON functions and expressions it is clear that many JSON accesses cannot be fast. To grab data from a JSON column, you will use a lot of $->>field expressions and similar, and without indexes nothing of this will be fast.

JSON cannot be indexed.

But MySQL 8 offers another feature that comes in handy: Generated columns and indexes on those. Let’s look at the parts, step by step, and how to make them work, because they are useful even outside the context of JSON.

An example table

For the following example we are going to define a table t1 with an integer id and two integer data fields, a and b. We will be filling it with random integers up to 999 for the data values:

mysql]> create table t1 (
->   id integer not null primary key auto_increment,
->    a integer,
->    b integer
-> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 ( id, a, b) values (NULL, ceil(rand()*1000), ceil(rand()*1000));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 (id, a, b) select NULL,  ceil(rand()*1000), ceil(rand()*1000) from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
...
mysql> insert into t1 (id, a, b) select NULL,  ceil(rand()*1000), ceil(rand()*1000) from t1;
Query OK, 524288 rows affected (6.83 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.04 sec)

Generated columns

A generated column is a column with values that are calculated from a deterministic expression provided in the column definition. It has the usual name and type, and then a GENERATED ALWAYS AS () term. The parentheses are part of the syntax and cannot be left off. The GENERATED ALWAYS is optional, and we are going to leave it off, because we are lazy.

The column can be VIRTUAL, in which case the expression is evaluated when reading every time a value is needed, or STORED, in which case the value is materialized and stored on write.

It may also contain inline index definitions and a column comment.

VIRTUAL generated columns

So we get our trivial example:

mysql> alter table t1 add column c integer as ( a+b ) virtual;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 limit 3;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  997 |  808 | 1805 |
|  2 |   51 |  831 |  882 |
|  3 |  998 |  499 | 1497 |
+----+------+------+------+
3 rows in set (0.00 sec)

That was fast — the table definition is changed, but because the column is VIRTUAL, no data values need to be changed. Instead, the data is calculated on read access. We could have written our sample read as SELECT id, a, b, a+b AS c FROM t1 LIMIT 3 for the same effect, because that is what happened.

We may even store that statement in a view and then call it, and that’s effectively the same:

mysql> create view v1 as select id, a, b, a+b as c from t1;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v1 limit 3;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  997 |  808 | 1805 |
|  2 |   51 |  831 |  882 |
|  3 |  998 |  499 | 1497 |
+----+------+------+------+
3 rows in set (0.00 sec)

Well, not quite. Let’s explain the same query on t1 and v1 and see what the optimizer has to say:

mysql> explain select * from t1 where c<50G
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1046904
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `kris`.`t1`.`id` AS `id`,`kris`.`t1`.`a` AS `a`,`kris`.`t1`.`b` AS `b`,`kris`.`t1`.`c` AS `c` from `kris`.`t1` where (`kris`.`t1`.`c` < 50)

mysql> explain select * from v1 where c<50G
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1046904
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `kris`.`t1`.`id` AS `id`,`kris`.`t1`.`a` AS `a`,`kris`.`t1`.`b` AS `b`,(`kris`.`t1`.`a` + `kris`.`t1`.`b`) AS `c` from `kris`.`t1` where ((`kris`.`t1`.`a` + `kris`.`t1`.`b`) < 50)

The output differs slightly in two places: the estimate given for filtered is different, and the view “sees” and exposes the definition for c as a+b in the reparsed statement in the “Note” section.

Further down we will also see how the generated column can be indexed, while the statement expression can not — and that in the end what makes the key difference in performance.

STORED generated columns

Let’s flip from VIRTUAL to STORED and see what happens. We drop the old definition of c, and re-add the same one, but with a STORED attribute.

mysql> alter table t1 drop column c, add column c integer as (a+b) stored;
Query OK, 1048576 rows affected (6.27 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

If we looked at the average row length in INFORMATION_SCHEMA.TABLES, we would see it as a bit longer (but as is usual with I_S.TABLES output for small and narrow tables, the values are a bit off).

We also see the ALTER TABLE now takes actual time, proportional to the table size. What happened is that the values for c now get materialized on write, as if we defined a BEFORE INSERT trigger maintaining the values in c.

Trying to write to a generated column fails (except when it doesn’t)

VIRTUALand STORED don’t matter: you can’t write to generated columns:

mysql> update t1 set c = 17 where id = 3;
ERROR 3105 (HY000): The value specified for generated column 'c' in table 't1' is not allowed.

mysql> replace into t1 set id=3, c=17;
ERROR 3105 (HY000): The value specified for generated column 'c' in table 't1' is not allowed.

With one exception:

mysql> update t1 set c=default where id = 3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

So if you aren’t actually writing to c, you are allowed to write to c. That sounds stupid until you define a view on t1 that includes c and is considered updatable — by allowing this construct, it stays updatable, even if it includes c.

Filling in the correct value is not the same as default and does not work:

mysql> select * from t1 limit 1;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  997 |  808 | 1805 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> update t1 set c=1805 where id=1;
ERROR 3105 (HY000): The value specified for generated column 'c' in table 't1' is not allowed.

Caution: CREATE TABLE … AS SELECT vs. generated columns

We already know (I hope) that CREATE TABLE ... AS SELECT is of the devil and should not be used to copy table definitions: It creates a table from the result set of the select statement, which is most definitively not the definition of the original table.

We have seen this fail already with indexes and foreign key definitions, and in case you didn’t, here is what I mean:

mysql> create table sane ( id integer not null primary key auto_increment, t1id integer, foreign key (t1id) references t1(i
d) );
Query OK, 0 rows affected (0.06 sec)

mysql> show create table saneG
       Table: sane
Create Table: CREATE TABLE `sane` (
  `id` int NOT NULL AUTO_INCREMENT,
  `t1id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t1id` (`t1id`),
  CONSTRAINT `sane_ibfk_1` FOREIGN KEY (`t1id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> insert into sane values (1, 1), (2, 2), (3, 3), (4, 4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table broken as select * from sane;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table brokenG
       Table: broken
Create Table: CREATE TABLE `broken` (
  `id` int NOT NULL DEFAULT '0',
  `t1id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

broken is most decidedly not the same table as sane. The definition of broken has been inferred from the format of the result set, which may or may not have the same types as the base table(s). It also has no indexes and no constraints.

The correct way to copy a table definition is CREATE TABLE ... LIKE ... and then move the data with INSERT ... SELECT .... You still have to move the foreign key constraints manually, though:

mysql> create table unbroken like sane;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into unbroken select * from sane;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table unbrokenG
       Table: unbroken
Create Table: CREATE TABLE `unbroken` (
  `id` int NOT NULL AUTO_INCREMENT,
  `t1id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t1id` (`t1id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

And here is how it works with generated columns:

mysql> create table t2 as select * from t1;
Query OK, 1048576 rows affected (14.89 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> show create table t2G
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL DEFAULT '0',
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

CREATE TABLE ... AS SELECT ... defined a table from the result set of the select clause, and the fact that c is generated is completely lost. So we now have a normal 4-column table.

So, how about CREATE TABLE ... LIKE ...?

mysql> drop table t2;
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.10 sec)

mysql> show create table t2G
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int GENERATED ALWAYS AS ((`a` + `b`)) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Yes! Success! Ok, now the data:

mysql> insert into t2 select * from t1;
ERROR 3105 (HY000): The value specified for generated column 'c' in table 't2' is not allowed.

Oh, right.

mysql> insert into t2 select id, a, b from t1;
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Aww, yes. Okay, the full monty:

mysql> insert into t2 (id, a, b) select id, a, b from t1;

Finally.

Ok, copying data between tables with generated columns requires a bit more engineering than a mindless INSERT ... SELECT *. The rules are not unexpected, we have explored them right above, still…

The wrong data type

Ok, let’s get a bit mean. What happens when we define c tinyint as (a+b) virtual so that the values exceed the range possible in a signed single bit value?

mysql> select * from t1 limit 3;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  997 |  808 | 1805 |
|  2 |   51 |  831 |  882 |
|  3 |  998 |  499 | 1497 |
+----+------+------+------+
3 rows in set (0.00 sec)

mysql> alter table t1 drop column c, add column c tinyint as (a+b) virtual;
ERROR 1264 (22003): Out of range value for column 'c' at row 1

Oh, they are on to us!?!? Are they?

They are not when we do it in two steps:

mysql> alter table t1 drop column c;
Query OK, 0 rows affected (9.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 add column c tinyint as (a+b) virtual;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 limit 3;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  997 |  808 |  127 |
|  2 |   51 |  831 |  127 |
|  3 |  998 |  499 |  127 |
+----+------+------+------+
3 rows in set (0.00 sec)

It clips the values according to the rules that MySQL always had, and that ate so much data.

Now, let’s CREATE TABLE ... AS SELECT again:

mysql> drop table broken;
Query OK, 0 rows affected (0.07 sec)

mysql> create table broken as select * from t1;
ERROR 1264 (22003): Out of range value for column 'c' at row 2
Error (Code 1264): Out of range value for column 'c' at row 2
Error (Code 1030): Got error 1 - 'Operation not permitted' from storage engine

Wow. No less than three error messages. At least they mention the column c and the word “range”, so we kind of can have an idea what goes on. Still, this is only medium helpful and initially confusing.

What happens, and why?

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode = "";
Query OK, 0 rows affected (0.00 sec)

mysql> create table broken as select * from t1;
...
Warning (Code 1264): Out of range value for column 'c' at row 1028
Warning (Code 1264): Out of range value for column 'c' at row 1029
Warning (Code 1264): Out of range value for column 'c' at row 1030

SQL_MODE helpfully detected the problem and prevented data loss. As usual, SQL_MODE was as useless as it was helpful — while it prevented data loss, it did not directly point us into the right direction with its error messages.

By turning off SQL_MODE we get the clipped values copied and a bunch of warnings that everybody ignores all the time, anyway, so I guess it’s an improvement.

Allowed and disallowed functions

For generated columns to work it is a requirement that the functions are deterministic, idempotent and side effect free. All user defined functions and stored functions are disallowed, and the usual suspects from the set of builtins are also out:

mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (sleep(2)));
ERROR 3763 (HY000): Expression of generated column 'c' contains a disallowed function: sleep.
mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (uuid()));
ERROR 3763 (HY000): Expression of generated column 'c' contains a disallowed function: uuid.
mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (rand()));
ERROR 3763 (HY000): Expression of generated column 'c' contains a disallowed function: rand.
mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (now()));
ERROR 3763 (HY000): Expression of generated column 'c' contains a disallowed function: now.
mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (connection_id()));
ERROR 3763 (HY000): Expression of generated column 'c' contains a disallowed function: connection_id.
mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (last_insert_id()));
ERROR 3763 (HY000): Expression of generated column 'c' contains a disallowed function: last_insert_id.


mysql> set @c := 1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (@c));
ERROR 3772 (HY000): Default value expression of column 'c' cannot refer user or system variables.

mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (id));
ERROR 3109 (HY000): Generated column 'c' cannot refer to auto-increment column.

mysql> create table testme (id integer not null primary key auto_increment, a integer, b integer, c integer as (a));
Query OK, 0 rows affected (0.09 sec)
mysql> alter table testme change column a x integer;
ERROR 3108 (HY000): Column 'a' has a generated column dependency.
mysql> alter table testme drop column c, change column a x integer, add column c integer as (x);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

From the final example above we learn that it is also impossible to change the existing definition of any column that is used by a generated column definition. We need to drop the generated column, change the definition of the base columns and then recreate the generated column.

For VIRTUAL columns that is cheap, for STORED — less so.

Secondary indexes and generated columns

So far, so nice. Now let’s cash in on this: Indexes, we have them. At least secondary indexes:

mysql> create table wtf ( b integer not null,  id integer as (b) not null primary key);
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.

mysql> show create table t1G
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1376221 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> alter table t1 add index(c);
Query OK, 0 rows affected (5.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

As expected, adding the index takes time, even if the column c is VIRTUAL: For an index we extract the indexed values from the table, sort them and store them together with pointers to the base row in the (secondary) index tree. In InnoDB, the pointer to the base row is always the primary key, so what we get in the index is actually pairs of (c, id).

We can prove that:

1. Queries for c can be answered from the index. The index is called covering, it saves us chasing the row pointer and access to the actual row. In an EXPLAIN we see this being indicated with using index.
2. Queries for c and id should also be covering: the queried values are all present in the index so that going to the base row is unnecessary.
3. Querying for c and a is not covering, so the using index should be gone.

And indeed:

mysql> explain select c from t1 where c < 50G
...
possible_keys: c
          key: c
...
         rows: 1257
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `kris`.`t1`.`c` AS `c` from `kris`.`t1` where (`kris`.`t1`.`c` < 50)

mysql> explain select c, id from t1 where c < 50G
          key: c
...
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `kris`.`t1`.`c` AS `c`,`kris`.`t1`.`id` AS `id` from `kris`.`t1` where (`kris`.`t1`.`c` < 50)
mysql> explain select c, a from t1 where c < 50G
...
possible_keys: c
          key: c
...
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `kris`.`t1`.`id` AS `id`,`kris`.`t1`.`a` AS `a`,`kris`.`t1`.`b` AS `b`,`kris`.`t1`.`c` AS `c` from `kris`.`t1` where (`kris`.`t1`.`c` < 50)

As predicted, the final query for c, a cannot be covering and is missing the using index notice in the Extra column. This is still a good query: it is considering and using the index on c — the index alone is just not sufficient to resolve the query.

This should give us an idea about how to design:

In almost all cases STORED columns will not be paying off. They use disk space, and still need to evaluate the expression at least once for storage. If indexed, they will use disk space in the index a second time — the column is actually materialized twice, in the table in primary key order and the index in index order.

STORED generated columns make sense only if the expression is complicated and slow to calculate. But with the set of functions available to us that is hardly going to be the case, ever. So unless the expression is being evaluated really often the cost for the storage is not ever amortized.

Even then, for generated columns STORED and VIRTUAL, many queries can probably be answered leveraging an index on the generated column so that we might try to get away with VIRTUAL columns all the time.

Generated columns and the Optimizer

The optimizer is aware of the generated column definitions, and can leverage them, as long as they match:

mysql> show create table t1G
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1376221 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> explain select a+b from t1 where a+b<50G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: c
          key: c
      key_len: 5
          ref: NULL
         rows: 1257
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select (`kris`.`t1`.`a` + `kris`.`t1`.`b`) AS `a+b` from `kris`.`t1` where (`kris`.`t1`.`c` < 50)

The optimizer is still the MySQL optimizer we all love to hate, so you have to be pretty literal for the match:

mysql> explain select b+a from t1 where b+a<50G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1046422
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select (`kris`.`t1`.`b` + `kris`.`t1`.`a`) AS `b+a` from `kris`.`t1` where ((`kris`.`t1`.`b` + `kris`.`t1`.`a`) < 50)