7

Generated Columns

The MySQL server cannot index JSON columns. Generally, you want indexes to be as small as practicable for speed, and trying to use up to a gigabyte of unstructured data would not be efficient. This situation is similar to that of other data BLOBs. Data from the JSON column, however, can be extracted into a generated column, and that column can be indexed.

There are two types of generated columns. The virtual generated column is evaluated when the column is read but before any existing triggers are fired for that column. The stored generated column is evaluated and stored when data is either inserted or updated. The default is virtual generated, but both types can be used together in a table.

Virtual generated columns cannot contain subqueries, parameters, variables, stored functions, or user-defined functions. You cannot use the AUTO_INCREMENT attribute in a virtual generated column or base a virtual generated column on a column that uses AUTO_INCREMENT. Foreign-key constraints on a stored generated column cannot use ON UPDATE CASCADE, ON DELETE SET NULL, ON UPDATE SET NULL, ON DELETE SET DEFAULT, or ON UPDATE SET DEFAULT. Also, foreign-key constraints cannot reference a virtual generated column. Several other constraints are detailed in the MySQL user manual that are worth reading at a later time but do not fit in this book.

Using Generated Columns

The keyword AS denotes a generated column. Example 7-1 calculates the taxable amount of an item given the item price and multiplies it by the tax rate. The taxRate is the percentage of the itemPrice to be taxed. The server calculates the taxAmount.

Example 7-1 Using generated columns to calculate values

Note that only the itemPrice and taxRate are entered into the table and the server calculates the taxAmount column.

Columns Generated from JSON

The world_x countryinfo table has a generated column and is a prime example of the MySQL document store table format. The InnoDB storage engine requires a PRIMARY KEY and will pick one, often a poor one, if it’s not specified. MySQL will create a column named _id when a collection is created and denote it as the primary key. If there is no _id data in the JSON document column named doc, the column will hold a NULL.

Example 7-2 How the MySQL document store creates collections

A simple DESCRIBE table will show the layout of the table and that there is a stored generated column, but it won’t show the actual code for the generation. More details on the Document Store’s table are available from SHOW CREATE TABLE than from the DESCRIBE table.

Example 7-3 In this case, the workings of the GENERATED column are shown.

A SHOW CREATE TABLE provides the details on the generation of the created table. It is easy to see that the _id column is created from JSON column doc’s key/value pair of _id. Note the UTF8 casting of this field.

Any other JSON key (or keys in combination; consult the MySQL manual regarding composite indexes) can be used in a generated column. If you are regularly extracting one key/value pair, it may be faster to use a generated column and index than a generated column to search via SQL.

Example 7-4 Using ALTER TABLE to add a generated column for PopulationCountry

It is better to use the STORED GENERATED column option for building indexes. The VIRTUAL GENERATED type is not stored and must be computed at access time, which is a lot of work. With a STORED GENERATED column, the value is materialized in a column that is stored when the data is written. If the structure of the countryinfo table is examined after Example 7-3, you can see that the new column is virtual generated and not stored generated.

Example 7-5 The description of countryinfo shows the PopulationCountry column setup from Example 7-4, which is a VIRTUAL GENERATED column, which is not desired.

Luckily, it is easy to remove the new columns using ALTER TABLE countryinfo DROP COLUMN PopulationCountry and then reissue the command to create the generated column, but this time with the keyword STORED appended. Checking the description shows that the new PopulationCountry column is indeed a STORED GENERATED column.

Example 7-6 The description of countryinfo now shows the desired STORED GENERATED PopulationCountry column.

One more step is needed to have a SQL usable index on the new column, which will be something along the lines of CREATE INDEX Population_Index on countryinfo (PopulationCountry); following an index naming convention of your choosing.

Generated Columns: Common Errors

When adding a generated column, the type definition is important. Under-sizing the length for a type can lead to some issues.

Example 7-7 The VARCHAR(20) column was too small for the data when trying to set up a STORED column. The VIRTUAL column, however, was able to be created.

The data will need to be studied to see if the virtual generated column has enough useful data after truncation to be useful. It would be useful to examine the raw data to determine exactly how wide the column needs to be to fit all of it in. But on the other hand, only the first several characters may be of interest.

Example 7-8 Examining the data for the generated column to make sure it is of adequate length. And for this record, it is.

The next step would be to use CREATE INDEX. But there is another problem—or an old problem has returned.

Example 7-9 Attempting to create an index on the virtual generated column returns us to the data that’s too long for the column issue.

MySQL 5.7 introduced the WITH VALIDATION clause to ALTER TABLE. It is used to make sure the calculated data is not out of range for the desired column. Using this, combined with trimming the data down to the desired width, provides a way to generate the column into something that can be indexed.

Example 7-10 Using WITH VALIDATION in the ALTER TABLE statement provides a way to create an easy-to-index column.