3 Essential MySQL Interview Questions *

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. For ENUM，默认为第一个枚举值.

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.

The updated table will then be as follows:

+-----------+--------+---------------------+---------------------+
| 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”具有不同的特权或密码，这可能非常令人困惑.