Can MySQL Automatically Calculate a Table Column Based on Another?

Compute columns based on other columns

Question:

Can a database table automatically calculate one column based on another column, similar to a view, but integrated into the table itself?

Answer:

In MySQL 5.7.6 and later, generated columns provide this functionality.

Generated column type:

• Virtual (default): Calculated on demand when reading records.
• Storage: Calculated when a new record is written or updated.

Implement calculated columns:

In the example provided, we want to create a calculated column called "calculated" with half the value of the "value" column. Using stored generated columns, we can implement it as follows:

<code class="language-sql">CREATE TABLE order_details (
    id INT PRIMARY KEY,
    value INT,
    calculated AS (value / 2) STORED
);

INSERT INTO order_details (id, value) VALUES
    (1, 6),
    (2, 70);</code>

The calculated column will be automatically populated with the required values ​​(3 for > ... The original text here is missing and should be completed).

The above is the detailed content of Can MySQL Automatically Calculate a Table Column Based on Another?. For more information, please follow other related articles on the PHP Chinese website!