"Coluna desconhecida na 'lista de campos'" quando o espaço reservado da instrução preparada está na subconsulta
Estou usando o PHP 5.5.9 e MySQL 5.5.44 com o mysqlnd 5.0.11-dev no Ubuntu 14.04 LTS. A seguinte declaração falha ao preparar:
$db->prepare("SELECT nr.x FROM (SELECT ? AS x) AS nr")
Isso apesar do fato de a seguinte declaração ser preparada com êxito:
$db->prepare("SELECT nr.x FROM (SELECT '1337' AS x) AS nr")
O que causa essa diferença?O manual diz "Os marcadores de parâmetro podem ser usados apenas onde os valores dos dados devem aparecer, não para palavras-chave, identificadores e assim por diante". Mas isso é para um valor de dados.
Não é culpa do PDOO mesmo acontece no cliente independente:
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
Minha motivaçãoEstou tentando adicionar uma linha a uma tabela que possui uma chave primária de incremento automático. No modo de bloqueio de incremento automático padrão do InnoDB, queo manual chama "consecutivo", o InnoDB ignora um valor de incremento automático quando uma linha pode ser inserida, mas não é, como é o caso deINSERT IGNORE
ouON DUPLICATE KEY UPDATE
que é executado em uma linha existente cujaUNIQUE
Os valores correspondem aos da linha que está sendo inserida. (Estes são chamados de "inserções de modo misto" no manual.)
A cada poucas horas, importo um feed do meu fornecedor. Isso tem cerca de 200.000 linhas, e todas, exceto em média, 200 dessas linhas têm valores exclusivos que correspondem aos valores já presentes na tabela. Então, se eu fosse usarINSERT IGNORE
ouON DUPLICATE KEY UPDATE
o tempo todo, eu gravava 199.800 IDs a cada poucas horas. Então eu não quero usarINSERT IGNORE
ouON DUPLICATE KEY UPDATE
por medo de esgotar o limite de 4,2 bilhões deINTEGER UNSIGNED
com inserções repetidas ao longo do tempo em uma tabela com o mesmoUNIQUE
chave. Não quero mudar a coluna paraBIGINT
digite porque o PHP de 32 bits não tem tipo com a mesma semântica que o MySQLBIGINT
. O administrador do servidor não está disposto a mudar para o PHP de 64 bits ou alterarinnodb_autoinc_lock_mode
para todos os usuários do servidor.
Então, em vez disso, decidi tentarINSERT INTO ... SELECT
, criando uma tabela de 1 linha com as colunas de chave exclusivas em uma subconsulta e deixando-a unida à tabela principal para rejeitar valores de chave exclusivos que já existem. (O manual dizINSERT INTO ... SELECT
é uma "inserção em massa", que não queima IDs.) A intenção é fazer algo assim:
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
Isso falhou, dando o erro do 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";
}
O que está causando esse erro? E existe uma maneira melhor de inserir uma linha apenas se a chave primária não existir sem os IDs de incremento automático exaustivos?