What is Generated Columns in MySQL

MySQL 5.7 introduced a new feature called generated column. It is called generated column because the data in this column is computed based on a predefined expression or from other columns.

The syntax is:

colun_name data_type  [ GENERATED ALWAYS ]   AS   ( ) 
[ VIRTUAL|STORED ] [ UNIQUE [KEY] ]   [ [PRIMARY] KEY ]   [ NOT NULL ]   [ COMMENT ]

Example 

CREATE TABLE `letter_lowercase` (
  `id` int(11) NOT NULL,
  `address1` varchar(45) DEFAULT NULL,
  `address2` varchar(45) DEFAULT NULL,
 `full_address` varchar(100) GENERATED ALWAYS AS (CONCAT(address1,' ',address2)))
 ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

Now we insert a record on table.

INSERT INTO `letters_lowercase`(`id`, `address1`, `address2`) VALUES (1,'90/20', 'Hyderabad')

Now read the data from table

SELECT * FROM `letters_lowercase`

Result-

1     90/20     Hyderabad     90/20 Hyderabad

The values in the full_address column are computed on the fly when you query data from the contacts table

There are two kinds of Generated Columns:

1. Virtual (default): Virtual means that the column will be calculated on the fly when a record is read from a table.  A virtual column takes no storage.

2. Stored: Stored means that the column will be calculated when a new record is written in the table, and after that it will be treated as a regular field. A stored column does require storage space and can be indexed.

Both types can have NOT NULL restrictions, but only a stored Generated Column can be be a part of an index. 

There are still a few limitations on Generated Columns:

1. Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

2. Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.

3. Generated column cannot refer to auto-increment column.

4. A generated column definition can refer to other generated columns, but only those occurring earlier in the table definition. A generated column definition can refer to any base (nongenerated) column in the table whether its definition occurs earlier or later.