Я использую PHP 5.5.9 и MySQL 5.5.44 с mysqlnd 5.0.11-dev на Ubuntu 14.04 LTS. Следующее утверждение не готовится:

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

И это несмотря на то, что следующее утверждение готовится успешно:

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

Что вызывает эту разницу?Руководство говорит: «Маркеры параметров могут использоваться только там, где должны отображаться значения данных, а не для ключевых слов SQL, идентификаторов и т. д.» Но это для значения данных.

Не вина ПДО

То же самое происходит в автономном клиенте:

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, которыйруководство вызывает "последовательный", InnoDB пропускает значение автоинкремента, когда строка может быть вставлена, но это не так, как в случае сINSERT IGNORE или жеON DUPLICATE KEY UPDATE который сталкивается с существующим рядом которогоUNIQUE значения соответствуют значениям вставляемой строки. (В руководстве они называются «вставками смешанного режима».)

Каждые несколько часов я импортирую канал от своего поставщика. Это имеет около 200 000 строк, и все, кроме в среднем 200 из этих строк имеют уникальные значения, которые соответствуют значениям, уже присутствующим в таблице. Так что, если бы я использовалINSERT IGNORE или жеON DUPLICATE KEY UPDATE все время я записывал 199 800 удостоверений личности каждые несколько часов. Поэтому я не хочу использоватьINSERT IGNORE или жеON DUPLICATE KEY UPDATE из-за страха, что я могу исчерпать предел в 4,2 миллиардаINTEGER UNSIGNED с повторными вставками со временем к столу с тем жеUNIQUE ключ. Я не хочу переключать колонку вBIGINT тип, потому что 32-битный PHP не имеет типа с той же семантикой, что и MySQLBIGINT, Администратор сервера не хочет переходить на 64-битный PHP или менятьinnodb_autoinc_lock_mode для всех пользователей сервера.

Поэтому вместо этого я решил попробоватьINSERT INTO ... SELECTсоздание таблицы из 1 строки с уникальными ключевыми столбцами в подзапросе и оставление соединения с основной таблицей, чтобы отклонить уже существующие уникальные значения ключей. (Руководство говоритINSERT INTO ... SELECT это «массовая вставка», которая не записывает идентификаторы.) Намерение сделать что-то вроде этого:

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,"Unknown column 'settlement_id' in 'field list'"]

<?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";
}

Что вызывает эту ошибку? И есть ли лучший способ вставить строку, только если первичный ключ не существует без исчерпывающих идентификаторов автоинкремента?