How Can I Create a Calculated Column Based on Another Column in MySQL?

Deriving New Data in MySQL: Calculated Columns

Database management often requires generating additional data based on existing information. MySQL offers a powerful solution: calculated columns. These columns automatically compute their values from other columns within the same table, eliminating the need for manual calculations.

Leveraging MySQL's Generated Columns

MySQL versions 5.7.6 and later support Generated Columns, a feature that dynamically calculates column values based on other columns. This functionality resembles a VIEW, but with the key difference that generated columns are integral parts of the table's structure.

Virtual vs. Stored: Choosing the Right Approach

Generated columns come in two varieties:

• Virtual Columns: Compute values on demand, only when a row is accessed.
• Stored Columns: Calculate values upon insertion or update of a row, and store the result.

A crucial distinction is that stored generated columns are indexable, unlike virtual columns.

Example: Calculating Half the Value

Let's illustrate with a practical example. Suppose we need a column representing half the value of an existing "value" column. Using stored generated columns:

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

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

This creates my_table with an id, a value, and a half_value column. half_value is automatically populated and updated whenever the value column changes. The STORED keyword ensures the calculated value is persistently stored.

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