MySQL Cheat Sheet

Connecting and Login

Default port is 3306. Build a URL or DSN for your host with the MySQL connection string generator.

On Amazon RDS with IAM auth, a rejected token shows up as Access denied for user on RDS MySQL.

Example: save a login path once, then connect to production over TLS without typing the password again.

mysql_config_editor set --login-path=prod --host=db.example.com --user=app --password
mysql --login-path=prod --ssl-mode=VERIFY_IDENTITY --ssl-ca=rds-ca.pem shop
mysql_config_editor print --all   # passwords are shown as *****

Databases

There is no RENAME DATABASE. Create the new database and move each table with RENAME TABLE.

Example: rename the shop database to store by moving every table across, then drop the empty one.

CREATE DATABASE store CHARACTER SET utf8mb4;
RENAME TABLE shop.users TO store.users, shop.orders TO store.orders;
SHOW TABLES FROM shop;   -- empty set
DROP DATABASE shop;

Tables

Example: the two tables every other example on this page uses.

CREATE TABLE users (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  plan       ENUM('free', 'pro', 'team') NOT NULL DEFAULT 'free',
  credit     DECIMAL(10, 2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  total      DECIMAL(10, 2) NOT NULL,
  status     VARCHAR(20) NOT NULL DEFAULT 'pending',
  meta       JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id)
);

Users and Privileges

An account is a user name plus a host, so 'app'@'localhost' and 'app'@'%' are two different accounts.

Example: a read-only reporting user limited to the office network, checked before you hand it out.

CREATE USER 'report'@'10.0.%' IDENTIFIED BY 'Rep0rt!only';
GRANT SELECT ON shop.* TO 'report'@'10.0.%';
SHOW GRANTS FOR 'report'@'10.0.%';
-- GRANT USAGE ON *.* TO `report`@`10.0.%`
-- GRANT SELECT ON `shop`.* TO `report`@`10.0.%`

Insert, Update, Delete

The VALUES(name) function in ON DUPLICATE KEY UPDATE still works but is deprecated. Use a row alias (AS new) after VALUES, or a derived table after SELECT.

Example: record a user's daily order count with one upsert, safe to run again for the same day.

CREATE TABLE daily_orders (
  user_id BIGINT UNSIGNED, day DATE, orders INT, PRIMARY KEY (user_id, day)
);
INSERT INTO daily_orders (user_id, day, orders)
SELECT * FROM (
  SELECT user_id, DATE(created_at) AS day, COUNT(*) AS n FROM orders
  WHERE created_at >= CURDATE() GROUP BY user_id, DATE(created_at)
) AS t
ON DUPLICATE KEY UPDATE orders = t.n;

Select and Filtering

Example: the top five customers by October spend, using a CTE (WITH).

WITH october AS (
  SELECT user_id, SUM(total) AS spent, COUNT(*) AS orders
  FROM orders
  WHERE status = 'paid' AND created_at >= '2026-10-01' AND created_at < '2026-11-01'
  GROUP BY user_id
)
SELECT u.email, o.spent, o.orders
FROM october o JOIN users u ON u.id = o.user_id
ORDER BY o.spent DESC
LIMIT 5;

Joins and Unions

MySQL has no FULL OUTER JOIN. Combine a LEFT and a RIGHT join with UNION.

Example: a full outer join between users and leads on email.

SELECT u.email, l.source FROM users u LEFT JOIN leads l ON l.email = u.email
UNION
SELECT l.email, l.source FROM users u RIGHT JOIN leads l ON l.email = u.email;

Common Functions

Example: a monthly revenue report with formatted months and a comma-separated list of statuses.

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
       ROUND(SUM(total), 2) AS revenue,
       GROUP_CONCAT(DISTINCT status ORDER BY status SEPARATOR ', ') AS statuses
FROM orders
GROUP BY month
ORDER BY month DESC;

Import, Export and Backup

LOAD DATA LOCAL needs local_infile=ON on the server and --local-infile=1 on the client.

In 1bench: the MySQL GUI exports a table, with your current filter and sort, to CSV, JSON, NDJSON or SQL on your own machine, so there is no INTO OUTFILE path or secure_file_priv folder to fight, and it imports CSV, JSON and SQL files the same way.

Example: copy a production database to staging in one pipe, without writing a dump file.

mysqldump --login-path=prod --single-transaction --routines --triggers shop \
  | mysql --login-path=staging shop_staging

CLI Output and Options

Your typed history is in ~/.mysql_history. Lines that look like IDENTIFIED BY or PASSWORD are left out of it.

Example: export a query to CSV from the shell, with a header row, for a spreadsheet.

mysql --login-path=prod -B -e "
  SELECT id, user_id, total, status, created_at FROM shop.orders
  WHERE created_at >= '2026-10-01'" | tr '\t' ',' > orders-october.csv
head -2 orders-october.csv   # id,user_id,total,status,created_at

Indexes and EXPLAIN

In EXPLAIN output, type: ALL means a full table scan and key: NULL means no index was used.

Example: check that a user's order history uses the composite index, then confirm with real timings.

CREATE INDEX idx_orders_user ON orders (user_id, created_at);
EXPLAIN SELECT id, total FROM orders WHERE user_id = 7 ORDER BY created_at DESC LIMIT 10;
-- type: ref, key: idx_orders_user, Extra: Backward index scan
EXPLAIN ANALYZE SELECT id, total FROM orders WHERE user_id = 7 ORDER BY created_at DESC LIMIT 10;

Processes and Transactions

In 1bench: the MySQL GUI cancels a running query from the tab, sending KILL QUERY on a side connection, so you do not need to look up the thread id first.

Example: move credit between two users atomically, locking both rows first.

START TRANSACTION;
SELECT id, credit FROM users WHERE id IN (1, 2) FOR UPDATE;
UPDATE users SET credit = credit - 25.00 WHERE id = 1;
UPDATE users SET credit = credit + 25.00 WHERE id = 2;
COMMIT;

JSON

Example: turn each order's items array into rows with JSON_TABLE to count units sold per SKU.

SELECT jt.sku, SUM(jt.qty) AS units
FROM orders,
     JSON_TABLE(meta, '$.items[*]' COLUMNS (sku VARCHAR(40) PATH '$.sku', qty INT PATH '$.qty')) AS jt
GROUP BY jt.sku
ORDER BY units DESC;

TiDB Notes

TiDB speaks the MySQL protocol on port 4000, so the mysql client and the SQL above work. These rows are TiDB only.

Example: after a bad delete, read the rows as they were before it ran. Stale reads only reach back as far as the GC window (tidb_gc_life_time, 10 minutes by default).

SELECT COUNT(*) FROM orders WHERE user_id = 7;   -- 0 after the bad delete
SELECT * FROM orders AS OF TIMESTAMP NOW() - INTERVAL 5 MINUTE
WHERE user_id = 7;                               -- the rows as they were

Gotchas

• UPDATE or DELETE without a WHERE clause changes every row, and autocommit is on by default, so there is no undo. Start the client with --safe-updates, or run START TRANSACTION; first and check the row count before COMMIT.
• REPLACE INTO is not an upsert. It deletes the old row and inserts a new one, which fires delete triggers, cascades foreign keys and drops any column you left out back to its default. Use INSERT ... ON DUPLICATE KEY UPDATE.
• mysqldump without --single-transaction locks tables while it runs. With it, InnoDB gets a consistent snapshot, but MyISAM tables are still not consistent.
• utf8 in MySQL is an alias for utf8mb3, which cannot store emoji or other 4-byte characters. Use utf8mb4.
• -pSecret (no space) puts the password in shell history and process lists, and -p Secret (with a space) treats Secret as the database name. Use -p alone or a login path.
• KILL on a connection running a large write rolls back everything it did, which can take as long as the write itself. Use KILL QUERY when you only want the statement stopped.