Top 20 MySQL Interview Questions Answered

MySQL is a multi-user SQL database management system founded by MySQL AB. Currently, Oracle owns MYSQL. It is the world’s 2nd most used & popular open-source database, i.e., free to use. A significant factor behind its popularity is its compatibility with different languages (like C, C++, JAVA, PHP, PERL, etc.), notably PHP. PHP is mostly used in web development accompanied by MySQL.

Being popular, interviewers expect a candidate to be thorough with MySQL. Here are Top MySQL Interview Questions, which might be pretty helpful to you.

1) What are the technical specifications of MySQL?


  • High Efficiency
  • Easy to Use & Manageable
  • Flexible Structure
  • Replication
  • Portable
  • Security & Storage Management

2) Is there any difference between MySQL & SQL? If yes, then what?


SQL or Structured Query Language is a database language used for creation, deletion, retrieving or modifying, etc. of the database. Meanwhile, MySQL is a database that stores different types of data safely. So MySQL is a database & SQL is a database language which works on MySQL.

3) MySQL supports large databases up to which extent?


MySQL supports up to 50 million rows or tables. And the table limit is up to 4 GB. However, you can increase these limits according to your operating system’s strength. Theoretically, the size of the table can extend up to 8 million terabytes. 

4) List all categories of datatypes of MySQL.


Type of data declared is termed as “Datatype.” Each data type has a fixed amount of space which will be allotted if declared. Example: declaration of int will create 2 bits. MySQL has three categories of data types; Numeric, Time & String. 

There are eight datatypes categorized in Numeric, five in the Time & eight in the String. 

5) What are the different tables in MySQL?


There are five different tables in MySQL, out of which MyISAM is the default database engine. 

  • MyISAM
  • Heap
  • Merge
  • ISAM

6) Name the triggers allowed in MySQL.

Ans: There are six triggers allowed in MySQL as follows:

  1. Before Insert
  2. After Insert
  3. Before Update
  4. After Update
  5. Before Delete
  6. After Delete 

7) What are the differences between the heap table & temporary table?


Heap Table:

  • Found in memory. It works as storage temporarily. 
  • BLOB & TEXT fields aren’t allowed
  • Indexes should be “NOT NULL”
  • Doesn’t supports “AUTO_INCREMENT”
  • Can be shared among clients
  • Only comparison operators can be used (=,<,>,>=, <=)

Temporary Table:

  • Used to store provisional data
  • Temporarily stored data is deleted after client session ends
  • Aren’t shared among clients
  • Special syntax is used; “create temporary table”

8) What is the difference between MySQL_connect & MySQL_pconnect?



  • Opens a new connection to the database
  • Depending on request you need to open & close a database connection
  • Opens page whenever loaded


  • In MySQL_pconnect, “p” stands for “Persistent” so its persistent connection
  • A database cannot be closed
  • No need to open & close thus suitable for sites with huge traffic

9) How to display nth highest salary from a table in MySQL?


  • nth highest salary:
    • Select distinct(salary) from the employee order by salary desc limit (n-1),1
  • 3rd highest salary:
    • Select distinct(salary) from employee order by salary desc limit 2,1
  • 6th highest salary:
    • Select distinct(salary) from employee order by salary desc limit 5,1

10) What is BLOB & TEXT in MySQL?



  • BLOB or Binary Large Object stores the variable amount of data. 
  • There are four types of BLOB:
    • BLOB(size)
Data Type SyntaxMaximum Size
TINYBLOBMaximum size of 255 Bytes
BLOB(size)Maximum size of 65,535 Bytes
MEDIUMBLOBMaximum size of 16,777,215 Bytes


  • Case-insensitive BLOB
  • Values are a character string or non-binary string
  • Have a character set
  • Four types of TEXT:
  • TEXT     
Data Type SyntaxMaximum Size
TINYTEXTMaximum Size of 255 characters
TEXT(size)Maximum Size of 65,535 characters
MEDIUMTEXTMaximum Size of 16,777,215 characters
LONGTEXTMaximum Size of 4 GB or 4,294,967,295 characters

The factor that makes BLOB & TEXT different is “sorting & comparison.” It is performed case sensitive for BLOB & case-insensitive for TEXT values.

11) What are the differences between CHAR & VARCHAR?

Column Length is fixedColumn Length isn’t fixed
Maximum characters holding capacity: 255 charactersMaximum characters holding capacity: 4000 characters
Faster than VARCHARLittle slower than CHAR
Uses Static Memory AllocationUses Dynamic Memory Allocation

12) What is SQLyog?


SQLyog program is a GUI tool for admin. It’s a popular MySQL manager & admin tool. It’s an integration of MySQL administration, phpMyAdmin & other MySQL GUI tools.

13) How is the MyISAM table stored?


You can store MyISAM table in the following format on disk:

  • ‘.frm‘ : File
  • .MYI‘: (MY Index) : Index file
  • .MYD‘: (MYData) : Data File

14) How can we make a conversion between UNIX & MySQL timestamp?


  • UNIX_TIMESTAMP is the command used to convert MySQL timestamp to Unix timestamp
  • FROM_TIMESTAMP is the command used to convert Unix timestamp to MySQL timestamp

15) What is the difference between UNIX & MySQL timestamp?


You can store UNIX & MySQL timestamp in 32-bit integers. However, MySQL is represented in “YYYY-MM-DD HH:MM:SS” format.

16) How to use “Like Condition”?


LIKE condition or pattern patch has three primary rules for use:

  • Used in the pattern to match anyone character
  • “%” corresponds to zero or more characters
  • “_” is exactly one character
  • ESCAPE used to provide ESCAPE character in the pattern

17) What are Regular Expressions? State their uses.


Regular Expressions are for complex search in a string. It’s a way of specifying patterns for search. 

PatternWhat it matches
*Zero instance or more instances of the preceding element
^Beginning of String
$End of String
+One instance or more instances of the preceding element
.Any Single Character
[…]Characters are written/listed between square brackets
[^…]Characters not written/listed between square brackets
{n}N instances of preceding element
{m,n}M through to n instances of preceding element
|Matches any one of the pattern
Example: p1|p2|p3 means p1,p2 or p3
?0 or 1 instance of the preceding element
  REGEXPMatches input character with database
Example: REGEXP ‘abc’ or REGEXP ‘10’

18) For what we use “i-am-a-dummy flag” in MySQL?


 “i-am-a-dummy” flag is used at places where we want to deny or stop “UPDATE & DELETE” commands unless there’s a WHERE clause present.

19) How to add a new column & delete an existing column in a table?


  • Add a new column:
    • ALTER TABLE ADD COLUMN column_name datatype(size);
  • Delete an existing column:
    • ALTER TABLE DROP COLUMN column_name;

20) What are the common MySQL functions?


CONCATAdds two or more strings/fields together to form one
CONCAT_WSSimilar like CONCAT but has a separator
INSERTInserts a string within a string at a specific position
REVERSEReverses a string 
STRCMPCompares two strings
LOWER & UPPERConverts string into lowercase & uppercase respectively
FORMATFormats a specified position from A to B in “#”
Example: “###IA”
CURRDATE() & CURRTIME()Returns current date & time
SUBSTRExtracts substring from a specific position of a string
REPLACEReplaces values of a substring within a string with a new substring 


These were Top MySQL Interview Questions commonly asked by interviewers. The purpose is to make you equipped for it. I hope you liked it!

By Dynamic Web Training

Dynamic Web Training is Australia's leading provider of instructor led software training.

We offer training courses in Adobe, Web Design, Graphic Design, Photoshop, InDesign, Dreamweaver and many more.