php - MySQL 忽略 NOT NULL 约束

Question

我在 MySQL 的某些列上创建了一个带有 NOT NULL 约束的表。然后在 PHP 中我写了一个脚本来插入数据，带有一个插入查询。当我在该插入语句中省略其中一个 NOT NULL 列时，我希望 MySQL 会发出一条错误消息，并且我希望我的脚本失败。相反，MySQL 在 NOT NULL 字段中插入空字符串。在其他省略的字段中，数据为 NULL，这很好。有人可以告诉我我在这里做错了什么吗？

我正在使用这张表:

CREATE TABLE IF NOT EXISTS tblCustomers (
  cust_id int(11) NOT NULL AUTO_INCREMENT,
  custname varchar(50) NOT NULL,
  company varchar(50),
  phone varchar(50),
  email varchar(50) NOT NULL,
  country varchar(50) NOT NULL,
  ...
  date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (cust_id)
) ;

这个插入语句:

$sql = "INSERT INTO tblCustomers (custname,company) 
        VALUES ('".$customerName."','".$_POST["CustomerCompany"]."')";
$res = mysqli_query($mysqli, $sql);

或者使用绑定(bind)变量:

$stmt = mysqli_prepare($mysqli, "INSERT INTO tblCustomers (custname,company, email, country) VALUES (?, ?, ?, ?)");

mysqli_stmt_bind_param($stmt, 'ssss', $customerName, $_POST["CustomerCompany"], $_POST["CustomerEmail"], $_POST["AddressCountry"]);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

Answer 1

如果您确定没有使用显式默认值，请检查您的严格模式:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

MySQL Data Type Default Values

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

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

• If strict 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.

Server SQL Modes