Multiply Two Column and initialize it into Another Column while creating MySQL Table

Question

I want to create a MySQL table that has four column For example, id,piece,price,totalprice

When data inserted into id,piece,price column totalprice must be initialized from (piece * price)

Insert data

id   piece   price
101   2       10

Result

id    piece price totalprice
101   2      10     20

I can create a table with four column but i can't automatically initialized into totalprice column using the piece and price column value.

How to do that?

[CREATE TABLE and Generated Columns](https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html) — Akina, Jan 07 '23 at 07:34
Trigger is also an option as is calculating on the insert..select — P.Salmon, Jan 07 '23 at 07:37

score 0 · Accepted Answer · answered Jan 07 '23 at 09:16

You can do it as follows :

CREATE TABLE orders (
  id int not null AUTO_INCREMENT primary key,
  piece DOUBLE,
  price DOUBLE,
  totalPrice DOUBLE AS (piece * price)
);

INSERT INTO orders (piece, price) VALUES(1,1),(3,4),(6,8);

check it here : https://dbfiddle.uk/7wcEDixV

Multiply Two Column and initialize it into Another Column while creating MySQL Table

1 Answers1