MySQL-specific syntax: (1) Storage engines: ENGINE=InnoDB (default, transactions) or ENGINE=MyISAM (no transactions). (2) JSON: attributes->>'$.key' gets value as text, JSON_EXTRACT(col, '$.key') gets JSON, JSON_SET(col, '$.key', value) updates. (3) AUTO_INCREMENT: INT AUTO_INCREMENT PRIMARY KEY, LAST_INSERT_ID() gets last ID. (4) Replication: CHANGE MASTER TO, START SLAVE, SHOW SLAVE STATUS. (5) Full-text: FULLTEXT(col), MATCH(col) AGAINST('query'), IN BOOLEAN MODE for +/-. (6) Generated columns: col AS (expression) STORED for indexing JSON.

Storage engines: ENGINE=InnoDB (default, transactions, foreign keys) or ENGINE=MyISAM (no transactions, fast reads)

JSON: attributes->>'$.key' gets text, -> gets JSON, JSON_SET(col, '$.path', value) updates, JSON_OBJECT() creates

AUTO_INCREMENT: INT AUTO_INCREMENT PRIMARY KEY, LAST_INSERT_ID() gets last ID, gaps after DELETE/ROLLBACK

Replication: CHANGE MASTER TO, START SLAVE, SHOW SLAVE STATUS, GTID for failover

Full-text: FULLTEXT(col), MATCH(col) AGAINST('query'), IN BOOLEAN MODE for +/-, IN NATURAL LANGUAGE MODE for ranking

Generated columns: col AS (expression) STORED for indexing JSON, VIRTUAL for computed values

Transactions: START TRANSACTION, COMMIT, ROLLBACK (InnoDB only), SAVEPOINT for partial rollback

InnoDB (ACID, transactions), JSON (5.7+), replication (master-slave), AUTO_INCREMENT, FULLTEXT (InnoDB/MyISAM)

More advanced: Arrays, JSONB (binary, faster), MVCC (better concurrency), streaming replication, SERIAL/IDENTITY

Different syntax: IDENTITY instead of AUTO_INCREMENT, JSON (2016+), Always On for replication, FULLTEXT different

Enterprise features: Advanced replication, RAC (clustering), partitioning, JSON (12c+), SEQUENCE instead of AUTO_INCREMENT

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

EXISTS

Tests whether a correlated or non-correlated subquery returns at least one row.

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'

PRIMARY KEY

Uniquely identifies each row and implicitly requires NOT NULL.

customer_id INT PRIMARY KEY

FOREIGN KEY