MySQL/MariaDB事务访问冲突1064 - MySQL/MariaDB transaction access violation 1064

MySQL/MariaDB transaction access violation 1064

我在事务查询中遇到了一点麻烦。我有两张桌子，"subjects"和链接表称为"tutorsubjects"。我使用的是MariaDB版本10.0.21。我已经创建了以下查询，但我一直得到"语法错误或访问冲突:1064"错误。

public function addSubject($values){        
    try {
        $temp = $this->db->query("
        BEGIN;
        INSERT INTO subjects
        (subject_code, subject_name, subject_grade, subject_description, subject_category)
        VALUES (:subject_code, :subject_name, :subject_grade, :subject_description, :subject_category);
        SET @last_id = LAST_INSERT_ID();
        INSERT INTO tutorsubject
        (tutor_id , subject_id)
        VALUES (:tutor_id, @last_id);
        COMMIT;",$values);
        return $temp;
    } catch (DBException $e) {
        echo "Error:<br/>" . $e->getMessage();
        return null;
    } catch (Exception $e) {
        echo "Error:<br/>" . $e->getMessage();
        return null;
    }
}

以下是解析到查询

的值

$array = array("subject_code" => $code,
    "subject_name" => $subject_name,
    "subject_grade" => $grade,
    "subject_description" => $subject_description,
    "subject_category" => $subject_category, 
    "tutor_id"=>$selecttutor);

我得到以下错误:

SQLSTATE[42000]: Syntax error or access violation: 1064 SQL语法错误;检查与您的MariaDB服务器版本对应的手册，以便在"reach"附近使用正确的语法。'subjects' ('subject_code'， 'subject_name'， 'subject_grade'， 'subject_de')在第一行原始SQL: INSERT INTO 'reach'。'subjects' ('subject_code'， 'subject_name'， 'subject_grade'， 'subject_description'， 'subject_category') VALUES (:subject_code，:subject_name，:subject_grade，:subject_description，:subject_category);

我的问题是，当我在phpMyAdmin中运行此查询时，它完成了没有任何问题。我使用PDO MySQL类在这里发现作为我的数据库交互的基础。我开始认为，也许类不直接支持事务?

尝试让PHP完成工作+使用PDO事务方法，值部分未测试，因此您需要确保其正确:

$this->db->beginTransaction();
$this->db->query("INSERT INTO subjects
(subject_code, subject_name, subject_grade, subject_description, subject_category)
VALUES (:subject_code, :subject_name, :subject_grade, :subject_description, :subject_category)", $values);
$values['last_id'] = $this->db->lastInsertId();
if (empty($values['last_id'])) {
    $this->db->rollBack();
} else {
    $this->db->query("INSERT INTO tutorsubject
    (tutor_id , subject_id)
    VALUES (:tutor_id, :last_id)", $values);
    $this->db->commit();
}

您应该尝试单独留下COMMIT和BEGIN查询。

试题:

 // start the transaction
 $this->db->query("BEGIN;");
 //rest of your queries with db->query() go here
 $this->db->query("INSERT INTO subjects
    (subject_code, subject_name, subject_grade, subject_description, subject_category)
    VALUES (:subject_code, :subject_name, :subject_grade, :subject_description, :subject_category);
    SET @last_id = LAST_INSERT_ID();
    INSERT INTO tutorsubject
    (tutor_id , subject_id)
    VALUES (:tutor_id, @last_id);");
 //commit
 $this->db->query("COMMIT;");

MySQL试图执行所有这些作为一个查询，它不识别整个命令作为单独的查询。PhpMyadmin将它们单独分开，这就是为什么它们在那里正确运行。

有人在数据库和表名周围添加了撇号。这在语法上是错误的(除非您打开了某个ansi模式)。