Le Hollandais Volant

Je reviens sur (et corrige) mes deux notes ici et là.

Dans PHP, avec PDO pour les bases de données SQL avec les requêtes préparées, on utilise par exemple cette syntaxe :

$req = $dbh->prepare("SELECT * FROM table WHERE col=? AND col2=? AND col3=?");
$req-> execute(array($col1, $col2, $col3));

Sachez que la taille du tableau array() que l’on place dans le execute() possède une limite sur la taille et la dépasser renvoi une erreur :

Erreur : SQLSTATE[HY000]: General error: 1 too many SQL variables

Chez moi, la limite semble être de 1000 variables (au dessus ça foire, en dessous ça marche, et je n’ai pas trouvé comment modifier ça : elle doit être interne aux modules PDO de PHP).

On me dira que c’est énorme et que personne ne les utilise. Bah si, moi \o/.

Je les utilise dans le cas où je veux insérer de multiples lignes simultanément :

// en SQLite
$query = "INSERT INTO tbl ( one, two) SELECT ?, ? UNION ALL SELECT ?, ? UNION ALL SELECT ?, ? ";

// en MySQL
$query = "INSERT INTO tbl ( one, two) VALUES ( (?,?), (?,?), (?,?) ) ";

Puis le tableau :

$array = array($ar[0][0], $ar[0][1], $ar[1][0], $ar[1][1], $ar[2][0], $ar[2][1]);

Ici j’ajoute 3 ligne d’un seul coup, où chaque ligne contient deux variables (deux « ? » dans la requête), au total j’ai donc six variables.


Chaque insertion insère 2 nouvelles variables (deux « ? ») dans la requête.
On peut donc au maximum insérer 500 lignes d’un coup (500×2 = 1000). Dans un de mes script, chaque ligne ajoute 14 nouveaux « ? ». Il faut en tenir compte, et on peut dont entrer au maximum floor(1000/14) lignes à chaque requête.

Une solution, si vous avez un énorme tableau qui contient toute les variables à insérer, c’est de le couper en de multiples petits tableaux avec array_chunk(), et de reboucler ensuite là dessus, de façon à ce que le nombre de variables maximal par requête ne dépasse pas les 1000.


J’ajoute quand même un mot sur l’intérêt d’utiliser les insertions multiples : si en MySQL on peut facilement faire 20000 requêtes séparées en une secondes, faire la même chose en SQLite vous prend une demi-heure (littéralement) : chaque requête génère un accès disque vers le fichier .sqlite.
C’est pour ça qu’en SQLite, il faut mieux privilégier la minimisation du nombre de requêtes, quitte à faire d’énormes requêtes.

image de n3wjack