为什么DBAL返回SQL语法错误?

I'm using Doctrine 2 along with CodeIgniter.

我和CodeIgniter一起使用了Doctrine 2。

I'm having a query in some of my functions that trigger a SQL Error. And I can't find the reason why

在我的一些函数中有一个查询会触发一个SQL错误。我找不到原因。

The function is the following:

其功能如下:

private function _key_exists($key)
    {
        $this->CI->load->library('doctrine');
        $em = $this->CI->doctrine->em;
        echo "<br />Key : ".$key;

        $key_object = $em->getRepository( 'Entity\Key' )->findOneBy( array( 'key' => $key ) );

        if( $key_object )
        {
            return TRUE;
        }
        else
        {
            return FALSE;
        }
        // return $this->db->where(config_item('rest_key_column'), $key)->count_all_results(config_item('rest_keys_table')) > 0;
    }

When Key ENtity is :

关键实体为:

<?php

namespace Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Keys
 *
 * @ORM\Table(name="keys")
 * @ORM\Entity
 */
class Key
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", precision=0, scale=0, nullable=false, unique=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="key", type="string", length=40, precision=0, scale=0, nullable=false, unique=false)
     */
    private $key;

    /**
     * @var integer
     *
     * @ORM\Column(name="level", type="integer", precision=0, scale=0, nullable=false, unique=false)
     */
    private $level;

    /**
     * @var boolean
     *
     * @ORM\Column(name="ignore_limits", type="boolean", precision=0, scale=0, nullable=false, unique=false)
     */
    private $ignoreLimits;

    /**
     * @var boolean
     *
     * @ORM\Column(name="is_private_key", type="boolean", precision=0, scale=0, nullable=false, unique=false)
     */
    private $isPrivateKey;

    /**
     * @var string
     *
     * @ORM\Column(name="ip_addresses", type="text", precision=0, scale=0, nullable=true, unique=false)
     */
    private $ipAddresses;

    /**
     * @var integer
     *
     * @ORM\Column(name="date_created", type="integer", precision=0, scale=0, nullable=false, unique=false)
     */
    private $dateCreated;

/* Getters and Setters ... */

}

And the error message is :

错误信息是:

Doctrine\DBAL\DBALException: An exception occurred while executing 'SELECT t0.id AS id1, t0.key AS key2, t0.level AS level3, t0.ignore_limits AS ignore_limits4, t0.is_private_key AS is_private_key5, t0.ip_addresses AS ip_addresses6, t0.date_created AS date_created7 FROM keys t0 WHERE t0.key = ? LIMIT 1' with params {"1":"0fedfa4d50653317df76a4dba79f9f07cd7a8273"}: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keys t0 WHERE t0.key = '0fedfa4d50653317df76a4dba79f9f07cd7a8273' LIMIT 1' at line 1 in E:\Programmes\wamp\www\myapp\application\libraries\Doctrine\DBAL\DBALException.php on line 47

Doctrine\ DBALException:执行“SELECT t0”时发生异常。id作为id1、t0。关键是key2 t0。水平level3 t0。ignore_limits ignore_limits4、t0。is_private_key is_private_key5、t0。ip_addresses ip_addresses6、t0。date_create是date_created7，从t0到t0。关键= ?[42000]:语法错误或访问违反:1064在您的SQL语法中有错误;检查与MySQL服务器版本对应的手册，以便正确使用接近“t0”的键t0。在E的第1行中，第1行的限值为1。php在47行

I do see : Key : c242c67787bd0b9a9b11a54fc942fde50f099235 in the output though

我确实看到了:在输出中，Key: c242c67787bd0b9a9b11a54fc942f099235。

Many thanks for your help

非常感谢您的帮助。

2 个解决方案

#1

The reason why this particular query is failing is because 'key' is a reserved word in mysql.

这个查询失败的原因是“key”是mysql中保留的单词。

FROM keys t0 WHERE t0.key = ? LIMIT 1'

从t0到t0。关键= ?限制1 '

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

So I'm guessing you didn't have this working before you did your orm change?

所以我猜你在做orm改变之前没有做这项工作?

Rename the property to something like value. $key->value reads better than $key->key anyways.

将属性重命名为值。$key->值比$key->键值好。

#2

Quoting tables and column names needs to be done explicitly using ticks in the definition, e.g:

引用表和列名需要在定义中明确地使用刻度，例如:

/**
 * @ORM\Column(name="`key`", type="string", length=255, nullable=false)
 */
protected $key;

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#quoting-reserved-words

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html quoting-reserved-words

#1