Stop 06 · SQL DatabasesTopics · 9/14

MySQL

In short

MySQL has powered a huge part of the web, from WordPress blogs to big social apps. There's a good chance you'll meet it at an internship, so learn its quirks.

One of the most widely deployed open-source relational databases, historically popular with the LAMP stack and still extremely common in production systems.

The blob happily drives an auto-rickshaw down a street full of autos.

Think of it like this

The auto-rickshaw of databases

Autos aren't fancy, but they're everywhere, every driver knows them, and spare parts are in every gali. MySQL is similar: easy to set up, offered by nearly every host, with tons of community help. Postgres has more gadgets; MySQL reliably gets you there.

Watch it happen

MySQL or PostgreSQL?

MySQL

InnoDB engine: transactions and foreign keys
AUTO_INCREMENT and ON DUPLICATE KEY UPDATE
Offered by almost every web host

PostgreSQL

Richer types: JSONB, arrays, ranges
RETURNING and ON CONFLICT upserts
Big extensions like PostGIS and pgvector

Both are solid SQL databases. Learn one well and the other takes a week. Pick what your team or host already runs.

The big ideas

What you need to know

Use InnoDB

It's the default engine and supports transactions and foreign keys. Avoid the old MyISAM engine for app data.

utf8mb4, not utf8

MySQL's old utf8 can't store emojis. utf8mb4 is real UTF-8 and the default in MySQL 8.

AUTO_INCREMENT ids

MySQL's version of SERIAL. Get the new id with LAST_INSERT_ID() or your driver's insertId.

Upserts

INSERT … ON DUPLICATE KEY UPDATE inserts a row, or updates it if the unique key already exists.

See it in code

Walk through the code

sql

1CREATE TABLE users (2  id INT AUTO_INCREMENT PRIMARY KEY,3  email VARCHAR(255) NOT NULL UNIQUE,4  logins INT NOT NULL DEFAULT 05) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;6 7-- First login inserts, later logins bump the counter8INSERT INTO users (email, logins) VALUES ('riya@college.edu', 1)9ON DUPLICATE KEY UPDATE logins = logins + 1;10 11SELECT LAST_INSERT_ID();

1/4AUTO_INCREMENT hands out ids 1, 2, 3, like SERIAL in Postgres.

From Node, the mysql2 package gives you result.insertId, so you rarely need the LAST_INSERT_ID() query.

Save yourself the pain

Common mistakes

Oops

Creating tables with CHARSET=utf8 and then losing emojis in names.

Do this instead

Use utf8mb4 everywhere: the database, the tables and the connection.

Oops

Assuming Postgres syntax like RETURNING or SERIAL works the same in MySQL.

Do this instead

Check the MySQL docs for each feature. Small syntax differences cause most migration headaches.

Your mission

MySQL side by side

~25 min

Quick check

You need to store user names that include emojis in MySQL. Which character set should you use?

Go deeper

1Install MySQL locally and run the same queries you practiced in PostgreSQL.
2Compare MySQL and PostgreSQL's syntax differences for a few common operations.
3Learn storage engines (InnoDB) and why they matter for transactions.