php - "Unknown column in ' 字段列表 '"当准备语句的占位符在子查询中

Question

我在 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 值与要插入的行的值匹配的现有行中。 (这些在手册中称为“混合模式插入”。)

每隔几个小时，我就会从我的供应商那里导入一个提要。这有大约 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'"]

<?php // MCVE follows

/* 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 http://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？

Answer 1

您最近的编辑使问题非常清楚，所以我会尝试回答:造成这种差异的原因是占位符。

记录在案here , 占位符只能在查询中的某些地方使用。特别是:

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

现在您可能已经注意到 SELECT ? as 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无法计算查询的执行计划。

换句话说，由于您的查询总是会发生变化，因此没有可以为其准备的"template"。

因此如果你真的想使用这个查询，你需要使用一个普通的(非准备好的)查询，或者重新打开 PDO 的模拟准备语句。

话虽如此，请务必考虑评论部分提供的各种替代方案。对于您要实现的目标，有更好的解决方案。