3 Essential MySQL Interview Questions *

question badge

Consider the following table definition in a MySQL database:

CREATE TABLE example (
  text1 VARCHAR(32) NOT NULL,
  text2 VARCHAR(32) NOT NULL DEFAULT 'foo'

What will be the result of the two INSERT statements below if strict SQL mode is enabled? How will the result differ if strict SQL mode is not enabled? Explain your answer.

INSERT INTO example (id) VALUES(1);
INSERT INTO example (text1) VALUES('test');
answer badge

Since the text1 and text2 columns are marked as NOT NULL and have no explicit DEFAULT clauses, attempts to insert implicit NULL values into these columns should presumably fail (note that you can use the SHOW CREATE TABLE statement to see which columns have an explicit DEFAULT clause). So accordingly, these two INSERT statements should fail since they don’t specify a value for one or more of these columns.

If strict SQL mode is enabled, an error will indeed occur and the statements will be rolled back. (Note: In the case of a multi-row statement on a non-transactional table, an error occurs, but if this happens for the second or subsequent row of the statement, the preceding rows will have been inserted.)

However, if strict SQL mode is *not enabled,* MySQL sets the column to the implicit default value for the column data type rather than failing.

In MySQL, implicit defaults are determined as follows:

  • For numeric types, the default is typically zero.
  • For date and time types, the default is the appropriate “zero” value for the type. (This actually a bit more complicated for fields with the type TIMESTAMP.)
  • For string types (other than ENUM), the default value is the empty string. For ENUM, the default is the first enumeration value.

Therefore, after the above two INSERT statements are executed, the contents of the table will be as follows:

mysql> SELECT * FROM example;
| id | text1 | text2 |
|  1 |       | foo   |
|  0 | test  | foo   |

As shown, MySQL has inserted an empty string into column text1 on the first row, and zero into column id on the second row, even though each column is defined as NOT NULL with no DEFAULT clause.

question badge

If we create a table and insert a row as follows:

CREATE TABLE game (team1 VARCHAR(32), team2 VARCHAR(32), start TIMESTAMP, end TIMESTAMP);
INSERT INTO game VALUES ("argentina", "brazil", now(), now());

The resulting table will be as follows:

| team1     | team2  | start               | end                 |
| argentina | brazil | 2014-07-17 20:44:35 | 2014-07-17 20:44:35 |

Given the above table definition, what will be the effect of the following SQL:

UPDATE game SET team1 = "uruguay" WHERE team1 = "argentina";
answer badge

The updated table will then be as follows:

| team1     | team2  | start               | end                 |
| uruguay   | brazil | 2014-07-17 20:50:10 | 2014-07-17 20:44:35 |

Note that, in addition to the team1 column being modified, the start column was automatically updated as well, but the end field remained unchanged. Why?

The reason the start column was automatically updated is that its type is TIMESTAMP. Fields of type TIMESTAMP have the behavior that when a record in the table is updated, the TIMESTAMP field (i.e., the start field in this example) gets updated to reflect the then-current time.

But surprisingly, if we have multiple columns of type TIMESTAMP, only the first TIMESTAMP column has this behavior but the others do not. That is why the end field remains unchanged in this example.

question badge

How are Users and Grants different in MySQL than in other databases?

answer badge

Creating a grant in MySQL can effectively create the user as well. MySQL users are implemented in a very rudimentary fashion. The biggest misunderstanding in this area surrounds the idea of a user. In most databases a username is unique by itself. In MySQL, however, it is the combination of user and hostname that must be unique. So, for example, if I create user [email protected], [email protected] and [email protected], they are actually three distinct users, which can have distinct passwords and privileges. It can be very confusing that “john” logging in from the local command line has different privileges or password than john logging in from server2 and server3.

Find top MySQL developers today. Toptal can match you with the best engineers to finish your project.

* There is more to interviewing than tricky technical questions, so these are intended merely as a guide. Not every “A” candidate worth hiring will be able to answer them all, nor does answering them all guarantee an “A” candidate. At the end of the day, hiring remains an art, a science — and a lot of work.
Submit an interview question
Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of Toptal, LLC.
All fields are required
Thanks for submitting your question.
Our editorial staff will review it shortly. Please note that submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of Toptal, LLC.
Looking for MySQL experts? Check out Toptal’s MySQL developers.
Matthew Alhonte
United States
Matt has officially worked as a Python-based data scientist for the past six years; however, he's spent the last ten at the intersection of stats and programming (before the term data scientist had caught on). He combines strong technical skills with a rigorous background in experiment design and statistical inference. More recently, he's been focusing on machine learning, including some natural language processing and computer vision.
Joseph McNulty
United States
Joseph is a man with strong leadership skills and who is also extremely experienced in software engineering—specializing in mission-critical systems in aerospace, defense, and telecommunications fields. He has over two decades of experience designing and developing sophisticated enterprise web applications using various technologies, most recently with ASP.NET MVC.