Topic 40 of 52 · Advanced
Functions Reference: System, Info & Security
Session and metadata functions, IDs, hashing/encryption helpers, locks and stored routines.
Concept
Information functions tell you about the session and the last statement: the current user, database, version, inserted id and affected rows.
Hashing (MD5, SHA2) is one-way and used for checksums; AES_ENCRYPT is reversible encryption with a key. Never store passwords with MD5 — hash them with bcrypt/argon2 in the application.
You can also write your own functions and procedures with CREATE FUNCTION / CREATE PROCEDURE and call them like built-ins.
Cheat sheet
| Function / command | What it does | Example → result |
|---|---|---|
| DATABASE() | Current database | DATABASE() → 'campus' |
| USER() / CURRENT_USER() | Login user / user used for privilege checks | CURRENT_USER() → 'app@%' |
| VERSION() | Server version | VERSION() → '8.0.36' |
| CONNECTION_ID() | Session id | CONNECTION_ID() → 42 |
| LAST_INSERT_ID() | Last AUTO_INCREMENT value generated in this session | LAST_INSERT_ID() → 101 |
| ROW_COUNT() | Rows changed by the previous statement | ROW_COUNT() → 3 |
| FOUND_ROWS() | Rows found by the previous SELECT (deprecated) | FOUND_ROWS() |
| UUID() / UUID_TO_BIN / BIN_TO_UUID | Unique id / compact 16-byte storage | UUID() → '3f06af63-...' |
| MD5(s) / SHA1(s) / SHA2(s, 256) | One-way hashes | SHA2('abc',256) → 'ba7816bf...' |
| AES_ENCRYPT(s, key) / AES_DECRYPT | Reversible encryption | AES_DECRYPT(AES_ENCRYPT('x','k'),'k') → 'x' |
| TO_BASE64 / FROM_BASE64 | Base64 encode / decode | TO_BASE64('sql') → 'c3Fs' |
| RANDOM_BYTES(n) | Cryptographically random bytes | HEX(RANDOM_BYTES(4)) |
| SLEEP(n) | Pause n seconds (testing) | SLEEP(1) → 0 |
| GET_LOCK / RELEASE_LOCK | Named application lock | GET_LOCK('payroll', 10) → 1 |
| BENCHMARK(n, expr) | Times an expression | BENCHMARK(1000000, MD5('a')) |
| SHOW TABLES / DESCRIBE t / SHOW CREATE TABLE t | Inspect schema | DESCRIBE students; |
| EXPLAIN / EXPLAIN ANALYZE | Query plan / actual timing | EXPLAIN SELECT * FROM students WHERE id = 1; |
| information_schema.COLUMNS | Metadata about every column | SELECT column_name FROM information_schema.columns WHERE table_name='students'; |
| CREATE FUNCTION | User-defined scalar function | CREATE FUNCTION grade(m INT) RETURNS CHAR(1) DETERMINISTIC RETURN IF(m>=60,'P','F'); |
| CREATE PROCEDURE / CALL | Stored procedure | CALL monthly_report(2026, 11); |
Code (SQL · MySQL 8)
-- Insert and read back the new id
INSERT INTO students (name, dept_id) VALUES ('Kiran', 1);
SELECT LAST_INSERT_ID() AS new_id, ROW_COUNT() AS rows_added;
-- Session info
SELECT DATABASE(), CURRENT_USER(), VERSION(), CONNECTION_ID();
-- A user-defined function used like a built-in
DELIMITER //
CREATE FUNCTION grade_of(m DECIMAL(5,2)) RETURNS VARCHAR(12) DETERMINISTIC
BEGIN
RETURN CASE WHEN m >= 75 THEN 'Distinction'
WHEN m >= 60 THEN 'First'
WHEN m >= 35 THEN 'Pass'
ELSE 'Fail' END;
END //
DELIMITER ;
SELECT name, grade_of(marks) AS grade, SHA2(email, 256) AS email_hash
FROM students;Remember
- LAST_INSERT_ID() is per connection — safe under concurrency.
- Hashes are one-way; encryption is reversible with the key.
- Use EXPLAIN to check if your query uses an index.
- Mark functions DETERMINISTIC only when the same input always gives the same output.
Interview questions
Is LAST_INSERT_ID() affected by other users' inserts?
No. It returns the last auto-generated id from the current connection only.
Function vs stored procedure?
A function returns a single value and can be used inside SELECT; a procedure is invoked with CALL, can return result sets and OUT parameters, and can run transactions.
Why not store passwords with MD5 or SHA2 in SQL?
They are fast, unsalted hashes that are easy to brute force; use slow salted algorithms like bcrypt or argon2 in the application layer.
Lesson test
Take an 8-question test on “Functions Reference: System, Info & Security” — concepts, output prediction, bug spotting and interview questions.