MySQL Cheat Sheet

Syntax

Server and client CTEs and window functions Transactions and locking Replication and backup

mysql -u user -p -h host -P 3306 dbname            # connect
mysql -u user -p db < script.sql                    # run script
mysqldump --single-transaction --routines --events db > db.sql
mysqladmin -u root -p status | version | shutdown
sudo systemctl start|stop|status mysql              # mysqld on RHEL

SELECT VERSION(); SHOW DATABASES; USE db; SHOW TABLES; DESCRIBE t; SHOW CREATE TABLE t\G
SHOW PROCESSLIST; KILL QUERY id; SHOW WARNINGS; SHOW VARIABLES LIKE '...'; SET PERSIST var = val;

CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE TABLE t (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (amount >= 0),
  parent_id BIGINT UNSIGNED NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY ix_t_parent (parent_id),
  CONSTRAINT fk_t_parent FOREIGN KEY (parent_id) REFERENCES p (id)
) ENGINE = InnoDB;
ALTER TABLE t ADD COLUMN c INT NULL AFTER code, ALGORITHM = INSTANT;
ALTER TABLE t MODIFY c BIGINT NOT NULL;   ALTER TABLE t RENAME COLUMN c TO d;   ALTER TABLE t DROP COLUMN d;
CREATE INDEX ix ON t (a, b DESC);  CREATE INDEX fx ON t ((LOWER(code)));  ALTER TABLE t ALTER INDEX ix INVISIBLE;
DROP TABLE IF EXISTS t;  TRUNCATE TABLE t;  RENAME TABLE a TO b;

INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y');
INSERT INTO t (a, b) VALUES (1, 'x') AS new ON DUPLICATE KEY UPDATE b = new.b;   -- upsert
INSERT IGNORE INTO t ...;  REPLACE INTO t ...;
INSERT INTO t (a, b) SELECT ...;
UPDATE t1 JOIN t2 ON t2.id = t1.id SET t1.x = t2.x WHERE ...;
UPDATE t SET x = x + 1 WHERE ... ORDER BY id LIMIT 1000;
DELETE t1 FROM t1 JOIN t2 ON ... WHERE ...;
LOAD DATA LOCAL INFILE 'f.csv' INTO TABLE t FIELDS TERMINATED BY ',' IGNORE 1 LINES (a, @b) SET b = STR_TO_DATE(@b, '%d/%m/%Y');
SELECT LAST_INSERT_ID(), ROW_COUNT();

SELECT DISTINCT a, CONCAT(b, ' ', c) AS bc FROM t
WHERE x BETWEEN 1 AND 10 AND y IN ('A', 'B') AND z LIKE 'P0%' AND w IS NULL AND v <=> NULL
ORDER BY a DESC, id LIMIT 20 OFFSET 40;

SELECT k, COUNT(*), SUM(v), GROUP_CONCAT(n ORDER BY n SEPARATOR ',') FROM t GROUP BY k WITH ROLLUP HAVING COUNT(*) > 1;
SELECT ... FROM a JOIN b ON ... LEFT JOIN c ON ... ;           -- no FULL OUTER JOIN: LEFT ... UNION RIGHT ...
SELECT ... WHERE EXISTS (SELECT 1 FROM ... WHERE ...);
SELECT ... FROM t, LATERAL (SELECT ... WHERE u.k = t.k ORDER BY ... LIMIT 1) x;
SELECT ... UNION ALL SELECT ...;  ... INTERSECT ...;  ... EXCEPT ...;      -- 8.0.31+

IFNULL(a, b)  COALESCE(a, b, c)  NULLIF(a, b)  IF(c, x, y)  CASE WHEN ... THEN ... ELSE ... END
CONCAT  CONCAT_WS  CHAR_LENGTH  LENGTH  SUBSTRING  SUBSTRING_INDEX  REPLACE  TRIM  LPAD  UPPER  LOWER  REGEXP_LIKE
ROUND  TRUNCATE  CEIL  FLOOR  MOD  POW  ABS  DIV
NOW()  CURDATE()  UTC_TIMESTAMP()  DATE_ADD(d, INTERVAL 7 DAY)  d - INTERVAL 1 MONTH  DATEDIFF(d2, d1)
TIMESTAMPDIFF(MINUTE, t1, t2)  DATE_FORMAT(d, '%Y-%m')  STR_TO_DATE(s, fmt)  LAST_DAY(d)  CONVERT_TZ(t, '+00:00', '+05:30')
CAST(x AS SIGNED | UNSIGNED | DECIMAL(10,2) | DATE | CHAR | JSON)  HEX  UNHEX  CONV(x, 16, 10)

WITH RECURSIVE r AS (SELECT ... UNION ALL SELECT ... FROM r WHERE ...) SELECT * FROM r;
ROW_NUMBER() OVER (PARTITION BY p ORDER BY o)   RANK()   DENSE_RANK()   NTILE(4)
LAG(x) OVER w   LEAD(x) OVER w   FIRST_VALUE(x) OVER w
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)                 -- running total
AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) -- moving average
... WINDOW w AS (PARTITION BY p ORDER BY o)

doc->'$.a'   doc->>'$.a'   JSON_EXTRACT  JSON_SET  JSON_REMOVE  JSON_ARRAY_APPEND  JSON_CONTAINS  'v' MEMBER OF (doc->'$.arr')
JSON_TABLE(doc, '$.items[*]' COLUMNS (n INT PATH '$.n')) AS jt
JSON_ARRAYAGG(x)  JSON_OBJECTAGG(k, v)
ALTER TABLE t ADD c VARCHAR(20) AS (doc->>'$.c') VIRTUAL, ADD INDEX (c);

START TRANSACTION; ... COMMIT; | ROLLBACK;   SAVEPOINT s; ROLLBACK TO SAVEPOINT s;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT ... FOR UPDATE [NOWAIT | SKIP LOCKED];   SELECT ... FOR SHARE;
SHOW ENGINE INNODB STATUS\G   SELECT * FROM sys.innodb_lock_waits\G

DELIMITER //
CREATE PROCEDURE p(IN a INT, OUT b INT) BEGIN DECLARE v INT DEFAULT 0; SELECT COUNT(*) INTO b FROM t WHERE x = a; END //
CREATE FUNCTION f(a INT) RETURNS INT DETERMINISTIC NO SQL RETURN a * 2 //
CREATE TRIGGER trg BEFORE INSERT ON t FOR EACH ROW BEGIN SET NEW.x = UPPER(NEW.x); END //
CREATE EVENT ev ON SCHEDULE EVERY 1 DAY DO DELETE FROM logs WHERE created_at < NOW() - INTERVAL 90 DAY //
DELIMITER ;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...';
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

Example

Which tool for which job:

INSERT... AS new ON DUPLICATE KEY UPDATE LOAD DATA [LOCAL] INFILE Top-N per group

ROW_NUMBER() in a CTE, or LATERAL... LIMIT WITH RECURSIVE FULLTEXT + MATCH... AGAINST JSON + generated-column index Retention of logs

Partitioning + DROP PARTITION via events CREATE EVENT Generated column + UNIQUE (NULLs allowed) FOR UPDATE SKIP LOCKED

Expected Result

You can write any common MySQL statement from this sheet in minutes.

Common Mistakes

Copying syntax that requires a newer MySQL version than you run — check SELECT VERSION();.

Best Practices

Annotate this sheet with your own team's conventions.

Practice Questions

Without looking, write an upsert, a LATERAL top-1 query, and a recursive CTE.