Laravel抱怨名称参数重复的查询 [英] Laravel complains about query with duplicate named parameters

问题描述

当我这样做时(在laravel中):

When I do (in laravel):

<?php
\DB::select('SELECT * FROM my_table WHERE id = :id || id = :id', [
    'id' => 1,
]);

它说:

SQLSTATE [HY093]:无效的参数编号(SQL:SELECT * FROM my_table，其中id =:id || id =:id)

SQLSTATE[HY093]: Invalid parameter number (SQL: SELECT * FROM my_table WHERE id = :id || id = :id)

但是当我这样做时(用纯PHP):

But when I do (in pure php):

<?php
$dbh = new PDO('mysql:dbname=...', '...', '...');
$stmt = $dbh->prepare('SELECT * FROM my_table WHERE id = :id || id = :id');
$r = $stmt->execute([
    'id' => 1,
]);
while ($row = $stmt->fetch()) {
    var_dump($row['id']);
}

成功.我在做什么错了?

It succeeds. What am I doing wrong?

P.S.显然，遇到此问题时我运行的查询更有意义.

P.S. Apparently, the query I ran when I encountered the issue was more meaningful.

UPD :或多或少的真实查询:

UPD More or less real query:

SELECT id
FROM objects
WHERE ACOS(
    SIN(RADIANS(lat)) * SIN(RADIANS(:lat))
    + COS(RADIANS(lat)) * COS(RADIANS(:lat)) * COS(RADIANS(:lng - lng))
) * 6371 < 10

推荐答案

据我所知，这全部归结为mysql无法处理命名参数.

From what I can see it all comes down to mysql being unable to deal with named parameters.

mysqli::prepare :

通过在适当位置嵌入问号(?)字符，此参数可以在SQL语句中包含一个或多个参数标记.

This parameter can include one or more parameter markers in the SQL statement by embedding question mark (?) characters at the appropriate positions.

pdo::prepare :

调用PDOStatement :: execute()时，对于要传递给语句的每个值，必须包含一个唯一的参数标记.除非启用了仿真模式，否则在准备好的语句中不能多次使用相同名称的命名参数标记.

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

Laravel具有仿真模式已禁用 默认情况下 .通过将'options' => [PDO::ATTR_EMULATE_PREPARES => TRUE]添加到连接设置中，可以在config/database.php中启用它.这样，您将获得与纯PHP相同的结果.不过，不确定这是个好主意.

Laravel has emulation mode disabled by default. One can enable it in config/database.php by adding 'options' => [PDO::ATTR_EMULATE_PREPARES => TRUE] to connection settings. That way you will get the same result as in pure php. Not sure that's a good idea, though.

这篇关于Laravel抱怨名称参数重复的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！