Part 7: Procedures, Transactions, Admin & Data Types

Section 16 — Stored Procedures, Functions & Triggers
Section 17 — Transactions
Section 18 — User & Permission Management
Section 19 — Utility Statements
Section 20 — Data Types Reference

Stored Procedures, Functions & Triggers

16.1 — DELIMITER

What it does: MySQL normally uses ; to mark the end of each statement. But stored procedures contain multiple semicolons inside the body. If we don't change the delimiter, MySQL would think the procedure definition ends at the first ; it finds. So we temporarily switch to a different delimiter (like //) so MySQL waits until the whole procedure is finished.

Changing the Delimiter

-- Default delimiter is ;
-- Change it so we can write procedures with ; inside:
DELIMITER //

-- Now we can write a procedure body with semicolons...
CREATE PROCEDURE hello_world()
BEGIN
    SELECT 'Hello from procedure!';   -- this ; does NOT end the CREATE
    SELECT NOW();                    -- neither does this one
END //   -- THIS // ends the CREATE PROCEDURE

-- Switch back to normal delimiter:
DELIMITER ;

Why DELIMITER is Needed

Normal mode: ; = end of statement

→

Problem: procedure has ; inside!

→

Solution: temporarily use // as end marker

16.2 — CREATE PROCEDURE with IN Parameter

What it does: Creates a reusable procedure that accepts input values. Think of it like a recipe — you give it ingredients (the IN parameter), and it does something with them. IN means "I am passing a value into the procedure."

Procedure to get an employee's sales

DELIMITER //

CREATE PROCEDURE get_employee_sales(IN p_employee_id INT)
BEGIN
    SELECT
        p.name AS product_name,
        o.total,
        o.status,
        c.name AS customer_name,
        o.order_date
    FROM orders o
    JOIN products p ON o.product_id = p.id
    JOIN customers c ON o.customer_id = c.id
    WHERE o.employee_id = p_employee_id
    ORDER BY o.order_date DESC;
END //

DELIMITER ;

Call the procedure

CALL get_employee_sales(101);

product_name	total	status	customer_name	order_date
Wireless Keyboard	149.50	Shipped	Acme Corp	2026-02-20
USB-C Hub	75.00	Delivered	GlobalTech Inc	2026-02-20
Monitor Stand	250.00	Processing	Bright Solutions	2026-02-19

16.3 — CREATE PROCEDURE with OUT Parameter

What it does: A procedure with an OUT parameter gives you back a value. Instead of showing a table, it stores a value in a variable you can use afterward.

Procedure that returns a count of pending orders

DELIMITER //

CREATE PROCEDURE count_pending_orders(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total
    FROM orders
    WHERE status = 'Pending';
END //

DELIMITER ;

Call and read the output variable

CALL count_pending_orders(@pending_total);
SELECT @pending_total AS pending_orders;

pending_orders
23

16.4 — CREATE PROCEDURE with INOUT Parameter

What it does: An INOUT parameter works both ways — you pass a value in, the procedure modifies it, and you get the modified value back. Like handing someone a form, they fill in extra fields, and hand it back.

Procedure that applies an express shipping surcharge

DELIMITER //

CREATE PROCEDURE apply_express_surcharge(INOUT amount DECIMAL(10,2))
BEGIN
    -- Express orders cost 50% more
    SET amount = amount * 1.50;
END //

DELIMITER ;

Pass value in, get modified value back

SET @price = 200.00;
CALL apply_express_surcharge(@price);
SELECT @price AS express_price;

express_price
300.00

Note: The original value (200.00) went in, got multiplied by 1.50 inside the procedure, and came back as 300.00.

16.5 — CALL procedure_name()

What it does: CALL executes a stored procedure. It is how you "run" a procedure after creating it. You pass arguments for IN/INOUT parameters, and user variables for OUT/INOUT parameters.

Different ways to CALL procedures

-- No parameters
CALL refresh_daily_stats();

-- With IN parameter (pass a literal value)
CALL get_employee_sales(101);

-- With OUT parameter (pass a user variable to receive a value)
CALL count_pending_orders(@total);
SELECT @total;

-- With INOUT parameter
SET @val = 100;
CALL apply_express_surcharge(@val);
SELECT @val;

Parameter Type	What You Pass	Direction
IN	A literal value or expression	Into the procedure
OUT	A @user_variable	Out from the procedure
INOUT	A @user_variable with initial value	Both directions

16.6 — CREATE FUNCTION

What it does: A function is like a procedure, but it returns a single value and can be used directly inside SELECT statements — anywhere you would use a built-in function like NOW(). It must be marked DETERMINISTIC (same input always produces the same output) or READS SQL DATA.

Function to calculate years of employment from hire date

DELIMITER //

CREATE FUNCTION years_employed(hire_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, hire_date, CURDATE());
END //

DELIMITER ;

Use the function inside a SELECT query

SELECT
    name,
    hire_date,
    years_employed(hire_date) AS tenure
FROM employees
LIMIT 4;

name	hire_date	tenure
Maria Santos	1985-04-12	40
Juan Reyes	1970-11-30	55
Ana Cruz	2001-07-22	24
Carlos Lim	1958-01-05	68

Key difference: Procedures are invoked with CALL. Functions are used inside SELECT, WHERE, and other expressions — just like built-in functions such as NOW() or UPPER().

16.7 — DROP PROCEDURE / DROP FUNCTION

What it does: Removes a stored procedure or function from the database permanently. Use IF EXISTS to avoid an error if it has already been removed.

Drop a procedure and a function

DROP PROCEDURE IF EXISTS get_employee_sales;

DROP FUNCTION IF EXISTS years_employed;

Statement	Effect
`DROP PROCEDURE get_employee_sales`	Procedure removed from database
`DROP FUNCTION years_employed`	Function removed from database

16.8 — CREATE TRIGGER BEFORE INSERT

What it does: A trigger runs automatically when a certain event happens (INSERT, UPDATE, or DELETE). A BEFORE INSERT trigger fires before the row is actually written, so you can modify the data being inserted. The keyword NEW refers to the incoming row.

Auto-set created_at timestamp on every insert

DELIMITER //

CREATE TRIGGER trg_employees_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END //

DELIMITER ;

Insert WITHOUT specifying created_at

INSERT INTO employees (name, department, salary)
VALUES ('Rosa Mendoza', 'Marketing', 55000.00);

SELECT name, created_at
FROM employees WHERE name = 'Rosa Mendoza';

name	created_at
Rosa Mendoza	2026-02-23 09:14:32

Notice: We never set created_at in our INSERT statement, but the trigger automatically filled it in with the current timestamp!

16.9 — CREATE TRIGGER AFTER INSERT

What it does: Fires after a row has been successfully inserted. Perfect for logging or creating related records automatically. You can read the newly inserted data with NEW.

Auto-create an activity log entry when a new order is inserted

DELIMITER //

CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO activity_log (action, table_name, record_id, description, created_at)
    VALUES (
        'INSERT',
        'orders',
        NEW.id,
        CONCAT('New order created for customer ', NEW.customer_id),
        NOW()
    );
END //

DELIMITER ;

Insert a new order, then check the activity log

INSERT INTO orders (customer_id, product_id, status)
VALUES (101, 5, 'Pending');

-- Check the activity log - entry was auto-created!
SELECT * FROM activity_log ORDER BY log_id DESC LIMIT 1;

log_id	action	table_name	record_id	description	created_at
847	INSERT	orders	502	New order created for customer 101	2026-02-23 09:16:05

16.10 — CREATE TRIGGER BEFORE UPDATE

What it does: Fires before a row is updated. You can access both OLD (the current values before the change) and NEW (the values about to be written). Perfect for auto-setting updated_at timestamps.

Auto-set updated_at on every update to products

DELIMITER //

CREATE TRIGGER trg_products_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //

DELIMITER ;

Update a product price — updated_at is set automatically

UPDATE products SET price = 15.99
WHERE id = 200;

SELECT id, price, updated_at
FROM products WHERE id = 200;

id	price	updated_at
200	15.99	2026-02-23 09:18:44

16.11 — CREATE TRIGGER AFTER DELETE

What it does: Fires after a row has been deleted. Use OLD to access the values of the row that was just removed. Great for keeping a permanent record of what was deleted.

Log deleted orders for audit trail

DELIMITER //

CREATE TRIGGER trg_orders_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO activity_log (action, table_name, record_id, description, created_at)
    VALUES (
        'DELETE',
        'orders',
        OLD.id,
        CONCAT('Deleted order #', OLD.id,
               ' for customer ', OLD.customer_id,
               ' (status: ', OLD.status, ')'),
        NOW()
    );
END //

DELIMITER ;

Delete an order, then verify the activity log

DELETE FROM orders WHERE id = 502;

SELECT * FROM activity_log ORDER BY log_id DESC LIMIT 1;

log_id	action	table_name	record_id	description	created_at
848	DELETE	orders	502	Deleted order #502 for customer 101 (status: Pending)	2026-02-23 09:20:11

Trigger Timeline — Order of Execution

BEFORE Trigger fires

→

Actual INSERT / UPDATE / DELETE executes

→

AFTER Trigger fires

Trigger Type	Access OLD?	Access NEW?	Can Modify NEW?
BEFORE INSERT	No	Yes	Yes
AFTER INSERT	No	Yes (read-only)	No
BEFORE UPDATE	Yes	Yes	Yes
AFTER UPDATE	Yes	Yes (read-only)	No
BEFORE DELETE	Yes	No	N/A
AFTER DELETE	Yes (read-only)	No	N/A

Transactions

17.1 — START TRANSACTION

What it does: Begins a transaction — a group of SQL statements that must all succeed together or all fail together. Think of it as "draft mode": nothing is saved permanently until you say COMMIT.

Begin a transaction

START TRANSACTION;

-- All changes from here are "pending" until you COMMIT or ROLLBACK

Statement	Effect
`START TRANSACTION`	Opens a new transaction; auto-commit is suspended
`BEGIN`	Alias for START TRANSACTION (same effect)

17.2 — COMMIT

What it does: Permanently saves all changes made since START TRANSACTION. Once committed, the changes are visible to everyone and cannot be undone with ROLLBACK.

Commit the transaction

COMMIT;

-- All pending changes are now permanently saved to disk

Statement	Effect
`COMMIT`	Saves all pending changes permanently

17.3 — ROLLBACK

What it does: Undoes ALL changes made since START TRANSACTION. It is like pressing "Cancel" — everything goes back to the way it was before the transaction started.

Rollback the transaction

ROLLBACK;

-- All pending changes are discarded as if they never happened

Statement	Effect
`ROLLBACK`	Discards all changes since START TRANSACTION

17.4 — Full Transaction Example (COMMIT)

Scenario: Transfer 500.00 from Account 101 to Account 102. Both updates must succeed — you cannot debit one account without crediting the other.

Step 1: Check balances BEFORE the transfer

SELECT id, balance FROM accounts
WHERE id IN (101, 102);

id	balance
101	2000.00
102	500.00

Step 2: Perform the transfer inside a transaction

START TRANSACTION;

-- Debit Account 101
UPDATE accounts SET balance = balance - 500.00
WHERE id = 101;

-- Credit Account 102
UPDATE accounts SET balance = balance + 500.00
WHERE id = 102;

-- Both succeeded, save permanently
COMMIT;

Step 3: Check balances AFTER the transfer

SELECT id, balance FROM accounts
WHERE id IN (101, 102);

id	balance
101	1500.00
102	1000.00

Transfer Flow Visual

Account 101: $2000

→

-$500

→

Account 101: $1500

Account 102: $500

→

+$500

→

Account 102: $1000

17.5 — ROLLBACK Example (Undo Mistakes)

Scenario: You accidentally set an account balance to zero, but you catch the mistake and ROLLBACK before committing. The data remains unchanged.

Step 1: Check balance BEFORE

SELECT id, balance FROM accounts WHERE id = 101;

id	balance
101	1500.00

Step 2: Make a mistake inside a transaction

START TRANSACTION;

-- Oops! Accidentally set balance to zero
UPDATE accounts SET balance = 0
WHERE id = 101;

-- Wait, that was a mistake! Undo everything.
ROLLBACK;

Step 3: Check balance AFTER — it is unchanged!

SELECT id, balance FROM accounts WHERE id = 101;

id	balance
101	1500.00

Key takeaway: The ROLLBACK completely undid the UPDATE. The balance is still 1500.00 — as if nothing happened.

17.6 — SAVEPOINT

What it does: Creates a named checkpoint within a transaction. Like saving your game progress — you can go back to that specific point without losing everything you did before it.

Create a savepoint within a transaction

START TRANSACTION;

-- Do some work...
UPDATE accounts SET balance = balance - 100 WHERE id = 101;

-- Save progress at this point
SAVEPOINT after_first_update;

-- Can now ROLLBACK TO after_first_update if later steps fail

Statement	Effect
`SAVEPOINT sp_name`	Creates a named checkpoint in the current transaction
`RELEASE SAVEPOINT sp_name`	Removes the savepoint (does not rollback or commit)

17.7 — ROLLBACK TO SAVEPOINT

What it does: Rolls back to a specific savepoint, undoing only the changes made after that savepoint. Everything before the savepoint is kept.

Full example: 3 operations, savepoint after 2nd, rollback 3rd

START TRANSACTION;

-- Operation 1: Deduct service fee
UPDATE accounts SET balance = balance - 100 WHERE id = 101;

-- Operation 2: Add loyalty bonus
UPDATE accounts SET balance = balance + 80 WHERE id = 101;

-- Save progress after operations 1 and 2
SAVEPOINT after_bonus;

-- Operation 3: Deduct express fee (we change our mind later)
UPDATE accounts SET balance = balance - 200 WHERE id = 101;

-- Undo operation 3 only, keep 1 and 2
ROLLBACK TO after_bonus;

-- Commit operations 1 and 2
COMMIT;

Savepoint Visual — What Gets Kept vs. Undone

Step	Operation	Balance	Status
Start	Initial balance	1500.00	—
Op 1	Deduct service fee (-100)	1400.00	KEPT
Op 2	Loyalty bonus (+80)	1480.00	KEPT
--- SAVEPOINT after_bonus ---
Op 3	Express fee (-200)	1280.00	UNDONE
Final	After COMMIT	1480.00	COMMITTED

17.8 — SET autocommit = 0

What it does: By default, MySQL auto-commits every single statement immediately. Setting autocommit = 0 means you must explicitly COMMIT to save any changes. This gives you a safety net — nothing is permanent until you say so.

Check and change the autocommit setting

-- Check current setting
SELECT @@autocommit;

@@autocommit
1

-- Disable auto-commit for this session
SET autocommit = 0;

-- Now EVERY change requires an explicit COMMIT
UPDATE accounts SET balance = 999 WHERE id = 101;

-- This change is NOT saved yet! Must commit:
COMMIT;

-- Re-enable auto-commit when done
SET autocommit = 1;

Setting	Behavior
`autocommit = 1` (default)	Every statement is automatically saved immediately
`autocommit = 0`	You must call COMMIT manually to save changes

Transaction Lifecycle Flow Diagram

START TRANSACTION

→

SQL Operations
INSERT / UPDATE / DELETE

→

Success?

▼ Yes

COMMIT

▼

Changes Saved Permanently

▼ No / Error

ROLLBACK

▼

All Changes Undone

Optional: Use SAVEPOINT within the operations for partial rollback capability

User & Permission Management

18.1 — CREATE USER

What it does: Creates a new MySQL user account. The format 'user'@'host' specifies both the username and where they are allowed to connect from. 'localhost' means only from this machine; '%' means from any machine on the network.

Create application database users

-- Database admin (connects locally only)
CREATE USER 'db_admin'@'localhost'
IDENTIFIED BY 'Str0ng_P@ss!2026';

-- Application user
CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'AppUser#Secure9';

-- Data entry clerk (connects from any machine)
CREATE USER 'data_entry'@'%'
IDENTIFIED BY 'D@taEntry2026';

-- Read-only report viewer
CREATE USER 'report_viewer'@'localhost'
IDENTIFIED BY 'V1ew_Only!';

User Created	Host	Result
db_admin	localhost	Query OK, 0 rows affected
app_user	localhost	Query OK, 0 rows affected
data_entry	% (anywhere)	Query OK, 0 rows affected
report_viewer	localhost	Query OK, 0 rows affected

18.2 — GRANT ALL PRIVILEGES ON db.*

What it does: Gives a user every permission on all tables in a database. Only do this for admin-level users who need full control.

Give db_admin full access to the company database

GRANT ALL PRIVILEGES ON company_db.*
TO 'db_admin'@'localhost';

User	Database	Permissions
db_admin@localhost	company_db.*	ALL (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, etc.)

18.3 — GRANT Specific Privileges

What it does: Gives a user only the specific permissions they need. This follows the principle of least privilege — only grant what is necessary for their job.

Grant targeted permissions to each application role

-- App user: can read, add, and update orders
GRANT SELECT, INSERT, UPDATE
ON company_db.orders
TO 'app_user'@'localhost';

-- App user: can also read products and customers tables
GRANT SELECT ON company_db.products TO 'app_user'@'localhost';
GRANT SELECT ON company_db.customers TO 'app_user'@'localhost';

-- Data entry: can read customers, create new orders
GRANT SELECT ON company_db.customers TO 'data_entry'@'%';
GRANT SELECT, INSERT ON company_db.orders TO 'data_entry'@'%';

-- Report viewer: read-only on the entire database
GRANT SELECT ON company_db.* TO 'report_viewer'@'localhost';

User	Table(s)	Permissions Granted
app_user	orders	SELECT, INSERT, UPDATE
app_user	products, customers	SELECT
data_entry	customers	SELECT
data_entry	orders	SELECT, INSERT
report_viewer	company_db.* (all tables)	SELECT

18.4 — REVOKE

What it does: Takes away a specific permission from a user. If someone no longer needs INSERT access, you remove just that permission without deleting the whole user.

Remove INSERT permission from data_entry on orders

REVOKE INSERT ON company_db.orders
FROM 'data_entry'@'%';

User	Table	Before REVOKE	After REVOKE
data_entry@%	orders	SELECT, INSERT	SELECT only

18.5 — SHOW GRANTS FOR

What it does: Displays all permissions assigned to a specific user. Very useful for auditing who has access to what.

View permissions for app_user

SHOW GRANTS FOR 'app_user'@'localhost';

Grants for app_user@localhost
`GRANT USAGE ON . TO 'app_user'@'localhost'`
`GRANT SELECT, INSERT, UPDATE ON company_db.orders TO 'app_user'@'localhost'`
`GRANT SELECT ON company_db.products TO 'app_user'@'localhost'`
`GRANT SELECT ON company_db.customers TO 'app_user'@'localhost'`

Note: GRANT USAGE ON *.* means "no special global privileges" — it is the default baseline that just allows the user to connect to the server.

18.6 — DROP USER

What it does: Completely removes a user account and all their permissions. The user can no longer connect to the database.

Remove a user

DROP USER IF EXISTS 'report_viewer'@'localhost';

Statement	Effect
`DROP USER 'report_viewer'@'localhost'`	User removed, all grants deleted, cannot connect

18.7 — CREATE ROLE (MySQL 8.0+)

What it does: Creates a named role that you can assign permissions to. Then you assign the role to users. Instead of granting permissions to each user individually, you grant them to the role once and assign the role to many users. Much easier to manage when you have many users!

Create roles for the application

CREATE ROLE 'admin_role', 'editor_role', 'readonly_role';

-- Grant permissions to each role
GRANT ALL PRIVILEGES ON company_db.* TO 'admin_role';

GRANT SELECT, INSERT, UPDATE
ON company_db.orders TO 'editor_role';

GRANT SELECT ON company_db.* TO 'readonly_role';

Role	Permissions
admin_role	ALL PRIVILEGES on company_db.*
editor_role	SELECT, INSERT, UPDATE on company_db.orders
readonly_role	SELECT on company_db.*

18.8 — GRANT Role TO User

What it does: Assigns a role to a user so they inherit all the permissions of that role. You also need to SET DEFAULT ROLE so the role is automatically activated when the user logs in.

Assign roles to users

-- Assign roles to users
GRANT 'admin_role' TO 'db_admin'@'localhost';
GRANT 'editor_role' TO 'app_user'@'localhost';
GRANT 'readonly_role' TO 'data_entry'@'%';

-- Activate roles automatically on login
SET DEFAULT ROLE ALL TO
    'db_admin'@'localhost',
    'app_user'@'localhost',
    'data_entry'@'%';

User	Assigned Role	Inherited Permissions
db_admin	admin_role	ALL on company_db.*
app_user	editor_role	SELECT, INSERT, UPDATE on orders
data_entry	readonly_role	SELECT on company_db.*

18.9 — FLUSH PRIVILEGES

What it does: Forces MySQL to reload the grant tables from disk. Normally not needed after GRANT/REVOKE (they auto-reload), but necessary if you manually edited the mysql system tables directly.

Reload privilege tables

FLUSH PRIVILEGES;

Statement	Result
`FLUSH PRIVILEGES`	Query OK, 0 rows affected

When to use: Only needed if you directly INSERT/UPDATE/DELETE rows in the mysql.user or mysql.db system tables. If you use GRANT and REVOKE, MySQL reloads automatically.

MySQL Permission Hierarchy — From Broadest to Most Specific

SERVER LEVEL
Global privileges (all databases)

DATABASE LEVEL
All tables in one db

TABLE LEVEL
Specific table

COLUMN LEVEL
Specific column

Broadest scope ←—————————————————→ Most specific scope

Higher-level grants automatically apply to all levels below. For example, server-level SELECT allows reading every table in every database.

Application Access Map — Who Can Do What

db_admin

Database Administrator

SELECT all tables
INSERT all tables
UPDATE all tables
DELETE all tables
CREATE / ALTER / DROP

app_user

Application User

SELECT orders, products, customers
INSERT orders
UPDATE orders
DELETE anything
CREATE / ALTER / DROP

data_entry

Data Entry Clerk

SELECT customers
SELECT orders
INSERT orders
UPDATE orders
DELETE anything

report_viewer

Report Viewer

SELECT all tables
INSERT anything
UPDATE anything
DELETE anything
CREATE / ALTER / DROP

Utility Statements

19.1 — SHOW DATABASES

What it does: Lists all databases on the MySQL server that you have permission to see.

List all databases

SHOW DATABASES;

Database
information_schema
company_db
mysql
performance_schema
sys

19.2 — SHOW TABLES

What it does: Lists all tables in the currently selected database.

List all tables in the company database

USE company_db;
SHOW TABLES;

Tables_in_company_db
accounts
activity_log
customers
departments
employees
orders
permissions
products
roles
role_permissions
users

19.3 — SHOW COLUMNS FROM / DESCRIBE

What it does: Shows the structure of a table — column names, data types, whether NULL is allowed, keys, and defaults. DESCRIBE and SHOW COLUMNS FROM produce the same output.

Show the structure of the employees table

DESCRIBE employees;
-- Same as: SHOW COLUMNS FROM employees;

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
name	varchar(100)	NO		NULL
department	varchar(50)	YES		NULL
salary	decimal(10,2)	YES		NULL
hire_date	date	YES		NULL
created_at	datetime	YES		CURRENT_TIMESTAMP	DEFAULT_GENERATED

19.4 — SHOW CREATE TABLE

What it does: Shows the exact CREATE TABLE statement needed to recreate the table from scratch, including all column definitions, indexes, constraints, and engine settings.

Show the CREATE statement for employees

SHOW CREATE TABLE employees\G

Output

CREATE TABLE employees (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  department varchar(50) DEFAULT NULL,
  salary decimal(10,2) DEFAULT NULL,
  hire_date date DEFAULT NULL,
  created_at datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci

19.5 — SHOW PROCESSLIST

What it does: Shows all currently active connections to the MySQL server. Useful for finding slow queries, stuck connections, or seeing who is connected right now.

Show active connections

SHOW PROCESSLIST;

Id	User	Host	db	Command	Time	State	Info
5	root	localhost	company_db	Query	0	init	SHOW PROCESSLIST
12	db_admin	localhost	company_db	Sleep	45		NULL
18	app_user	localhost	company_db	Query	3	executing	SELECT * FROM orders WHERE...

19.6 — SHOW VARIABLES LIKE '...'

What it does: Displays MySQL server configuration settings. Use LIKE with a pattern to filter for specific variables. The % wildcard matches any characters.

Show server configuration settings

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer%';

Variable_name	Value
max_connections	151

Variable_name	Value
innodb_buffer_pool_chunk_size	134217728
innodb_buffer_pool_instances	1
innodb_buffer_pool_size	134217728

19.7 — SHOW STATUS

What it does: Shows server performance counters — how many queries have run, how many connections exist, how long the server has been up, and more.

Show useful server statistics

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Uptime';

Variable_name	Value	Meaning
Threads_connected	3	Currently connected clients
Questions	48923	Total queries executed since server start
Uptime	259200	Seconds since server started (3 days)

19.8 — SHOW INDEXES FROM table

What it does: Lists all indexes on a table, including the primary key. Indexes speed up searches but use extra disk space.

Show indexes on the orders table

SHOW INDEXES FROM orders;

Table	Key_name	Column_name	Non_unique	Index_type
orders	PRIMARY	id	0	BTREE
orders	idx_customer_id	customer_id	1	BTREE
orders	idx_product_id	product_id	1	BTREE
orders	idx_order_date	order_date	1	BTREE

Non_unique: 0 means the index enforces uniqueness (like PRIMARY KEY). 1 means duplicates are allowed.

19.9 — SHOW TABLE STATUS

What it does: Shows metadata about tables — engine type, estimated row count, data size, index size, and creation date. Useful for understanding storage usage.

Show table metadata

SHOW TABLE STATUS LIKE 'employees';

Name	Engine	Rows	Data_length	Index_length	Create_time
employees	InnoDB	1247	196608	16384	2026-01-10 08:30:00

Note: For InnoDB tables, the Rows value is an estimate, not an exact count. Use SELECT COUNT(*) FROM employees for a precise number.

Data Types Reference

20.1 — Numeric Types

Integer types store whole numbers. Pick the smallest type that fits your range to save storage space.

Type	Bytes	Signed Range	Unsigned Range	Common Use Case
`TINYINT`	1	-128 to 127	0 to 255	Age, status codes, flags
`SMALLINT`	2	-32,768 to 32,767	0 to 65,535	Department numbers, small counts
`MEDIUMINT`	3	-8,388,608 to 8,388,607	0 to 16,777,215	Medium-range IDs
`INT`	4	-2.1 billion to 2.1 billion	0 to 4.3 billion	Primary keys, employee IDs, order IDs
`BIGINT`	8	-9.2 quintillion to 9.2 quintillion	0 to 18.4 quintillion	Very large counters, activity log IDs

Decimal and floating-point types store numbers with fractional parts.

Type	Precision	Storage	Common Use Case
`DECIMAL(m,d)`	Exact — m total digits, d after decimal	~4 bytes per 9 digits	Prices, salaries, account balances
`FLOAT`	Approximate, ~7 significant digits	4 bytes	Non-critical sensor readings
`DOUBLE`	Approximate, ~15 significant digits	8 bytes	Scientific calculations

DECIMAL vs FLOAT — When Precision Matters

DECIMAL(10,2) stores exact values like 123.45. FLOAT might store it as 123.44999695... Always use DECIMAL for money, prices, and financial values where precision is critical. Use FLOAT or DOUBLE only for approximate scientific measurements.

BIT type

Type	Description	Range	Use Case
`BIT(n)`	Stores n bits (1 to 64)	BIT(1) = 0 or 1	Boolean flags, bit masks

20.2 — String Types

CHAR vs VARCHAR — the two main types for short text.

Type	Max Length	Storage	Padding	Best For
`CHAR(n)`	255 characters	Always n bytes	Pads with spaces to fill n	Fixed-length codes (status, state)
`VARCHAR(n)`	65,535 characters	Actual length + 1-2 bytes	No padding at all	Names, addresses, descriptions

Visual: CHAR(10) vs VARCHAR(10) — Storing the word "Sale"

Type	Stored As	Bytes Used
`CHAR(10)`	`S a l e _ _ _ _ _ _` (padded)	10 bytes always
`VARCHAR(10)`	`S a l e` (no padding)	5 bytes (4 + 1 length byte)

TEXT types — for longer text content.

Type	Max Size	Approximate	Common Use Case
`TINYTEXT`	255 bytes	~255 characters	Short notes, brief comments
`TEXT`	65,535 bytes	~64 KB	Product descriptions, order notes
`MEDIUMTEXT`	16,777,215 bytes	~16 MB	Full report content, articles
`LONGTEXT`	4,294,967,295 bytes	~4 GB	Rarely needed — very large documents

BLOB types — for binary data (images, files, etc.).

Type	Max Size	Use Case
`TINYBLOB`	255 bytes	Tiny binary data
`BLOB`	64 KB	Small images, thumbnails
`MEDIUMBLOB`	16 MB	Product images, document scans
`LONGBLOB`	4 GB	Large files (prefer filesystem storage instead)

ENUM — pick exactly one value from a predefined list.

ENUM with order statuses

CREATE TABLE orders (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    status   ENUM('Pending', 'Processing', 'Shipped',
                  'Delivered', 'Cancelled') DEFAULT 'Pending',
    priority ENUM('Low', 'Normal', 'High') DEFAULT 'Normal'
);

Feature	Detail
Storage	1-2 bytes (very efficient)
Max distinct values	65,535
Stored internally as	Integer index (1, 2, 3, ...)

SET — pick multiple values from a predefined list.

SET example — a product can belong to multiple categories

CREATE TABLE product_tags (
    product_id INT PRIMARY KEY,
    name       VARCHAR(100),
    tags       SET('Electronics', 'Sale', 'Featured', 'New', 'Clearance')
);

INSERT INTO product_tags VALUES
    (1, 'Wireless Mouse', 'Electronics'),
    (2, 'USB-C Hub',      'Electronics,Sale');

product_id	name	tags
1	Wireless Mouse	Electronics
2	USB-C Hub	Electronics,Sale

20.3 — Date/Time Types

Type	Format	Range	Bytes	Common Use Case
`DATE`	YYYY-MM-DD	1000-01-01 to 9999-12-31	3	Hire date, order date
`TIME`	HH:MM:SS	-838:59:59 to 838:59:59	3	Duration of task, elapsed time
`DATETIME`	YYYY-MM-DD HH:MM:SS	1000-01-01 to 9999-12-31	8	Scheduled events, historical dates
`TIMESTAMP`	YYYY-MM-DD HH:MM:SS	1970-01-01 to 2038-01-19	4	created_at, updated_at tracking
`YEAR`	YYYY	1901 to 2155	1	Fiscal year, model year

DATETIME vs TIMESTAMP — Key Differences

Feature	DATETIME	TIMESTAMP
Date range	1000-01-01 to 9999-12-31	1970-01-01 to 2038-01-19
Storage size	8 bytes	4 bytes (more efficient)
Time zone handling	Stores exactly as given (no conversion)	Converts to UTC on save, back to session tz on read
Auto-update support	Must set manually	Can auto-update on INSERT/UPDATE
Best for	Historical dates (hire dates, events)	Tracking when records were created/modified

20.4 — Other Types (JSON, BOOLEAN)

JSON — stores valid JSON documents. MySQL 8.0 can index and query inside JSON fields.

Storing and querying JSON data

CREATE TABLE product_config (
    config_id INT PRIMARY KEY,
    settings  JSON
);

INSERT INTO product_config VALUES (1,
    '{"currency": "USD", "min_price": 10, "max_price": 500}'
);

-- Extract values from JSON using ->> operator
SELECT
    config_id,
    settings->>'$.currency'  AS currency,
    settings->>'$.min_price' AS min_price,
    settings->>'$.max_price' AS max_price
FROM product_config;

config_id	currency	min_price	max_price
1	USD	10	500

BOOLEAN — this is not a real separate type. It is just an alias for TINYINT(1).

What You Write	What MySQL Actually Stores	TRUE value	FALSE value
`BOOLEAN` or `BOOL`	`TINYINT(1)`	1	0

BOOLEAN in practice

CREATE TABLE products (
    id        INT PRIMARY KEY,
    name      VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE   -- stored as TINYINT(1) DEFAULT 1
);

SELECT name, is_active FROM products;

name	is_active
Wireless Keyboard	1
USB-C Hub	1
Legacy Adapter X	0

20.5 — Which Data Type Should I Use?

Quick reference for choosing the right data type in common scenarios:

Storing money?DECIMAL(10,2)

Storing age?TINYINT UNSIGNED

Storing a name?VARCHAR(100)

Storing a status?ENUM or VARCHAR(20)

Storing an address?VARCHAR(255)

Date only?DATE

Date + time?DATETIME

Tracking changes?TIMESTAMP

Primary key?INT UNSIGNED AUTO_INCREMENT

Yes/no flag?BOOLEAN (TINYINT 1)

Product price?DECIMAL(8,2)

Config / metadata?JSON

Fixed-length code?CHAR(n)

Storing an image?MEDIUMBLOB or filesystem

General Rules of Thumb

Use the smallest type that fits your data — saves storage and improves performance.
Use UNSIGNED when values are never negative (IDs, ages, counts).
Use DECIMAL instead of FLOAT for money and financial values where exact precision matters.
Use VARCHAR over CHAR unless the length is truly fixed (like 2-letter state codes).
Use TIMESTAMP for tracking record changes; DATETIME for dates you set manually.
Use ENUM when the list of values is small and rarely changes.
Store large files (images, PDFs) in the filesystem and save the file path in the database — this is faster than BLOB.

MySQL 8.0 Learning Guide — Part 7: Procedures, Transactions, Admin & Data Types

← Back to Main Guide