Tutorials Logic, IN info@tutorialslogic.com
FAQs Support
Navigation
Home About Us Contact Us Blogs FAQs
Tutorials
HTML CSS JavaScript TypeScript Python Angular React Java Golang Node.js PHP MongoDB AWS Azure Google Cloud
All Tutorials
Services
Academic Projects Resume Writing Website Development
Practice
Quiz Challenge Interview Questions Certification Practice
Tools
Online Compiler JSON Formatter Regex Tester CSS Unit Converter Color Picker
Compiler Tools
  1. Home
  2. Interview Questions
  3. MySQL
MySQL

Top 50 MySQL Interview Questions

Curated questions covering queries, joins, indexes, transactions, stored procedures, normalization, and database optimization techniques.

01

What is MySQL and what are its key features?

MySQL is an open-source relational database management system (RDBMS). Key features: ACID compliance, support for SQL standard, multiple storage engines (InnoDB, MyISAM), replication, partitioning, full-text search, JSON support, and wide adoption in web applications (LAMP stack).

02

What is the difference between InnoDB and MyISAM storage engines?

  • InnoDB - supports ACID transactions, foreign keys, row-level locking, crash recovery. Default engine since MySQL 5.5.
  • MyISAM - no transactions, no foreign keys, tl-table-level locking. Faster for read-heavy workloads. No crash recovery.
  • Always use InnoDB for new applications.
03

What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

  • INNER JOIN - returns only rows with matching values in both tables.
  • LEFT JOIN - returns all rows from the left tl-table + matching rows from the right (NULL if no match).
  • RIGHT JOIN - returns all rows from the right tl-table + matching rows from the left.
  • FULL OUTER JOIN - returns all rows from both tables (NULL where no match). MySQL does not support FULL OUTER JOIN directly - use UNION of LEFT and RIGHT JOIN.
Example
SELECT u.name, o.total\nFROM users u\nINNER JOIN orders o ON u.id = o.user_id;\n\nSELECT u.name, o.total\nFROM users u\nLEFT JOIN orders o ON u.id = o.user_id; -- includes users with no orders
04

What is the difference between WHERE and HAVING?

  • WHERE - filters rows before grouping. Cannot use aggregate functions.
  • HAVING - filters groups after GROUP BY. Can use aggregate functions (SUM, COUNT, AVG).
Example
SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_sal\nFROM employees\nWHERE salary > 30000          -- filter rows first\nGROUP BY department\nHAVING COUNT(*) > 5;          -- filter groups after
05

What is the difference between DELETE, TRUNCATE, and DROP?

  • DELETE - DML. Removes specific rows with WHERE clause. Can be rolled back. Triggers fire. Slow for large tables.
  • TRUNCATE - DDL. Removes all rows. Cannot be rolled back. Faster. Resets AUTO_INCREMENT.
  • DROP - DDL. Removes the entire tl-table including structure. Cannot be rolled back.
Example
DELETE FROM users WHERE id = 1;  -- specific rows, rollback-able\nTRUNCATE tl-table temp_logs;         -- all rows, fast, no rollback\nDROP tl-table old_table;             -- removes tl-table entirely
06

What is normalization? Explain 1NF, 2NF, and 3NF.

  • 1NF - each column contains atomic values; no repeating groups; each tl-row is unique.
  • 2NF - in 1NF + no partial dependency (every non-key attribute depends on the entire primary key).
  • 3NF - in 2NF + no transitive dependency (non-key attributes depend only on the primary key).
  • BCNF - stricter version of 3NF.
07

What is the difference between primary key and unique key?

  • Primary key - uniquely identifies each row. Cannot be NULL. Only one per table. Automatically creates a clustered index.
  • Unique key - ensures all values in a column are unique. Can have NULL values (one NULL per column in MySQL). Multiple unique keys allowed per table.
Example
CREATE tl-table users (\n  id INT PRIMARY KEY AUTO_INCREMENT,\n  email VARCHAR(255) UNIQUE NOT NULL,\n  username VARCHAR(50) UNIQUE\n);
08

What is the difference between clustered and non-clustered indexes?

  • Clustered index - physically sorts and stores tl-table rows in index order. Only one per table. In InnoDB, the primary key is the clustered index.
  • Non-clustered index - separate structure with pointers to the actual rows. Multiple allowed per table.
Example
-- Primary key creates clustered index automatically\nCREATE tl-table users (id INT PRIMARY KEY, name VARCHAR(100));\n\n-- Non-clustered index\nCREATE INDEX idx_name ON users(name);\nCREATE INDEX idx_email_status ON users(email, status); -- composite
09

What is the difference between CHAR and VARCHAR?

  • CHAR(n) - fixed-length string. Always stores n bytes. Faster for fixed-length data. Pads with spaces.
  • VARCHAR(n) - variable-length string. Stores only actual length + 1-2 bytes overhead. More space-efficient for variable data.
  • Use CHAR for fixed-length data (country codes, hashes); VARCHAR for variable-length data.
Example
CREATE tl-table example (\n  country_code CHAR(2),      -- always 2 bytes\n  description VARCHAR(500)   -- variable length\n);
10

What is the difference between DATETIME and TIMESTAMP?

  • DATETIME - stores date and time. Range: 1000-01-01 to 9999-12-31. Not timezone-aware. 8 bytes.
  • TIMESTAMP - stores date and time as UTC. Range: 1970-01-01 to 2038-01-19. Timezone-aware (converts to/from UTC). 4 bytes. Auto-updates with DEFAULT CURRENT_TIMESTAMP.
Example
CREATE tl-table events (\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n  scheduled_at DATETIME  -- no timezone conversion\n);
11

What is the difference between UNION and UNION ALL?

  • UNION - combines result sets and removes duplicate rows. Slower due to deduplication.
  • UNION ALL - combines result sets and keeps all rows including duplicates. Faster.
  • Both require the same number of columns with compatible data types.
Example
SELECT name FROM customers\nUNION\nSELECT name FROM suppliers;  -- removes duplicates\n\nSELECT name FROM customers\nUNION ALL\nSELECT name FROM suppliers;  -- keeps duplicates, faster
12

What are window functions in MySQL?

Window functions perform calculations across a set of rows related to the current tl-row without collapsing them into groups. Available since MySQL 8.0.

Example
SELECT name, salary, department,\n  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,\n  SUM(salary) OVER (PARTITION BY department) as dept_total,\n  LAG(salary) OVER (ORDER BY salary) as prev_salary\nFROM employees;
13

What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

  • ROW_NUMBER() - assigns a unique sequential number to each row. No ties.
  • RANK() - assigns the same rank to ties, then skips numbers (1,1,3).
  • DENSE_RANK() - assigns the same rank to ties, no gaps (1,1,2).
Example
SELECT name, score,\n  ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,\n  RANK()       OVER (ORDER BY score DESC) as rank_val,\n  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank\nFROM students;
14

What is a stored procedure?

A stored procedure is a precompiled set of SQL statements stored in the database. Benefits: reusability, reduced network traffic, better performance, and centralized business logic.

Example
DELIMITER //\nCREATE PROCEDURE GetUserById(IN userId INT)\nBEGIN\n  SELECT * FROM users WHERE id = userId;\nEND //\nDELIMITER ;\n\nCALL GetUserById(1);
15

What is the difference between stored procedure and function?

  • Stored Procedure - can return multiple result sets; uses OUT parameters; called with CALL; can perform DML and DDL.
  • Function - must return a single value; called within SQL expressions (SELECT, WHERE); cannot perform DML in most cases.
Example
-- Function\nCREATE FUNCTION GetFullName(first VARCHAR(50), last VARCHAR(50))\nRETURNS VARCHAR(100)\nDETERMINISTIC\nRETURN CONCAT(first, " ", last);\n\nSELECT GetFullName(first_name, last_name) FROM users;
16

What is a trigger in MySQL?

A trigger automatically executes in response to INSERT, UPDATE, or DELETE events on a table. Types: BEFORE and AFTER.

Example
CREATE TRIGGER before_user_update\nBEFORE UPDATE ON users\nFOR EACH ROW\nBEGIN\n  INSERT INTO audit_log(user_id, old_email, new_email, changed_at)\n  VALUES (OLD.id, OLD.email, NEW.email, NOW());\nEND;
17

What is the difference between a view and a table?

  • Table - physically stores data. Has its own storage.
  • View - virtual tl-table based on a SELECT query. Does not store data. Always reflects current data. Can simplify complex queries and restrict access.
Example
CREATE VIEW active_users AS\nSELECT id, name, email\nFROM users\nWHERE status = "active";\n\nSELECT * FROM active_users; -- use like a table
18

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

  • EXPLAIN - shows the query execution plan without running the query. Shows estimated rows, indexes used, join types.
  • EXPLAIN ANALYZE (MySQL 8.0.18+) - actually executes the query and shows actual vs estimated statistics.
Example
EXPLAIN SELECT * FROM users WHERE email = "alice@example.com";\n-- Check: type (ALL=bad, ref/eq_ref=good), key (index used), rows (estimated)
19

What is the difference between index types: B-tree, Hash, and Full-text?

  • B-tree (default) - supports range queries, ORDER BY, GROUP BY, and equality. Most versatile.
  • Hash - only supports equality comparisons (=, IN). Faster for exact lookups but no range queries. Only in MEMORY engine.
  • Full-text - for text search. Supports MATCH...AGAINST syntax. Better than LIKE for text search.
20

What is the difference between INNER JOIN and subquery?

  • JOIN - combines rows from multiple tables. Generally faster as the optimizer can use indexes on both tables.
  • Subquery - a query nested inside another. Can be correlated (references outer query) or non-correlated. Sometimes less efficient.
Example
-- JOIN (usually faster)\nSELECT u.name FROM users u\nINNER JOIN orders o ON u.id = o.user_id;\n\n-- Subquery equivalent\nSELECT name FROM users\nWHERE id IN (SELECT user_id FROM orders);
21

What is the difference between correlated and non-correlated subqueries?

  • Non-correlated subquery - executes once independently of the outer query. Result is reused.
  • Correlated subquery - references columns from the outer query. Executes once per tl-row of the outer query. Can be slow.
Example
-- Non-correlated (executes once)\nSELECT * FROM users WHERE id IN (SELECT user_id FROM orders);\n\n-- Correlated (executes per tl-row - slow)\nSELECT * FROM users u\nWHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 5;
22

What is the difference between ACID properties in MySQL?

  • Atomicity - transaction is all-or-nothing. ROLLBACK undoes all changes.
  • Consistency - transaction brings database from one valid state to another.
  • Isolation - concurrent transactions do not interfere with each other.
  • Durability - committed transactions persist even after system failure (written to disk).
23

What are MySQL transaction isolation levels?

  • READ UNCOMMITTED - can read uncommitted changes (dirty reads possible).
  • READ COMMITTED - only reads committed data. Prevents dirty reads.
  • REPEATABLE READ (default in InnoDB) - same query returns same results within a transaction.
  • SERIALIZABLE - highest isolation. Transactions execute as if sequential.
Example
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;\nSTART TRANSACTION;\n  SELECT balance FROM accounts WHERE id = 1;\n  UPDATE accounts SET balance = balance - 100 WHERE id = 1;\nCOMMIT;
24

What is the difference between optimistic and pessimistic locking?

  • Pessimistic locking (SELECT ... FOR UPDATE) - locks the tl-row when read. Prevents others from modifying until released.
  • Optimistic locking - no lock on read. Uses a version column to detect conflicts at update time. Better for low-conflict scenarios.
Example
-- Pessimistic\nSTART TRANSACTION;\nSELECT * FROM products WHERE id = 1 FOR UPDATE;\nUPDATE products SET stock = stock - 1 WHERE id = 1;\nCOMMIT;\n\n-- Optimistic\nUPDATE products SET stock = stock-1, version = version+1\nWHERE id = 1 AND version = 5; -- fails if version changed
25

What is the difference between GROUP BY and DISTINCT?

  • DISTINCT - removes duplicate rows from the result set. Simple deduplication.
  • GROUP BY - groups rows for aggregate functions (COUNT, SUM, AVG). Can also deduplicate but is more powerful.
Example
SELECT DISTINCT department FROM employees;  -- unique departments\n\nSELECT department, COUNT(*), AVG(salary)\nFROM employees\nGROUP BY department;  -- aggregation per group
26

What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

  • COUNT(*) - counts all rows including NULLs.
  • COUNT(column) - counts non-NULL values in the column.
  • COUNT(DISTINCT column) - counts unique non-NULL values.
Example
SELECT\n  COUNT(*) as total_rows,\n  COUNT(email) as rows_with_email,\n  COUNT(DISTINCT email) as unique_emails\nFROM users;
27

What is the difference between IFNULL, COALESCE, and NULLIF?

  • IFNULL(expr, alt) - returns alt if expr is NULL. MySQL-specific.
  • COALESCE(v1, v2, ...) - returns the first non-NULL value. Standard SQL. Accepts multiple arguments.
  • NULLIF(v1, v2) - returns NULL if v1 = v2, otherwise returns v1. Useful to avoid division by zero.
Example
SELECT IFNULL(phone, "N/A") FROM users;\nSELECT COALESCE(phone, mobile, "N/A") FROM users;\nSELECT total / NULLIF(count, 0) FROM stats; -- avoid division by zero
28

What is the difference between LIKE and REGEXP?

  • LIKE - simple pattern matching. % matches any sequence, _ matches one character. Uses indexes for prefix patterns.
  • REGEXP (RLIKE) - full regular expression matching. More powerful but slower. Cannot use indexes.
Example
SELECT * FROM users WHERE name LIKE "Al%";      -- starts with Al\nSELECT * FROM users WHERE email REGEXP "^[a-z]+@gmail\.com$";
29

What is the difference between STRAIGHT_JOIN and regular JOIN?

STRAIGHT_JOIN forces MySQL to join tables in the order they appear in the query, overriding the optimizer. Use only when you know the optimizer is choosing a suboptimal join order.

Example
-- Force users to be the driving table\nSELECT STRAIGHT_JOIN u.name, o.total\nFROM users u\nINNER JOIN orders o ON u.id = o.user_id;
30

What is the difference between CHAR_LENGTH and LENGTH?

  • LENGTH(str) - returns the length in bytes. For multi-byte characters (UTF-8), one character may be multiple bytes.
  • CHAR_LENGTH(str) - returns the number of characters. Correct for multi-byte character sets.
Example
SET NAMES utf8mb4;\nSELECT LENGTH("hello");       -- 5 bytes\nSELECT CHAR_LENGTH("hello"); -- 5 chars\n-- For emoji (4 bytes in UTF-8):\nSELECT LENGTH("hi");       -- 6 bytes\nSELECT CHAR_LENGTH("hi"); -- 3 chars
31

What is the difference between CONCAT and CONCAT_WS?

  • CONCAT(s1, s2, ...) - concatenates strings. Returns NULL if any argument is NULL.
  • CONCAT_WS(separator, s1, s2, ...) - concatenates with a separator. Skips NULL values.
Example
SELECT CONCAT("Hello", " ", "World");          -- "Hello World"\nSELECT CONCAT("Hello", NULL, "World");         -- NULL\nSELECT CONCAT_WS(", ", "Alice", NULL, "NYC");  -- "Alice, NYC"
32

What is the difference between DATE_FORMAT and STR_TO_DATE?

  • DATE_FORMAT(date, format) - converts a date to a formatted string.
  • STR_TO_DATE(str, format) - converts a formatted string to a date.
Example
SELECT DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%s"); -- "2026-04-22 10:30:00"\nSELECT STR_TO_DATE("22/04/2026", "%d/%m/%Y");   -- 2026-04-22
33

What is the difference between DATEDIFF and TIMESTAMPDIFF?

  • DATEDIFF(d1, d2) - returns the difference in days between two dates.
  • TIMESTAMPDIFF(unit, d1, d2) - returns the difference in the specified unit (SECOND, MINUTE, HOUR, DAY, MONTH, YEAR).
Example
SELECT DATEDIFF("2026-12-31", "2026-01-01");  -- 364 days\nSELECT TIMESTAMPDIFF(MONTH, "2026-01-01", "2026-12-31"); -- 11 months
34

What is the difference between a composite index and multiple single indexes?

  • Composite index (a, b, c) - one index covering multiple columns. Efficient for queries filtering on the prefix columns. Follows leftmost prefix rule.
  • Multiple single indexes - separate indexes on each column. MySQL can only use one index per tl-table per query (usually).
  • Composite indexes are generally more efficient for multi-column queries.
Example
-- Composite index: efficient for WHERE a=? AND b=?\nCREATE INDEX idx_name_dept ON employees(last_name, department);\n\n-- Leftmost prefix rule: can use for:\n-- WHERE last_name = ?\n-- WHERE last_name = ? AND department = ?\n-- Cannot use for: WHERE department = ? alone
35

What is the difference between SAVEPOINT and ROLLBACK?

  • ROLLBACK - undoes all changes in the current transaction.
  • SAVEPOINT name - creates a named checkpoint within a transaction.
  • ROLLBACK TO SAVEPOINT name - undoes changes only back to the savepoint, not the entire transaction.
Example
START TRANSACTION;\n  INSERT INTO orders VALUES (1, 100);\n  SAVEPOINT after_order;\n  INSERT INTO payments VALUES (1, 100);\n  ROLLBACK TO SAVEPOINT after_order; -- undo payment only\nCOMMIT; -- commits the order
36

What is the difference between ENUM and SET data types?

  • ENUM - stores one value from a predefined list. Stored as an integer internally.
  • SET - stores zero or more values from a predefined list. Stored as a bitmask.
Example
CREATE tl-table shirts (\n  size ENUM("XS","S","M","L","XL"),\n  colors SET("red","green","blue","black")\n);\nINSERT INTO shirts VALUES ("M", "red,blue");
37

What is the difference between STRAIGHT_JOIN hint and USE INDEX hint?

  • STRAIGHT_JOIN - forces join order.
  • USE INDEX (idx) - suggests an index to use. MySQL may still choose a different one.
  • FORCE INDEX (idx) - forces MySQL to use the specified index.
  • IGNORE INDEX (idx) - tells MySQL not to use the specified index.
Example
SELECT * FROM users USE INDEX (idx_email) WHERE email = "a@b.com";\nSELECT * FROM users FORCE INDEX (idx_email) WHERE email = "a@b.com";
38

What is the difference between SHOW PROCESSLIST and INFORMATION_SCHEMA?

  • SHOW PROCESSLIST - shows currently running queries and their status. Useful for finding slow or blocking queries.
  • INFORMATION_SCHEMA - a virtual database with metadata about all databases, tables, columns, indexes, and constraints.
Example
SHOW PROCESSLIST;  -- active connections and queries\nSHOW FULL PROCESSLIST;  -- includes full query text\n\nSELECT table_name, table_rows, data_length\nFROM information_schema.tables\nWHERE table_schema = "mydb";
39

What is the difference between MySQL replication and clustering?

  • Replication - asynchronous or semi-synchronous copying of data from primary to replicas. Used for read scaling and backup.
  • MySQL Cluster (NDB) - synchronous multi-primary clustering. High availability with no single point of failure. More complex.
  • MySQL InnoDB Cluster - uses Group Replication for synchronous multi-primary setup with automatic failover.
40

What is the difference between FULLTEXT search and LIKE?

  • LIKE "%term%" - scans every row. Cannot use indexes. Very slow on large tables.
  • FULLTEXT search - uses an inverted index. Supports relevance ranking, boolean mode, and natural language mode. Much faster for text search.
Example
-- Create full-text index\nCREATE FULLTEXT INDEX ft_idx ON articles(title, body);\n\n-- Natural language search\nSELECT *, MATCH(title,body) AGAINST("mysql performance") AS score\nFROM articles\nWHERE MATCH(title,body) AGAINST("mysql performance")\nORDER BY score DESC;
41

What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?

  • ON DELETE CASCADE - when a parent tl-row is deleted, all child rows referencing it are automatically deleted.
  • ON DELETE SET NULL - when a parent tl-row is deleted, the foreign key column in child rows is set to NULL.
  • ON DELETE RESTRICT (default) - prevents deletion of parent tl-row if child rows exist.
Example
CREATE tl-table orders (\n  id INT PRIMARY KEY,\n  user_id INT,\n  FOREIGN KEY (user_id) REFERENCES users(id)\n    ON DELETE CASCADE\n    ON UPDATE CASCADE\n);
42

What is the difference between PARTITION BY and GROUP BY?

  • GROUP BY - collapses rows into groups. Returns one tl-row per group.
  • PARTITION BY (window function) - divides rows into partitions for window function calculations. All rows are preserved.
Example
-- GROUP BY: one tl-row per department\nSELECT department, AVG(salary) FROM employees GROUP BY department;\n\n-- PARTITION BY: all rows preserved with department average\nSELECT name, salary, department,\n  AVG(salary) OVER (PARTITION BY department) as dept_avg\nFROM employees;
43

What is the difference between TEMPORARY tl-table and regular table?

  • Temporary tl-table - exists only for the current session. Automatically dropped when session ends. Not visible to other sessions.
  • Regular tl-table - persists until explicitly dropped. Visible to all sessions with appropriate permissions.
Example
CREATE TEMPORARY tl-table temp_results AS\nSELECT user_id, SUM(amount) as total\nFROM orders\nGROUP BY user_id;\n\nSELECT u.name, t.total\nFROM users u JOIN temp_results t ON u.id = t.user_id;
44

What is the difference between CROSS JOIN and INNER JOIN?

  • CROSS JOIN - returns the Cartesian product of both tables (every tl-row from tl-table A combined with every tl-row from tl-table B). No join condition.
  • INNER JOIN - returns only rows where the join condition is met.
Example
-- CROSS JOIN: 3 * 4 = 12 rows\nSELECT * FROM colors CROSS JOIN sizes;\n\n-- INNER JOIN: only matching rows\nSELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;
45

What is the difference between SELF JOIN and regular JOIN?

A SELF JOIN joins a tl-table with itself. Used for hierarchical data (employees and managers) or comparing rows within the same table.

Example
-- Find employees and their managers\nSELECT e.name as employee, m.name as manager\nFROM employees e\nLEFT JOIN employees m ON e.manager_id = m.id;
46

What is the difference between BETWEEN and >= AND <=?

  • BETWEEN a AND b - inclusive range check. Equivalent to >= a AND <= b. More readable.
  • Both are equivalent in MySQL. BETWEEN works with dates, numbers, and strings.
Example
SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;\n-- Equivalent to:\nSELECT * FROM orders WHERE amount >= 100 AND amount <= 500;\n\nSELECT * FROM events WHERE event_date BETWEEN "2026-01-01" AND "2026-12-31";
47

What is the difference between IS NULL and = NULL?

  • IS NULL - correct way to check for NULL values. NULL is not equal to anything, including itself.
  • = NULL - always returns NULL (unknown), never TRUE. Never use = NULL in WHERE clauses.
Example
SELECT * FROM users WHERE phone IS NULL;     -- correct\nSELECT * FROM users WHERE phone = NULL;      -- always returns 0 rows!\nSELECT * FROM users WHERE phone IS NOT NULL; -- has a phone number
48

What is the difference between LIMIT and OFFSET?

  • LIMIT n - returns at most n rows.
  • LIMIT n OFFSET m - skips m rows then returns n rows. Used for pagination.
  • LIMIT m, n - shorthand for LIMIT n OFFSET m.
Example
SELECT * FROM products ORDER BY id LIMIT 10;          -- first 10\nSELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; -- rows 21-30\nSELECT * FROM products ORDER BY id LIMIT 20, 10;       -- same as above
49

What is the difference between CAST and CONVERT?

  • CAST(value AS type) - standard SQL syntax for type conversion.
  • CONVERT(value, type) - MySQL-specific syntax. Also has CONVERT(value USING charset) for character set conversion.
Example
SELECT CAST("42" AS UNSIGNED);           -- 42\nSELECT CAST("2026-04-22" AS DATE);       -- date\nSELECT CONVERT("42", UNSIGNED);          -- 42\nSELECT CONVERT("hello" USING utf8mb4);   -- charset conversion
50

What is the difference between SHOW TABLES and INFORMATION_SCHEMA.TABLES?

  • SHOW TABLES - simple command to list tables in the current database.
  • INFORMATION_SCHEMA.TABLES - provides detailed metadata: tl-row count, data size, engine, creation time, etc. Queryable with SQL.
Example
SHOW TABLES;  -- simple list\nSHOW TABLES LIKE "user%";  -- filtered\n\nSELECT table_name, engine, table_rows, data_length\nFROM information_schema.tables\nWHERE table_schema = DATABASE()\nORDER BY data_length DESC;

Level Up Your MySQL Skills

Master MySQL with these hand-picked resources

Interview Questions
Ace your next interview
50+ questions with detailed answers
Prepare Now
All MySQL Tutorials
Complete learning path
From basics to advanced concepts
View All
10,000+ learners
Free forever
Updated 2026
Previous Next

Ready to Level Up Your Skills?

Explore 500+ free tutorials across 20+ languages and frameworks.

Browse Tutorials Contact Us