What is MySQL GENERATED COLUMN and how to use it when creating a table?

Basically generated columns are a feature that can be used in a CREATE TABLE or ALTER TABLE statement and are a way to store data without going through it in SQL The INSERT or UPDATE clause actually sends the data. This feature was added in MySQL 5.7. The generated columns work within table fields. The syntax is as follows -

Syntax

column_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]]

Here, first specify the column name and its data type.

• Then add the GENERATED ALWAYS clause to indicate that the column is a generated column.
• Then, use the appropriate option to indicate the type of generated column - VIRTUAL or STORED. By default, MySQL uses VIRTUAL if you do not explicitly specify the type of the generated column. After

, specify the expression within curly braces following the AS keyword. Expressions can contain literals, built-in functions without parameters, operators, or references to any column in the same table. If a function is used, it must be scalar and deterministic.

Finally, if you store the generated column, you can define a unique constraint on it.

Example

In this example, we create a table named employee_data which contains the employee’s details along with the generated columns as shown below -

mysql> Create table employee_data(ID INT AUTO_INCREMENT PRIMARY KEY, First_name VARCHAR(50) NOT NULL, Last_name VARCHAR(50) NOT NULL, FULL_NAME VARCHAR(90) GENERATED ALWAYS AS(CONCAT(First_name,' ',Last_name)));
Query OK, 0 rows affected (0.55 sec)

mysql> DESCRIBE employee_data;
+------------+-------------+------+-----+---------+-------------------+
| Field      | Type        | Null | Key | Default | Extra             |
+------------+-------------+------+-----+---------+-------------------+
| ID         | int(11)     | NO   | PRI | NULL    | auto_increment    |
| First_name | varchar(50) | NO   |     | NULL    |                   |
| Last_name  | varchar(50) | NO   |     | NULL    |                   |
| FULL_NAME  | varchar(90) | YES  |     | NULL    | VIRTUAL GENERATED |
+------------+-------------+------+-----+---------+-------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO employee_data(first_name, Last_name) values('Yashpal','Sharma');
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO employee_data(first_name, Last_name) values('Krishan','Kumar');
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO employee_data(first_name, Last_name) values('Rakesh','Arora');
Query OK, 1 row affected (0.08 sec)

mysql> Select * from employee_data;
+----+------------+-----------+----------------+
| ID | First_name | Last_name | FULL_NAME      |
+----+------------+-----------+----------------+
| 1  | Yashpal    | Sharma    | Yashpal Sharma |
| 2  | Krishan    | Kumar     | Krishan Kumar  |
| 3  | Rakesh     | Arora     | Rakesh Arora   |
+----+------------+-----------+----------------+
3 rows in set (0.00 sec)

The above is the detailed content of What is MySQL GENERATED COLUMN and how to use it when creating a table?. For more information, please follow other related articles on the PHP Chinese website!