«Неизвестный столбец в« списке полей »», когда заполнитель подготовленного оператора находится в подзапросе
Я использую 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";
}
Что вызывает эту ошибку? И есть ли лучший способ вставить строку, только если первичный ключ не существует без исчерпывающих идентификаторов автоинкремента?