{"id":4982,"date":"2013-01-14T02:30:21","date_gmt":"2013-01-14T09:30:21","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4982"},"modified":"2024-01-04T06:56:51","modified_gmt":"2024-01-04T13:56:51","slug":"mysql-generated-columns","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-generated-columns\/","title":{"rendered":"How to Use the MySQL Generated Columns"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL generated columns to store data computed from an expression or other columns.<\/p>\n\n\n\n

Introduction to MySQL Generated Column<\/h2>\n\n\n\n

When you create a new table, you specify the table columns in the CREATE TABLE<\/a><\/code> statement. Then, you use the INSERT<\/a><\/code>, UPDATE<\/code><\/a>, and DELETE<\/a><\/code> statements to modify directly the data in the table columns.<\/p>\n\n\n\n

MySQL 5.7 introduced a new feature called the generated<\/em> column. Columns are generated because the data in these columns are computed based on predefined expressions.<\/p>\n\n\n\n

For example, you have the contacts<\/code> with the following structure:<\/p>\n\n\n

DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> contacts;\n\nCREATE<\/span> TABLE<\/span> contacts (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    first_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    last_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    email VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    id<\/span>, \n    CONCAT<\/span>(first_name, ' '<\/span>, last_name), \n    email\nFROM<\/span>\n    contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This is not the most beautiful query yet.<\/p>\n\n\n\n
By using a Generated Column, you can recreate the contacts<\/code> table as follows:<\/p>\n\n\n
DROP TABLE IF<\/span> EXISTS contacts;\n\nCREATE TABLE contacts (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    first_name VARCHAR(50<\/span>) NOT NULL<\/span>,\n    last_name VARCHAR(50<\/span>) NOT NULL<\/span>,\n    fullname varchar(101<\/span>) GENERATED ALWAYS AS<\/span> (CONCAT(first_name,' '<\/span>,last_name)),\n    email VARCHAR(100<\/span>) NOT NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The GENERATED ALWAYS as (expression)<\/code> is the syntax for creating a generated column.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> contacts(first_name,last_name, email)\nVALUES<\/span>('john'<\/span>,'doe'<\/span>,'john.doe@mysqltutorial.org'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span>\n    contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The values in the fullname<\/code> column are computed on the fly when you query data from the contacts<\/code> table.<\/p>\n\n\n\n
MySQL provides two types of generated columns: stored and virtual. The virtual columns are calculated on the fly each time data is read whereas the stored columns are calculated and stored physically when the data is updated.<\/p>\n\n\n\n
Based on this definition, the  fullname<\/code> column that in the example above is a virtual column.<\/p>\n\n\n\n
Syntax for MySQL Generated Column<\/h2>\n\n\n\n
The syntax for defining a generated column is as follows:<\/p>\n\n\n
column_name data_type [GENERATED ALWAYS] AS (expression)\n   [VIRTUAL | STORED] [UNIQUE [KEY]]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
First, specify the column name and its data type.<\/p>\n\n\n\n
Next, add the GENERATED ALWAYS<\/code> clause to indicate that the column is a generated column.<\/p>\n\n\n\n
Then, indicate the type of the generated column by using the corresponding option: VIRTUAL<\/code> or STORED<\/code>. By default, MySQL uses VIRTUAL<\/code> if you don’t specify explicitly the type of the generated column.<\/p>\n\n\n\n
After that, specify the expression within the braces after the AS<\/code> keyword. The expression can contain literals, and built-in functions with no parameters, operators, or references to any column within the same table. If you use a function, it must be scalar and deterministic.<\/p>\n\n\n\n
MySQL stored column example<\/h2>\n\n\n\n
Let\u2019s look at the products<\/code> table in the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The data from quantityInStock<\/code> and buyPrice<\/code> columns allow us to calculate the stock’s value per SKU using the following expression:<\/p>\n\n\n
quantityInStock * buyPrice<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
ALTER<\/span> TABLE<\/span> products\nADD<\/span> COLUMN<\/span> stockValue DEC<\/span>(10<\/span>,2<\/span>)\nGENERATED<\/span> ALWAYS<\/span> AS<\/span> (buyprice*quantityinstock) STORED<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Typically, the ALTER TABLE<\/code> statement requires a full table rebuild, therefore, it is time-consuming if you change the big tables. However, this is not the case for the virtual column.<\/p>\n\n\n\n
Now, we can query the stock value directly from the products<\/code> table.<\/p>\n\n\n
SELECT<\/span> \n    productName, \n    ROUND<\/span>(stockValue, 2<\/span>) stock_value\nFROM<\/span>\n    products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use a MySQL Generated column to store data computed from an expression or other columns.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
However, we can add a stored generated column named stock_value<\/code> to the products<\/code> table using the following ALTER TABLE ...ADD COLUMN<\/code><\/a> statement:<\/p>\n\n\n
Finally, if the generated column is stored, you can define a unique constraint<\/a> for it.<\/p>\n\n\n\n
Now, you can query data<\/a> from the contacts<\/code> table.<\/p>\n\n\n
To test the fullname<\/code> column, you insert<\/a> a row into the contacts<\/code> table.<\/p>\n\n\n
To get the full name of a contact, you use the CONCAT()<\/a><\/code> function as follows:<\/p>\n\n\n