binary.jpg Je reviens sur (et corrige) mes deux notes ici et .

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

12 commentaires

gravatar
qwerty a dit :

Je pense pas que je fasse autant de requêtes d'un coup. Quand en a tu besoin o.0 ?

gravatar
GoustiFruit a dit :

Oulàlà, qu'est-ce que c'est que cette façon de faire ???
Il suffit de boucler sur tes variables, et avec SQLite d'utiliser les *transactions* (beginTransaction/commit) !

gravatar
Le Hollandais Volant a dit :

@GoustiFruit : ouais mais je sais pas si les transactions marchent dans MySql. Alors que ça, ça passe partout.

Je boucle bien sur des variables, ainsi que sur la requête, vu que celle ci est construite au cours de la boucle.

@qwerty : dans l'import export d'une sauvegarde : quand il faut enregistrer quelques milliers de commentaires et articles...

gravatar
Sined a dit :

Salut Timo,

Pour ma part dans ce genre de cas, je pense que je générerais à la volée directement la requêtes SQL avec les valeurs dedans (sans passer de paramètres). Ok c'est bourrin mais ainsi exit le système de paramètres et ses limitations.

Si ton système est seulement pour l'import/export d'une sauvegarde, il va etre assez peu utiliser aussi peut etre peux-tu te permettre de modifier un peu le systeme. Le temps de reponse de la bdd n'est peut etre pas si primordiale que cela pour une telle action.

gravatar
Exagone313 a dit :

Bonjour,

Et en mettant les variables directement dans prepare() ça fonctionne ?
Bizarrement je met les variables directement dans prepare(), j'avais oublié qu'on pouvait faire comme ça, ton article m'a rappelé une syntaxe :)

gravatar
baba a dit :

Et en codant l'ordre SQL directement, sans fioriture ? Ca irait plus vite et ça marcherait (c'est ce que je fais pour gérer de grosses insertions en une seule fois).

gravatar
Le Hollandais Volant a dit :

@Exagone313 : directement dans prépare ? Fais gaffe aux injections SQL avec ça !
prepare("SELECT * FROM table WHERE champ=$recherche");
Imagines que $recherche vaut « valeur; DROP TABLE table; »

Je ne sais pas si je me trompe, mais les requêtes préparées avec d’un côté les « ?, ?, ? » et de l’autre les variables permet justement d’éviter ça : même si $recherche contient du code SQL, en séparant ainsi, l’injection échouera.

@baba : comment ça ? Tu veux dire, utiliser un « $bdd->query() »

gravatar
Peck a dit :

Euh ca apporte quoi de mettre 1000 lignes dans un prepare ?

En plus je parie que c'est plus lent, le prepare ne sert pas seulement à la sécurité, il sert aussi aux perfs. Le but est justement de ne faire qu'une fois le parsing sql de la requête. Et autant de fois que besoin l'exécution. Donc en faisant le parsing d'un insert monstrueux en y préparant la place pour les pointeurs sur le données tu dois ralentir le code par rapport au parsing d'un insert simple. De toute façon la boucle que tu dois ajouter autour mysql la fait aussi en interne.

Et si tu veux des transactions, utilise innodb.

gravatar
Amnesy a dit :

Si c'est pour du backup il vaut mieux passer par la ligne de commande :

sqlite3 data.db .dump > export01012012.sql
sqlite3 data.db < export01012012.sql


Concernant les requêtes préparées, oui elles permettent en soi d'éviter les injections en échappant les métacaractères, mais il est tout à fait possible d'échapper soi-même ces caractères, grâce à SQLite3::escapeString().

gravatar
Le Hollandais Volant a dit :

@Amnesy : ah, je connaissais pas cette forme de backup. Mais j’ai surtout un format XML, qui permet plus facilement de passer d’un CMS à l’autre. Blogotext peut par exemple importer une base Wordpress.

Oui, on peut les échaper avec des fonctions spécifiques à SQLite. Mais blogotext doit pouvoir traiter du SQLite et du MySQL avec le même code, c’est pour ça que j’utilise le système d’échappement de PDO, qui est de toute façon là : je me prend plus la tête avec l’échappement ni rien là.

@Peck : oui, ok c’est normal que prepare() soit ralenti avec plus de données. Toutes les fonctions le sont. Mais SQLite est extrêmement lent quand on utilise plein de requêtes séparées.
Pour importer 5000 commentaires et 500 articles, je mettais 20 minutes. En faisant de grosses requêtes, mais moins de requêtes, ça se fait en 6~7 secondes. Le choix est vite fait.

gravatar
Nigel Sheldon a dit :

On as eu ce problème au boulot la semaine dernière mais avec un système de newsletter : en fait depuis PHP 5.4 (ou .3 je sais plus), PHP limite a 1000 variables par page. C'est modifiable dans php.ini si mes souvenirs sont bon.

gravatar
doc a dit :

On devrait interdire a certain de coder...

Les commentaires sont fermés pour cet article