php中sql语句中占位符,php – 当“预处理语句”的占位符位于子查询中时,“字段列表中的未知列”...

我在Ubuntu 14.04 LTS上使用PHP 5.5.9和MySQL 5.5.44以及mysqlnd 5.0.11-dev.以下声明未能准备：

$db->prepare("SELECT nr.x FROM (SELECT ? AS x) AS nr")

尽管以下声明成功准备,但这是事实：

$db->prepare("SELECT nr.x FROM (SELECT '1337' AS x) AS nr")

是什么导致这种差异？ The manual说“参数标记只能用于应该出现数据值的地方,而不能用于SQL关键字,标识符等等.”但这是一个数据值.

不是PDO的错

在独立客户端中也会发生同样的事情：

mysql -uredacted -predacted redacted

-- Type 'help;' or '\h' for help.

SELECT nr.x FROM (SELECT '1337' AS x) AS nr;

-- x

-- 1337

-- 1 row in set (0.00 sec)

PREPARE workingstmt FROM 'SELECT nr.x FROM (SELECT ''1337'' AS x) AS nr';

-- Query OK, 0 rows affected (0.00 sec)

-- Statement prepared

DEALLOCATE PREPARE workingstmt;

-- Query OK, 0 rows affected (0.00 sec)

PREPARE brokenstmt FROM 'SELECT nr.x FROM (SELECT ? AS x) AS nr';

-- ERROR 1054 (42S22): Unknown column 'nr.x' in 'field list'

^D

-- Bye

我的动机

我正在尝试向具有自动递增主键的表添加行.在InnoDB的默认自动增量锁定模式(the manual称为“连续”)中,InnoDB在插入行时跳过自动增量值但不是,如INSERT IGNORE或ON DUPLICATE KEY UPDATE遇到的那样UNIQUE值与要插入的行匹配的行. (这些在手册中称为“混合模式插入”.)

每隔几个小时,我就会从供应商处导入一个Feed.这有大约200,000行,并且这些行中平均有200行具有与表中已存在的值对应的唯一值.因此,如果我一直使用INSERT IGNORE或ON DUPLICATE KEY UPDATE,我每隔几个小时就会烧掉199,800个ID.所以我不想使用INSERT IGNORE或ON DUPLICATE KEY UPDATE,因为我担心随着时间的推移,我可能会耗尽重复插入的INTEGER UNSIGNED的42亿个限制到具有相同UNIQUE键的表.我不想将列切换为BIGINT类型,因为32位PHP没有与MySQL BIGINT具有相同语义的类型.服务器管理员不愿意切换到64位PHP或为服务器的所有用户更改innodb_autoinc_lock_mode.

因此,我决定尝试INSERT INTO … SELECT,创建一个包含子查询中唯一键列的1行表,并将其连接到主表以拒绝已存在的唯一键值. (手册说INSERT INTO … SELECT是一个“批量插入”,它不会刻录ID.)目的是做这样的事情：

INSERT INTO the_table

(uniquecol, othercol1, othercol2)

SELECT nr.uniquecol, :o1 AS othercol1, :o2 AS othercol2

FROM (

SELECT ? AS uniquecol

) AS nr

LEFT JOIN the_table ON nr.settlement_id = the_table.settlement_id

WHERE the_table.row_id IS NULL

这失败了,给出了PDO错误：

[“42S22”,1054,“字段列表’中的未知列’settlement_id’”]

/* Connect to database */

$pdo_dsn = 'mysql:host=127.0.0.1;dbname=redacted';

$pdo_username = 'redacted';

$pdo_password = 'redacted';

$pdo_options = [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',];

$db = new PDO($pdo_dsn, $pdo_username, $pdo_password, $pdo_options);

$pdo_dsn = $pdo_username = $pdo_password = 'try harder';

// ensure that PDO doesn't convert everything to strings

// per https://stackoverflow.com/a/15592818/2738262

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

/* Create mock data with which to test the statements */

$prep_stmts = ["

CREATE TEMPORARY TABLE sotemp (

file_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,

settlement_id VARCHAR(30) NOT NULL,

num_lines INTEGER UNSIGNED NOT NULL DEFAULT 0,

UNIQUE (settlement_id)

)

","

INSERT INTO sotemp (settlement_id, num_lines) VALUES

('15A1', 150),

('15A2', 273),

('15A3', 201)

"];

foreach ($prep_stmts as $stmt) $db->exec($stmt);

/* Now the tests */

$working_stmt = $db->prepare("

SELECT nr.settlement_id

FROM (

-- change this to either a value in sotemp or one not in sotemp

-- and re-run the test program

SELECT '15A3' AS settlement_id

) AS nr

LEFT JOIN sotemp ON nr.settlement_id = sotemp.settlement_id

WHERE sotemp.file_id IS NULL

");

if ($working_stmt) {

$working_stmt->execute();

$data = $working_stmt->fetchAll(PDO::FETCH_ASSOC);

echo "Working: ".json_encode($data)."\n";

} else {

echo "Working statement failed: ".json_encode($db->errorInfo())."\n";

}

$broken_stmt = $db->prepare("

SELECT nr.settlement_id

FROM (

SELECT ? AS settlement_id

) AS nr

LEFT JOIN sotemp ON nr.settlement_id = sotemp.settlement_id

WHERE sotemp.file_id IS NULL

");

if ($broken_stmt) {

$broken_stmt->execute(['15A4']);

$data = $broken_stmt->fetchAll(PDO::FETCH_ASSOC);

echo "Broken: ".json_encode($data)."\n";

} else {

echo "Broken statement failed: ".json_encode($db->errorInfo())."\n";

}

导致此错误的原因是什么？并且只有在没有耗尽自动增量ID的情况下主键不存在时才有更好的方法来插入行吗？

解决方法:

你的最新编辑使问题非常清楚,所以我会尝试一个答案：

造成这种差异的原因是占位符.

如here所述,占位符只能在查询中的某些位置使用.尤其是：

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

现在你可能已经注意到了SELECT？因为x准备好了,但不是SELECT nr.x FROM(SELECT？AS x)AS nr.这是为什么？这是最好的解释an anonymous author on PHP’s doc,所以让我复制/粘贴：

There is a common misconception about how the placeholders in prepared statements work: they are not simply substituted in as (escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to “prepare” a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

所以简单地说：因为你在FROM子句中的子查询中使用占位符,所以MySQL无法计算查询的执行计划.

换句话说,由于您的查询将始终更改,因此没有可以为其准备的“模板”.

因此,如果您确实想要使用此查询,则需要使用正常(未准备好的)查询,或者返回PDO的模拟预准备语句.

话虽如此,请考虑评论部分提供的各种备选方案.对于您要实现的目标,有更好的解决方案.

标签：php,mysql,pdo,prepared-statement

来源： https://codeday.me/bug/20191002/1843584.html