mysql - 对Mysql中的主键感到困惑？

Question

我正在学习 SQL 并使用 Mysql 来计算查询。关于主键的话题，我读到了

Whichever column is declared as Primary Key, it can not be null.

因此，我创建了一个简单的employee 表，其中包含字段id 和name 以及PRIMARY KEY 作为编号;

并输入以下查询，

INSERT INTO employee (id, name) VALUES (1, 'John');
INSERT INTO employee (id, name) VALUES (2, 'Frank');

现在要测试主键列是否为 Null 属性，我将其放入以下查询中。

INSERT INTO employee(name) VALUES('Joe');

结果没有显示错误，查询运行成功

从整个表中选择记录后，我看到了

id |name
0  | Joe
1  | John
2  | Frank

我期待看到类似这样的错误

Column 'id' cannot be NULL

但如您所见，Joe 的主键自动设置为 0。我尝试在不提供 id 的情况下再次插入它

现在我收到消息了，

Duplicate entry for '0' for key 'PRIMARY'

那么这里发生了什么？主键不是从 1 开始吗？

Answer 1

MySQL(5.7 之前)对主键使用隐式默认值 0，请参阅 documentation :

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. Exception: If the column is defined as part of a PRIMARY KEY but not explicitly as NOT NULL, MySQL creates it as a NOT NULL column (because PRIMARY KEY columns must be NOT NULL). Before MySQL 5.7.3, the column is also assigned a DEFAULT clause using the implicit default value. To prevent this, include an explicit NOT NULL in the definition of any PRIMARY KEY column.

如果您将 null 插入到这样的列中，则采用隐式值，除非您设置严格模式:

For data entry into a NOT NULL column that has no explicit DEFAULT clause [...]

• If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

• If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

数字类型的隐式默认值为 0。因此每次您使用 null(或根本不使用该列)时，MySQL 都会使用 0。这第一次有效，但第二次无效。