How can we have multiple virtuals GENERATED COLUMNS in MySQL table with CREATE TABLE statement?

It is quite possible to add multiple virtual generated columns in a MySQL table. It can be illustrated with the following example as follows −

Example

mysql> Create table profit(cost int, price int, profit int AS (price-cost), price_revised int AS (price-2));
Query OK, 0 rows affected (0.73 sec)

mysql> Describe profit;
+---------------+---------+------+-----+---------+-------------------+
| Field         | Type    | Null | Key | Default | Extra             |
+---------------+---------+------+-----+---------+-------------------+
| cost          | int(11) | YES  |     | NULL    |                   |
| price         | int(11) | YES  |     | NULL    |                   |
| profit        | int(11) | YES  |     | NULL    | VIRTUAL GENERATED |
| price_revised | int(11) | YES  |     | NULL    | VIRTUAL GENERATED |
+---------------+---------+------+-----+---------+-------------------+
4 rows in set (0.00 sec)

mysql> Insert into profit(Cost, Price) values(100,110);
Query OK, 1 row affected (0.04 sec)

mysql> Insert into profit(Cost, Price) values(200,220);
Query OK, 1 row affected (0.04 sec)

mysql> Select * from profit;
+------+-------+--------+---------------+
| cost | price | profit | price_revised |
+------+-------+--------+---------------+
| 100  | 110   | 10     | 108           |
| 200  | 220   | 20     | 218           |
+------+-------+--------+---------------+
2 rows in set (0.00 sec)

Paul Richard

Published on 22-Feb-2018 12:07:58

• Related Questions & Answers
• How we have multiple stored GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
• How can we alter table to add MySQL virtual GENERATED COLUMNS?
• How can we alter table to add MySQL stored GENERATED COLUMNS?
• How to create conditions in a MySQL table with multiple columns?
• How can we add multiple columns, with single command, to an existing MySQL table?
• How can we set PRIMARY KEY on multiple columns of a MySQL table?
• SHOW TABLE statement with multiple LIKE values in MySQL?
• How can I see the CREATE TABLE statement of an existing MySQL table?
• How can we set PRIMARY KEY on multiple columns of an existing MySQL table?
• How can we delete multiple rows from a MySQL table?
• Update multiple values in a table with MySQL IF Statement
• Create a Pivot Table with multiple columns – Python Pandas
• How can we add columns with default values to an existing MySQL table?
• How can we update columns values on multiple rows with a single MySQL UPDATE statement?
• How can we get more details about columns of an existing table than return by MySQL SHOW COLUMNS statement?