How Can I Automatically Calculate a Column Based on Another Column in MySQL?

Automatically calculate a column based on another column in MySQL

In the world of database programming, tables are the cornerstone of data storage, but sometimes it is necessary to derive additional information from existing columns. This is where computed columns come in, providing a way to create new columns whose values ​​are automatically updated based on another column.

Database Settings

Consider the following form:

<code>id | value
--------------
1     6
2     70</code>

Problem Statement

The task is to add a column called "calculated" which is half of the "value" column. This calculated column should automatically update when the "value" column changes.

Solution: Generate columns

MySQL 5.7.6 and later introduces a feature called "generated columns". These columns allow dynamic calculations based on existing columns, similar to views, but as an integral part of the table.

There are two types of generated columns:

• Virtual (default): Calculated on the fly when reading records from the table.
• Storage: Calculated when a new record is inserted or updated in the table.

Achievement

In this example we will use a stored generated column. Here is the SQL statement to add it:

<code>ALTER TABLE table_name ADD COLUMN calculated AS (value / 2) STORED;</code>

This will create a new column called "calculated" with half the value of each row's "value" column.

Advantages and Notes

Generating columns has the following advantages:

• Auto-update: Calculated columns automatically update when the source column changes, ensuring the value is always accurate.
• Indexable: Stored generated columns can be used as part of the index, improving query performance.
• NOT NULL restriction: Both virtual and stored generated columns can have NOT NULL restriction, ensuring data integrity.

Example

After adding the calculated column, the table will look like this:

<code>id | value | calculated
-----------------------
1     6       3
2     70      35</code>

Conclusion

Generated columns provide a powerful way to add calculated columns to a table. By leveraging the "stored" option, it ensures that calculated columns are automatically updated and can be indexed, providing greater flexibility and efficiency in data management.

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