php - MySQL:在 PHP 中，DELETE 语句会将某些值替换为 NULL，而不是删除

Question

我的数据库中有一个表，我正在尝试从中删除一行。

当我在 PHP 中使用查询 DELETE FROM Examinations WHERE id = 2 时，它最终只是将 exercise_name 变量设置为 NULL，并将 submission_deadline 变量设置为 '0000-00-00 00:00:00'，使所有其他字段保持完整(可能是因为其他字段被明确定义为“NOT空”。

最奇怪的事情是，当我在控制台中使用相同的复制和粘贴查询时，该行被正确删除。在这两种情况下，我都以 root 身份登录 MySQL，因此权限应该不成问题。

这是我的数据库中的表，我试图从中删除一行:

DROP TABLE IF EXISTS `Exercises`;

CREATE TABLE `Exercises` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL COMMENT 'Unique ID of this exercise',
  `exercise_name` MEDIUMTEXT NULL DEFAULT NULL COMMENT 'Descriptive name of this exercise',
  `default_state_data` MEDIUMTEXT NOT NULL COMMENT 'The default state data for this exercise',
  `solution_data` MEDIUMTEXT NOT NULL COMMENT 'Example solution for this exercise.',
  `solution_gate` MEDIUMTEXT NOT NULL COMMENT 'The label of the gate that will be compared',
  `submission_deadline` DATETIME NOT NULL DEFAULT '9999-12-31 23:59:59' COMMENT 'Deadline for the submission of answers to this exercise',

  PRIMARY KEY (`id`)
) COMMENT 'All logic gate exercises';

注意:如果重要的话，上面的“id”在其他两个地方被引用为外键。

出于测试目的，我用两行填充它，如下所示:

INSERT INTO `Exercises` (`exercise_name`,`submission_deadline`, `default_state_data`, `solution_gate`, `solution_data`) VALUES
('Logic Gate Playground','2016-03-30 23:59:59', '<massive json object>', 'S_OR', '<massive json object>');
INSERT INTO `Exercises` (`exercise_name`,`submission_deadline`, `default_state_data`, `solution_gate`, `solution_data`) VALUES
('Building with NAND','2016-04-07 23:59:59', '<massive json object>', 'XNOR', '<massive json object>');

我用来从此表中删除行的 PHP 代码摘录:

$exercise_id = intval($_POST['exercise_id']);
// Let's remove the exercise with a prepared statement
$prepared_statement = $mysql_connection->prepare("DELETE FROM Exercises WHERE id = ?");
$prepared_statement->bind_param("i", $exercise_id);
$result = $prepared_statement->execute();

另外，在失败之后，我尝试了上面的一个更简单的版本来测试它是否有效(但没有):

$exercise_id = intval($_POST['exercise_id']);
// Let's remove the exercise
$result = mysqli_query($mysql_connection, "DELETE FROM Exercises WHERE id = {$exercise_id}");

我还尝试在我输入的变量周围添加单引号。

此时此刻，我完全不知所措。有谁知道为什么会发生这种情况？

编辑:

当我用以下内容替换整个 PHP 文件时，它正常工作并正确删除了整行):

<?php
require_once '../includes/database-connect.php';
mysqli_query($mysql_connection, "DELETE FROM Exercises WHERE id = 2");

这仍然让我想知道为什么我的原始文件不起作用。这是整个文件(不起作用):

<?php
require_once '../includes/database-connect.php';
require_once '../includes/functions.php';
$_SESSION['user_id'] = 'adm12345'; // TODO Remove debug line
// Check if the mysql connection is established
if (!$mysql_connection) {
    echo "<b>Failed to connect to MySQL database; MySQL error below:</b><br><pre><code>".mysqli_error($mysql_connection)."</code></pre>";
}
// Retrieve the user ID from the session
$user_id = check_login($mysql_connection, TRUE);
if ($user_id) {
    // User is logged in as an admin
    $exercise_id = intval($_POST['exercise_id']);
    // Let's remove the exercise with a prepared statement
    $prepared_statement = $mysql_connection->prepare("DELETE FROM Exercises WHERE id = ?");
    $prepared_statement->bind_param("i", $exercise_id);
    $result = $prepared_statement->execute();
    if ($result) {
        echo 1;
    } else {
        $error = mysqli_error($mysql_connection);
        if ($error) {
            echo "<b>Failed to submit to server; MySQL error below:</b><br><pre>" . mysqli_error($mysql_connection) . "</pre>";
        } else {
            echo "<b>Failed to submit to server even though MySQL did not throw an error.</b>";
        }
    }
} else {
    if (isset($_SESSION['user_id'])) {
        echo "<b>You are not an admin, you cannot delete exercises.</b>";
    } else {
        echo "<b>You're not logged in.</b>";
    }
}

Answer 1

DELETE 语句，如果成功则删除数据。如果不成功，则不会更改数据。它不会将值更新为 null。如果您在发出 DELETE 语句后看到空值，则错误出在您读回这些值的代码中。