Offensive Security Researcher & Quantum Cryptography Analyst

How to Perform Data Transformation in MySQL Database

Abdul Wahab Junaid
How to Perform Data Transformation in MySQL Database

Somewhere along the way, I stopped thinking of SQL as just a way to fetch data and started treating it as a genuinely powerful transformation engine in its own right. A well-written set of SQL transformations can outperform an equivalent script in Python or a general-purpose ETL tool by a wide margin, simply because MySQL’s query optimizer is purpose-built for exactly this kind of set-based work. This article covers the techniques I actually use to transform data inside MySQL — cleaning, reshaping, aggregating, and enriching it — from beginner fundamentals through advanced patterns.

Why Transform Data Inside the Database

Before diving into technique, it’s worth being clear on when this approach makes sense. I push transformation logic into MySQL when:

I move transformation out of MySQL when the logic involves complex procedural steps, external API calls, machine learning inference, or business rules that are painful to express in SQL.

Data Cleaning: The Foundation

Almost every transformation pipeline starts with cleaning. Here are patterns I use constantly.

Trimming, Casing, and Normalizing Text

UPDATE customers
SET
    email = LOWER(TRIM(email)),
    full_name = TRIM(REGEXP_REPLACE(full_name, '\\s+', ' '));

I normalize emails to lowercase and collapse repeated whitespace in names — small things that prevent duplicate-looking records and broken joins downstream.

Handling NULLs and Defaults

SELECT
    order_id,
    COALESCE(discount_code, 'NONE') AS discount_code,
    IFNULL(shipping_cost, 0) AS shipping_cost
FROM orders;

COALESCE handles multiple fallback values in sequence; IFNULL is MySQL’s simpler two-argument shorthand. I use COALESCE by default since it’s ANSI-standard and portable if the query ever needs to run against another database engine.

Deduplication

A pattern I use often — identifying and removing duplicate rows while keeping the most recent:

WITH ranked AS (
    SELECT
        customer_id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM customers
)
DELETE c FROM customers c
JOIN ranked r ON c.customer_id = r.customer_id
WHERE r.rn > 1;

ROW_NUMBER() window function (available since MySQL 8.0) makes this dramatically cleaner than the old self-join-and-subquery approach required in MySQL 5.7 and earlier.

Reshaping Data

Pivoting Rows to Columns

MySQL doesn’t have a native PIVOT statement like SQL Server, but conditional aggregation achieves the same result:

SELECT
    customer_id,
    SUM(CASE WHEN order_status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN order_status = 'shipped' THEN 1 ELSE 0 END) AS shipped_orders,
    SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
GROUP BY customer_id;

Unpivoting with UNION ALL

Going the other direction — turning columns into rows — I typically use UNION ALL:

SELECT customer_id, 'pending' AS status, pending_orders AS order_count FROM order_summary
UNION ALL
SELECT customer_id, 'shipped', shipped_orders FROM order_summary
UNION ALL
SELECT customer_id, 'cancelled', cancelled_orders FROM order_summary;

JSON Transformation

For semi-structured data stored in JSON columns (common in modern MySQL 5.7+ schemas), I extract and reshape using native JSON functions:

SELECT
    order_id,
    JSON_EXTRACT(metadata, '$.utm_source') AS utm_source,
    JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.referrer')) AS referrer,
    JSON_EXTRACT(metadata, '$.items[*].sku') AS item_skus
FROM orders
WHERE JSON_CONTAINS(metadata, '"promo"', '$.tags');

The shorthand -> and ->> operators do the same extraction more concisely:

SELECT order_id, metadata->>'$.utm_source' AS utm_source
FROM orders;

Aggregation and Window Functions

This is where MySQL transformation gets genuinely powerful. Window functions (MySQL 8.0+) let me compute running totals, rankings, and comparisons without collapsing the result set the way GROUP BY does.

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    RANK() OVER (
        PARTITION BY customer_id
        ORDER BY total_amount DESC
    ) AS order_rank_by_value,
    LAG(total_amount, 1) OVER (
        PARTITION BY customer_id ORDER BY order_date
    ) AS previous_order_amount
FROM orders;

I use this pattern constantly for things like “customer lifetime value running total” or “how does this order compare to the customer’s previous one” — transformations that would otherwise require multiple self-joins or application-side loops.

flowchart LR
    A[Raw Rows] --> B[PARTITION BY - group logically without collapsing rows]
    B --> C[ORDER BY within partition]
    C --> D[Window Function applied per row: SUM/RANK/LAG/etc]
    D --> E[Result: original row count preserved, enriched with computed values]

Common Table Expressions (CTEs) for Multi-Step Transformations

I use CTEs heavily to break complex transformations into readable, testable steps rather than deeply nested subqueries:

WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        customer_id,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE order_status != 'cancelled'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m'), customer_id
),
customer_segments AS (
    SELECT
        customer_id,
        AVG(revenue) AS avg_monthly_revenue,
        CASE
            WHEN AVG(revenue) > 500 THEN 'high_value'
            WHEN AVG(revenue) > 100 THEN 'mid_value'
            ELSE 'low_value'
        END AS segment
    FROM monthly_revenue
    GROUP BY customer_id
)
SELECT segment, COUNT(*) AS customer_count, AVG(avg_monthly_revenue) AS avg_revenue
FROM customer_segments
GROUP BY segment;

Recursive CTEs handle hierarchical transformations — something I use for category trees or org charts:

WITH RECURSIVE category_tree AS (
    SELECT category_id, category_name, parent_category_id, 0 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_category_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_category_id = ct.category_id
)
SELECT * FROM category_tree ORDER BY depth, category_name;

Materializing Transformations: Views, Materialized Approaches, and Generated Columns

Regular Views

For transformation logic reused across many queries, I wrap it in a view:

CREATE VIEW v_customer_ltv AS
SELECT
    customer_id,
    SUM(total_amount) AS lifetime_value,
    COUNT(*) AS total_orders,
    MAX(order_date) AS last_order_date
FROM orders
WHERE order_status != 'cancelled'
GROUP BY customer_id;

Regular views re-execute the underlying query every time they’re referenced — no storage/performance benefit, purely a readability and reuse mechanism.

“Materialized” Tables (MySQL Has No Native Materialized Views)

Since MySQL lacks true materialized views, I simulate them with a scheduled job that populates a real table:

CREATE TABLE mv_customer_ltv (
    customer_id BIGINT UNSIGNED PRIMARY KEY,
    lifetime_value DECIMAL(12,2),
    total_orders INT,
    last_order_date DATE,
    refreshed_at DATETIME
) ENGINE=InnoDB;

Refreshed via a MySQL Event Scheduler job or an external orchestrator:

CREATE EVENT refresh_customer_ltv
ON SCHEDULE EVERY 1 HOUR
DO
  REPLACE INTO mv_customer_ltv
  SELECT customer_id, SUM(total_amount), COUNT(*), MAX(order_date), NOW()
  FROM orders
  WHERE order_status != 'cancelled'
  GROUP BY customer_id;

Generated Columns

For row-level transformations I want computed and optionally indexed automatically, generated columns are excellent:

ALTER TABLE orders
  ADD COLUMN total_with_tax DECIMAL(10,2)
  GENERATED ALWAYS AS (total_amount * 1.0825) STORED,
  ADD INDEX idx_total_with_tax (total_with_tax);

STORED computes and persists the value on write (indexable, faster reads, slightly more storage/write cost); VIRTUAL computes it on read (no extra storage, but not usable in all index scenarios in older versions).

Stored Procedures for Complex Procedural Transformations

When a transformation genuinely needs procedural logic — loops, conditional branching across multiple steps — I use a stored procedure rather than forcing it into a single (unreadable) SQL statement:

DELIMITER //

CREATE PROCEDURE transform_and_archive_old_orders(IN cutoff_date DATE)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_order_id BIGINT;
    DECLARE cur CURSOR FOR
        SELECT order_id FROM orders WHERE order_date < cutoff_date;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    START TRANSACTION;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_order_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        INSERT INTO orders_archive
        SELECT * FROM orders WHERE order_id = v_order_id;

        DELETE FROM orders WHERE order_id = v_order_id;
    END LOOP;
    CLOSE cur;

    COMMIT;
END //

DELIMITER ;

I’m honest with myself about when this is the right tool though — cursors are row-by-row and slow at scale. For large volumes I prefer set-based batch deletes/inserts over cursors wherever possible:

INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < '2025-01-01';
DELETE FROM orders WHERE order_date < '2025-01-01' LIMIT 10000;
-- repeated in a loop from the application/orchestrator side, checking ROW_COUNT()

Date and Time Transformations

Extremely common in reporting transformations:

SELECT
    order_id,
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-01') AS month_start,
    YEARWEEK(order_date, 3) AS iso_year_week,
    TIMESTAMPDIFF(DAY, order_date, NOW()) AS days_since_order,
    CONVERT_TZ(order_date, '+00:00', '-05:00') AS order_date_est
FROM orders;

CONVERT_TZ requires the time zone tables to be loaded (mysql_tzinfo_to_sql), which I always set up in any environment doing timezone-aware transformations.

Performance Considerations for In-Database Transformation

EXPLAIN ANALYZE
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE order_date > '2026-01-01'
GROUP BY customer_id;

Security Considerations

Troubleshooting Common Transformation Issues

ProblemCauseFix
Window function query is unexpectedly slowMissing index supporting the PARTITION BY/ORDER BY columnsAdd a composite index matching the window’s partition/order columns
Generated column can’t be indexedUsing VIRTUAL in a context requiring STORED for that MySQL version/index typeSwitch to STORED if index support requires it
Recursive CTE runs foreverMissing or incorrect termination conditionVerify base case and recursive step logic; add cte_max_recursion_depth safeguard
Stored procedure with cursor is very slow at scaleRow-by-row processing instead of set-based operationsRewrite as batched set-based INSERT/UPDATE/DELETE
JSON extraction returns unexpected quotingUsing JSON_EXTRACT instead of JSON_UNQUOTE(JSON_EXTRACT(...)) or ->>Use ->> for unquoted scalar extraction

Best Practices I Follow

Interview Questions

  1. How would you deduplicate rows in MySQL using window functions?
  2. What’s the difference between a regular view and a “materialized” approach in MySQL, given MySQL has no native materialized views?
  3. When would you choose a stored procedure with a cursor versus a set-based query for a transformation task?
  4. What’s the difference between STORED and VIRTUAL generated columns?
  5. How do window functions differ from GROUP BY in terms of preserving row-level detail?
  6. How would you safely transform and archive millions of old rows without long lock waits?
  7. What are the risks of using DEFINER-privileged stored procedures for transformations?

FAQs

Should I do transformations in SQL or in application code? I lean toward SQL for anything genuinely set-based (filtering, joining, aggregating), since MySQL’s optimizer is purpose-built for it and it avoids unnecessary data movement. I move to application code for complex procedural logic, external integrations, or anything that doesn’t map cleanly to SQL.

Does MySQL support materialized views? Not natively, unlike PostgreSQL or Oracle. I simulate the behavior using a real table refreshed on a schedule via the Event Scheduler or an external orchestrator.

Are window functions available in all MySQL versions? No — they were introduced in MySQL 8.0. If you’re still on 5.7, you’ll need self-joins and correlated subqueries to achieve similar results, which is a strong argument for upgrading if you do heavy analytical transformation work.

How do I keep transformation queries fast as data grows? Index the columns driving WHERE, JOIN, and window/GROUP BY clauses, batch large-scale updates instead of single massive transactions, and periodically re-verify query plans with EXPLAIN ANALYZE since plans can shift as data volume and distribution change.

Summary and Key Takeaways

MySQL is a genuinely capable transformation engine once you move past treating it as just a place to store and fetch rows. Between window functions, CTEs, JSON functions, generated columns, and set-based batch operations, I can handle the vast majority of real-world data transformation needs directly in SQL — faster and with less operational complexity than shipping data out to an external tool and back.

Key takeaways:

References

Exit mobile version