Databases
MySQL
MySQL is an open-source relational database, historically the 'M' in the LAMP stack that powered a huge share of early web applications. It's been owned by Oracle since 2010, with an actively maintained open-source community edition; MariaDB is a community-governed fork that started shortly after that acquisition and remains compatible with much of MySQL's behavior.
Why it matters
- It has a huge installed base in existing applications
- WordPress, many older PHP and Rails applications, and a large share of shared web hosting still run on MySQL, so working on existing systems often means working with it whether or not it'd be the first pick for something new.
- It's still a default in many hosting environments
- A lot of managed hosting and beginner-friendly platforms default to MySQL, which keeps it a common starting point even for new, small projects.
- It's a required skill for maintaining legacy systems
- Understanding its specific behaviors, including some historical quirks, is necessary for anyone working on a codebase that was built on it years ago.
A table and a query
The syntax will look immediately familiar to anyone who knows SQL from another engine — the differences from Postgres show up more in default behaviors and available types than in the core query language itself.
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;
SELECT id, status FROM orders WHERE customer_id = 42 ORDER BY id DESC;Storage engines: why InnoDB is the default
MySQL supports pluggable storage engines, but in practice InnoDB has been the default for years and should be assumed unless there's a specific reason otherwise — it's the one that provides real ACID transactions, foreign key enforcement, and row-level locking. The older MyISAM engine still exists but lacks real transaction support, which makes it a poor fit for anything beyond narrow, specific use cases.
Where MySQL and Postgres diverge in practice
Historically, MySQL's default SQL mode was more forgiving — silently truncating or coercing values that would raise an error elsewhere — though modern versions default to strict mode, which behaves much closer to Postgres. Character set handling has also had a real history: older defaults didn't fully support four-byte UTF-8 characters like emoji until utf8mb4 became the recommended default. Advanced SQL features like window functions and common table expressions arrived in MySQL later than they were available in Postgres.
Mistakes people make here
- Relying on old, loose SQL mode behavior
- Modern MySQL defaults to strict mode, but configurations and tutorials written against older defaults still assume values get silently truncated or coerced rather than rejected — a team moving between environments can hit surprising differences if the mode isn't consistent.
- Picking MyISAM out of habit or an old tutorial
- It has no real transaction support and no foreign key enforcement, which removes exactly the guarantees that make a relational database worth using for most application data — InnoDB should be the default choice.
- Assuming string comparison behaves identically to Postgres
- MySQL's default collation and case-sensitivity rules for string comparison and uniqueness differ from Postgres's, which can produce different results for the same-looking unique constraint or WHERE clause if a team isn't aware of the difference.
- Not setting utf8mb4 explicitly
- Older default character sets in MySQL can't store four-byte UTF-8 characters like emoji or some international text correctly, which has caused real data-corruption bugs in applications that didn't set the character set explicitly.
Strengths and trade-offs
Where it is strong
- Mature and extremely widely deployed, with abundant hosting and tooling support built around it.
- InnoDB provides real ACID transactions and row-level locking as the standard, default engine.
- A straightforward replication story that many managed hosting providers support out of the box.
- A large, long-established talent pool, since it's widely taught and widely used across the industry.
The trade-offs
- Historically looser default type handling than Postgres has caught out teams unaware that strict mode needed to be turned on (it's now the default, but older systems and tutorials predate that change).
- Fewer advanced native types and extensions than Postgres — there's no equivalent to PostGIS or pgvector built into MySQL the same way.
- Governance sits with Oracle, which is part of why the community-governed MariaDB fork exists for teams that want an alternative.
- Some modern SQL features, like window functions and common table expressions, arrived later in MySQL's history than in Postgres.
Who needs this
Necessary for anyone maintaining an existing MySQL-based system, and a reasonable choice for a new project where team familiarity or hosting constraints point that direction.
Questions about mysql
- Is MySQL the same as MariaDB?
- No — MariaDB is a fork that started after Oracle acquired MySQL, created by some of MySQL's original developers as a community-governed alternative. It remains largely compatible but is maintained separately.
- Is MySQL still a good choice for a new project?
- It's a reasonable choice, especially with existing team familiarity or hosting constraints that favor it, though many new projects default to Postgres today for its broader feature set.
- Which storage engine should I use?
- InnoDB, essentially always — it's been the default for years and is the one that provides real transactions and foreign key enforcement.
- Does MySQL support JSON columns?
- Yes, a native JSON type exists, though it's less deeply integrated with indexing and query planning than Postgres's JSONB.