Ordre interne d'execution SQL

Bonjour,
dans le cadre d'un script permettant de décaler les id (PRIMARY KEY) d'une table dans un sens ou dans l'autre, je rencontre un problème qui pourrait peut-être se résoudre en changeant la façon dont la requête est interprétée par le moteur MySQL.

Si je part d'une simple table dont [id] est une clé primaire comme ceci :

Code:

---

1 2 3 4 5

id valeur 5 Valeur#1 6 Valeur#2 7 Valeur#3

---

Je peux sans problème la décaler vers une valeur inférieure en une ligne :

Code:

---

UPDATE maTable SET id = id - 1

---

Cela me donne la table suivante :

Code:

---

1 2 3 4 5

id valeur 4 Valeur#1 5 Valeur#2 6 Valeur#3

---

Cela fonctionne sans problème car il n'existe pas de collision interne entre les id lors de l’exécution (5 passe à 4, puis 6 passe à 5...)

Par contre, si je souhaite incrémenter l'id de 1 pour obtenir ce résultat :

Code:

---

1 2 3 4 5

id valeur 6 Valeur#1 7 Valeur#2 8 Valeur#3

---

Cela ne fonctionnera pas car en commençant par la première valeur (6), il ne pourra pas lui donner une valeur incrémentée (7) car celle-ci est déjà occupée par la ligne suivante. La requête est interrompue.

L'idée serait de faire travailler cette fonction en partant de la fin vers le début.
J'ai essayé d'utiliser le mot clé DESC en espérant que cela fonctionne sans succès :

Code:

---

UPDATE maTable SET id = id + 1 DESC

---

J'ai également tenter de trouver des solutions sur le web sans succès également.

Actuellement, je passe par une boucle en php qui modifie ligne par ligne chacune des valeurs en partant de la fin.
Ça fonctionne, mais ce n'est ni élaguant ni optimisé, donc, si par hasard, vous avez une idée permettant de modifier l'ordre interne d’exécution de MySQL ou même une méthode alternative permettant d’exécuter le tout en une seule ligne, je suis preneur.

Merci d'avoir pris le temps de me lire !

Citation:

---

Envoyé par Pascal P.

Bonjour,
dans le cadre d'un script permettant de décaler les id (PRIMARY KEY) d'une table dans un sens ou dans l'autre, je rencontre un problème qui pourrait peut-être se résoudre en changeant la façon dont la requête est interprétée par le moteur MySQL.

---

Quel est le besoin fonctionnel qui vous a conduit à une idée aussi saugrenue et aussi dangereuse à tout point de vue :aie::weird:
Ceci va prendre un temps considérable si votre base contient un tant soit peu de volume : mise à jour de toutes les FK en cascade, journalisation, tables et index... la cata ! :aie:

Citation:

---

Envoyé par Pascal P.

Cela fonctionne sans problème car il n'existe pas de collision interne entre les id lors de l’exécution (5 passe à 4, puis 6 passe à 5...)

---

En principe non : un SGBD fonctionne de façon ensembliste, ce n'est donc pas une première mise à jour puis une suivante, mais une mise à jour globale. Celà étant, MySQL est souvent un cas :weird: particulier :roll:

Citation:

---

Envoyé par Pascal P.

Actuellement, je passe par une boucle en php qui modifie ligne par ligne chacune des valeurs en partant de la fin.
Ça fonctionne, mais ce n'est ni élaguant ni optimisé, donc, si par hasard, vous avez une idée permettant de modifier l'ordre interne d’exécution de MySQL ou même une méthode alternative permettant d’exécuter le tout en une seule ligne, je suis preneur.

---

C'est ce qu'on peut faire de pire avec un SGBD relationnel : traiter ligne à ligne alors que le moteur de toute base relationnelle est optimisé pour traiter de façon ensembliste !

Donc je reviens à la première question : quel est le besoin ?

Bonsoir escartefigue et merci pour la réactivité de votre réponse.

Ahah, oui, je ne suis pas du tout un expert en MySQL et ma non-expertise peux en effet me guider vers des choix incongrus.
Néanmoins, je pense avoir une vision relativement claire sur le fonctionnement du code (gestion du processeur et de la mémoire) et l'optimisation ; une fois le système MySQL mieux compris, je devrais éviter les erreurs de débutants.

Citation:

---

Donc je reviens à la première question : quel est le besoin ?

---

Alors, concrètement, cette fonctionnalité fait parti d'un système visant à déplacer une ligne au sein d'une table de façon à toujours garder un ordre continu dans les id (1,2,3,4...).
Le but final est de laisser à l'utilisateur le moyen d'organiser l'ordre de ses données (par exemple une galerie d'images).

Ainsi, par exemple, cette fonction déplace la ligne 5 en position 2*:

Code:

---

1 2 3 4 5 6 7

id valeur 1 Valeur#1 2 Valeur#2 < 3 Valeur#3 | 4 Valeur#4 | 5 Valeur#5 > 6 Valeur#6

---

Code:

---

1 2 3 4 5 6 7

id valeur 1 Valeur#1 2 Valeur#5 3 Valeur#2 4 Valeur#3 5 Valeur#4 6 Valeur#6

---

Peut-être existe t'il déjà une fonction adaptée à mes besoins ? (sachant qu'en soi, l'ordre peut très bien s'opérer sur un clé non-primaire, mais, j'aime me compliquer la vie, on en apprend toujours plus)

Cette manipulation reste d'ailleurs relativement rare et n'est utilisée que par l'administrateur du site, ainsi, je préfère une lecture plus optimisée que l'écriture, car plus fréquente.
En réalité, dans les faits, ça ne changera pas grand chose au niveau performance car le nombre de lignes ne devrait, à priori, pas dépasser les 1000 occurrences, voire dans un premier temps les 100 occurrences.

À côté de ça, c'est surtout un moyen d'apprendre et de mieux comprendre le fonctionnement de MySQL*; c'est principalement cet aspect de défi qui m'a conduit à envisager cette approche.
À noter que dans un premier temps, j'utilisai une entrée "order" permettant de stocker une même donnée sur plusieurs lignes (en tant que clé non-primaire)*; cette solution me permettrait de résoudre ce problème mais acceptait potentiellement une même valeur d'ordre sur plusieurs entrées.
Créer un système "chaîné" (indiquer la clé du ou des voisins) me semble lourd, peu optimisé et dangereux (certains maillons pouvant ne plus être liés à la chaîne).

Citation:

---

En principe non : un SGBD fonctionne de façon ensembliste, ce n'est donc pas une première mise à jour puis une suivante, mais une mise à jour globale. Celà étant, MySQL est souvent un cas particulier

---

Pour préciser, lorsque vous évoquez les systèmes de gestion de base de donnée, voulez-vous dire que ces derniers fonctionnent fréquemment en calculs parallèles afin d’accélérer les requêtes ?
En tout cas, à toute petite échelle dans les humbles tests que j'ai réalisés, le comportement ressemblait plus à une simple boucle while (évidemment en langage compilée plutôt qu'en interprété).
Je ferai des tests pour voir ce que ça donne sur 10000 entrées ^^.

Citation:

---

Envoyé par Pascal P.

Alors, concrètement, cette fonctionnalité fait parti d'un système visant à déplacer une ligne au sein d'une table de façon à toujours garder un ordre continu dans les id (1,2,3,4...).
Le but final est de laisser à l'utilisateur le moyen d'organiser l'ordre de ses données (par exemple une galerie d'images).

---

Dans tout SGBD relationnel, la vocation principale de l'identifiant primaire, est d'identifier à coup sur une et une seule occurrence dans une table
Son intérêt secondaire est de garantir l'intégrité des données dans les tables filles, via les contraintes de type "reference" installées sur celles-ci
Pour cette raison, on choisit le plus souvent un identifiant technique, attribué par le SGBD (colonne de type auto-incrément dans le cas de mysql) dont l'intérêt est d'être concis et non sémantique donc stable.
Pour les besoins fonctionnels, on définit d'autres identifiants, uniques ou non, qui permettent d'interroger la base de données sur des critères métiers : nom, numéro de sécurité sociale, numéro de siren, etc...
Si vous avez besoin d'ordonner la restitution des données d'une table alors, la seule solution est d'utiliser la commande "ORDER BY"
Penser qu'un identifiant technique puisse servir de critère de rangement est une erreur très fréquente. Rien ne garantit en effet que l'id technique (auto_incrément pour mysql) n° 100 ait été créé avant l'id technique n° 101
La chronologie est souvent respectée quand la base est peu sollicitée (peu d'utilisateurs et d'accès simultanés) mais ce n'est certainement pas une règle !
Il n'existe qu'une et une seule façon de trier les données résultantes d'une requête sur une base relationnelle : utiliser la commande "order by"

Si vous voulez plus de détails sur les raisons d'utiliser le tri plus que tout autre solution, je vous recommande de lire ce sujet : https://www.developpez.net/forums/d1.../probleme-tri/

Bonjour,

Citation:

---

Envoyé par Pascal P.

Cela ne fonctionnera pas car en commençant par la première valeur (6), il ne pourra pas lui donner une valeur incrémentée (7) car celle-ci est déjà occupée par la ligne suivante. La requête est interrompue.

---

Et pourtant, cela devrait fonctionner ! En effet, à la fin de l"opération, chaque ligne a bien une valeur unique, et ce qui se passe sous le capot ne devrait pas poser de problème, c'est au SGBDR de s'occuper de cela.
Toute transaction est censée être atomique, et passer de son état initial a son état final sans étapes intermédiaire, d'un point de vue logique. Les contraintes physiques sous-jacentes imposant des "statuts intermédiaires" ne devraient en aucun cas interférer.
Mais MySQL prend des libertés avec les principes relationnels, et refuse ce genre de chose.

Salut à tous.

Bidouiller dans la clef primaire est une grosse connerie et je rejoins l'opinion négative de mes confrères sur cette question.
Mais avant tout, vous devez apprendre à travailler correctement et non pas faire n'importe quoi !!!
Voici un exemple de ce qu'il faut faire :

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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167

-------------- SET AUTOCOMMIT = 0 --------------   -------------- START TRANSACTION --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS `test` --------------   -------------- CREATE TABLE `test` ( `id` integer unsigned not null primary key, `lib` varchar(255) not null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `test` (`id`,`lib`) values (10,'un'),(11,'deux'),(12,'trois'),(13,'quatre'),(14,'cinq') --------------   -------------- select * from `test` --------------   +----+--------+ | id | lib | +----+--------+ | 10 | un | | 11 | deux | | 12 | trois | | 13 | quatre | | 14 | cinq | +----+--------+ -------------- describe `test` --------------   +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | lib | varchar(255) | NO | | NULL | | +-------+------------------+------+-----+---------+-------+ -------------- alter table `test` drop primary key --------------   -------------- describe `test` --------------   +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | lib | varchar(255) | NO | | NULL | | +-------+------------------+------+-----+---------+-------+ -------------- alter table `test` add column `bis` integer unsigned not null --------------   -------------- describe `test` --------------   +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | lib | varchar(255) | NO | | NULL | | | bis | int(10) unsigned | NO | | NULL | | +-------+------------------+------+-----+---------+-------+ -------------- update `test` set bis = id + 1 --------------   -------------- select * from `test` --------------   +----+--------+-----+ | id | lib | bis | +----+--------+-----+ | 10 | un | 11 | | 11 | deux | 12 | | 12 | trois | 13 | | 13 | quatre | 14 | | 14 | cinq | 15 | +----+--------+-----+ -------------- alter table `test` drop column `id` --------------   -------------- describe `test` --------------   +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | lib | varchar(255) | NO | | NULL | | | bis | int(10) unsigned | NO | | NULL | | +-------+------------------+------+-----+---------+-------+ -------------- select * from `test` --------------   +--------+-----+ | lib | bis | +--------+-----+ | un | 11 | | deux | 12 | | trois | 13 | | quatre | 14 | | cinq | 15 | +--------+-----+ -------------- alter table `test` change column `bis` `id` integer unsigned not null primary key first --------------   -------------- describe `test` --------------   +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | lib | varchar(255) | NO | | NULL | | +-------+------------------+------+-----+---------+-------+ -------------- select * from `test` --------------   +----+--------+ | id | lib | +----+--------+ | 11 | un | | 12 | deux | | 13 | trois | | 14 | quatre | | 15 | cinq | +----+--------+ -------------- COMMIT --------------   -------------- SET AUTOCOMMIT = 1 --------------   Appuyez sur une touche pour continuer...

---

@+

Citation:

---

Envoyé par aieeeuuuuu

Bonjour,

Et pourtant, cela devrait fonctionner ! En effet, à la fin de l"opération, chaque ligne a bien une valeur unique, et ce qui se passe sous le capot ne devrait pas poser de problème, c'est au SGBDR de s'occuper de cela.
Toute transaction est censée être atomique, et passer de son état initial a son état final sans étapes intermédiaire, d'un point de vue logique. Les contraintes physiques sous-jacentes imposant des "statuts intermédiaires" ne devraient en aucun cas interférer.
Mais MySQL prend des libertés avec les principes relationnels, et refuse ce genre de chose.

---

j'ai déjà démontré à mainte reprise que MySQmerde n'était absolument pas un SGBD RELATIONNEL !
En effet, depuis l'origine Codd (l'inventeur des SGBDR) attire l'attention sur le fait que les opérations doivent être "ensemblistes". C'est la règle n°7 :
"
RÈGLE 7 - Insertion, suppression et modification ensemblistes :
Le SGBDR retourne un ensemble d’éléments en réponse aux requêtes qui lui sont soumises. Il doit pouvoir mettre à jour un ensemble d'éléments en exécutant une seule requête.
"
À me lire :
http://sqlpro.developpez.com/SGBDR/ReglesCodd/

J'ai d'ailleurs donné un exemple identique qui consiste à mettre à jour un PK. Tous les bons SBDR le font, mais ni MySQL ni PostGreSQL.... Et il faut se rappeler que SQL ne veut pas dire relationnel ! On peut faire du SQL sur du fihcier texte avec ODBC...

A +

Désolé, je n'ai pas trop accès à internet en ce moment et n'ai pas pu prendre le temps de vous remercier pour votre aide et compréhension de la chose.
Donc, grand merci à vous tous pour votre éclairage sur le sujet et mes erreurs.