SQL : insérer en milieu de table

Version imprimable

Bonjour

J'ai une table ordonnée dans une base de donnée et je dois pouvoir insérer au milieu de la table une nouvelle ligne, connaissant les deux valeurs entre lesquelles la placer :

1=> valeur 1
insérer la nouvelle ligne "valeur 1b"
2=> valeur 2
3=> valeur 3
4=> valeur 4

Le problème est alors qu'il faut décaler les ordre d'un. Or la table contient environ 7000 valeurs, donc il y a un temps de latence de 20s à chaque insertion, ce qui est très pénible. De plus, je n'ai pas trouvé de requête qui permette d'insérer après l'id n comme on a en python : insert(index)
Le but est d'arriver à :

1=> valeur 1
2=> valeur 1b
3=> valeur 2
4=> valeur 3
5=> valeur 4

Connaissez vous des méthodes autres qu'une boucle sur les 7000 valeurs avec une 7000 requêtes ?
Merci beaucoup
Mon code pour l'instant :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 //on récupère toutes les entrées à modifier $req4 = $bdd->prepare('SELECT * FROM decoupage WHERE ordre>=:ordre'); $req4->execute(array('ordre'=>$ordre_debut)); while ($donnees4 = $req4->fetch()) { // on récupère et on indente $i=$donnees4['ordre']; $j=$donnees4['ordre']+1; // on modifie $req2 = $bdd->prepare('UPDATE decoupage SET ordre=:ordre WHERE ordre=:ordre2'); $req2->execute(array('ordre'=>$j, 'ordre2'=>$i)); }

07/12/2018, 22h24
badaze

Comment fais-tu pour déterminer la place du nouvel enregistrement ?
07/12/2018, 22h37
Léo-Nils

C'est une donnée : je connais $ordre juste avant. J'incrémente donc tous ceux au dessus pour faire la place au petit nouveau, puis je l'insère à cet endroit
07/12/2018, 22h42
badaze

Oui mais qu'est-ce qui fait que cet "ordre" a telle ou telle valeur ? Pourquoi insérer en 1B plutôt qu'en 2B ?
07/12/2018, 23h08
badaze

1 pièce(s) jointe(s)
Il faut commencer par modifier l'ordre de la plus grande valeur vers la plus petite afin d'éviter des clés en double si on partait du plus petit vers le plus grand.

Supposons que tu veuilles insérer en 11ème position.

D'abord il faut décaler les enregistrements à partir du 11ème (donc supérieur au 10ème ou supérieur ou égal au 11ème)
Code:

1 2 3 4 update `table` set `nombre`=`nombre`+1 where nombre > 10 order by nombre desc
ou
Code:

1 2 3 4 update `table` set `nombre`=`nombre`+1 where nombre >= 11 order by nombre desc
Pièce jointe 432840

Et une fois la modification faite tu insères en 11ème position.

L'application décrite ici consite en une liste auquel l'utilisateur peut ajouter des données où il le souhaite. Si par exemple la liste est ce poème

Un éclair... puis la nuit ! - Fugitive beauté (ordre = 0, id=0)
Dont le regard m'a fait soudainement renaître, (ordre = 1, id=1)
Ne te verrai-je plus que dans l'éternité ? (ordre = 2, id=2)
Ailleurs, bien loin d'ici ! trop tard ! jamais peut-être ! (ordre = 3, id=3)
Ô toi que j'eusse aimée, ô toi qui le savais ! (ordre = 4, id=4)
Charles Baudelaire (ordre = 5, id=5)

Mais l'utilisateur a oublié ce vers : "Car j'ignore où tu fuis, tu ne sais où je vais," à l'avant dernière position. Il décide alors de le rajouter, en précisant la position, que je récupère dans $ordre (à savoir ici 3 : juste après "ailleurs..." et alors, il faut rajouter dans la liste le vers, et décaler les suivant

Un éclair... puis la nuit ! - Fugitive beauté (ordre = 0, id=0)
Dont le regard m'a fait soudainement renaître, (ordre = 1, id=1)
Ne te verrai-je plus que dans l'éternité ? (ordre = 2, id=2)
Ailleurs, bien loin d'ici ! trop tard ! jamais peut-être ! (ordre = 3, id=3)
Car j'ignore où tu fuis, tu ne sais où je vais, (ordre = 4, id=6)
Ô toi que j'eusse aimée, ô toi qui le savais ! (ordre = 5, id=4)
Charles Baudelaire (ordre = 6, id=5)

Par ailleurs, j'ai fais une erreur en recopiant a fonction, il faut en effet ne pas modifier tout, donc c'est bien :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 //on récupère toutes les entrées à modifier $req4 = $bdd->prepare('SELECT * FROM decoupage WHERE ordre>=:ordre'); $req4->execute(array('ordre'=>$ordre_debut)); while ($donnees4 = $req4->fetch()) { // on récupère et on indente $i=$donnees4['id']; $j=$donnees4['ordre']+1; // on modifie $req2 = $bdd->prepare('UPDATE decoupage SET ordre=:ordre WHERE id=:id'); $req2->execute(array('ordre'=>$j, 'id'=>$i)); }
Ainsi l'ordre n'est pas modifié de proche en proche mais bien une seule fois.

Sinon j'ai aussi un code comme celui
Code:

1 2 3 4 5 6 7 8 //De la dernière ligne à celle juste après, on descend for ($i=$count-1; $i>=$ordremax; $i--) { $j = $i + 1; //on modifie $req = $bdd->prepare('UPDATE decoupage SET ordre=:ordre WHERE ordre=:ordreprecedent'); $req->execute(array('ordre'=>$j, 'ordreprecedent'=>$i)); }
Là, on n'a pas non plus de modification récursive : on utilise i-- décroissant et j=i+1 croissant.

07/12/2018, 23h39
badaze

As tu vu ma réponse ?
07/12/2018, 23h40
Watilin
Pour moi il y a un problème de conception. Théoriquement, un jeu de données dans une table SQL n’est pas ordonné (en pratique il est toujours stocké dans un certain ordre, mais c’est de la mécanique interne et on n’est pas censé le savoir). On ordonne quand on fait un SELECT, et au dernier moment on peut ajouter une variable de « comptage ».
Du coup je ferais comme ça :
1. on garde si on veut la colonne id, mais on ne s’en sert plus
2. on ajoute un index (si ce n’est pas déjà le cas) sur la colonne ordre
3. Code:
  
  1 2 3 4 SET @n := 0; SELECT decoupage.*, @n := @n + 1 AS n FROM decoupage ORDER BY ordre;
Dans la pratique, PDO nous oblige en général à faire les requêtes une seule à la fois, donc on peut remplacer la séquence SET – SELECT par un bête JOIN :
Code:

1 2 3 SELECT decoupage.*, @n := @n + 1 AS n FROM decoupage INNER JOIN (SELECT @n := 0) AS subquery ORDER BY ordre;
Une idée piquée à badaze… Ce n’est pas la première fois :mrgreen:

Edit : mais je crois que la proposition de badaze dans son post #5 sera plus efficace.
07/12/2018, 23h54
Léo-Nils
Citation:

Envoyé par badaze

As tu vu ma réponse ?

Oui, je suis désolé, il y a eut un croisement de posts. Merci beaucoup. C'est vrai que j'ai trop tendance à tout faire en php et pas coder grand chose dans mes requêtes... c'est pas bien... J'ai pris la mauvaise habitude d'utiliser uniquement
Code:

1 2 3 4 $req = $bdd->exec('UPDATE ... WHERE ... '); $req = $bdd->exec('INSERT INTO... VALUES...); $req = $bdd->exec('SELECT ... WHERE ... ORDRE BY ...);
J'ai fais un petit comparatif des 2 solutions que je proposais et de celle de badaze : avec 4000 entrées
Exécution du script : 4.2860720157623 secondes. (avec le while en parcourant la table)
Exécution du script : 22.532021999359 secondes. (avec le for entre la dernière case et la case juste après celle à modifier)
Exécution du script : 0.016216039657593 secondes. (avec la méthode de badaze)
Je crois que j'ai choisi ma solution

Par contre savez vous pourquoi celle avec le for donne des résultats si mauvais par rapport au while?
En tout cas merci beaucoup. Je vais essayer d'étudier sql de plus près. J'aurais peu être d'autres parties de mon code qui vont se simplifier grandement.
Bonne soirée.
08/12/2018, 00h39
badaze

Le tout SQL n’est pas toujours le plus rapide. Tout dépend de la complexité de la requête, des index, du nombre d’enregistrements et du nombre de fois où le code est exécuté.

Par exemple si un traitement est lancé une fois par jour qu’il dure 4 ou 30 secondes cela n’a pas grande importance. S’il est lancé des centaines de fois par jour c’est une autre paire de manches.

6 pièce(s) jointe(s)

Un exemple basique.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?php
//---- DATABASE
define('DB_HOST', '127.0.0.1');
define('DB_NAME', 'tests');
define('DB_USER', 'root');
define('DB_PWD' , '');$pdo    = new PDO('mysql:host='.DB_HOST.'; dbname='.DB_NAME,DB_USER,DB_PWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
if (isset($_POST['submit']))
{
  // decalage 
  $query  = "update `decoupage` set `ordre`=`ordre`+1 where ordre >= :ordre order by ordre desc ";
  $sth    = $pdo->prepare($query);
 
  $sth->bindParam(':ordre', $_POST['ordre'], PDO::PARAM_INT);
  $sth->execute();  
 
  // insertion
  $query  = "insert into `decoupage` (ordre,texte) values(:ordre,:texte)";
  $sth    = $pdo->prepare($query);
 
  $sth->bindParam(':ordre', $_POST['ordre'], PDO::PARAM_INT);
  $sth->bindParam(':texte', $_POST['texte'], PDO::PARAM_STR); 
  $sth->execute();  
}
 
$query  = "SELECT * FROM decoupage order by ordre ";
$sth    = $pdo->prepare($query);
$sth->execute();  
$result = $sth->fetchAll();
foreach ($result as $subArray) {
 print $subArray['ordre']."-".$subArray['texte']."</br>";
} 
?>
<form method="post" />
Texte <input type="text" name="texte" /><br/>
Ordre <input type="text" name="ordre" />
<input type="submit" name="submit" />
</form>

Pièce jointe 432865
Pièce jointe 432868

Pièce jointe 432869

Pièce jointe 432871

Pièce jointe 432872

08/12/2018, 02h18
Watilin

Citation:

Envoyé par Léo-Nils

Par contre savez vous pourquoi celle avec le for donne des résultats si mauvais par rapport au while?

Je crois que l’explication la plus probable est celle-ci : tu appelles prepare à l’intérieur de la boucle, la requête est donc re-préparée à chaque itération. Si tu mets l’appel à prepare en-dehors de la boucle, ça sera déjà moins lent.
08/12/2018, 10h34
Léo-Nils

Bonjour
Très bien
Merci beaucoup pour vos réponses
Bonne journée
08/12/2018, 15h45
CosmoKnacki

Une question me taraude: Est-ce que l'utilisateur pouvant faire une insertion est unique? En d'autres termes, peut-on se trouver dans la situation où lorsqu'un utilisateur A procède à une insertion, un utilisateur B a déjà modifié la table (A ayant toujours l'ancienne version de la table)?

D'ailleurs il faudrait considérer le problème plutôt en terme de session unique que d'utilisateur unique.
08/12/2018, 15h47
badaze

Je me suis aussi posé cette question.