MySQL Features

Last updated: October 19, 2026Oct 19, 2026 6 min read

MySQL has grown from a simple, fast database into a full-featured RDBMS. This page tours its most important features. Each one comes with a short example that was run on MySQL, so you can see exactly what it does.

1. Feature Overview

The examples below use a products table in a store database:

Table: products (6 rows)

2. Open Source and Cross-Platform

• The Community Edition is free under the GPL, with source code on GitHub.
• Runs on Linux, Windows, macOS, FreeBSD and Solaris, on x86 and ARM, and in Docker containers.
• Offered as a managed service by all major clouds.

3. ACID Transactions and Crash Safety

With the default InnoDB engine, a group of changes either fully succeeds or is fully undone. Committed data survives a power cut thanks to the redo log.

START TRANSACTION;
UPDATE products SET stock = stock - 5 WHERE name = 'Wireless Mouse';
UPDATE products SET stock = stock - 5 WHERE name = 'Laptop Stand';
ROLLBACK;   -- the customer cancelled: undo both changes

SELECT name, stock FROM products WHERE name IN ('Wireless Mouse', 'Laptop Stand');

Result: 2 rows

4. Data Integrity with Constraints

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT and (since 8.0.16) CHECK constraints stop bad data at the door:

INSERT INTO products (name, category, price) VALUES ('Free Sticker', 'Accessories', 0);

ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.

5. Indexes and a Cost-Based Optimizer

Indexes let MySQL find rows without reading the whole table, and the optimizer chooses the best index for each query. EXPLAIN shows its decision:

CREATE INDEX idx_category ON products (category);
EXPLAIN SELECT name FROM products WHERE category = 'Displays';

Result: 1 row

Other index types include UNIQUE, composite (multi-column), FULLTEXT, SPATIAL, functional and invisible indexes.

6. Window Functions

Window functions (MySQL 8.0+) calculate across related rows without collapsing them, such as ranking products within each category:

SELECT category, name, price,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products
ORDER BY category, price_rank;

Result: 6 rows

7. Common Table Expressions (WITH)

CTEs give a sub-query a name, which makes long queries readable. Recursive CTEs can even generate rows:

WITH category_value AS (
  SELECT category, SUM(price * stock) AS stock_value
  FROM products
  GROUP BY category
)
SELECT category, stock_value
FROM category_value
WHERE stock_value > 100000
ORDER BY stock_value DESC;

Result: 2 rows

WITH RECURSIVE days AS (
  SELECT DATE('2026-03-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-03-05'
)
SELECT d AS sale_day FROM days;

Result: 5 rows

8. Native JSON Support

A JSON column stores validated JSON documents, with functions to read and change them:

ALTER TABLE products ADD COLUMN specs JSON;
UPDATE products SET specs = '{"size": 27, "panel": "IPS", "hz": 144}' WHERE id = 3;
UPDATE products SET specs = '{"size": 24, "panel": "VA",  "hz": 75}'  WHERE id = 5;

SELECT name, specs->>'$.panel' AS panel, specs->'$.hz' AS refresh_rate
FROM products
WHERE specs->'$.hz' >= 100;

Result: 1 row

9. Generated Columns

A generated column is calculated automatically from other columns and can be stored and indexed:

ALTER TABLE products
  ADD COLUMN price_with_gst DECIMAL(10,2) AS (ROUND(price * 1.18, 2)) STORED;

SELECT name, price, price_with_gst FROM products WHERE category = 'Displays';

Result: 2 rows

10. Views, Stored Programs and Triggers

• Views save a query under a name and can hide columns from users.
• Stored procedures and functions keep business logic inside the database.
• Triggers run automatically before or after an INSERT, UPDATE or DELETE.
• Events run SQL on a schedule, like a built-in cron.

CREATE VIEW low_stock AS
SELECT name, stock FROM products WHERE stock < 15;

SELECT * FROM low_stock;

Result: 2 rows

11. Security

• User accounts are tied to a host: 'app'@'10.0.0.%' can log in only from that network.
• Fine-grained privileges per database, table, column or routine, plus roles to group them.
• Strong password hashing (caching_sha2_password), password expiry and validation component.
• TLS-encrypted connections and encryption of data files at rest (InnoDB tablespace encryption).

CREATE ROLE 'store_reader';
GRANT SELECT ON store.* TO 'store_reader';
CREATE USER 'priya'@'localhost' IDENTIFIED BY 'S3cure#2026';
GRANT 'store_reader' TO 'priya'@'localhost';
SHOW GRANTS FOR 'priya'@'localhost' USING 'store_reader';

Result: 3 rows

12. Replication, Clustering and Partitioning

13. Tools and Connectors

14. Things to Keep in Mind

• Some features arrived late compared with other databases (CHECK constraints in 8.0.16, window functions in 8.0).
• MySQL has no built-in materialized views; summary tables or events are used instead.
• Default string comparisons are case-insensitive (collation utf8mb4_0900_ai_ci), which surprises developers coming from PostgreSQL.
• Writes scale up more easily than out; very large write loads need sharding or a distributed layer.

15. Practice Exercises

SELECT name, price, DENSE_RANK() OVER (ORDER BY price DESC) AS rnk
FROM products;

WITH avg_price AS (
  SELECT category, AVG(price) AS avg_p FROM products GROUP BY category
)
SELECT category, ROUND(avg_p, 2) AS avg_price FROM avg_price WHERE avg_p > 2000;

16. Summary

• MySQL is free, open source and runs everywhere.
• InnoDB gives ACID transactions, crash safety and row-level locking.
• Modern SQL: window functions, CTEs, JSON, generated columns, CHECK constraints.
• Strong security with users, roles, privileges and encryption.
• Replication, Group Replication and InnoDB Cluster provide scale and high availability.