How Can I Efficiently Calculate a Column Based on Other Columns in MySQL?

How to efficiently calculate columns based on other columns in MySQL

MySQL 5.7.6 and above introduces generated columns (Generated Columns), which is an efficient way to automatically calculate the value of new columns based on existing columns in the same table. Unlike views, generated columns are part of the table definition and are automatically updated when the source columns change.

Virtual generated columns and stored generated columns

Generated columns can be virtual or stored:

• Virtual: Calculated dynamically when reading table records.
• Stored: Calculated when a table record is written or updated.

In this case, storing a generated column is more appropriate because the calculated value needs to be available immediately when a new record is inserted or an existing record is updated.

Implementation of generated columns using storage

Suppose a table has two columns: price and quantity, and we want to add a new column amount whose value is the product of price and quantity. Storage generated columns can be created using the following SQL statement:

<code class="language-sql">CREATE TABLE order_details (
  price DOUBLE,
  quantity INT,
  amount DOUBLE AS (price * quantity) STORED
);</code>

Once the column is created, amount will be automatically calculated and stored for each row in the table. Any changes to price or quantity will trigger an update of amount.

Example containing table data

After inserting some sample data:

<code class="language-sql">INSERT INTO order_details (price, quantity) VALUES(100,1),(300,4),(60,8);</code>

The table will look like this:

id	price	quantity	amount
1	100	1	100
2	300	4	1200
3	60	8	480

amount Columns will automatically populate and update as needed, allowing efficient access to calculated values.

The above is the detailed content of How Can I Efficiently Calculate a Column Based on Other Columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!