Cheat sheet

MySQL cheat sheet

Q: What's the difference between TIMESTAMP and DATETIME in MySQL?

TIMESTAMP auto-converts to/from UTC based on the session timezone, range 1970-2038. DATETIME stores a fixed wall-clock value with no timezone awareness, range 1000-9999. Most teams prefer DATETIME plus an application-side UTC convention to avoid the auto-conversion gotcha.

Q: Why does GROUP_CONCAT return truncated text?

MySQL caps the result at 1024 bytes by default, via group_concat_max_len. Raise it: SET SESSION group_concat_max_len = 1000000. Or use JSON_ARRAYAGG which doesn't have the same limit. The truncation is silent, easy to miss until you notice values ending mid-word.

Q: Why is not concatenation in MySQL?

By default, is logical OR in MySQL, so 'a' 'b' returns 1. Use CONCAT(a, b, c) for concatenation. Or set SQL_MODE='PIPES_AS_CONCAT' to make behave per ANSI standard, but the safer choice is to always use CONCAT for portability.

Q: How do I reset AUTO_INCREMENT?

ALTER TABLE t AUTO_INCREMENT = 1, but it's rarely a good idea on production tables. The next insert resumes from MAX(id) + 1 (or higher), not from 1, if existing IDs would collide. For testing/empty tables, TRUNCATE TABLE t is the cleanest reset.

Q: How do I see the SQL that created a table?

SHOW CREATE TABLE table_name returns the original CREATE TABLE statement, perfect for migrations, copies, or understanding existing indexes/constraints. SHOW CREATE VIEW, SHOW CREATE PROCEDURE, etc. work similarly. One of MySQL's most useful commands.

MySQL-specific syntax and the gotchas that catch people coming from Postgres or SQL Server: AUTO_INCREMENT, ON DUPLICATE KEY UPDATE, GROUP_CONCAT, STR_TO_DATE, || defaulting to OR, TIMESTAMP auto-converting to UTC, and the InnoDB / FK behavior.

Type system & auto-increment #

sql

-- Auto-incrementing primary key.
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY    -- safer for tables that may grow

-- Get last insert ID.
SELECT LAST_INSERT_ID();                          -- after INSERT

-- Common types.
TINYINT (1B), SMALLINT (2B), MEDIUMINT (3B), INT (4B), BIGINT (8B)  -- each has UNSIGNED variant
DATETIME (no TZ, fixed wall clock)
TIMESTAMP (auto-converts to UTC for storage, back to session TZ on read)
JSON                                              -- native, indexable via expression indexes
TEXT, MEDIUMTEXT, LONGTEXT                        -- long strings (different from VARCHAR)
ENUM('a','b','c')                                 -- restricted string set

Upsert: ON DUPLICATE KEY UPDATE #

sql

INSERT INTO users (id, email)
VALUES (1, 'a@x.com')
ON DUPLICATE KEY UPDATE
  email = VALUES(email),                          -- 'VALUES(col)' references the inserted value
  updated_at = NOW();

-- Skip duplicates entirely (no update).
INSERT IGNORE INTO users (id, email) VALUES (1, 'a@x.com');

-- Replace existing row entirely (deletes + inserts).
REPLACE INTO users (id, email) VALUES (1, 'a@x.com');     -- careful: triggers DELETE + INSERT events

Date functions (where MySQL diverges) #

sql

NOW()                              -- current timestamp (server TZ for TIMESTAMP, fixed for DATETIME)
CURRENT_DATE                       -- today
CURDATE()                          -- alias for CURRENT_DATE
UTC_TIMESTAMP()                    -- UTC explicitly

YEAR(d), MONTH(d), DAY(d)          -- shortcuts; equivalent to EXTRACT
DAYOFWEEK(d)                       -- 1=Sunday, 7=Saturday (ISO is different)
WEEKDAY(d)                         -- 0=Monday, 6=Sunday

DATE_ADD(d, INTERVAL 7 DAY)
DATE_SUB(d, INTERVAL 1 MONTH)
DATEDIFF(end, start)               -- days; (end, start) order, NOT (unit, start, end)
TIMESTAMPDIFF(MONTH, start, end)   -- for non-day units; (unit, start, end)

-- No DATE_TRUNC! Workaround:
DATE_FORMAT(d, '%Y-%m-01')         -- first of month
LAST_DAY(d)                        -- last day of month (built-in)

-- Format / parse.
DATE_FORMAT(d, '%Y-%m-%d %H:%i:%s')   -- format date as string
STR_TO_DATE('26/04/2026', '%d/%m/%Y') -- parse string

String functions (the MySQL-specific ones) #

sql

CONCAT(a, b, c)                    -- USE THIS — || is OR by default in MySQL
CONCAT_WS(', ', city, state, zip)  -- with separator, skips NULL

GROUP_CONCAT(col ORDER BY col SEPARATOR ', ')   -- aggregate strings
SET SESSION group_concat_max_len = 1000000;     -- raise the 1024-byte default!

LOCATE('sub', s)                   -- find position
SUBSTRING_INDEX(s, ',', 2)         -- everything up to nth delimiter

REGEXP_LIKE(s, 'pattern')          -- 8.0+
REGEXP_REPLACE(s, 'pattern', '?')  -- 8.0+

JSON #

sql

-- Extract.
JSON_EXTRACT(payload, '$.user.id')
payload -> '$.user.id'             -- shorthand (returns JSON)
payload ->> '$.user.id'            -- shorthand unquote (returns text)

-- Modify.
JSON_SET(payload, '$.user.plan', 'pro')
JSON_REMOVE(payload, '$.tmp_field')

-- Build.
JSON_OBJECT('id', id, 'email', email)
JSON_ARRAYAGG(name)                -- aggregate to array
JSON_OBJECTAGG(key, value)         -- aggregate to object

LIMIT and pagination #

sql

SELECT * FROM users LIMIT 20;
SELECT * FROM users LIMIT 20 OFFSET 40;       -- skip 40, take 20
SELECT * FROM users LIMIT 40, 20;             -- MySQL shorthand: offset, count

-- DELETE / UPDATE with LIMIT (handy for batched migrations).
DELETE FROM old_logs WHERE created_at < '2024-01-01' LIMIT 10000;
UPDATE users SET status = 'archived' WHERE last_seen < '2024-01-01' LIMIT 1000;

Joins and subqueries #

sql

-- UPDATE with JOIN (MySQL-specific syntax).
UPDATE orders o
JOIN   users u ON u.id = o.user_id
SET    o.user_email = u.email;

-- DELETE with JOIN.
DELETE o
FROM   orders o
JOIN   users u ON u.id = o.user_id
WHERE  u.is_test = TRUE;

-- Multi-table UPDATE.
UPDATE a JOIN b ON a.id = b.a_id
SET    a.x = ..., b.y = ...;       -- updating two tables in one statement is MySQL-only

Schema introspection #

sql

SHOW DATABASES;
SHOW TABLES;
SHOW TABLES FROM other_db;
DESCRIBE table_name;               -- or DESC
SHOW CREATE TABLE table_name;      -- the original DDL — extremely useful
SHOW CREATE VIEW view_name;
SHOW INDEX FROM table_name;
SHOW PROCESSLIST;                  -- active queries
KILL <process_id>;                 -- cancel a query

-- Sizes via INFORMATION_SCHEMA.
SELECT
  table_schema, table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 1) AS mb
FROM   information_schema.tables
ORDER  BY data_length + index_length DESC;

MySQL gotchas #

|| is logical OR by default ('a' || 'b' returns 1). Use CONCAT everywhere or set PIPES_AS_CONCAT SQL mode.
TIMESTAMP auto-converts to/from UTC based on session timezone; DATETIME does not. Read the TIMESTAMP vs DATETIME page if this is news.
GROUP_CONCAT truncates at 1024 bytes by default, silently. Set group_concat_max_len higher.
DDL is implicit-commit on InnoDB. You cannot ROLLBACK a CREATE TABLE inside a transaction. Don't mix DDL and DML in one transaction on MySQL.
TRUNCATE TABLE is non-transactional on InnoDB, irreversible once it runs. Treat as permanent.
Foreign keys not enforced on MyISAM, only InnoDB. Default storage engine since 5.5 is InnoDB; you usually don't have to think about this.
ENUM types are convenient but rigid: adding a value requires ALTER TABLE, which can be slow on huge tables. Many teams prefer a lookup table.

FAQ #

How do I do an upsert in MySQL?

INSERT INTO table (id, value) VALUES (1, 'x') ON DUPLICATE KEY UPDATE value = VALUES(value) is MySQL's native upsert. It triggers when any unique key collision occurs (not just the primary key). Use INSERT IGNORE to silently skip duplicates instead.

What's the difference between TIMESTAMP and DATETIME in MySQL?

TIMESTAMP auto-converts to/from UTC based on the session timezone, range 1970-2038. DATETIME stores a fixed wall-clock value with no timezone awareness, range 1000-9999. Most teams prefer DATETIME plus an application-side UTC convention to avoid the auto-conversion gotcha.

Why does GROUP_CONCAT return truncated text?

MySQL caps the result at 1024 bytes by default, via group_concat_max_len. Raise it: SET SESSION group_concat_max_len = 1000000. Or use JSON_ARRAYAGG which doesn't have the same limit. The truncation is silent, easy to miss until you notice values ending mid-word.

Why is || not concatenation in MySQL?

By default, || is logical OR in MySQL, so 'a' || 'b' returns 1. Use CONCAT(a, b, c) for concatenation. Or set SQL_MODE='PIPES_AS_CONCAT' to make || behave per ANSI standard, but the safer choice is to always use CONCAT for portability.

How do I reset AUTO_INCREMENT?

ALTER TABLE t AUTO_INCREMENT = 1, but it's rarely a good idea on production tables. The next insert resumes from MAX(id) + 1 (or higher), not from 1, if existing IDs would collide. For testing/empty tables, TRUNCATE TABLE t is the cleanest reset.

How do I see the SQL that created a table?

SHOW CREATE TABLE table_name returns the original CREATE TABLE statement, perfect for migrations, copies, or understanding existing indexes/constraints. SHOW CREATE VIEW, SHOW CREATE PROCEDURE, etc. work similarly. One of MySQL's most useful commands.

Tip

MySQL's SHOW CREATE TABLE is one of the underrated commands in any database. It returns the exact DDL that created the table: a perfect starting point for migrations, copies, or just understanding what indexes / constraints / engine settings exist. SHOW CREATE VIEW does the same for views.