php - MySQL 上的 Doctrine findBy() 包含带有特殊字符的列

Question

我有一个 MySQL 表，其中包含以下简短描述。

+-----------------------------------+---------------+------+-----+---------+----------------+
| Field                             | Type          | Null | Key | Default | Extra          |
+-----------------------------------+---------------+------+-----+---------+----------------+
| id                                | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| trailer_number                    | varchar(20)   | YES  | MUL | NULL    |                |
| discount_structure (%)            | decimal(10,2) | NO   |     | NULL    |                |
| net_purchase_price                | decimal(10,2) | NO   |     | NULL    |                |
| capitalised_extras                | decimal(10,2) | NO   |     | NULL    |                |
| maximum_write-off_period (Months) | int(11)       | NO   |     | NULL    |                |
| maximum_write-off_period (KMS)    | int(11)       | NO   |     | NULL    |                |
| residual_value (%)                | decimal(10,2) | NO   |     | NULL    |                |
| interest_rate (%)                 | decimal(10,2) | NO   |     | NULL    |                |
| insurance (%)                     | decimal(10,2) | NO   |     | NULL    |                |
| coastal_wholesale_price_of_fuel   | decimal(10,2) | NO   |     | NULL    |                |
+-----------------------------------+---------------+------+-----+---------+----------------+

为了处理列名中的 (%) 等，我必须在 SQL 调用中使用反引号，例如。

SELECT 
    t0.trailer_number AS trailer_number_1, 
    t0.`discount_structure (%)` AS discount_structure_68, 
    t0.net_purchase_price AS net_purchase_price_69, 
    t0.capitalised_extras AS capitalised_extras_70, 
    t0.`maximum_write-off_period (Months)` AS maximum_writeoff_periodMonths_71, 
    t0.`maximum_write-off_period (KMS)` AS maximum_writeoff_periodKMS_72, 
    t0.`residual_value (%)` AS residual_value_73, 
    t0.`interest_rate (%)` AS interest_rate_74, 
    t0.`insurance (%)` AS insurance_75, 
    t0.coastal_wholesale_price_of_fuel AS coastal_wholesale_price_of_fuel_76, 
    t0.id AS id_77 
FROM fox.trailer t0 WHERE t0.deleted = 0

在我的实体中，我已将反引号添加到我的列名称中，例如

/**
 * @var string
 * @ORM\Column(name="`discount_structure (%)`", type="decimal", precision=10, scale=2, nullable=false)
 */
private $discountStructure;

当我打电话时

$trailers = $this->em->getRepository('FoxBundle:Trailer')->findBydeleted(0);

我收到以下错误

[Doctrine\DBAL\Exception\SyntaxErrorException]                                                                                                                                                                                            
  An exception occurred while executing 'SELECT t0.trailer_number AS trailer_number_1,t0.discount_structure (%) AS discount_structure_68, t0.net_purchase_price AS net_purchase_price_69, t0.capitalised_extras AS capitalised_extras_70, t0.maximum_write-off_period (Months) AS maximum_writeoff_periodMonths_71, t0.maximum_write-off_period (KMS) AS maximum_writeoff_periodKMS_72, t0.residual_value (%) AS residual_value_73, t0.interest_rate (%) AS interest_rate_74, t0.insurance (%) AS insurance_75, t0.coastal_wholesale_price_of_fuel AS coastal_wholesale_price_of_fuel_76, t0.id AS id_77 FROM trailer t0 WHERE t0.deleted = ?' with params [0]:                                                                                                                                                                      

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 '%) AS discount_structure_68, t0.net_purchase_price AS net_purchase_price_69, t0.' at line 1

我不拥有该数据库，因此无法更改列名称。我还能做些什么才能使 findBy() 函数正常工作吗？

Answer 1

向实体列名称添加反引号确实有效。

/**
 * @var string
 * @ORM\Column(name="`discount_structure (%)`", type="decimal", precision=10, scale=2, nullable=false)
 */
private $discountStructure;

您必须记住清除缓存。

php bin/console cache:clear 
php bin/console cache:clear --env=prod 
php bin/console doctrine:cache:clear-metadata 
php bin/console doctrine:cache:clear-query 
php bin/console doctrine:cache:clear-result