Much of the MySQL interview prep you’ll find online is based on outdated versions that reached end-of-life years ago.
If your answer to a connection question still mentions mysql_pconnect(), or you write string comparisons without quotes, an experienced interviewer will immediately know you haven’t worked with a modern MySQL server.
This is the third installment in our MySQL interview series, and every question and example has been verified on a currently supported MySQL release.
If you haven’t read the previous parts yet, they’re a great place to start before continuing.
All examples in this article were tested on a MySQL 9.7 LTS server.
Whenever a command or behavior is different from MySQL 5.7, we’ll point it out, since those version differences are common interview topics.
To keep things simple, every example uses the same users table, so it’s easier to follow along as you work through the questions.
Two built-in functions can quickly show this information.
VERSION() displays the MySQL server version, while DATABASE() shows the database currently selected for your session.
The NULL value means you haven’t selected a database yet.
Choose one with the USE command, then run the query again.
Interviewers may also ask which MySQL versions are currently supported.
MySQL now has two release tracks:
Older releases such as 5.7 and 8.0 have reached end-of-life and no longer receive security updates.
If you need more details about your current MySQL session, such as the connection ID, server version, character set, and socket path, use the s (status) command.
This command is useful when troubleshooting connection issues or confirming the server you’re connected to during an interview or while working on a production system.
To exclude a specific value, you can use the != operator (or , which works the same way).
Since ‘Sam’ is a string, it must be enclosed in quotes.
Without quotes, MySQL assumes Sam is a column name and returns an Unknown column error.
Now let’s run a similar query on the email column, which contains a NULL value.
Notice that Gunjit is missing from the results.
That’s because the email value is NULL.
In MySQL, comparing anything with NULL doesn’t return TRUE or FALSE, it returns NULL.
Since the WHERE clause only keeps rows where the condition is TRUE, rows containing NULL are filtered out.
If you want NULL values to be treated as comparable values, use the NULL-safe equality operator () together with NOT.
This time, Gunjit appears in the results because NULL ‘[email protected]’ evaluates to FALSE, and NOT FALSE becomes TRUE.
This is a common interview question because it tests whether you understand how MySQL handles NULL values in comparisons.
Yes.
The NOT, AND, and OR operators can all be used together in the same WHERE clause.
For example, the following query returns every user who is not from Mumbai and does not have more than 1,000 posts.
The condition inside the parentheses matches only Ravi, who is from Mumbai and has more than 1,000 posts.
The NOT operator reverses that result, so every other row is returned.
This query can also be written without using NOT by applying De Morgan’s Law.
Both queries return the same four rows.
A simple rule to remember is:
When you combine AND and OR in the same query, always use parentheses to make your logic clear.
They also help avoid mistakes, especially in more complex queries.
The IFNULL() function checks whether a value is NULL.
This is commonly used to replace missing values with something more readable in query results.
Here, Gunjit’s email is NULL, so IFNULL() replaces it with not provided.
If you need to check more than two values, use COALESCE() instead.
It accepts multiple arguments and returns the first value that isn’t NULL.
In this example, Gunjit’s email is NULL, so COALESCE() returns the value from the city column instead.
If both email and city were NULL, it would return ‘unknown’.
Another related function you’ll often see is NULLIF().
It returns NULL if a and b are equal; otherwise, it returns a.
It’s commonly used in calculations to avoid divide-by-zero errors.
The LIMIT clause controls how many rows a query returns.
To make sure you always get the expected rows, use it together with ORDER BY.
For example, to display the earliest user based on the joined date:
To get the most recently joined users, sort the results in descending order with DESC.
You can also use OFFSET to skip a number of rows before returning the results.
This is commonly used for pagination.
The following query skips the first two rows and returns the next two.
One important thing to remember is that using LIMIT without ORDER BY doesn’t guarantee a consistent result.
MySQL can return rows in any order, so always specify how the rows should be sorted before limiting them.
Another common interview question is about pagination performance.
While LIMIT with OFFSET works well for small result sets, large offsets become slower because MySQL still has to scan and skip all the preceding rows before returning the requested ones.
For large tables, a better approach is keyset (seek) pagination, where you continue from the last value you retrieved instead of skipping thousands of rows.
This approach is much more efficient because MySQL can jump directly to the matching rows instead of reading and discarding a large number of records first.
This is a common interview question, especially for Linux administrator and database roles.
MySQL and MariaDB share the same roots, but they’ve evolved into separate database systems over the years.
While they still support much of the same SQL syntax, they’re no longer considered drop-in replacements for each other.
Reasons to choose MySQL:
Reasons to choose MariaDB:
There isn’t a single “best” choice.
The right answer depends on your environment and requirements.
In an interview, a good answer is that MySQL and MariaDB have diverged since MySQL 5.5.
Although they remain similar in many ways, they have different features, release cycles, and compatibility rules.
Replication isn’t supported in every direction between the two, and moving databases from one to the other may require changes to dump files or application code.
In practice, most organizations choose the database system that’s already supported by their application stack, Linux distribution, or cloud provider.
MySQL provides several built-in functions for working with the current date and time.
Each one serves a slightly different purpose, so it’s useful to know when to use each.
Here’s what each function returns:
You’ll also see CURRENT_DATE(), which is simply another name for CURDATE().
One interview question that comes up frequently is the difference between NOW() and SYSDATE().
For example:
Notice that both calls to NOW() return the same timestamp, while each call to SYSDATE() returns the current system time at the moment it is executed.
Because of this behavior, SYSDATE() isn’t considered safe for statement-based replication, while NOW() is.
You can export the output of a query as an XML file by combining the MySQL client’s –xml and -e options.
Here’s what each option does:
A common interview misconception is that -e means export.
It actually stands for –execute, which simply tells the MySQL client to execute the specified SQL statement and then exit.
The XML output comes from the –xml option, not from -e.
If you want to export an entire database in XML format instead of a single query result, use mysqldump.
This command exports every table in the tecmint database as XML.
You may also be asked about exporting data as JSON.
The classic mysql client doesn’t provide a –json option.
If you need JSON output, you can either:
The mysql_* extension, including mysql_pconnect(), is no longer available in modern PHP.
It was deprecated in PHP 5.5 and removed completely in PHP 7.0.
That means functions like mysql_connect(), mysql_pconnect(), and mysql_close() don’t exist in any supported PHP version today.
If an interviewer asks about persistent connections, the correct answer is to use either PDO or MySQLi.
With PDO, enable persistent connections by setting the PDO::ATTR_PERSISTENT attribute.
With MySQLi, use the p: prefix before the hostname.
The idea behind a persistent connection is simple.
Instead of opening a new database connection for every request, PHP reuses an existing connection whenever possible.
This avoids the overhead of creating a new TCP connection and authenticating with the MySQL server each time.
However, persistent connections also have some drawbacks:
Because of these trade-offs, persistent connections aren’t always the best choice.
They’re most useful for applications with high traffic where the benefits of reusing connections outweigh the additional resource usage.
To view all indexes on a table, use the SHOW INDEX statement.
This command displays information about every index on the table, including:
Notice the G at the end of the command.
Instead of displaying the output as a wide table, it prints each row vertically, making it much easier to read when there are many columns.
One feature introduced in MySQL 8.0 is invisible indexes.
An invisible index is still updated whenever data changes, but the query optimizer ignores it.
You can make an index invisible like this:
This is useful when you want to find out whether an index is actually needed before deleting it.
If queries continue to perform well, you can safely remove the index later.
If performance drops, simply make the index visible again.
Because the index is still maintained while it’s invisible, changing it back to VISIBLE is almost instant.
This is much faster and safer than dropping an index and rebuilding it on a large production table.
If you regularly back up MySQL databases, it’s also worth automating the process.
Instead of running mysqldump manually, you can schedule backups with a Bash script, add log rotation, and configure alerts to notify you if a backup fails.
This question is about the CSV storage engine, not CSV files in general.
When you create a table using ENGINE=CSV, MySQL stores the table data as a plain comma-separated values (CSV) file on disk.
Since it’s a regular text file, you can open it with a spreadsheet application or any text editor.
Here’s an example:
mysql> CREATE TABLE reports (
-> id INT NOT NULL,
-> city VARCHAR(30) NOT NULL
-> ) ENGINE=CSV;
Query OK, 0 rows affected (0.02 sec)
When the table is created, MySQL generates two files in the database directory:
The CSV storage engine has several limitations:
Because of these limitations, the CSV storage engine is mainly used for data exchange, not for everyday database tables.
If your goal is simply to export data as a CSV file, it’s usually better to keep your table as InnoDB and export the results using SELECT …
INTO OUTFILE.
A common reason is that the client doesn’t support the authentication method used by newer MySQL servers.
The default authentication plugin has changed over the years:
If you’re using an older MySQL client or connector that only supports mysql_native_password, you’ll get an authentication error when connecting to a newer server.
You can check which authentication plugin a user account is using with:
If the account is using caching_sha2_password, the best solution is to upgrade your MySQL client or connector.
Downgrading the server or trying to switch back to the old authentication plugin is generally not recommended.
The caching_sha2_password plugin provides stronger security by using either a TLS-encrypted connection or an RSA key exchange during authentication.
Another related change that often appears in interviews is how GRANT works.
Older MySQL versions could create a user automatically when you ran a GRANT statement.
Modern MySQL no longer allows this.
You must create the user first and then grant the required privileges.
This change helps prevent accidentally creating user accounts with incorrect names or privileges.
This is a common MySQL interview question because many people assume utf8 supports all Unicode characters but it doesn’t.
The original MySQL utf8 character set stores up to 3 bytes per character, which means it can’t store 4-byte Unicode characters such as many emojis and some less common language characters.
The utf8mb4 character set supports up to 4 bytes per character, allowing it to store the entire Unicode character set.
Starting with MySQL 8.0, utf8mb4 became the default character set, along with the utf8mb4_0900_ai_ci collation.
The old utf8 alias now points to utf8mb3, which is deprecated and will be removed in a future MySQL release.
You can check the server’s default character set with:
If you have an older table that still uses utf8mb3, you can convert it to utf8mb4 with:
This command converts all character columns in the table to utf8mb4.
Keep in mind that MySQL rebuilds the table during the conversion, so it can take some time for large tables.
One more thing to watch for is index size.
Since utf8mb4 uses up to 4 bytes per character, indexed VARCHAR columns require more storage than they did with utf8mb3.
In some cases, you may need to shorten the indexed column or use a prefix index after converting the table.
This usually happens because the ONLY_FULL_GROUP_BY SQL mode is enabled.
Starting with MySQL 5.7, ONLY_FULL_GROUP_BY is enabled by default and it requires every column in the SELECT list to either:
For example, this query fails because name is neither grouped nor aggregated:
In older MySQL versions, this query often worked, but the value returned for name was arbitrary and could change depending on the data.
Modern MySQL prevents this by reporting an error.
The correct solution is to use an aggregate function for the non-grouped column.
You can check the current SQL mode with:
It’s possible to disable ONLY_FULL_GROUP_BY at the session or server level, but that’s usually not the right solution.
In an interview, the best answer is that you would fix the query, not disable the SQL mode.
The check exists to prevent ambiguous queries and ensure the results are correct and predictable.
MySQL 8.0 introduced Common Table Expressions (CTEs) and window functions, making many queries simpler and easier to read.
These features are now common interview topics because they replace many of the complex subqueries used in older MySQL versions.
A CTE starts with the WITH keyword and creates a temporary named result set that you can reference in the main query.
In this example:
Unlike GROUP BY, window functions don’t combine rows.
They return every row from the query while adding calculated values such as rankings, running totals, or averages.
If you want the ranking to restart for each city, add a PARTITION BY clause inside the OVER() clause.
Interviewers also like to ask about the difference between MySQL’s ranking functions:
Knowing when to use each one is a good way to show you’re comfortable writing modern MySQL queries instead of relying on older subquery-based approaches.
Don’t just read these questions—run every query on a local MySQL server before your next interview.
The candidates who stand out are the ones who can explain not only what a query does, but also why it works and what happens when it fails.
The best way to build that confidence is through hands-on practice.
If you’ve been asked a MySQL interview question that isn’t covered here, share it in the comments along with how you answered it.
Your feedback helps shape the next part of this interview series, so other readers can prepare for the questions companies are asking today.
Preparing for a MySQL interview isn’t about memorizing syntax, it’s about understanding how MySQL behaves in real-world situations.
Many interview questions are based on features that have changed in recent releases, so practicing on a current MySQL version is just as important as knowing the SQL itself.
The 15 questions in this article covered common topics such as NULL handling, GROUP BY, window functions, authentication, character sets, indexes, and modern MySQL features that interviewers frequently ask about.
Spend some time running each example on your own system, experimenting with different inputs, and understanding the output.
The more hands-on experience you have, the easier it becomes to explain your reasoning during an interview and that’s often what makes the difference between simply knowing the answer and landing the job.
20 Linux Networking Interview Questions and Answers for 2026
15 Linux Interview Questions and Answers for System Administrators
10 Core Linux Interview Questions and Answers – Part 4
10 Linux Interview Questions with Examples – Part 3
Top 15 VsFTP Server Interview Questions with Detailed Answers
15 Linux Interview Questions with Answers (Level Up) – Part 2
Very Nice Post sir..
It was very helpful for me for crack MySql Interview.
Thanks For Share It was Very Helpful.
Regards,
Mahesh
Thanks this will be very helpful for my up coming interview.
it is very help full contents.
thanks for updating this content.
The command to extract xml is by either using -X flag; or use –xml (two dashes)
Thank you.
it is really helpful.
Keep posting these kind of valuable interview questions.
it really helps
Number 3 is actually wrong.
Your logic is X AND Y == !X OR !Y
When it should actually be X AND Y == !(!X OR !Y)
hello this is really very good content …………….but sir i want more specific which help me to use with my php code….thanks
Dear DJ_HITMAN,
If you have to ask any question not relataed to above topic, you may ask it at linuxsay.com.
linuxsay.com is a forum of ours where you can get solution to your problems.
It’s very useful…Thanks.
Welcome Shailesh, Keep Connected!
Hi sir,
i am a commerce graduate.
but i love computer science.
now i am learning mysql Database.
it’s really help.
Thank you very much for sharing this article.
Thanks
G s krishna
Welcome Krishna, keep connected!
How to Install Icinga2 Monitoring Tool on Ubuntu 20.04/22.04
How to Monitor Remote Linux Systems with Glances
How to Configure Custom Access and Error Log Formats in Nginx
nload – Monitor Linux Network Bandwidth Usage in Real Time
Dool – All-in-One Linux Server Performance Monitoring Tool
A Shell Script to Monitor Network, Diske, Uptime, Load, and RAM in Linux
MultiCD – Create a MultiBoot Linux Live USB
How to Block USB Storage Devices in Linux Servers
How to Reconfigure Installed Package in Ubuntu and Debian
How to Use ‘cat’ and ‘tac’ Commands with Examples in Linux
12 Practical Examples of Linux Grep Command
Zaloha.sh – A Simple Local Directory Synchronizer Script for Linux
10 Best Clipboard Managers for Linux
7 Best CCleaner Alternatives for Ubuntu
Top 5 Open-Source Enterprise Software for Linux in 2024
5 Best Platforms for Hosting Your Web Projects in 2024
How to Open and Edit Apple iWork Files on Linux
6 Best Whiteboard Applications for Your Linux Systems
—
**📚 Original Source:**
[15 Advanced MySQL Database Interview Questions and Answers](https://www.tecmint.com/mysql-advance-interview-questions/)
About The Author
