Ich verwende PHP 5.5.9 und MySQL 5.5.44 mit mysqlnd 5.0.11-dev unter Ubuntu 14.04 LTS. Die folgende Anweisung kann nicht vorbereitet werden:

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

Das ist trotz der Tatsache, dass die folgende Anweisung erfolgreich vorbereitet:

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

Was verursacht diesen Unterschied?Das Handbuc sagt: "Parametermarken können nur dort verwendet werden, wo Datenwerte angezeigt werden sollen, nicht für SQL-Schlüsselwörter, Bezeichner usw." Aber das ist für einen Datenwert.

Not PDO ist schuld

Das gleiche passiert im Standalone-Client:

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

Meine Motivatio

Ich versuche, einer Tabelle eine Zeile mit einem automatisch inkrementierenden Primärschlüssel hinzuzufügen. In InnoDBs Standard-Auto-Increment-Sperrmodus, derdas Handbuc ruft "konsekutiv" auf, InnoDB überspringt einen Auto-Inkrement-Wert, wenn eine Zeile eingefügt wird, aber nicht, wie dies bei @ der Fall isINSERT IGNORE oderON DUPLICATE KEY UPDATE das läuft in eine bestehende Zeile, derenUNIQUE -Werte stimmen mit denen der einzufügenden Zeile überein. (Diese werden im Handbuch als "Mixed-Mode-Inserts" bezeichnet.)

Alle paar Stunden importiere ich einen Feed von meinem Lieferanten. Dies hat ungefähr 200.000 Zeilen, und alle bis auf durchschnittlich 200 dieser Zeilen haben eindeutige Werte, die den Werten entsprechen, die bereits in der Tabelle vorhanden sind. Also, wenn ich @ verwenden würINSERT IGNORE oderON DUPLICATE KEY UPDATE Die ganze Zeit habe ich alle paar Stunden 199.800 IDs durchgebrannt. Also möchte ich nicht @ verwendINSERT IGNORE oderON DUPLICATE KEY UPDATE aus Angst, dass ich die 4,2 Milliarden Grenze von @ erschöpfen könnINTEGER UNSIGNED mit wiederholten Einfügungen im Laufe der Zeit zu einer Tabelle mit dem gleichenUNIQUE Schlüssel. Ich möchte die Spalte nicht auf @ umstellBIGINT type, da 32-Bit-PHP keinen Typ mit der gleichen Semantik wie MySQL hatBIGINT. Der Serveradministrator ist nicht bereit, auf 64-Bit-PHP zu wechseln oder @ zu änderinnodb_autoinc_lock_mode für alle Benutzer des Servers.

So stattdessen habe ich beschlossen, @ zu versuchINSERT INTO ... SELECT, Erstellen einer einzeiligen Tabelle mit den eindeutigen Schlüsselspalten in einer Unterabfrage und Verbinden mit der Haupttabelle, um bereits vorhandene eindeutige Schlüsselwerte abzulehnen. (Das Handbuch sagtINSERT INTO ... SELECT ist eine "Masseneinfügung", die keine IDs brennt.) Die Absicht ist, so etwas zu tun:

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

Dies ist fehlgeschlagen und hat den PDO-Fehler verursacht:["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";
}

Was verursacht diesen Fehler? Und gibt es eine bessere Möglichkeit, eine Zeile nur einzufügen, wenn der Primärschlüssel nicht vorhanden ist, ohne dass die IDs für die automatische Inkrementierung erschöpft sind?