生成的列在數(shù)據(jù)庫中用於自動(dòng)計(jì)算基於其他列表達(dá)式的值，簡(jiǎn)化查詢並提高性能。它們避免重複複雜計(jì)算，例如通過unit_price和quantity自動(dòng)生成total_price；可提升查詢時(shí)的效率，如預(yù)計(jì)算order_year以加速按年過濾；確保多應(yīng)用間邏輯一致性，如統(tǒng)一計(jì)算稅後價(jià)格；分為虛擬（讀取時(shí)計(jì)算）和存儲(chǔ)（寫入時(shí)計(jì)算）兩種類型，應(yīng)根據(jù)用例選擇。

Generated Columns in databases are columns whose values are automatically computed based on expressions involving other columns in the same table. These columns aren't manually written by users but are derived from existing data, making them both powerful and convenient for certain use cases.

Simplifying Queries with Derived Data

One of the main reasons to use Generated Columns is to avoid repeating complex calculations in queries. For example, if you have a table storing order details including unit_price and quantity , you can create a generated column called total_price that automatically multiplies those two values.

This way, whenever you query the table, the total price is already available without needing to calculate it on the fly in every query. This improves readability and reduces processing overhead at query time.

Examples:

• Calculating totals or subtotals
• Concatenating names (eg, full name from first and last name)
• Extracting parts of dates or strings

Improving Performance by Precomputing Values

If your application frequently runs queries that involve expressions or transformations, using a stored generated column can significantly improve performance. Since the value is precomputed and stored (in the case of stored generated columns), the database doesn't need to recalculate the expression every time it's accessed.

For instance, if you often search based on the year extracted from a date column ( order_date ), you could generate a column like order_year and index it. This makes filtering by year much faster than extracting it during each query.

Types of performance-focused uses:

• Date or time extractions
• String formatting for searches
• Hashes or keys derived from other data

Keep in mind: There are two types of generated columns — virtual (computed on read) and stored (computed on write). Depending on your use case, one might be more appropriate than the other.

Enforcing Consistency Across Applications

When multiple applications or services access the same database, having logic defined directly in the schema helps ensure consistency. Instead of relying on each application to compute the same derived value correctly, the database handles it automatically.

For example, if you're calculating tax-inclusive pricing, defining a generated column ensures all systems get the same result without duplicating logic across codebases.

This also simplifies maintenance — if the calculation needs to change, you only update the column definition once in the database rather than modifying every service that performs the calculation.

They're not always needed, but when you want to simplify queries, boost performance, or enforce logic at the database level, Generated Columns can be a useful tool. Just make sure to choose between virtual and stored wisely, and keep expressions simple and deterministic.基本上就這些。

以上是什麼是生成的列，它們的用例是什麼？的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！