Adding a Stored Generated Column in MySQL 5.7+

Desired outcome

Create a column whose value is always computed from other columns in the same row, so that applications do not need to update it manually. For example, a full_name column that stores CONCAT(first_name, ' ', last_name) for each employee.

Prerequisites

• MySQL server version 5.7 or later (generated columns were introduced in 5.7).
• The MySQL account used must have the ALTER privilege on the target table.
• Enough free disk space for the column if you choose the STORED variant (the expression result is persisted).
• A recent backup of the table or database is recommended before altering the schema.

Procedure

1. Connect to the MySQL server with a client that can run DDL statements (e.g., the mysql command‑line tool).
2. Run the ALTER TABLE statement to add the stored generated column. Replace placeholders with your actual table and column names:

   ALTER TABLE employees
     ADD COLUMN full_name VARCHAR(100)
       GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED;

3. Confirm that the column exists and that its generation expression is recorded:

SHOW COLUMNS FROM employees LIKE 'full_name';

Or query the information schema:

SELECT COLUMN_NAME, GENERATION_EXPRESSION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'employees'
  AND COLUMN_NAME = 'full_name';

Verification

After the column is added, verify that its value matches the expression for existing rows and for new inserts/updates.

1. Run a comparison query that should return zero rows if the column is correct:

   SELECT full_name,
          CONCAT(first_name, ' ', last_name) AS computed
   FROM employees
   WHERE full_name <> CONCAT(first_name, ' ', last_name)
      OR (full_name IS NULL AND CONCAT(first_name, ' ', last_name) IS NOT NULL)
      OR (full_name IS NOT NULL AND CONCAT(first_name, ' ', last_name) IS NULL);

2. Insert a test row and check that the generated column is filled automatically:

   INSERT INTO employees (first_name, last_name)
   VALUES ('Ana', 'Martinez');
   
   SELECT first_name, last_name, full_name
   FROM employees
   WHERE first_name = 'Ana' AND last_name = 'Martinez';

3. Update a source column and confirm the generated column changes accordingly:

   UPDATE employees
   SET last_name = 'Smith'
   WHERE first_name = 'John' AND last_name = 'Doe';
   
   SELECT first_name, last_name, full_name
   FROM employees
   WHERE first_name = 'John' AND last_name = 'Smith';

Recovery options

If the generated column must be removed (e.g., the expression is wrong or causes unexpected storage growth), drop it:

ALTER TABLE employees DROP COLUMN full_name;

If you need to change the expression without losing data, you can modify the column in place (MySQL 5.7+ supports this for stored generated columns):

ALTER TABLE employees
  MODIFY full_name VARCHAR(100)
    GENERATED ALWAYS AS (CONCAT(UPPER(first_name), ' ', UPPER(last_name))) STORED;

In case of suspected corruption, restore the table from a recent backup or use a tool like pt-online-schema-change to rebuild the table while keeping it available.

Limitations and considerations

• Storage impact: A stored generated column occupies disk space equal to the size of its expression result for each row.
• Write performance: The expression is evaluated on every INSERT and UPDATE, which can add CPU overhead.
• Indexing: Only stored generated columns can be indexed directly, and the expression must be deterministic (no NOW(), RAND(), or user‑defined functions that may return different results).
• Replication: Both master and replica must run MySQL 5.7 or later, and the expression must be deterministic to avoid drift between servers.
• Virtual vs. stored: Virtual generated columns do not consume storage but cannot be indexed; choose stored when you need indexing or frequent reads of the derived value.

To check that the column is using the expected amount of space, you can examine the table’s DATA_LENGTH and INDEX_LENGTH in INFORMATION_SCHEMA.TABLES before and after adding the column, keeping in mind that other factors also influence size.