top of page

MySQL JSON datatype with Storing values in generated columns

This blog is the continuation of previous blog “MySQL JSON datatype with generated virtual columns and indexing” and put up information how to use STORED keyword when creating virtual column from JSON datatype elements.

Using the STORED keyword when set up a created column is normally not favored since you're mostly storing values two times in a table: once in the JSON document and yet again in the STORED column. However, there are three situations when MySQL suggests you use a STORED created column:

  1. Indexing primary keys,

  2. Needed a full text/R-tree index

  3. A column that is scanned a lot.

The syntax for adding a STORED created column is the same as making VIRTUAL created columns, except we add the keyword STORED after the look like:

To demonstration you how to use STORED, let's create additional table that will take the IDs from our JSON documents and store them in a STORED created column. Then we will set the PRIMARY KEY to the id column. At the similar time, we will generate all of our VIRTUAL columns and set up indexes for those columns. This is done completely within the original table creation:

Following, we will insert the same dataset into players_two but we will eliminate the ID that we before added to the INSERT action:

After our data has been inserted into the table, we can run SHOW COLUMNS on the new table to see how MySQL has created our columns. Notice that the id field now has STORED GENERATED alongside it with a PRIMARY KEY index.

One item to note about making a PRIMARY KEY with created columns is that MySQL will not allow you to generate primary keys on VIRTUAL created columns. In fact, if we left out STORED on the id field, MySQL will throw the subsequent error:

At the same time, if you authorization off the primary key index and effort to insert a data, MySQL will throw an error asserting:

This means that you don't have a PK (primary key) on your table. Therefore, you must go back and moreover drop and reconstruct your table, or drop the id column and add the column as a STORED created column with a PRIMARY KEY like:


By means of created columns will allow you to put indexes on detailed elements of your JSON documents without indexing them in their whole, and offer you with the flexibility of also storing or not storing values within a table.

11 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa


bottom of page