Part 8C: Flow Control, Events, Partitioning, Locking & Miscellaneous

Section J — Flow Control in Stored Procedures
Section K — Event Scheduler
Section L — Table Partitioning
Section M — Locking & Concurrency
Section N — Miscellaneous Useful Syntax

Flow Control in Stored Procedures

J.1 — IF ... THEN ... ELSEIF ... ELSE ... END IF

This is the procedural IF statement used inside stored procedures and functions. It works like an if/else block in any programming language. Do not confuse it with the IF() function used in SELECT queries — this one spans multiple lines and uses THEN / END IF.

Procedure with IF...ELSEIF...ELSE

DELIMITER //

CREATE PROCEDURE classify_salary(
    IN emp_id INT,
    OUT salary_level VARCHAR(20)
)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);

    SELECT salary INTO emp_salary
    FROM employees WHERE id = emp_id;

    IF emp_salary >= 80000 THEN
        SET salary_level = 'Senior';
    ELSEIF emp_salary >= 50000 THEN
        SET salary_level = 'Mid-Level';
    ELSE
        SET salary_level = 'Junior';
    END IF;
END //

DELIMITER ;

-- Usage:
CALL classify_salary(1, @level);
SELECT @level AS salary_level;

salary_level
Senior

IF() function vs IF statement: IF(condition, true_val, false_val) is a function used in SELECT. The procedural IF...THEN...END IF is only valid inside procedures, functions, and triggers.

J.2 — CASE ... WHEN ... THEN ... END CASE

The procedural CASE statement works like a switch statement in other languages. You test a variable against multiple possible values and run different code for each match. There is also a "searched CASE" where each WHEN has its own condition.

CASE statement in a procedure

DELIMITER //

CREATE PROCEDURE get_department_bonus(
    IN dept_name VARCHAR(50),
    OUT bonus_pct DECIMAL(5,2)
)
BEGIN
    CASE dept_name
        WHEN 'Engineering' THEN
            SET bonus_pct = 15.00;
        WHEN 'Sales' THEN
            SET bonus_pct = 20.00;
        WHEN 'HR' THEN
            SET bonus_pct = 10.00;
        ELSE
            SET bonus_pct = 5.00;
    END CASE;
END //

DELIMITER ;

CALL get_department_bonus('Sales', @bonus);
SELECT @bonus AS bonus_percentage;

bonus_percentage
20.00

J.3 — WHILE ... DO ... END WHILE

A WHILE loop checks its condition before each iteration. If the condition is false from the start, the loop body never executes. Think of it like: "While this is true, keep doing this."

Generate a number sequence with WHILE

DELIMITER //

CREATE PROCEDURE generate_numbers(IN max_val INT)
BEGIN
    DECLARE counter INT DEFAULT 1;

    DROP TEMPORARY TABLE IF EXISTS temp_numbers;
    CREATE TEMPORARY TABLE temp_numbers (num INT);

    WHILE counter <= max_val DO
        INSERT INTO temp_numbers VALUES (counter);
        SET counter = counter + 1;
    END WHILE;

    SELECT * FROM temp_numbers;
END //

DELIMITER ;

CALL generate_numbers(5);

num
1
2
3
4
5

J.4 — REPEAT ... UNTIL condition END REPEAT

A REPEAT loop checks its condition after each iteration. This means the loop body always runs at least once, even if the condition is already true. It is the "do-while" of MySQL.

REPEAT always executes at least once

DELIMITER //

CREATE PROCEDURE countdown(IN start_val INT)
BEGIN
    DECLARE counter INT DEFAULT start_val;
    DECLARE result VARCHAR(255) DEFAULT '';

    REPEAT
        SET result = CONCAT(result, counter, ' ');
        SET counter = counter - 1;
    UNTIL counter < 1
    END REPEAT;

    SELECT TRIM(result) AS countdown_sequence;
END //

DELIMITER ;

-- Even with start_val = 0, the body runs once:
CALL countdown(0);

countdown_sequence
0

Proof it runs at least once: Even though counter starts at 0 (which is already < 1), the body executes once before UNTIL is checked.

J.5 — LOOP ... END LOOP with LEAVE

A generic LOOP has no built-in condition — it runs forever until you explicitly say LEAVE to break out. You write your own exit condition using an IF inside the loop.

LOOP with LEAVE (break)

DELIMITER //

CREATE PROCEDURE sum_to_n(IN n INT, OUT total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;

    sum_loop: LOOP
        IF i > n THEN
            LEAVE sum_loop;   -- break out of the loop
        END IF;

        SET total = total + i;
        SET i = i + 1;
    END LOOP sum_loop;
END //

DELIMITER ;

CALL sum_to_n(10, @result);
SELECT @result AS sum_1_to_10;

sum_1_to_10
55

J.6 — ITERATE (Continue to Next Iteration)

ITERATE is like continue in other languages. It skips the rest of the current iteration and jumps back to the top of the loop. The loop must have a label so ITERATE knows which loop to continue.

ITERATE to skip odd numbers

DELIMITER //

CREATE PROCEDURE even_numbers_only(IN max_val INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(255) DEFAULT '';

    num_loop: LOOP
        SET i = i + 1;

        IF i > max_val THEN
            LEAVE num_loop;
        END IF;

        IF i % 2 != 0 THEN
            ITERATE num_loop;   -- skip odd numbers
        END IF;

        SET result = CONCAT(result, i, ' ');
    END LOOP num_loop;

    SELECT TRIM(result) AS even_nums;
END //

DELIMITER ;

CALL even_numbers_only(10);

even_nums
2 4 6 8 10

J.7 — Labels (Naming Loops for LEAVE / ITERATE)

A label is a name you place before a loop (or a BEGIN block) so you can reference it with LEAVE or ITERATE. The label goes at the start with a colon, and optionally at the end after END LOOP.

Label syntax

-- Syntax: label_name: LOOP ... END LOOP label_name;

DELIMITER //

CREATE PROCEDURE nested_loop_demo()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j INT;
    DECLARE result VARCHAR(500) DEFAULT '';

    outer_loop: WHILE i < 3 DO
        SET i = i + 1;
        SET j = 0;

        inner_loop: WHILE j < 3 DO
            SET j = j + 1;

            IF i = 2 AND j = 2 THEN
                LEAVE inner_loop;  -- break inner only
            END IF;

            SET result = CONCAT(result, '(', i, ',', j, ') ');
        END WHILE inner_loop;
    END WHILE outer_loop;

    SELECT result AS pairs;
END //

DELIMITER ;

CALL nested_loop_demo();

pairs
(1,1) (1,2) (1,3) (2,1) (3,1) (3,2) (3,3)

Notice: Pairs (2,2) and (2,3) are missing because LEAVE inner_loop was triggered when i=2, j=2.

J.8 — DECLARE var_name datatype DEFAULT value

DECLARE creates a local variable inside a procedure or function. It only exists within the BEGIN...END block where it is declared. All DECLARE statements must come before any executable statements in a block.

DECLARE local variables

DELIMITER //

CREATE PROCEDURE declare_demo()
BEGIN
    -- All DECLARE statements come first
    DECLARE emp_name VARCHAR(100) DEFAULT 'Unknown';
    DECLARE emp_salary DECIMAL(10,2) DEFAULT 0.00;
    DECLARE is_active BOOLEAN DEFAULT TRUE;
    DECLARE hire_date DATE;  -- DEFAULT is NULL if not specified

    -- Executable statements come after
    SET emp_name = 'Alice Johnson';
    SET emp_salary = 65000.00;

    SELECT emp_name, emp_salary, is_active, hire_date;
END //

DELIMITER ;

CALL declare_demo();

emp_name	emp_salary	is_active	hire_date
Alice Johnson	65000.00	1	NULL

J.9 — SET variable = value

SET assigns a value to a variable. It works with local variables (DECLARE), user variables (@var), and session/global system variables. You can assign multiple variables in one SET statement.

SET variable assignments

-- Assign to a user variable (no DECLARE needed)
SET @greeting = 'Hello World';
SELECT @greeting;

-- Multiple assignments in one SET
SET @x = 10, @y = 20, @z = @x + @y;
SELECT @x, @y, @z;

-- Inside a procedure (local vars)
DELIMITER //
CREATE PROCEDURE set_demo()
BEGIN
    DECLARE total INT;
    SET total = (SELECT COUNT(*) FROM employees);
    SELECT total AS employee_count;
END //
DELIMITER ;

@x	@y	@z
10	20	30

Loop Comparison: WHILE vs REPEAT vs LOOP

When Does Each Loop Check Its Condition?

Loop Type	Condition Check	Minimum Runs	Exit Mechanism	Equivalent In Other Languages
WHILE...DO...END WHILE	BEFORE each iteration	0 (may never run)	Condition becomes false	while (cond) { }
REPEAT...UNTIL...END REPEAT	AFTER each iteration	1 (always runs once)	UNTIL condition becomes true	do { } while (!cond)
LOOP...END LOOP	No built-in check	Infinite (until LEAVE)	LEAVE statement (manual break)	while (true) { if (...) break; }

Visual Flow Comparison

WHILE

Check condition

↓

TRUE: Run body

↓

Back to check

↓

FALSE: Exit

REPEAT

Run body first

↓

Check UNTIL

↓

TRUE: Exit

FALSE: Repeat

LOOP

Run body

↓

IF cond THEN LEAVE

↓

Keep looping

↓

LEAVE: Exit

Event Scheduler

The MySQL Event Scheduler lets you schedule SQL tasks to run automatically at specific times or on a recurring basis — like a built-in cron job inside the database. It can clean up old data, generate summaries, or do any SQL task on a schedule.

K.1 — Check Event Scheduler Status

Check if the event scheduler is running

SHOW VARIABLES LIKE 'event_scheduler';

Variable_name	Value
event_scheduler	OFF

K.2 — Enable the Event Scheduler

The event scheduler is OFF by default. You must turn it ON before any events will fire.

Enable event scheduler

SET GLOBAL event_scheduler = ON;

-- Verify it's on:
SHOW VARIABLES LIKE 'event_scheduler';

Variable_name	Value
event_scheduler	ON

Persistence: This setting resets when MySQL restarts. To make it permanent, add event_scheduler=ON to your my.cnf / my.ini file.

K.3 — CREATE EVENT (One-Time) — AT timestamp

A one-time event runs once at a specific time in the future, then is automatically dropped (unless you say ON COMPLETION PRESERVE).

One-time event: run once 1 hour from now

CREATE EVENT cleanup_temp_data
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ON COMPLETION PRESERVE   -- keep event after it runs (optional)
DO
    DELETE FROM activity_logs
    WHERE created_at < NOW() - INTERVAL 90 DAY;

K.4 — CREATE EVENT (Recurring) — EVERY interval

A recurring event runs repeatedly at a fixed interval. You can specify STARTS and ENDS to control the active window.

Recurring event: every day at midnight

CREATE EVENT daily_summary_refresh
ON SCHEDULE EVERY 1 DAY
STARTS '2026-01-01 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Refreshes daily order summary table'
DO
BEGIN
    TRUNCATE TABLE daily_order_summary;
    INSERT INTO daily_order_summary (order_date, total_orders, revenue)
    SELECT CURDATE() - INTERVAL 1 DAY,
           COUNT(*),
           SUM(total)
    FROM orders
    WHERE order_date = CURDATE() - INTERVAL 1 DAY;
END;

Other interval examples

-- Every hour
ON SCHEDULE EVERY 1 HOUR

-- Every 30 minutes
ON SCHEDULE EVERY 30 MINUTE

-- Every week, active only in 2026
ON SCHEDULE EVERY 1 WEEK
STARTS '2026-01-01 00:00:00'
ENDS   '2026-12-31 23:59:59'

K.5 — ALTER EVENT (Enable / Disable / Modify)

Alter an event

-- Disable an event (stop it from firing)
ALTER EVENT daily_summary_refresh DISABLE;

-- Re-enable it
ALTER EVENT daily_summary_refresh ENABLE;

-- Change the schedule to every 6 hours
ALTER EVENT daily_summary_refresh
ON SCHEDULE EVERY 6 HOUR;

-- Rename the event
ALTER EVENT daily_summary_refresh
RENAME TO six_hour_summary_refresh;

K.6 — DROP EVENT

Remove an event

DROP EVENT IF EXISTS cleanup_temp_data;

K.7 — SHOW EVENTS

List all events in the current database

SHOW EVENTS;

-- Or filter by database
SHOW EVENTS FROM mydb;

Db	Name	Type	Execute at	Interval	Starts	Status
mydb	daily_summary_refresh	RECURRING	NULL	1 DAY	2026-01-01 00:00:00	ENABLED
mydb	cleanup_temp_data	ONE TIME	2026-02-23 15:30:00	NULL	NULL	ENABLED

K.8 — Practical: Auto-Delete Old Logs Every Midnight

Event: purge logs older than 30 days at midnight

CREATE EVENT purge_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS (CURDATE() + INTERVAL 1 DAY)  -- starts tomorrow at midnight
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Delete activity logs older than 30 days'
DO
BEGIN
    DELETE FROM activity_logs
    WHERE created_at < NOW() - INTERVAL 30 DAY
    LIMIT 10000;  -- batch delete to avoid long locks
END;

Tip: Use LIMIT on bulk deletes inside events so you do not lock the table for too long. The next run will catch remaining rows.

Visual Timeline: One-Time vs Recurring Events

One-Time Event (AT)

Created

Fires once then done

Recurring Event (EVERY)

Day 1Day 2Day 3Day 4Day 5

Table Partitioning

Partitioning splits one large table into smaller internal pieces called partitions. Each partition stores a subset of the rows. MySQL can then scan only the relevant partition(s) for a query instead of the entire table, making queries on huge tables much faster.

How Partitioning Works: One Table, Multiple Partitions

orders (full table — 4 million rows)

Before partitioning: one huge block

↓ PARTITION BY RANGE (YEAR(order_date))

p2023
Year 2023
~1M rows

p2024
Year 2024
~1M rows

p2025
Year 2025
~1M rows

p2026
Year 2026
~1M rows

Query for 2025 data only scans the highlighted partition!

L.1 — PARTITION BY RANGE

RANGE partitioning divides rows based on a column value falling within a range. Most commonly used with dates (e.g., one partition per year).

Create orders table partitioned by year

CREATE TABLE orders (
    id          INT          NOT NULL AUTO_INCREMENT,
    customer_id INT          NOT NULL,
    product_id  INT          NOT NULL,
    quantity    INT          DEFAULT 1,
    total       DECIMAL(10,2),
    region      VARCHAR(20),
    order_date  DATE         NOT NULL,
    PRIMARY KEY (id, order_date)  -- partition key must be in PK
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Important: The partition column must be part of every UNIQUE index (including PRIMARY KEY). That is why order_date is in the PRIMARY KEY above.

L.2 — PARTITION BY LIST

LIST partitioning assigns rows to partitions based on exact matching of values, like specific region codes or category IDs.

Partition by region

CREATE TABLE orders_by_region (
    id          INT          NOT NULL AUTO_INCREMENT,
    customer_id INT,
    total       DECIMAL(10,2),
    region_id   INT          NOT NULL,
    order_date  DATE,
    PRIMARY KEY (id, region_id)
)
PARTITION BY LIST (region_id) (
    PARTITION p_north   VALUES IN (1, 2, 3),
    PARTITION p_south   VALUES IN (4, 5, 6),
    PARTITION p_east    VALUES IN (7, 8, 9),
    PARTITION p_west    VALUES IN (10, 11, 12)
);

Partition	Region IDs	Example Regions
p_north	1, 2, 3	New York, Boston, Chicago
p_south	4, 5, 6	Miami, Dallas, Atlanta
p_east	7, 8, 9	Philadelphia, D.C., Charlotte
p_west	10, 11, 12	Los Angeles, Seattle, Denver

L.3 — PARTITION BY HASH

HASH partitioning distributes rows evenly across partitions using a modulus of the column value. Good when you have no natural range or list to split on.

Hash partition for even distribution

CREATE TABLE customers (
    id    INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100),
    email VARCHAR(100)
)
PARTITION BY HASH (id)
PARTITIONS 4;

-- Row goes to: partition number = id MOD 4
-- id=1 → partition 1, id=2 → partition 2, ... id=5 → partition 1

L.4 — PARTITION BY KEY

KEY partitioning is like HASH but MySQL uses its own internal hashing function. You do not need an integer column — it works with strings too.

Key partition (MySQL chooses hash algorithm)

CREATE TABLE students (
    id         INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100),
    email      VARCHAR(100),
    enrolled   DATE
)
PARTITION BY KEY ()  -- empty () means use the PRIMARY KEY
PARTITIONS 4;

L.5 — SUBPARTITION (Partition Within a Partition)

You can create sub-partitions (also called composite partitioning). For example, first partition by RANGE (year), then sub-partition by HASH within each year partition.

RANGE + HASH subpartitioning

CREATE TABLE orders_sub (
    id          INT          NOT NULL AUTO_INCREMENT,
    customer_id INT,
    total       DECIMAL(10,2),
    order_date  DATE         NOT NULL,
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH (MONTH(order_date))
SUBPARTITIONS 4 (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027)
);

Result: 3 partitions x 4 subpartitions = 12 total physical partitions.

L.6 — ALTER TABLE ADD PARTITION

When a new year comes, you need to add a new partition. But if you have a MAXVALUE catch-all, you must REORGANIZE it instead (see L.8).

Add a new partition (when no MAXVALUE exists)

-- If the table had no p_future / MAXVALUE partition:
ALTER TABLE orders
ADD PARTITION (
    PARTITION p2027 VALUES LESS THAN (2028)
);

L.7 — ALTER TABLE DROP PARTITION

WARNING: Dropping a partition permanently deletes ALL data in that partition! This is much faster than DELETE for removing millions of rows, but it cannot be undone.

Drop a partition (and all its data!)

-- This instantly removes all 2023 orders:
ALTER TABLE orders
DROP PARTITION p2023;

-- Much faster than: DELETE FROM orders WHERE YEAR(order_date) = 2023;

L.8 — ALTER TABLE REORGANIZE PARTITION (Split / Merge)

Use REORGANIZE to split a MAXVALUE partition into a specific year + a new MAXVALUE, or to merge partitions.

Split the MAXVALUE partition

-- Split p_future into p2027 + new p_future
ALTER TABLE orders
REORGANIZE PARTITION p_future INTO (
    PARTITION p2027    VALUES LESS THAN (2028),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Merge two partitions into one
ALTER TABLE orders
REORGANIZE PARTITION p2023, p2024 INTO (
    PARTITION p_old VALUES LESS THAN (2025)
);

L.9 — EXPLAIN Showing Partition Pruning

When MySQL can figure out which partition(s) a query needs, it only scans those partitions. This is called partition pruning. EXPLAIN shows you which partitions were scanned.

EXPLAIN shows partition pruning

EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

id	select_type	table	partitions	type	rows	Extra
1	SIMPLE	orders	p2025	ALL	1048576	Using where

Only p2025 was scanned!

Instead of scanning all 4 million rows across all partitions, MySQL only scanned the ~1M rows in partition p2025. Without partitioning, it would scan the entire table.

L.10 — Practical: Orders Partitioned by Year

Full practical example with data verification

-- Check how data is distributed across partitions
SELECT
    PARTITION_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
ORDER BY PARTITION_ORDINAL_POSITION;

PARTITION_NAME	TABLE_ROWS	data_mb	index_mb
p2023	987654	48.50	12.30
p2024	1045231	51.20	13.10
p2025	1048576	51.40	13.20
p2026	456789	22.40	5.70
p_future	0	0.02	0.02

Locking & Concurrency

When multiple users or processes access the same data, you need locks to prevent conflicts. MySQL has row-level locks (InnoDB), table-level locks, and advisory locks (application-level). Understanding locks helps you write safe concurrent code.

READ Lock vs WRITE Lock Behavior

READ Lock (Shared)

Multiple readers allowed. Writers blocked.

Reader A Reader B Reader C

All readers proceed

Writer X (BLOCKED)

Writers must wait

WRITE Lock (Exclusive)

Only the lock holder can access. Everyone else blocked.

Writer A (holds lock)

Only the lock holder proceeds

Reader B (BLOCKED) Reader C (BLOCKED) Writer D (BLOCKED)

Everyone else must wait

M.1 — SELECT ... FOR UPDATE (Row-Level Write Lock)

Locks selected rows so that no other transaction can read or modify them until your transaction commits or rolls back. Use this when you plan to update the rows you just read.

Lock rows for update

START TRANSACTION;

-- Lock employee id=5 so nobody else can change it
SELECT id, name, salary
FROM employees
WHERE id = 5
FOR UPDATE;

-- Now safely update knowing nobody else changed it
UPDATE employees SET salary = salary + 5000 WHERE id = 5;

COMMIT;  -- lock released here

id	name	salary
5	Diana Prince	72000.00

M.2 — SELECT ... FOR SHARE / LOCK IN SHARE MODE

Locks rows so that other transactions can read them but cannot modify them. Multiple sessions can hold a shared lock on the same rows simultaneously.

Shared lock (read lock on rows)

START TRANSACTION;

-- MySQL 8.0+ syntax:
SELECT id, name, salary
FROM employees
WHERE department = 'Engineering'
FOR SHARE;

-- Older equivalent syntax (still works):
SELECT id, name, salary
FROM employees
WHERE department = 'Engineering'
LOCK IN SHARE MODE;

-- Other sessions CAN read these rows
-- Other sessions CANNOT update/delete these rows until COMMIT

COMMIT;

M.3 — LOCK TABLES table READ

Locks an entire table so everyone (including yourself) can only read it — no writes allowed until the lock is released. Other sessions can also acquire READ locks.

Table-level read lock

LOCK TABLES employees READ;

-- You can SELECT:
SELECT * FROM employees;  -- OK

-- You CANNOT insert/update/delete (even you!):
UPDATE employees SET salary = 50000 WHERE id = 1;
-- ERROR 1099: Table 'employees' was locked with a READ lock and can't be updated

UNLOCK TABLES;

M.4 — LOCK TABLES table WRITE

Locks an entire table exclusively. Only your session can read or write. All other sessions are blocked from any access until you release the lock.

Table-level write lock

LOCK TABLES employees WRITE;

-- You can do anything:
SELECT * FROM employees;  -- OK
UPDATE employees SET salary = 50000 WHERE id = 1;  -- OK

-- Other sessions: SELECT hangs, UPDATE hangs, everything waits

UNLOCK TABLES;

M.5 — UNLOCK TABLES

Release all table locks

UNLOCK TABLES;

-- Releases ALL table locks held by the current session.
-- Also implicitly released when:
--   1. The session disconnects
--   2. You issue another LOCK TABLES (releases old, acquires new)
--   3. You START TRANSACTION (releases table locks)

M.6 — GET_LOCK('name', timeout) — Advisory Lock

Advisory locks are not tied to any table or row — they are named application-level locks. Your code decides what they protect. Other code must cooperate by also calling GET_LOCK before accessing the shared resource.

Acquire an advisory lock

-- Try to get lock named 'order_processing' with 10 second timeout
SELECT GET_LOCK('order_processing', 10) AS lock_result;

lock_result	Meaning
1	Lock acquired successfully
0	Timed out (someone else holds it)
NULL	An error occurred

Full advisory lock pattern

-- Acquire
SELECT GET_LOCK('import_customers', 30) AS got_lock;

-- Do critical work (only one process at a time)...
INSERT INTO customers (name, email)
SELECT name, email FROM staging_customers;

-- Release
SELECT RELEASE_LOCK('import_customers') AS released;

M.7 — RELEASE_LOCK('name')

Release an advisory lock

SELECT RELEASE_LOCK('order_processing') AS released;

released	Meaning
1	Lock was released
0	Lock exists but is held by another session (not yours to release)
NULL	Lock did not exist

M.8 — IS_FREE_LOCK('name')

Check if an advisory lock is available

SELECT IS_FREE_LOCK('order_processing') AS is_free;

is_free	Meaning
1	The lock is free (nobody holds it)
0	The lock is in use
NULL	An error occurred

M.9 — IS_USED_LOCK('name')

Returns the connection ID of the session holding the lock, or NULL if nobody holds it. Useful for debugging which session is holding a lock.

Find who holds a lock

SELECT IS_USED_LOCK('order_processing') AS held_by_connection;

held_by_connection	Meaning
42	Connection ID 42 holds the lock
NULL	Nobody holds the lock

Miscellaneous Useful Syntax

N.1 — INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE (Upsert from Query)

Combines INSERT from a SELECT query with an "upsert" — if a row with the same unique key already exists, it updates instead of inserting. Perfect for syncing data from one table to another.

Upsert from another table

-- Sync product prices from staging into production
INSERT INTO products (id, name, price, updated_at)
SELECT id, name, price, NOW()
FROM staging_products
ON DUPLICATE KEY UPDATE
    name       = VALUES(name),
    price      = VALUES(price),
    updated_at = NOW();

Action	Condition	Result
INSERT	id does not exist in products	New row created
UPDATE	id already exists (duplicate key)	name, price, updated_at overwritten

N.2 — CREATE TABLE IF NOT EXISTS

Creates a table only if it does not already exist. No error is thrown if it already exists — you just get a warning. Useful in setup scripts that may run multiple times.

Safe table creation

CREATE TABLE IF NOT EXISTS activity_logs (
    id         INT          AUTO_INCREMENT PRIMARY KEY,
    user_id    INT          NOT NULL,
    action     VARCHAR(100) NOT NULL,
    created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
);

-- If the table already exists:
-- Query OK, 0 rows affected, 1 warning
-- Warning: Table 'activity_logs' already exists

N.3 — SELECT ... INTO OUTFILE (Export to CSV)

Writes query results directly to a file on the server's filesystem (not your local machine). The MySQL server process must have write permission to the directory. The file must not already exist.

Export to CSV

SELECT id, name, department, salary
FROM employees
INTO OUTFILE '/tmp/employees_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Resulting file: /tmp/employees_export.csv

"1","Alice Johnson","Engineering","85000.00"
"2","Bob Smith","Sales","62000.00"
"3","Carol White","HR","58000.00"

Note: The file is written on the MySQL server machine, not the client. If you need the file locally, use mysql -e "SELECT ..." > file.csv from the command line, or use mysqldump.

N.4 — LOAD DATA INFILE (Import from CSV)

The opposite of INTO OUTFILE — reads a CSV (or other delimited file) from the server's filesystem and bulk-loads it into a table. Extremely fast for large imports.

Import from CSV

LOAD DATA INFILE '/tmp/employees_import.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES    -- skip header row
(name, department, salary, hire_date);

-- Use LOCAL to load from the client machine instead:
LOAD DATA LOCAL INFILE 'C:/data/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

N.5 — DO expression (Execute Without Result)

DO executes an expression but does not return any result. Useful when you want a side-effect (like setting a variable or calling a function) without generating output.

DO — execute silently

-- Execute SLEEP without returning a result set
DO SLEEP(2);

-- Release a lock silently
DO RELEASE_LOCK('my_lock');

-- Equivalent to SELECT SLEEP(2), but no output returned

N.6 — HANDLER (Low-Level Table Access)

HANDLER provides direct, low-level access to a table's storage engine, bypassing the SQL optimizer. It is faster than SELECT for sequential scanning but does not support WHERE clauses, JOINs, or indexes (beyond basic navigation).

HANDLER open, read, close

-- Open a handler for the employees table
HANDLER employees OPEN;

-- Read the first row
HANDLER employees READ FIRST;

-- Read the next row
HANDLER employees READ NEXT;

-- Read next 5 rows
HANDLER employees READ NEXT LIMIT 5;

-- Close the handler
HANDLER employees CLOSE;

id	name	department	salary	hire_date
1	Alice Johnson	Engineering	85000.00	2020-03-15

N.7 — SHOW WARNINGS

Displays warnings generated by the last statement. Warnings are not errors — the statement still executed, but something might not be as expected (truncated data, implicit conversions, etc.).

Show warnings

-- This INSERT truncates a too-long string:
INSERT INTO employees (name, department) VALUES ('Alice', 'A very long department name that exceeds column length');

SHOW WARNINGS;

Level	Code	Message
Warning	1265	Data truncated for column 'department' at row 1

N.8 — SHOW ERRORS

Like SHOW WARNINGS but only shows actual errors (not warnings or notes). If the last statement succeeded, this returns an empty set.

Show errors

SHOW ERRORS;

Level	Code	Message
Error	1146	Table 'mydb.nonexistent_table' doesn't exist

N.9 — SET @user_var = value (User-Defined Variables)

User-defined variables (prefixed with @) live for the entire session and do not need DECLARE. They can hold any type. They are different from local variables (DECLARE) which only exist inside procedures.

Setting user variables

-- Set with SET statement
SET @company_name = 'Acme Corp';
SET @tax_rate = 0.08;
SET @max_id = (SELECT MAX(id) FROM employees);

-- Set with SELECT ... INTO
SELECT COUNT(*) INTO @emp_count FROM employees;

-- Set inline with SELECT (also displays it)
SELECT @running := @running + salary AS running_total
FROM employees, (SELECT @running := 0) init;

N.10 — SELECT @user_var (Read User Variable)

Reading user variables

SELECT @company_name, @tax_rate, @max_id, @emp_count;

@company_name	@tax_rate	@max_id	@emp_count
Acme Corp	0.08	25	25

Uninitialized variables: If you SELECT a user variable that was never SET, it returns NULL.

N.11 — SET SESSION wait_timeout = value

SESSION variables affect only your current connection. When the connection closes, the setting is gone. Every new connection starts with the global defaults.

Set session variable

-- Set timeout for this session only (in seconds)
SET SESSION wait_timeout = 3600;  -- 1 hour

-- These are equivalent:
SET wait_timeout = 3600;          -- SESSION is default
SET @@session.wait_timeout = 3600;

-- Verify
SELECT @@session.wait_timeout;

@@session.wait_timeout
3600

N.12 — SET GLOBAL max_connections = value

GLOBAL variables affect ALL new connections (but not existing ones). They reset when MySQL restarts unless saved in the config file. Requires the SUPER or SYSTEM_VARIABLES_ADMIN privilege.

Set global variable

-- Allow more simultaneous connections (requires SUPER privilege)
SET GLOBAL max_connections = 500;

-- Verify
SELECT @@global.max_connections;

@@global.max_connections
500

SESSION vs GLOBAL Scope

Aspect	SET SESSION	SET GLOBAL
Affects	Current connection only	All NEW connections
Existing connections	Only yours	Not affected until they reconnect
After MySQL restart	Gone	Also gone (use my.cnf for persistence)
Privilege needed	Usually none	SUPER or SYSTEM_VARIABLES_ADMIN

N.13 — OPTIMIZE TABLE (Defragment)

After many DELETEs and UPDATEs, a table can have wasted space and fragmented data files. OPTIMIZE TABLE reclaims unused space and reorganizes the data for better performance.

Defragment a table

OPTIMIZE TABLE employees;

Table	Op	Msg_type	Msg_text
mydb.employees	optimize	status	OK

InnoDB note: For InnoDB, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table. It acquires a lock briefly, so run during low-traffic periods.

N.14 — ANALYZE TABLE (Update Statistics)

Updates the table's index statistics so the query optimizer can make better decisions about which indexes to use. Run this after large data changes.

Update table statistics

ANALYZE TABLE employees;

Table	Op	Msg_type	Msg_text
mydb.employees	analyze	status	OK

N.15 — CHECK TABLE (Verify Integrity)

Checks if a table has any corruption or errors. It is a read-only operation — it does not fix anything, just reports problems.

Check table integrity

CHECK TABLE employees, orders;

Table	Op	Msg_type	Msg_text
mydb.employees	check	status	OK
mydb.orders	check	status	OK

N.16 — REPAIR TABLE (Fix Corruption)

Attempts to repair a corrupted table. Only works with MyISAM, ARCHIVE, and CSV storage engines — not InnoDB. For InnoDB corruption, you need to use different recovery methods (innodb_force_recovery, dump and reload).

Repair a MyISAM table

REPAIR TABLE legacy_data; -- must be MyISAM/ARCHIVE/CSV

Table	Op	Msg_type	Msg_text
mydb.legacy_data	repair	status	OK

InnoDB does not support REPAIR TABLE. If you run it on an InnoDB table, you will see: "The storage engine for the table doesn't support repair." For InnoDB, use ALTER TABLE tablename FORCE; to rebuild the table, or consult the MySQL recovery documentation.

Quick Reference: Table Maintenance Commands

Command	Purpose	When to Use	InnoDB?
OPTIMIZE TABLE	Defragment, reclaim space	After many deletes/updates	Yes (rebuilds table)
ANALYZE TABLE	Update index statistics	After large data changes	Yes
CHECK TABLE	Verify integrity	Routine health checks	Yes
REPAIR TABLE	Fix corruption	After crashes (MyISAM only)	No