3 Essential MySQL Interview Questions *
最好的MySQL开发人员和工程师可以回答的基本问题. 在我们社区的推动下,我们鼓励专家提交问题并提供反馈.
Hire a Top MySQL Developer NowInterview Questions
考虑一下MySQL数据库中表的定义:
CREATE TABLE example (
id INT NOT NULL,
text1 VARCHAR(32) NOT NULL,
text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);
What will be the result of the two INSERT
如果启用了严格的SQL模式,下面的语句? 如果严格的SQL模式是 not enabled? Explain your answer.
INSERT INTO example (id) VALUES(1);
INSERT INTO example (text1) VALUES('test');
Since the text1
and text2
columns are marked as NOT NULL
and have no explicit DEFAULT
clauses, attempts to insert implicit NULL
这些列中的值可能会失败(请注意,您可以使用 SHOW CREATE TABLE
语句查看哪些列具有显式的 DEFAULT
clause). So accordingly, these two INSERT
statements should 失败,因为它们没有为其中的一个或多个列指定值.
If strict SQL mode is enabled, 错误确实会发生,语句将被回滚. (注意:对于非事务性表上的多行语句, an error occurs, 但是如果这发生在语句的第二行或者后面的行, the preceding rows will have been inserted.)
However, if strict SQL mode is *not enabled,* MySQL sets the column to the implicit default value 对于列数据类型而不是失败.
在MySQL中,隐式默认值确定如下:
- 对于数字类型,默认值通常为零.
- 对于日期和时间类型,默认值是该类型的适当“零”值. (对于类型为。的字段,这实际上有点复杂
TIMESTAMP
.) - For string types (other than
ENUM
), the default value is the empty string. ForENUM
,默认为第一个枚举值.
Therefore, after the above two INSERT
语句执行时,表的内容如下:
mysql> SELECT * FROM example;
+----+-------+-------+
| id | text1 | text2 |
+----+-------+-------+
| 1 | | foo |
| 0 | test | foo |
+----+-------+-------+
如图所示,MySQL在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.
如果我们创建一个表并插入一行,如下所示:
创建TABLE game (team1 VARCHAR(32), team2 VARCHAR(32), start TIMESTAMP, end TIMESTAMP);
INSERT INTO game VALUES(“阿根廷”,“巴西”,now(), now());
The resulting table will be as follows:
+-----------+--------+---------------------+---------------------+
| team1 | team2 | start | end |
+-----------+--------+---------------------+---------------------+
|阿根廷|巴西| 2014-07-17 20:44:35 | 2014-07-17 20:44:35 |
+-----------+--------+---------------------+---------------------+
根据上面的表定义,下面的SQL语句会产生什么效果:
更新比赛设置team1 = "乌拉圭",其中team1 = "阿根廷";
更新后的表如下:
+-----------+--------+---------------------+---------------------+
| team1 | team2 | start | end |
+-----------+--------+---------------------+---------------------+
|乌拉圭|巴西| 2014-07-17 20:50:10 | 2014-07-17 20:44:35 |
+-----------+--------+---------------------+---------------------+
Note that, in addition to the team1
column being modified, the start
列也自动更新了,但是 end
field remained unchanged. Why?
The reason the start
列被自动更新是因为它的类型为 TIMESTAMP
. Fields of type TIMESTAMP
有这样的行为:当表中的记录被更新时 TIMESTAMP
field (i.e., the start
字段(在此示例中)被更新以反映当时的当前时间.
But surprisingly, if we have multiple columns of type TIMESTAMP
, only the first TIMESTAMP
列具有此行为,但其他列没有. That is why the end
field remains unchanged in this example.
在MySQL中创建授权也可以有效地创建用户. MySQL用户是以非常初级的方式实现的. 这个领域最大的误解围绕着用户的概念. 在大多数数据库中,用户名本身是唯一的. In MySQL, however, it is the combination of user and hostname that must be unique. So, for example, if I create user john@localhost, john@server2 and john@server3, they are actually three distinct users, 哪些可以有不同的密码和权限. 从本地命令行登录的“john”与从server2和server3登录的“john”具有不同的特权或密码,这可能非常令人困惑.
Apply to Join Toptal's Development Network
and enjoy reliable, steady, remote Freelance MySQL Developer Jobs
面试不仅仅是棘手的技术问题, so these are intended merely as a guide. 并不是每一个值得雇佣的“A”候选人都能回答所有的问题, 回答所有问题也不能保证成为A级考生. At the end of the day, 招聘仍然是一门艺术,一门科学,需要大量的工作.
Why Toptal
Tired of interviewing candidates? 不知道该问什么才能让你得到一份好工作?
Let Toptal find the best people for you.
Hire a Top MySQL Developer NowOur Exclusive Network of MySQL Developers
希望找到一份MySQL开发人员的工作?
Let Toptal find the right job for you.
Apply as a MySQL DeveloperJob Opportunities From Our Network
Submit an interview question
提交的问题和答案将被审查和编辑, 并可能会或可能不会选择张贴, at the sole discretion of Toptal, LLC.
Looking for MySQL Developers?
Looking for MySQL Developers? Check out Toptal’s MySQL developers.
Nicholas Wakeman
Nick从事软件项目的开发、架构和领导工作已有十多年了. 除了丰富的全栈技能集, 他的商业头脑和与客户和利益相关者有效沟通的能力,无论他们的技术知识如何,都使他与众不同. 虽然过去几年一直专注于技术领导, 尼克仍然喜欢开发早期的原型和产品.
Show MoreAbhimanyu Veer Aditya
Abhimanyu是一名机器学习专家,拥有19年为商业和科学应用创建预测解决方案的经验. He’s a cross-functional technology leader, 有组建团队和与c级高管共事的经验. Abhimanyu在计算机科学和软件工程方面有着成熟的技术背景,在高性能计算方面拥有专业知识, big data, algorithms, databases, and distributed systems.
Show MoreDaniel Ramteke
Daniel是一名拥有15年以上iOS专业经验的全栈开发人员, Android, and server-side development. 他开发了几个应用程序,并在App Store上发布. Daniel是一位经验丰富的导师,他可以解释技术概念,并支持非技术人员做出明智的决策. 他喜欢帮助他的客户从头开始构建应用程序,并重新启动旧项目, as he excels at organizing projects, hiring new roles, and managing the development process.
Show MoreToptal Connects the Top 3% of Freelance Talent All Over The World.
Join the Toptal community.