man mysql

MySQL Cheatsheet

MySQL 8.4 LTS on one page: connecting and logging in, databases and tables, accounts and grants, inserts, upserts, updates and deletes, filtering, joins and set operations, built-in functions, mysqldump backups and CSV import/export, client output options, indexes and EXPLAIN, sessions and transactions, JSON, TiDB-only extras, common pitfalls and quick answers. SQL examples run through mysql heredocs against a store database (credentials from ~/.my.cnf or a login path), so each block can be pasted as-is; outputs are shown only where the result follows exactly from the example itself.

Showing 204 of 204 commands

Connect & log in

14 commands

Open a session with the mysql client: local socket or TCP, remote hosts, TLS, stored login paths and one-shot statements. The server listens on port 3306 unless told otherwise.

mysql -u root -p beginner Log in to the server on this machine as root; the client asks for the password instead of taking it on the command line.

Example

bash
mysql -u root -p
mysql -u webapp -p store beginner Log in and make store the default database straight away, so table names need no prefix.

Example

bash
mysql -u webapp -p store
mysql -h HOST -P PORT beginner Reach a server on another machine or a non-standard port.

Example

bash
mysql -h db.internal -P 3306 -u webapp -p
mysql -h 127.0.0.1 intermediate Writing localhost makes the client use the Unix socket; an IP address forces a real TCP connection, which is what you want when testing firewall rules or a container port.

Example

bash
mysql -h 127.0.0.1 -P 3306 -u root -p

Tip: --protocol=TCP does the same thing while still letting you write localhost.

mysql -S /path/to/socket intermediate Connect through a particular Unix socket file, handy when several servers run on one host.

Example

bash
mysql -S /var/run/mysqld/mysqld.sock -u root -p
mysql --ssl-mode=VERIFY_IDENTITY advanced Require TLS and check both the certificate chain and that the host name matches it, the strictest client-side setting.

Example

bash
mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=ca.pem -h db.example.com -u webapp -p

Tip: REQUIRED only encrypts; VERIFY_CA adds the chain check; VERIFY_IDENTITY also stops a man-in-the-middle with a valid certificate for another host.

mysql_config_editor set intermediate Store host, user and password for a named login path in ~/.mylogin.cnf, an obfuscated file, so scripts never carry the password.

Example

bash
mysql_config_editor set --login-path=prod --host=db.internal --user=webapp --password
mysql --login-path=NAME intermediate Connect with the credentials saved under a login path.

Example

bash
mysql --login-path=prod store
MYSQL_PWD intermediate Passing the password through this environment variable still works but is deprecated: any user who can read the process environment can see it.

Example

bash
# avoid in new scripts
MYSQL_PWD='s3cret' mysql -u webapp store

Tip: Use a login path or an option file with mode 600 instead.

mysql -e "SQL" beginner Run a single statement and return to the shell, which is what cron jobs and scripts usually need.

Example

bash
mysql -u webapp -p -e "SELECT NOW()"
SELECT CURRENT_USER(), DATABASE(); beginner Confirm which account the server matched you to and which database is selected.

Example

bash
mysql store <<'SQL'
SELECT CURRENT_USER(), DATABASE();
SQL

Tip: CURRENT_USER() is the account that was authenticated, such as webapp@%, which can differ from the name you typed (USER()).

\s beginner Print a status block for the session: server version, connection id, current user, TLS cipher and character sets. The long form is status.

Example

bash
mysql <<'SQL'
status
SQL
exit beginner Close the client; \q, quit and Ctrl+D do the same.

Example

bash
exit
Worked example: saved login over TLS intermediate Save a production login path once, then connect with certificate checks and no password typed at the prompt.

Example

bash
mysql_config_editor set --login-path=prod --host=db.example.com --user=webapp --password
mysql --login-path=prod --ssl-mode=VERIFY_IDENTITY --ssl-ca=rds-ca.pem store
mysql_config_editor print --all

Tip: print --all lists every login path with the password masked as *****.

Databases

11 commands

List, create, inspect, switch and remove databases (MySQL also calls them schemas).

SHOW DATABASES; beginner List every database your account has some privilege on.

Example

bash
mysql <<'SQL'
SHOW DATABASES;
SQL
SHOW DATABASES LIKE 'pattern'; beginner Filter the list with a LIKE pattern: % matches any run of characters.

Example

bash
mysql <<'SQL'
SHOW DATABASES LIKE 'store%';
SQL
CREATE DATABASE beginner Create an empty database that takes the server's default character set and collation.

Example

bash
mysql <<'SQL'
CREATE DATABASE store;
SQL
CREATE DATABASE IF NOT EXISTS ... CHARACTER SET beginner Create a database only when it is missing, pinning the charset and collation so the result does not depend on server defaults.

Example

bash
mysql <<'SQL'
CREATE DATABASE IF NOT EXISTS store
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SQL
USE db; beginner Make another database the default for the statements that follow.

Example

bash
mysql <<'SQL'
USE store;
SHOW TABLES;
SQL
SELECT DATABASE(); beginner Show which database is currently selected; NULL means none.

Example

bash
mysql store <<'SQL'
SELECT DATABASE();
SQL
DATABASE()
store
SHOW CREATE DATABASE intermediate Print the statement that would recreate the database, including its charset, collation and encryption flag.

Example

bash
mysql <<'SQL'
SHOW CREATE DATABASE store;
SQL
ALTER DATABASE ... CHARACTER SET intermediate Change the default charset for tables created from now on; existing tables keep theirs.

Example

bash
mysql <<'SQL'
ALTER DATABASE store CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SQL

Tip: Convert an existing table with ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4.

DROP DATABASE beginner Destructive: remove the database together with every table, view and routine inside it.

Example

bash
mysql <<'SQL'
DROP DATABASE store_old;
SQL

Tip: There is no confirmation and no undo; take a mysqldump first.

DROP DATABASE IF EXISTS beginner Destructive: same as above, but quietly does nothing when the database is not there, which suits teardown scripts.

Example

bash
mysql <<'SQL'
DROP DATABASE IF EXISTS store_test;
SQL
Worked example: rename a database intermediate MySQL has no RENAME DATABASE. Create the target, move each table across with RENAME TABLE (a metadata-only operation), then drop the empty source.

Example

bash
mysql <<'SQL'
CREATE DATABASE shopfront CHARACTER SET utf8mb4;
RENAME TABLE store.customers TO shopfront.customers,
             store.orders    TO shopfront.orders;
SHOW TABLES FROM store;
DROP DATABASE store;
SQL

Tip: Views, triggers, routines and grants that name the old database have to be recreated by hand.

Tables

17 commands

Inspect table structure and change it: columns, constraints, renames, truncation and drops. The worked example creates the customers and orders tables used throughout this page.

Worked example: sample schema beginner The two tables every other example on this page queries.

Example

bash
mysql store <<'SQL'
CREATE TABLE customers (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  full_name  VARCHAR(120) NOT NULL,
  tier       ENUM('basic', 'plus', 'business') NOT NULL DEFAULT 'basic',
  balance    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,
  customer_id BIGINT UNSIGNED NOT NULL,
  amount      DECIMAL(10, 2) NOT NULL,
  state       VARCHAR(20) NOT NULL DEFAULT 'pending',
  details     JSON NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);
SQL
SHOW TABLES; beginner List the tables and views in the current database.

Example

bash
mysql store <<'SQL'
SHOW TABLES;
SQL
SHOW TABLES FROM db LIKE 'pattern'; beginner List tables from a different database, filtered by a name pattern.

Example

bash
mysql <<'SQL'
SHOW TABLES FROM store LIKE 'ord%';
SQL
SHOW FULL TABLES WHERE Table_type = 'VIEW'; intermediate The FULL keyword adds a Table_type column, so you can pick out only the views.

Example

bash
mysql store <<'SQL'
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SQL
DESCRIBE table; beginner One row per column with its type, whether it allows NULL, key membership, default and extras such as auto_increment.

Example

bash
mysql store <<'SQL'
DESCRIBE customers;
SQL

Tip: DESC customers and SHOW COLUMNS FROM customers are synonyms.

SHOW FULL COLUMNS FROM table; intermediate Same as DESCRIBE plus each column's collation, privileges and comment.

Example

bash
mysql store <<'SQL'
SHOW FULL COLUMNS FROM customers;
SQL
SHOW CREATE TABLE table\G beginner The complete CREATE TABLE statement, with indexes, foreign keys and table options; \G prints it vertically so it is readable.

Example

bash
mysql store <<'SQL'
SHOW CREATE TABLE customers\G
SQL
SHOW TABLE STATUS LIKE 'table'\G intermediate Storage engine, estimated row count, data and index size, auto-increment counter and timestamps for a table.

Example

bash
mysql store <<'SQL'
SHOW TABLE STATUS LIKE 'orders'\G
SQL

Tip: InnoDB row counts here are estimates; use SELECT COUNT(*) when you need the exact number.

CREATE TABLE new LIKE old; beginner Make an empty clone with identical columns and indexes, but no rows.

Example

bash
mysql store <<'SQL'
CREATE TABLE customers_backup LIKE customers;
SQL

Tip: Follow it with INSERT INTO customers_backup SELECT * FROM customers to copy the data too.

ALTER TABLE ... ADD COLUMN ... AFTER beginner Add a column and choose where it appears in the column order (FIRST is also allowed).

Example

bash
mysql store <<'SQL'
ALTER TABLE customers ADD COLUMN last_seen DATETIME NULL AFTER email;
SQL
ALTER TABLE ... MODIFY intermediate Redefine a column's type, length or NULL rule; the whole definition must be restated.

Example

bash
mysql store <<'SQL'
ALTER TABLE customers MODIFY full_name VARCHAR(200) NOT NULL;
SQL

Tip: Leaving out NOT NULL or DEFAULT in MODIFY silently drops them.

ALTER TABLE ... RENAME COLUMN beginner Give a column a new name without touching its type or attributes.

Example

bash
mysql store <<'SQL'
ALTER TABLE customers RENAME COLUMN full_name TO display_name;
SQL
ALTER TABLE ... DROP COLUMN beginner Destructive: delete a column and all the values stored in it.

Example

bash
mysql store <<'SQL'
ALTER TABLE customers DROP COLUMN last_seen;
SQL
ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY intermediate Link a child column to a parent key; ON DELETE CASCADE removes children when their parent row goes.

Example

bash
mysql store <<'SQL'
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer_cascade
  FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
SQL

Tip: Both columns need the same type and signedness, and InnoDB creates an index on the child column if one is missing.

RENAME TABLE old TO new; beginner Rename one or more tables in a single atomic statement.

Example

bash
mysql store <<'SQL'
RENAME TABLE customers_backup TO customers_2026;
SQL
TRUNCATE TABLE intermediate Destructive: empty a table almost instantly and reset its AUTO_INCREMENT counter.

Example

bash
mysql store <<'SQL'
TRUNCATE TABLE audit_log;
SQL

Tip: It is DDL: it commits implicitly, cannot be rolled back and fails if another table's foreign key points at it.

DROP TABLE IF EXISTS beginner Destructive: delete the table definition and its data.

Example

bash
mysql store <<'SQL'
DROP TABLE IF EXISTS customers_2026;
SQL

Users & privileges

15 commands

Accounts, passwords and grants. An account is a user name paired with a host pattern, so 'webapp'@'localhost' and 'webapp'@'%' are two separate accounts with separate passwords and rights.

SELECT user, host, plugin FROM mysql.user; beginner List every account and the authentication plugin it uses.

Example

bash
mysql <<'SQL'
SELECT user, host, plugin FROM mysql.user;
SQL

Tip: MySQL 8.4 defaults to caching_sha2_password; mysql_native_password is disabled unless you turn it back on.

CREATE USER ... @'%' beginner Create an account that may connect from any host.

Example

bash
mysql <<'SQL'
CREATE USER 'webapp'@'%' IDENTIFIED BY 'Ch4nge!me';
SQL
CREATE USER ... @'10.0.%' intermediate Limit where an account can log in from with a host pattern such as a subnet.

Example

bash
mysql <<'SQL'
CREATE USER 'analyst'@'10.0.%' IDENTIFIED BY 'Ch4nge!me';
SQL
GRANT ALL PRIVILEGES ON db.* beginner Give an account every privilege on one database (but not GRANT OPTION).

Example

bash
mysql <<'SQL'
GRANT ALL PRIVILEGES ON store.* TO 'webapp'@'%';
SQL
GRANT SELECT ON db.* beginner Read-only access to every table in a database.

Example

bash
mysql <<'SQL'
GRANT SELECT ON store.* TO 'analyst'@'10.0.%';
SQL
GRANT ... ON db.table intermediate Grant specific privileges on a single table.

Example

bash
mysql <<'SQL'
GRANT SELECT, INSERT, UPDATE ON store.orders TO 'webapp'@'%';
SQL
SHOW GRANTS FOR account; beginner List the GRANT statements that describe what an account may do.

Example

bash
mysql <<'SQL'
SHOW GRANTS FOR 'webapp'@'%';
SQL
SHOW GRANTS; beginner Show the privileges of the account you are logged in as.

Example

bash
mysql <<'SQL'
SHOW GRANTS;
SQL
REVOKE ... FROM intermediate Withdraw privileges that were granted earlier.

Example

bash
mysql <<'SQL'
REVOKE INSERT, UPDATE ON store.orders FROM 'webapp'@'%';
SQL
ALTER USER ... IDENTIFIED BY beginner Set a new password for an account.

Example

bash
mysql <<'SQL'
ALTER USER 'webapp'@'%' IDENTIFIED BY 'N3w!secret';
SQL

Tip: To change your own password use ALTER USER USER() IDENTIFIED BY '...'.

ALTER USER ... ACCOUNT LOCK intermediate Stop an account from logging in while keeping its grants, so it can be unlocked later with ACCOUNT UNLOCK.

Example

bash
mysql <<'SQL'
ALTER USER 'webapp'@'%' ACCOUNT LOCK;
SQL
RENAME USER intermediate Change an account's name or host part; its privileges move with it.

Example

bash
mysql <<'SQL'
RENAME USER 'webapp'@'%' TO 'api'@'%';
SQL
DROP USER beginner Destructive: delete an account and every privilege attached to it.

Example

bash
mysql <<'SQL'
DROP USER 'analyst'@'10.0.%';
SQL
FLUSH PRIVILEGES; intermediate Reload the grant tables from disk. Only needed after editing mysql.user and friends directly; CREATE USER, GRANT and ALTER USER take effect immediately.

Example

bash
mysql <<'SQL'
FLUSH PRIVILEGES;
SQL
Worked example: read-only reporting account intermediate Create a login for analysts that can only read store and only from the office network, then confirm exactly what it received.

Example

bash
mysql <<'SQL'
CREATE USER 'analyst'@'10.0.%' IDENTIFIED BY 'R3ad!only';
GRANT SELECT ON store.* TO 'analyst'@'10.0.%';
SHOW GRANTS FOR 'analyst'@'10.0.%';
SQL
Grants for analyst@10.0.%
GRANT USAGE ON *.* TO `analyst`@`10.0.%`
GRANT SELECT ON `store`.* TO `analyst`@`10.0.%`

Tip: USAGE means "no global privileges"; it is always listed.

Insert, update & delete

14 commands

Add, change and remove rows: multi-row inserts, INSERT ... SELECT, upserts, joined updates and deletes, and batched cleanup.

INSERT INTO ... VALUES beginner Add a single row, naming the columns you supply; the rest take their defaults.

Example

bash
mysql store <<'SQL'
INSERT INTO customers (email, full_name) VALUES ('ada@example.com', 'Ada');
SQL
INSERT ... VALUES (...), (...) beginner Insert several rows in one statement, far quicker than one statement per row.

Example

bash
mysql store <<'SQL'
INSERT INTO customers (email, full_name)
VALUES ('bo@example.com', 'Bo'), ('cy@example.com', 'Cy');
SQL
INSERT INTO ... SELECT intermediate Insert whatever a query returns, for copies and archive tables.

Example

bash
mysql store <<'SQL'
INSERT INTO customers_backup SELECT * FROM customers WHERE tier = 'basic';
SQL
SELECT LAST_INSERT_ID(); beginner The AUTO_INCREMENT value generated by this connection's most recent insert. After a multi-row insert it is the id of the first row.

Example

bash
mysql store <<'SQL'
INSERT INTO customers (email, full_name) VALUES ('dee@example.com', 'Dee');
SELECT LAST_INSERT_ID();
SQL

Tip: It is per connection, so other sessions inserting at the same time do not affect it.

INSERT IGNORE intermediate Skip rows that would break a unique or primary key, turning the error into a warning.

Example

bash
mysql store <<'SQL'
INSERT IGNORE INTO customers (email, full_name) VALUES ('ada@example.com', 'Ada');
SHOW WARNINGS;
SQL

Tip: IGNORE also downgrades other errors such as bad values, so data can be truncated silently.

INSERT ... AS new ON DUPLICATE KEY UPDATE intermediate Upsert: insert the row, or if the key already exists update the existing row using the alias for the incoming values.

Example

bash
mysql store <<'SQL'
INSERT INTO customers (email, full_name) VALUES ('ada@example.com', 'Ada Lovelace') AS new
ON DUPLICATE KEY UPDATE full_name = new.full_name;
SQL

Tip: The older VALUES(full_name) form still runs but is deprecated; use the row alias (MySQL 8.0.19+).

REPLACE INTO intermediate Destructive: when a key collides, the old row is deleted and a fresh one inserted, so columns you omit fall back to defaults.

Example

bash
mysql store <<'SQL'
REPLACE INTO customers (id, email, full_name) VALUES (1, 'ada@example.com', 'Ada');
SQL

Tip: See Pitfalls: this is not an upsert.

UPDATE ... SET ... WHERE beginner Change values in the rows the WHERE clause selects.

Example

bash
mysql store <<'SQL'
UPDATE customers SET tier = 'plus' WHERE id = 1;
SQL
UPDATE with a date cutoff intermediate Combine conditions with date arithmetic, for example to expire stale pending orders.

Example

bash
mysql store <<'SQL'
UPDATE orders SET state = 'cancelled'
WHERE state = 'pending' AND created_at < NOW() - INTERVAL 7 DAY;
SQL
UPDATE ... JOIN advanced Update one table based on matching rows in another.

Example

bash
mysql store <<'SQL'
UPDATE orders o
JOIN customers c ON c.id = o.customer_id
SET o.state = 'review'
WHERE c.tier = 'basic';
SQL
DELETE FROM ... WHERE beginner Remove the rows that match.

Example

bash
mysql store <<'SQL'
DELETE FROM orders WHERE id = 42;
SQL
DELETE ... LIMIT intermediate Delete in chunks so each statement holds locks briefly and replication does not fall behind; repeat until it affects 0 rows.

Example

bash
mysql store <<'SQL'
DELETE FROM audit_log WHERE created_at < '2026-01-01' LIMIT 10000;
SQL
DELETE alias FROM ... JOIN advanced Delete rows from one table chosen through a join with another.

Example

bash
mysql store <<'SQL'
DELETE o FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email LIKE '%@test.local';
SQL
Worked example: idempotent daily rollup advanced Record each customer's order count for today with one upsert; running it again just refreshes the numbers. A derived table supplies the alias the UPDATE part reads from.

Example

bash
mysql store <<'SQL'
CREATE TABLE IF NOT EXISTS daily_orders (
  customer_id BIGINT UNSIGNED, day DATE, orders INT,
  PRIMARY KEY (customer_id, day)
);
INSERT INTO daily_orders (customer_id, day, orders)
SELECT * FROM (
  SELECT customer_id, DATE(created_at) AS day, COUNT(*) AS n
  FROM orders
  WHERE created_at >= CURDATE()
  GROUP BY customer_id, DATE(created_at)
) AS t
ON DUPLICATE KEY UPDATE orders = t.n;
SQL

Select & filter

17 commands

Read rows: WHERE conditions, patterns and ranges, NULL handling, sorting, paging, grouping, conditional columns, subqueries and CTEs.

SELECT ... WHERE col = value beginner Return chosen columns from rows that match a condition.

Example

bash
mysql store <<'SQL'
SELECT id, email FROM customers WHERE tier = 'plus';
SQL
WHERE col IN (...) beginner Match any value from a list.

Example

bash
mysql store <<'SQL'
SELECT * FROM customers WHERE tier IN ('plus', 'business');
SQL
WHERE col LIKE 'pattern' beginner Simple pattern matching: % stands for any run of characters, _ for exactly one.

Example

bash
mysql store <<'SQL'
SELECT * FROM customers WHERE email LIKE '%@example.com';
SQL

Tip: A leading % stops MySQL from using an index on the column.

WHERE col NOT LIKE 'pattern' beginner Keep the rows that do not fit a pattern.

Example

bash
mysql store <<'SQL'
SELECT * FROM customers WHERE email NOT LIKE '%@test.local';
SQL
WHERE col REGEXP 'regex' intermediate Match with a regular expression (ICU syntax in MySQL 8).

Example

bash
mysql store <<'SQL'
SELECT * FROM customers WHERE email REGEXP '^[a-c]';
SQL
WHERE col BETWEEN a AND b beginner Range test that includes both ends.

Example

bash
mysql store <<'SQL'
SELECT * FROM orders
WHERE created_at BETWEEN '2026-10-01' AND '2026-10-31 23:59:59';
SQL

Tip: For datetimes a half-open range (>= start AND < next day) is safer than guessing the last second.

WHERE col IS NULL beginner Test for missing values. col = NULL is never true, so it always returns nothing.

Example

bash
mysql store <<'SQL'
SELECT * FROM orders WHERE details IS NULL;
SQL
WHERE col <> value beginner Not equal; != is accepted too. Rows where col is NULL are not returned.

Example

bash
mysql store <<'SQL'
SELECT * FROM customers WHERE full_name <> 'Ada';
SQL
ORDER BY a DESC, b DESC beginner Sort by several keys; later columns break ties in earlier ones.

Example

bash
mysql store <<'SQL'
SELECT * FROM orders ORDER BY created_at DESC, id DESC;
SQL
LIMIT n OFFSET m beginner Paging: skip m rows and return the next n. This returns page 3 at 20 rows per page.

Example

bash
mysql store <<'SQL'
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 40;
SQL

Tip: Large offsets still read and discard the skipped rows; for deep pages use WHERE id > last_seen_id ORDER BY id LIMIT 20.

SELECT DISTINCT beginner Collapse duplicate values so each appears once.

Example

bash
mysql store <<'SQL'
SELECT DISTINCT state FROM orders;
SQL
GROUP BY with COUNT and SUM beginner Aggregate per group: how many orders and how much revenue in each state.

Example

bash
mysql store <<'SQL'
SELECT state, COUNT(*) AS n, SUM(amount) AS revenue
FROM orders GROUP BY state;
SQL
GROUP BY ... HAVING intermediate Filter groups after aggregation, which WHERE cannot do.

Example

bash
mysql store <<'SQL'
SELECT customer_id, COUNT(*) AS n
FROM orders GROUP BY customer_id HAVING COUNT(*) > 5;
SQL
GROUP BY ... WITH ROLLUP intermediate Append a summary row whose group column is NULL and whose aggregates cover everything.

Example

bash
mysql store <<'SQL'
SELECT state, COUNT(*) FROM orders GROUP BY state WITH ROLLUP;
SQL

Tip: Wrap the column in GROUPING(state) to tell the rollup row apart from a real NULL group.

CASE WHEN ... THEN ... ELSE ... END intermediate Compute a column from conditions, like an if/else chain.

Example

bash
mysql store <<'SQL'
SELECT id,
       CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END AS size
FROM orders;
SQL
WHERE EXISTS (subquery) intermediate Keep rows that have at least one related row elsewhere, here customers who ordered.

Example

bash
mysql store <<'SQL'
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
SQL
Worked example: top spenders with a CTE intermediate Name an intermediate result with WITH, then join it back to customers to list October's five biggest buyers.

Example

bash
mysql store <<'SQL'
WITH october AS (
  SELECT customer_id, SUM(amount) AS spent, COUNT(*) AS orders
  FROM orders
  WHERE state = 'paid'
    AND created_at >= '2026-10-01' AND created_at < '2026-11-01'
  GROUP BY customer_id
)
SELECT c.email, o.spent, o.orders
FROM october o JOIN customers c ON c.id = o.customer_id
ORDER BY o.spent DESC
LIMIT 5;
SQL

Joins & set operations

11 commands

Combine tables side by side with joins, or stack result sets with UNION, INTERSECT and EXCEPT. Examples use a second table, prospects (email, source), for leads that may or may not be customers yet.

JOIN ... ON beginner Inner join: only pairs of rows that match on both sides.

Example

bash
mysql store <<'SQL'
SELECT c.email, o.amount
FROM customers c JOIN orders o ON o.customer_id = c.id;
SQL
LEFT JOIN beginner Every row from the left table; columns from the right are NULL where nothing matches.

Example

bash
mysql store <<'SQL'
SELECT c.email, o.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;
SQL
LEFT JOIN ... WHERE right.id IS NULL intermediate Anti-join: left rows with no partner, here customers who never ordered.

Example

bash
mysql store <<'SQL'
SELECT c.*
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
SQL
RIGHT JOIN intermediate Mirror of LEFT JOIN that keeps every row from the right table; most people just swap the tables and use LEFT JOIN.

Example

bash
mysql store <<'SQL'
SELECT *
FROM orders o RIGHT JOIN customers c ON c.id = o.customer_id;
SQL
JOIN ... USING (col) intermediate Shorthand when both tables share the join column's name; the column appears once in SELECT *.

Example

bash
mysql store <<'SQL'
SELECT * FROM customers JOIN prospects USING (email);
SQL
CROSS JOIN intermediate Cartesian product: every row of one table paired with every row of the other.

Example

bash
mysql store <<'SQL'
SELECT c.email, t.tier
FROM customers c
CROSS JOIN (SELECT 'basic' AS tier UNION ALL SELECT 'plus') AS t;
SQL
UNION beginner Stack two results and drop duplicate rows; column counts and types must line up.

Example

bash
mysql store <<'SQL'
SELECT email FROM customers
UNION
SELECT email FROM prospects;
SQL
UNION ALL beginner Stack results and keep every row; cheaper than UNION because there is no de-duplication step.

Example

bash
mysql store <<'SQL'
SELECT email FROM customers
UNION ALL
SELECT email FROM prospects;
SQL
INTERSECT intermediate Rows present in both results (MySQL 8.0.31 and later).

Example

bash
mysql store <<'SQL'
SELECT email FROM customers
INTERSECT
SELECT email FROM prospects;
SQL
EXCEPT intermediate Rows from the first result that are missing from the second (MySQL 8.0.31 and later).

Example

bash
mysql store <<'SQL'
SELECT email FROM customers
EXCEPT
SELECT email FROM prospects;
SQL
Worked example: emulate FULL OUTER JOIN advanced MySQL does not support FULL OUTER JOIN. Glue a LEFT join and a RIGHT join together with UNION to get unmatched rows from both sides.

Example

bash
mysql store <<'SQL'
SELECT c.email, p.source
FROM customers c LEFT JOIN prospects p ON p.email = c.email
UNION
SELECT p.email, p.source
FROM customers c RIGHT JOIN prospects p ON p.email = c.email;
SQL

Built-in functions

14 commands

Everyday date, NULL-handling, conditional, string and numeric functions. Outputs show the tab-separated format the client prints when it reads from a heredoc.

NOW(), CURDATE(), UTC_TIMESTAMP() beginner Current date and time in the session time zone, today's date, and the current time in UTC.

Example

bash
mysql store <<'SQL'
SELECT NOW(), CURDATE(), UTC_TIMESTAMP();
SQL
DATE_FORMAT(date, format) beginner Render a date with format codes. Watch out: %i is minutes and %m is the month number.

Example

bash
mysql store <<'SQL'
SELECT DATE_FORMAT('2026-11-10 14:05:00', '%Y-%m-%d %H:%i') AS formatted;
SQL
formatted
2026-11-10 14:05
DATEDIFF(a, b) beginner Whole days from b to a; time parts are ignored.

Example

bash
mysql store <<'SQL'
SELECT DATEDIFF('2026-10-31', '2026-10-01') AS days;
SQL
days
30
TIMESTAMPDIFF(unit, start, end) intermediate Difference between two moments in the unit you choose: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR and so on.

Example

bash
mysql store <<'SQL'
SELECT TIMESTAMPDIFF(HOUR, '2026-11-09 08:00:00', '2026-11-10 14:00:00') AS hours;
SQL
hours
30
date - INTERVAL n unit beginner Add or subtract time directly in an expression.

Example

bash
mysql store <<'SQL'
SELECT DATE('2026-11-10') - INTERVAL 30 DAY AS cutoff;
SQL
cutoff
2026-10-11
COALESCE(a, b, ...) beginner Return the first argument that is not NULL.

Example

bash
mysql store <<'SQL'
SELECT COALESCE(NULL, NULL, 'anonymous') AS who;
SQL
who
anonymous
IFNULL(value, fallback) beginner Two-argument version of COALESCE: swap a NULL for a default.

Example

bash
mysql store <<'SQL'
SELECT IFNULL(NULL, 0) AS discount;
SQL
discount
0
IF(condition, then, else) beginner Inline conditional for simple two-way choices.

Example

bash
mysql store <<'SQL'
SELECT IF(150 > 100, 'large', 'small') AS size;
SQL
size
large
CONCAT(a, b, ...) beginner Glue strings together. A single NULL argument makes the whole result NULL; CONCAT_WS skips NULLs.

Example

bash
mysql store <<'SQL'
SELECT CONCAT('Ada', ' <', 'ada@example.com', '>') AS contact,
       CONCAT('Ada', NULL) AS broken;
SQL
contact	broken
Ada <ada@example.com>	NULL
SUBSTRING(str, pos, len) beginner Cut out part of a string; positions start at 1.

Example

bash
mysql store <<'SQL'
SELECT SUBSTRING('ada@example.com', 1, 3) AS head;
SQL
head
ada
SUBSTRING_INDEX(str, delim, count) intermediate Text before the nth delimiter, or with a negative count, after the nth one from the end; ideal for pulling the domain out of an email.

Example

bash
mysql store <<'SQL'
SELECT SUBSTRING_INDEX('ada@example.com', '@', -1) AS domain;
SQL
domain
example.com
GROUP_CONCAT(... ORDER BY ... SEPARATOR ...) intermediate Fold a group's values into one delimited string.

Example

bash
mysql store <<'SQL'
SELECT GROUP_CONCAT(v ORDER BY v SEPARATOR ', ') AS joined
FROM (SELECT 'b' AS v UNION ALL SELECT 'a' UNION ALL SELECT 'c') AS t;
SQL
joined
a, b, c

Tip: Output is cut at group_concat_max_len (1024 bytes by default); raise it per session for long lists.

ROUND(x, d) beginner Round to d decimal places; exact DECIMAL values round half away from zero.

Example

bash
mysql store <<'SQL'
SELECT ROUND(19.99 * 1.2, 2) AS gross;
SQL
gross
23.99
Worked example: monthly revenue report intermediate Group orders by month, total the revenue and list which states occurred in each month.

Example

bash
mysql store <<'SQL'
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
       ROUND(SUM(amount), 2) AS revenue,
       GROUP_CONCAT(DISTINCT state ORDER BY state SEPARATOR ', ') AS states
FROM orders
GROUP BY month
ORDER BY month DESC;
SQL

Backup, import & export

14 commands

Logical backups with mysqldump, restoring .sql files, and moving CSV data in and out of tables.

mysqldump --single-transaction db > file.sql beginner Back up one database. --single-transaction takes a consistent InnoDB snapshot instead of locking tables.

Example

bash
mysqldump -u root -p --single-transaction store > store.sql
mysqldump --routines --triggers --events intermediate Include stored procedures, functions, triggers and scheduled events, which a plain dump can leave behind.

Example

bash
mysqldump -u root -p --single-transaction --routines --triggers --events store > store-full.sql

Tip: Triggers are dumped by default; routines and events are not.

mysqldump --databases a b intermediate Dump several databases into one file, with CREATE DATABASE and USE statements so it restores into the same names.

Example

bash
mysqldump -u root -p --databases store crm > two-dbs.sql
mysqldump --all-databases intermediate Back up every database on the server.

Example

bash
mysqldump -u root -p --all-databases --single-transaction > server.sql
mysqldump --no-data beginner Export only the schema: CREATE statements without any rows.

Example

bash
mysqldump -u root -p --no-data store > schema.sql
mysqldump db table --where="..." intermediate Dump a single table, limited to rows matching a condition.

Example

bash
mysqldump -u root -p store orders --where="created_at >= '2026-10-01'" > orders-oct.sql
mysqldump ... | gzip beginner Compress the dump on the fly; SQL text shrinks a lot.

Example

bash
mysqldump -u root -p --single-transaction store | gzip > store.sql.gz
mysql db < file.sql beginner Restore a dump into an existing database.

Example

bash
mysql -u root -p store < store.sql
gunzip < file.sql.gz | mysql beginner Restore a compressed dump without unpacking it to disk first.

Example

bash
gunzip < store.sql.gz | mysql -u root -p store
SOURCE file.sql beginner Run a SQL file from inside a client session.

Example

bash
mysql store <<'SQL'
SOURCE /home/rocky/store.sql
SQL
SELECT ... INTO OUTFILE intermediate Write query results as a CSV file on the database server's filesystem (not your laptop).

Example

bash
mysql store <<'SQL'
SELECT * FROM orders
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
SQL

Tip: Needs the FILE privilege, and the file must not already exist.

SHOW VARIABLES LIKE 'secure_file_priv'; intermediate The only directory INTO OUTFILE and LOAD DATA INFILE are allowed to touch; empty means anywhere, NULL means disabled.

Example

bash
mysql store <<'SQL'
SHOW VARIABLES LIKE 'secure_file_priv';
SQL
LOAD DATA LOCAL INFILE intermediate Load a CSV that sits on the client machine into a table, skipping the header line.

Example

bash
mysql --local-infile=1 store <<'SQL'
LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES (email, full_name);
SQL

Tip: The server must also have local_infile=ON; both sides refuse by default.

Worked example: copy production to staging advanced Stream a dump from one server straight into another with a pipe, so no dump file ever lands on disk.

Example

bash
mysqldump --login-path=prod --single-transaction --routines --triggers store \
  | mysql --login-path=staging store_staging

Client output & options

14 commands

Shape what the mysql client prints: vertical rows, tab-separated batch output, tables, HTML, pagers, session logs, safety switches and built-in help.

\G beginner End a statement with \G instead of ; to print each column on its own line, the readable way to view wide rows.

Example

bash
mysql store <<'SQL'
SELECT * FROM customers WHERE id = 1\G
SQL
mysql -B beginner Batch mode: tab-separated columns without borders, ready for other tools. It is the default whenever output is piped.

Example

bash
mysql -B store -e "SELECT id, email FROM customers"
mysql -B -N beginner Batch output without the header row, for feeding values into shell loops.

Example

bash
mysql -B -N store -e "SELECT email FROM customers" | while read -r email; do echo "$email"; done
mysql -t intermediate Force the boxed table layout even when output goes to a pipe or file.

Example

bash
mysql -t store -e "SELECT id, email FROM customers" > report.txt
mysql --html / --xml intermediate Produce an HTML table or XML document instead of plain text.

Example

bash
mysql --html store -e "SELECT id, email FROM customers" > customers.html
mysql -B ... | tr '\t' ',' intermediate Quick-and-dirty CSV. It breaks as soon as a value contains a comma, tab or newline, so use INTO OUTFILE or a real CSV tool for messy data.

Example

bash
mysql -B store -e "SELECT id, email FROM customers" | tr '\t' ',' > customers.csv
mysql --pager='less -S' intermediate Send results through a pager; -S lets you scroll sideways through wide rows instead of wrapping them.

Example

bash
mysql --pager='less -S' store

Tip: Inside an interactive session the same thing is pager less -S, and nopager switches it off.

mysql --tee=file intermediate Copy everything you type and everything the server returns into a log file. Interactively: tee session.log, then notee.

Example

bash
mysql --tee=session.log store
mysql -v intermediate Echo each statement before its result, useful when running a script and reading the log later.

Example

bash
mysql -v store -e "SELECT 1"
mysql --safe-updates beginner Refuse UPDATE and DELETE statements that have no key column in WHERE and no LIMIT. Also known as --i-am-a-dummy.

Example

bash
mysql --safe-updates store
SHOW WARNINGS; beginner Show the notes and warnings left by the previous statement, such as truncated values or ignored duplicates.

Example

bash
mysql store <<'SQL'
INSERT IGNORE INTO customers (email, full_name) VALUES ('ada@example.com', 'Ada');
SHOW WARNINGS;
SQL
help STATEMENT beginner Built-in syntax reference for any statement or function, served from the server's help tables.

Example

bash
mysql <<'SQL'
help ALTER TABLE
SQL
~/.mysql_history intermediate The client records interactive input here. Lines matching *IDENTIFIED* or *PASSWORD* are skipped by default.

Example

bash
tail -n 20 ~/.mysql_history

Tip: Set MYSQL_HISTFILE=/dev/null to stop recording altogether.

Worked example: CSV report with a header intermediate Export October's orders from the shell in a form a spreadsheet can open, keeping the column names as the first line.

Example

bash
mysql --login-path=prod -B -e "
  SELECT id, customer_id, amount, state, created_at FROM store.orders
  WHERE created_at >= '2026-10-01'" | tr '\t' ',' > orders-october.csv
head -1 orders-october.csv
id,customer_id,amount,state,created_at

Indexes & EXPLAIN

13 commands

Create, hide and drop indexes, then read the optimizer's plan to confirm queries use them.

SHOW INDEX FROM table; beginner Every index on a table with its columns in order, uniqueness and estimated cardinality.

Example

bash
mysql store <<'SQL'
SHOW INDEX FROM orders;
SQL
CREATE INDEX ... (a, b) intermediate Composite index. Column order matters: it serves filters on a, or on a and b, but not on b alone.

Example

bash
mysql store <<'SQL'
CREATE INDEX idx_orders_customer ON orders (customer_id, created_at);
SQL
ADD UNIQUE INDEX beginner Enforce that no two rows share a value, and get an index for lookups at the same time.

Example

bash
mysql store <<'SQL'
ALTER TABLE customers ADD UNIQUE INDEX uq_customers_email (email);
SQL
ALGORITHM=INPLACE, LOCK=NONE advanced Ask for an online index build and make the statement fail outright if MySQL would have to block writes instead.

Example

bash
mysql store <<'SQL'
ALTER TABLE orders ADD INDEX idx_state (state), ALGORITHM=INPLACE, LOCK=NONE;
SQL
CREATE FULLTEXT INDEX intermediate Word-based index used by MATCH ... AGAINST searches.

Example

bash
mysql store <<'SQL'
CREATE FULLTEXT INDEX ft_customers_name ON customers (full_name);
SELECT id, full_name FROM customers
WHERE MATCH(full_name) AGAINST ('lovelace');
SQL
ALTER INDEX ... INVISIBLE advanced Hide an index from the optimizer while keeping it maintained, so you can see whether anything slows down before dropping it. VISIBLE brings it back instantly.

Example

bash
mysql store <<'SQL'
ALTER TABLE orders ALTER INDEX idx_state INVISIBLE;
SQL
DROP INDEX ... ON beginner Remove an index.

Example

bash
mysql store <<'SQL'
DROP INDEX idx_state ON orders;
SQL
EXPLAIN SELECT beginner Show the plan without running the query: access type, chosen key and estimated rows to examine.

Example

bash
mysql store <<'SQL'
EXPLAIN SELECT * FROM orders WHERE customer_id = 7;
SQL

Tip: type: ALL means a full table scan; key: NULL means no index was chosen.

EXPLAIN FORMAT=TREE intermediate Display the plan as an indented tree of operations, easier to follow for joins and subqueries.

Example

bash
mysql store <<'SQL'
EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE customer_id = 7;
SQL
EXPLAIN ANALYZE advanced Actually execute the query and annotate each plan step with real timings and row counts.

Example

bash
mysql store <<'SQL'
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 7;
SQL

Tip: It runs the statement for real, so be careful with expensive queries on busy servers.

FORCE INDEX (name) advanced Override the optimizer and insist on a specific index.

Example

bash
mysql store <<'SQL'
SELECT * FROM orders FORCE INDEX (idx_orders_customer) WHERE customer_id = 7;
SQL
ANALYZE TABLE intermediate Recalculate index statistics so the optimizer's estimates match the data again.

Example

bash
mysql store <<'SQL'
ANALYZE TABLE orders;
SQL
Worked example: verify an index is used intermediate Add a composite index for a customer's latest orders, check the plan picks it, then measure the real run.

Example

bash
mysql store <<'SQL'
CREATE INDEX idx_orders_customer ON orders (customer_id, created_at);
EXPLAIN SELECT id, amount FROM orders
WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
EXPLAIN ANALYZE SELECT id, amount FROM orders
WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
SQL

Tip: Expect type: ref, key: idx_orders_customer and "Backward index scan" in Extra: the index already holds the rows in date order.

Sessions & transactions

17 commands

See who is connected and what they are running, stop runaway queries, and group statements into transactions with savepoints and row locks.

SHOW PROCESSLIST; beginner Current connections and what each is doing; the statement text is cut to 100 characters.

Example

bash
mysql <<'SQL'
SHOW PROCESSLIST;
SQL
SHOW FULL PROCESSLIST; beginner The same list with complete statement text.

Example

bash
mysql <<'SQL'
SHOW FULL PROCESSLIST;
SQL
information_schema.processlist intermediate Query connections like a table: here only active ones, longest-running first.

Example

bash
mysql <<'SQL'
SELECT id, user, time, info
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;
SQL

Tip: performance_schema.processlist is the non-blocking alternative on busy servers.

KILL QUERY id; intermediate Cancel the statement a connection is running but leave the connection open.

Example

bash
mysql <<'SQL'
KILL QUERY 1234;
SQL
KILL id; intermediate Destructive: disconnect the session; any open transaction it had is rolled back.

Example

bash
mysql <<'SQL'
KILL 1234;
SQL
SELECT CONNECTION_ID(); beginner Your own thread id, the number other sessions would pass to KILL.

Example

bash
mysql store <<'SQL'
SELECT CONNECTION_ID();
SQL
START TRANSACTION; beginner Begin a transaction so the following statements succeed or fail together. BEGIN is a synonym.

Example

bash
mysql store <<'SQL'
START TRANSACTION;
UPDATE customers SET tier = 'plus' WHERE id = 1;
COMMIT;
SQL
COMMIT; beginner Make the transaction's changes permanent and visible to others.

Example

bash
mysql store <<'SQL'
START TRANSACTION;
DELETE FROM orders WHERE id = 42;
COMMIT;
SQL
ROLLBACK; beginner Throw away every change made since the transaction started.

Example

bash
mysql store <<'SQL'
START TRANSACTION;
DELETE FROM orders;
ROLLBACK;
SQL

Tip: DDL such as ALTER or DROP commits implicitly and cannot be rolled back.

SAVEPOINT name; intermediate Mark a point inside a transaction that you can return to later.

Example

bash
mysql store <<'SQL'
START TRANSACTION;
UPDATE orders SET state = 'paid' WHERE id = 42;
SAVEPOINT before_refund;
UPDATE customers SET balance = balance + 20 WHERE id = 1;
ROLLBACK TO SAVEPOINT before_refund;
COMMIT;
SQL
ROLLBACK TO SAVEPOINT name; intermediate Undo only the work done after the savepoint while keeping the earlier part of the transaction.

Example

bash
mysql store <<'SQL'
ROLLBACK TO SAVEPOINT before_refund;
SQL
SELECT ... FOR UPDATE intermediate Read rows and lock them against other writers until you commit or roll back.

Example

bash
mysql store <<'SQL'
START TRANSACTION;
SELECT * FROM orders WHERE id = 42 FOR UPDATE;
UPDATE orders SET state = 'shipped' WHERE id = 42;
COMMIT;
SQL

Tip: Add SKIP LOCKED to build simple job queues, or NOWAIT to fail instead of waiting.

SET SESSION TRANSACTION ISOLATION LEVEL advanced Change the isolation level for this connection; InnoDB defaults to REPEATABLE READ.

Example

bash
mysql store <<'SQL'
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
SQL
@@transaction_isolation
READ-COMMITTED
SHOW ENGINE INNODB STATUS\G advanced InnoDB's internal report: the latest detected deadlock, current lock waits, buffer pool and I/O figures.

Example

bash
mysql <<'SQL'
SHOW ENGINE INNODB STATUS\G
SQL
SHOW GLOBAL STATUS LIKE 'Threads_connected'; intermediate How many client connections are open right now.

Example

bash
mysql <<'SQL'
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SQL
SHOW VARIABLES LIKE 'max_connections'; intermediate The connection ceiling; beyond it clients get "Too many connections".

Example

bash
mysql <<'SQL'
SHOW VARIABLES LIKE 'max_connections';
SQL
Worked example: atomic balance transfer intermediate Move credit between two customers safely: lock both rows first so no other session can change them mid-transfer, then commit both updates together.

Example

bash
mysql store <<'SQL'
START TRANSACTION;
SELECT id, balance FROM customers WHERE id IN (1, 2) FOR UPDATE;
UPDATE customers SET balance = balance - 25.00 WHERE id = 1;
UPDATE customers SET balance = balance + 25.00 WHERE id = 2;
COMMIT;
SQL

JSON

11 commands

Read, filter, modify, build and index JSON documents stored in a JSON column (orders.details in these examples).

col->'$.path' beginner Extract a value as JSON, so strings come back wrapped in double quotes.

Example

bash
mysql store <<'SQL'
SELECT details->'$.channel' FROM orders;
SQL
col->>'$.path' beginner Extract and unquote in one step, the form you usually want for display and comparisons.

Example

bash
mysql store <<'SQL'
SELECT details->>'$.channel' FROM orders;
SQL
JSON_EXTRACT(doc, path) intermediate The function behind ->. Array positions start at 0.

Example

bash
mysql store <<'SQL'
SELECT JSON_EXTRACT('{"items": [{"sku": "A1"}, {"sku": "B2"}]}', '$.items[1].sku') AS sku;
SQL
sku
"B2"
WHERE col->>'$.path' = value beginner Filter rows on a field inside the document.

Example

bash
mysql store <<'SQL'
SELECT * FROM orders WHERE details->>'$.channel' = 'web';
SQL
JSON_CONTAINS(doc, value) intermediate Test whether a JSON array or object contains a value; the value itself must be valid JSON, hence the inner quotes.

Example

bash
mysql store <<'SQL'
SELECT * FROM orders WHERE JSON_CONTAINS(details->'$.tags', '"gift"');
SQL
JSON_SET(doc, path, value) intermediate Add a key or overwrite an existing one, returning the new document.

Example

bash
mysql store <<'SQL'
SELECT JSON_SET('{"channel": "web"}', '$.channel', 'app') AS doc;
SQL
doc
{"channel": "app"}

Tip: Use it in UPDATE orders SET details = JSON_SET(details, ...) WHERE id = 42 to change stored documents.

JSON_REMOVE(doc, path) intermediate Delete a key from a document.

Example

bash
mysql store <<'SQL'
UPDATE orders SET details = JSON_REMOVE(details, '$.coupon') WHERE id = 42;
SQL
JSON_OBJECT(key, value, ...) beginner Build a JSON object from columns or literals.

Example

bash
mysql store <<'SQL'
SELECT JSON_OBJECT('id', 7, 'email', 'ada@example.com') AS doc;
SQL
doc
{"id": 7, "email": "ada@example.com"}

Tip: MySQL stores object keys sorted by length then name, which is why id comes before email.

JSON_ARRAYAGG(expr) intermediate Aggregate a column across rows into a single JSON array.

Example

bash
mysql store <<'SQL'
SELECT JSON_ARRAYAGG(email) FROM customers;
SQL
Generated column + index advanced JSON fields cannot be indexed directly; expose one through a stored generated column and index that.

Example

bash
mysql store <<'SQL'
ALTER TABLE orders
  ADD COLUMN channel VARCHAR(20) AS (details->>'$.channel') STORED,
  ADD INDEX idx_channel (channel);
SQL

Tip: MySQL 8.0.13+ also accepts functional indexes: INDEX ((CAST(details->>'$.channel' AS CHAR(20)) COLLATE utf8mb4_bin)).

Worked example: JSON_TABLE units per SKU advanced Explode each order's items array into rows with JSON_TABLE, then aggregate them like a normal table.

Example

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

TiDB compatibility

11 commands

TiDB speaks the MySQL wire protocol on port 4000, so the client and nearly all SQL above work against it. The entries below exist only in TiDB.

mysql -P 4000 beginner Connect to a local TiDB with the ordinary mysql client.

Example

bash
mysql -h 127.0.0.1 -P 4000 -u root
SELECT tidb_version(); beginner Version, git hash and build details of the TiDB server.

Example

bash
mysql <<'SQL'
SELECT tidb_version();
SQL
AS OF TIMESTAMP intermediate Stale read: query a table as it looked at an earlier moment.

Example

bash
mysql store <<'SQL'
SELECT * FROM orders AS OF TIMESTAMP NOW() - INTERVAL 5 MINUTE;
SQL
AUTO_RANDOM advanced Primary keys with random high bits, so inserts spread across storage nodes instead of piling onto one hot region.

Example

bash
mysql store <<'SQL'
CREATE TABLE events (
  id BIGINT AUTO_RANDOM PRIMARY KEY,
  created_at DATETIME
);
SQL
TTL = col + INTERVAL advanced Have TiDB delete rows automatically once they pass an age limit.

Example

bash
mysql store <<'SQL'
ALTER TABLE events TTL = created_at + INTERVAL 90 DAY;
SQL
IMPORT INTO advanced High-speed bulk load of CSV files, including from object storage.

Example

bash
mysql store <<'SQL'
IMPORT INTO customers FROM 's3://my-bucket/customers.*.csv' WITH skip_rows=1;
SQL
/*+ USE_INDEX(t, idx) */ advanced Optimizer hint, written as a special comment, that steers the plan towards an index.

Example

bash
mysql store <<'SQL'
SELECT /*+ USE_INDEX(orders, idx_orders_customer) */ *
FROM orders WHERE customer_id = 7;
SQL
SET tidb_mem_quota_query advanced Raise the memory a single query may use; here to 8 GiB.

Example

bash
mysql store <<'SQL'
SET tidb_mem_quota_query = 8 << 30;
SQL
information_schema.slow_query intermediate The slowest recent statements, straight from TiDB's slow log.

Example

bash
mysql <<'SQL'
SELECT query_time, query
FROM information_schema.slow_query
ORDER BY query_time DESC LIMIT 10;
SQL
SHOW VARIABLES LIKE 'sql_mode'; beginner Check the SQL mode; TiDB keeps it in line with MySQL for compatibility.

Example

bash
mysql <<'SQL'
SHOW VARIABLES LIKE 'sql_mode';
SQL
Worked example: recover after a bad delete intermediate Look at rows as they were before a mistaken DELETE. Stale reads only reach back as far as the GC window, tidb_gc_life_time, which is 10 minutes by default.

Example

bash
mysql store <<'SQL'
SELECT COUNT(*) FROM orders WHERE customer_id = 7;
SELECT * FROM orders AS OF TIMESTAMP NOW() - INTERVAL 5 MINUTE
WHERE customer_id = 7;
SQL

Tip: Copy the old rows back with INSERT INTO orders SELECT ... AS OF TIMESTAMP before the GC window closes.

Pitfalls

6 commands

Mistakes that cost real data or downtime, and how to avoid each one.

UPDATE / DELETE without WHERE beginner Forgetting the WHERE clause changes every row, and with autocommit on there is nothing to roll back. Start the client with --safe-updates, or open a transaction and check the affected row count before committing.

Example

bash
mysql store <<'SQL'
START TRANSACTION;
UPDATE customers SET tier = 'plus' WHERE id = 1;
SELECT ROW_COUNT();
COMMIT;
SQL

Tip: If ROW_COUNT() is not what you expected, run ROLLBACK instead of COMMIT.

REPLACE INTO is not an upsert intermediate REPLACE deletes the colliding row and inserts a new one. That fires delete triggers, cascades through foreign keys and resets any column you did not list. Reach for INSERT ... ON DUPLICATE KEY UPDATE instead.

Example

bash
mysql store <<'SQL'
INSERT INTO customers (email, full_name) VALUES ('ada@example.com', 'Ada') AS new
ON DUPLICATE KEY UPDATE full_name = new.full_name;
SQL
mysqldump without --single-transaction intermediate Without the flag, tables are locked for the whole dump. With it, InnoDB tables come from one consistent snapshot, though MyISAM tables are still not consistent.

Example

bash
mysqldump -u root -p --single-transaction store > store.sql
utf8 is not full UTF-8 beginner In MySQL utf8 means utf8mb3, which tops out at 3-byte characters and rejects emoji. Always declare utf8mb4.

Example

bash
mysql store <<'SQL'
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SQL
-pSecret vs -p Secret beginner -pSecret, with no space, leaves the password in shell history and in the process list. -p Secret, with a space, prompts for a password and treats Secret as the database name. Use -p on its own or a login path.

Example

bash
mysql -u webapp -p store
KILL rolls back the whole transaction advanced Killing a connection in the middle of a large write undoes everything it changed, and the rollback can take about as long as the write did. Use KILL QUERY when you only want the current statement stopped.

Example

bash
mysql <<'SQL'
KILL QUERY 1234;
SQL

Quick answers

5 commands

Short answers to the questions people search for most often.

Which version am I running? beginner mysql --version reports the client and mysqld --version the server binary on this machine. Once connected, SELECT VERSION() returns the version of the server you are really talking to, which matters when client and server come from different installs.

Example

bash
mysql --version
mysql -e "SELECT VERSION()"

Tip: The \s status command shows the server version as well.

Is MySQL case sensitive? intermediate Keywords and column names are not. String comparisons follow the collation: the 8.4 default utf8mb4_0900_ai_ci ignores both case and accents. Add COLLATE utf8mb4_bin, or use a _bin or _as_cs collation, for an exact match.

Example

bash
mysql store <<'SQL'
SELECT 'Ada' = 'ADA' AS ci, 'Ada' = _utf8mb4'ADA' COLLATE utf8mb4_bin AS cs;
SQL
ci	cs
1	0

Tip: Table and database names follow lower_case_table_names: 0 (case sensitive) on Linux, 1 on Windows, 2 on macOS.

Is there a BOOLEAN type? beginner BOOL and BOOLEAN are aliases for TINYINT(1), and TRUE and FALSE are just 1 and 0. Such a column will happily store 5 or -1 unless you add CHECK (flag IN (0, 1)).

Example

bash
mysql store <<'SQL'
SELECT TRUE, FALSE;
SQL
TRUE	FALSE
1	0
Where are the data files and error log? intermediate Ask the running server for the exact paths. Typical defaults are /var/lib/mysql on Linux packages, /usr/local/mysql/data with the macOS installer and /opt/homebrew/var/mysql for Homebrew on Apple silicon.

Example

bash
mysql <<'SQL'
SELECT @@datadir, @@log_error;
SQL

Tip: Only copy the data directory with the server stopped; use mysqldump for a backup of a live server.

How do I reset the root password? advanced Stop the server and start it with --skip-grant-tables --skip-networking, so it accepts anyone but only through the local socket. Log in, reload the grant tables with FLUSH PRIVILEGES (ALTER USER fails without this step), set the new password, then restart normally.

Example

bash
sudo systemctl stop mysqld
sudo mysqld --user=mysql --skip-grant-tables --skip-networking &
mysql -u root <<'SQL'
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3w-root-pass';
SQL
sudo mysqladmin -u root -p shutdown
sudo systemctl start mysqld

Tip: The service is called mysql rather than mysqld on Debian and Ubuntu.

↑ ↓ navigate Enter open Esc close