MySQL Cheat Sheet

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE shirts (
  size ENUM('S','M','L','XL') DEFAULT 'M',
  colors SET('red','green','blue')
);

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data JSON NOT NULL
);

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
event_date DATETIME  -- No auto-update, no timezone conversion

token BINARY(16),  -- Fixed-length
file_data MEDIUMBLOB,  -- Up to 16MB
INSERT INTO t VALUES (UNHEX(REPLACE(UUID(), '-', '')));

SELECT data->'$.name' AS json_val,
       data->>'$.name' AS text_val
FROM events;

SELECT * FROM events,
JSON_TABLE(data, '$.items[*]' COLUMNS (
  name VARCHAR(100) PATH '$.name',
  qty INT PATH '$.quantity'
)) AS jt;

UPDATE events SET data = JSON_SET(data, '$.status', 'active');
UPDATE events SET data = JSON_REMOVE(data, '$.temp');

SELECT JSON_ARRAYAGG(name) FROM users;
SELECT JSON_OBJECTAGG(key_col, val_col) FROM pairs;

SELECT * FROM events
WHERE JSON_CONTAINS(data, '"click"', '$.type');
SELECT * FROM events WHERE JSON_SEARCH(data, 'one', 'click') IS NOT NULL;

SELECT author, GROUP_CONCAT(title ORDER BY title SEPARATOR ', ')
FROM books GROUP BY author;

SELECT * FROM users WHERE email REGEXP '^[a-z]+@gmail';
SELECT REGEXP_REPLACE('foo-bar', '-', '_');

SELECT * FROM shirts WHERE FIND_IN_SET('red', colors) > 0;

SELECT SUBSTRING_INDEX('www.example.com', '.', 2);
-- 'www.example'
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain FROM users;

SELECT LPAD(id, 6, '0') AS padded FROM orders;
-- '000042'

EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 100;

CREATE INDEX idx_multi ON orders(user_id, created_at);
-- Leftmost prefix rule applies

ALTER TABLE posts ADD FULLTEXT INDEX ft_idx(title, body);
SELECT * FROM posts
WHERE MATCH(title, body) AGAINST('database' IN BOOLEAN MODE);

SELECT * FROM orders FORCE INDEX (idx_date)
WHERE created_at > '2026-01-01';

ALTER TABLE users ALTER INDEX idx_name INVISIBLE;
-- Test performance without dropping
ALTER TABLE users ALTER INDEX idx_name VISIBLE;

DELIMITER //
CREATE PROCEDURE get_user(IN uid INT)
BEGIN
  SELECT * FROM users WHERE id = uid;
END //
DELIMITER ;
CALL get_user(1);

CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(101) DETERMINISTIC
RETURN CONCAT(first, ' ', last);

CREATE TRIGGER before_insert_user
BEFORE INSERT ON users FOR EACH ROW
SET NEW.created_at = NOW();

CREATE EVENT cleanup_logs
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM logs WHERE ts < NOW() - INTERVAL 30 DAY;

SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;

SHOW TABLE STATUS LIKE 'users';
SELECT table_name, table_rows, data_length
FROM information_schema.tables WHERE table_schema = DATABASE();

CREATE USER 'app'@'%' IDENTIFIED BY 'secret';
GRANT SELECT, INSERT ON mydb.* TO 'app'@'%';
SHOW GRANTS FOR 'app'@'%';

mysqldump -u root -p mydb > backup.sql
mysqldump --single-transaction mydb | gzip > backup.sql.gz
mysql -u root -p mydb < backup.sql

SHOW VARIABLES LIKE 'innodb_buffer%';
SET GLOBAL max_connections = 200;
SET SESSION wait_timeout = 300;