Discussion :

[kabkab]

Bonjour,
Dans ma BDD j'ai une table avec ID, NOM, PRENOM, CREATED_DATE ...
Je souhaite lui rajouter une colonne ORDRE qui me servira à ordonner (ORDER BY DESC) les enregistrements autre qu'avec ID. Je sais ajouter cette colonne mais je cherche une fonction qui permet d'initialiser la colonne ORDRE de 1 à .... selon les ID.
A savoir que, telle qu'elle existe, ma table commence par ID = 1 mais qu'il y a des creux à l'intérieur à cause des enregistrements supprimés.

Je ne souhaite pas le faire en PHP à cause de la lenteur que cela va occasionner durant la période de mise à jour mais avec une fonction MySql.

Merci,

[escartefigue]

bonjour,

Si vous avez MySQL V8, il n'est pas néccessaire d'ajouter une colonne dans la table, vous pouvez la calculer via une fonction fenetrée avec ROW_NUMBER() :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select id
     , row_number() over(order by id) as rang
from ma_table

Cela étant dit, il faut rapeller que si votre colonne ID est de type auto_increment, alors les valeurs ne respectent pas systématiquement la chronologie, donc je ne sais pas ce que vous voulez faire de cette nouvelle colonne, mais il faut surtout pas en déduire une chronologie. La seule façon de gérer une chronologie est d'avoir une colonne d'horodatage.

[CinePhil]

Une colonne ordre qui va donc accueillir des valeurs à la création de la colonne... puis qui va devoir être entièrement re-valorisée à la moindre insertion dans la table !

Alors qu'un simple ORDER BY dans une requête permet d'avoir le bon ordre à tout moment dans une requête... je ne vois pas l'intérêt de la colonne !

Les seules colonnes "ordre" que je mets sont dans les tables de référence pour déterminer l'ordre d'affichage dans une liste qui ne peut pas être déterminé par un ORDER BY.

Exemple concret :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `tr_mode_financement_mfi` (
 `mfi_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identifiant du mode de financement',
 `mfi_code` char(5) NOT NULL COMMENT 'Code du mode de financement',
 `mfi_libelle` varchar(45) NOT NULL COMMENT 'Libellé du mode de financement',
 `mfi_ordre` tinyint(4) NOT NULL COMMENT 'Ordre de présentation dans les listes',
 PRIMARY KEY (`mfi_id`),
 UNIQUE KEY `xu_mfi_code` (`mfi_code`),
 UNIQUE KEY `xu_mfi_libelle` (`mfi_libelle`),
 UNIQUE KEY `xu_mfi_ordre` (`mfi_ordre`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='Mode de financement des études'

Insertion des données telles qu'elles sont arrivées (ordre donné par la clé primaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
INSERT INTO `tr_mode_financement_mfi` (`mfi_id`, `mfi_code`, `mfi_libelle`, `mfi_ordre`) VALUES
(1, 'IND', 'Financement individuel', 1),
(2, 'PE', 'Pôle emploi', 2),
(3, 'CIF', 'Congé Individuel de Formation', 3),
(4, 'EMP', 'Financement par l\'employeur', 4),
(5, 'NC', 'Non connu', 99),
(6, 'SO', 'Sans objet', 100),
(7, 'AUF', 'Agence Universitaire de la Francophonie', 5);

Comme cette table alimente une liste déroulante dans un formulaire, on voit que, grâce à la colonne mfi_ordre :
- je peux afficher en dernier les cas "Non connu" et "Sans objet" ;
- je peux placer avant ces derniers cas la dernière ligne insérée de code AUF qui ne concerne qu'un cas particulier pour un seul diplôme alors que les autres cas placés avant sont plus génériques.

Si demain on me dit qu'il vaut mieux inverser par exemple les lignes 2 et 3 dans les listes déroulantes, je change juste la colonne mfi_ordre.

[escartefigue]

Les seules colonnes "ordre" que je mets sont dans les tables de référence pour déterminer l'ordre d'affichage dans une liste qui ne peut pas être déterminé par un ORDER BY.

On peut aussi le faire avec un order by case when...

[CinePhil]

Oui mais ça complique les requêtes ; ça demande à modifier le code de l'application alors que là je ne modifie que les valeurs dans la table. Je peux même faire une procédure SQL qui fait le boulot et qui peut être appelée par l'application de backend sur les données de référence.

[SQLpro]

On peut aussi le faire avec un order by case when...

et dans ce cas pas indexable !!!!!!!!

A +

[escartefigue]

Oui je suis bien d'accord, je réagissais seulement à

Les seules colonnes "ordre" que je mets sont dans les tables de référence pour déterminer l'ordre d'affichage dans une liste qui ne peut pas être déterminé par un ORDER BY.

car l'ORDER BY reste bien sur possible

Pour le reste, ce qui m'inquiète c'est cette recherche de tri sur un identifiant qui n'est surtout pas fait pour ça, je crains qu'on soit encore une fois dans l'incompréhension du fonctionnement des identifiants techniques attribués par le SGBD. Attendons des explications sur le besoin fonctionnel pour en avoir le coeur net

[kabkab]

Bonjour,
Pour bien m'expliquer, ma colonne ORDRE doit obligatoirement exister pour la simple raison que l'affichage des articles sera dépendant de cette colonne.
Les enregistrements que j'ajoute auront certes une valeur de (Last_Order + 1) mais il doit être possible de changer cet ordre plus tard.

Autrement, j'ai trouvé la solution tout seul (comme d'habitude).

Après un ALTER TABLE il me suffit de lancer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SET @a  = 0;  
UPDATE `My_table` SET `ORDRE` =  @a:=@a+1  WHERE 1;

Merci à tous et pour tout.

[SQLpro]

Bonjour,
Pour bien m'expliquer, ma colonne ORDRE doit obligatoirement exister pour la simple raison que l'affichage des articles sera dépendant de cette colonne.
Les enregistrements que j'ajoute auront certes une valeur de (Last_Order + 1) mais il doit être possible de changer cet ordre plus tard.

Autrement, j'ai trouvé la solution tout seul (comme d'habitude).

Après un ALTER TABLE il me suffit de lancer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SET @a  = 0;  
UPDATE `My_table` SET `ORDRE` =  @a:=@a+1  WHERE 1;

Merci à tous et pour tout.

Obligatoirement NON, parce que vous pouvez faire une vue !

Il serait temps d'apprendre ce qu'est une vue !

A +

[Artemus24]

Salut à tous.

Je souhaite lui rajouter une colonne ORDRE qui me servira à ordonner (ORDER BY DESC) les enregistrements autre qu'avec ID.

Bien que je ne vois pas trop l'utilité de cette approche, sur quel critère désirez-vous ordonner vos lignes ?

Je sais ajouter cette colonne mais je cherche une fonction qui permet d'initialiser la colonne ORDRE de 1 à .... selon les ID.

Si vous procédez ainsi, votre colonne "ORDRE" ne servira à rien. Vos lignes seront triées dans le même ordre que la colonne "ID".

A savoir que, telle qu'elle existe, ma table commence par ID = 1 mais qu'il y a des creux à l'intérieur à cause des enregistrements supprimés.

En quoi avoir des trous dans la numérotation de la colonne "ID" posent des problèmes ?

Les enregistrements que j'ajoute auront certes une valeur de (Last_Order + 1) mais il doit être possible de changer cet ordre plus tard.

Pour incrémenter votre colonne "ORDRE" en faisant +1, je veux bien. Mais c'est le "Last_Order" qui pose un problème de performance.
Il faudra que votre colonne "ORDRE" soit indexée et la recherche du "Last_Order" se fera sur un "max(ordre)".

La grosse connerie est d'affirmer que vous allez renuméroter l'ordre de vos lignes.
Le but des colonnes est de créer de la stabilité dans votre base de données.
Si vous changez constamment cette numérotation, fonctionnellement, cela n'a pas de sens.

Obligatoirement NON, parce que vous pouvez faire une vue !

C'est en effet vers cette solution qu'il faut se diriger, sauf que le "view" n'admet pas les "User Defined Variables" (je parle de @a dans l'exemple).

Ci-après, je reprends la solution d'Escartefigue qui ne fonctionne qu'avec la version MySql 8.0.
Et j'indique la solution pour les anciennes versions de MySql.

L'exemple sera basée sur l'ordre de la date de création.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
create table `test`
( `id`            integer  unsigned  not null auto_increment primary key,
  `nom`           varchar(255)           not null,
  `prenom`        varchar(255)           not null,
  `created_date`  timestamp(6)           not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`nom`,`prenom`,`created_date`) VALUES
  ('Nom 1','Prénom 1','2019-08-17 15:23:47.123456'),
  ('Nom 2','Prénom 2','2019-05-05 14:02:06.234567'),
  ('Nom 3','Prénom 3','2019-03-23 13:14:15.345678'),
  ('Nom 4','Prénom 4','2019-09-14 12:47:58.456789'),
  ('Nom 5','Prénom 5','2019-06-12 11:53:21.567890')
--------------
 
--------------
select * from `test`
--------------
 
+----+-------+----------+----------------------------+
| id | nom   | prenom   | created_date               |
+----+-------+----------+----------------------------+
|  1 | Nom 1 | Prénom 1 | 2019-08-17 15:23:47.123456 |
|  2 | Nom 2 | Prénom 2 | 2019-05-05 14:02:06.234567 |
|  3 | Nom 3 | Prénom 3 | 2019-03-23 13:14:15.345678 |
|  4 | Nom 4 | Prénom 4 | 2019-09-14 12:47:58.456789 |
|  5 | Nom 5 | Prénom 5 | 2019-06-12 11:53:21.567890 |
+----+-------+----------+----------------------------+
--------------
select  *,
        row_number() over(order by created_date) as ordre
  from  `test`
--------------
 
+----+-------+----------+----------------------------+-------+
| id | nom   | prenom   | created_date               | ordre |
+----+-------+----------+----------------------------+-------+
|  3 | Nom 3 | Prénom 3 | 2019-03-23 13:14:15.345678 |     1 |
|  2 | Nom 2 | Prénom 2 | 2019-05-05 14:02:06.234567 |     2 |
|  5 | Nom 5 | Prénom 5 | 2019-06-12 11:53:21.567890 |     3 |
|  1 | Nom 1 | Prénom 1 | 2019-08-17 15:23:47.123456 |     4 |
|  4 | Nom 4 | Prénom 4 | 2019-09-14 12:47:58.456789 |     5 |
+----+-------+----------+----------------------------+-------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
           select  t1.*,
                   count(t2.id)+1 as ordre
             from  `test` as t1
  left outer join  `test` as t2
               on  t2.created_date < t1.created_date
         group by  t1.id
         order by  t1.created_date
--------------
 
--------------
select * from `vue`
--------------
 
+----+-------+----------+----------------------------+-------+
| id | nom   | prenom   | created_date               | ordre |
+----+-------+----------+----------------------------+-------+
|  3 | Nom 3 | Prénom 3 | 2019-03-23 13:14:15.345678 |     1 |
|  2 | Nom 2 | Prénom 2 | 2019-05-05 14:02:06.234567 |     2 |
|  5 | Nom 5 | Prénom 5 | 2019-06-12 11:53:21.567890 |     3 |
|  1 | Nom 1 | Prénom 1 | 2019-08-17 15:23:47.123456 |     4 |
|  4 | Nom 4 | Prénom 4 | 2019-09-14 12:47:58.456789 |     5 |
+----+-------+----------+----------------------------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

[kabkab]

Bonjour,
Malgré le Résolu, je reçois encore des réponses.

J'explique donc le pourquoi de cette colonne et la raison qui fait que ma solution me satisfait.

C'est pour un site de news quotidiennes. Chaque jour un lot d'articles seront accessibles à partir de la même heure mais l'ordre d'introduction des données se fera à partir du moment où les rédacteurs auront terminé chacun son article.

A la fin et lorsque tous les articles auront été saisis, un ordre de parution dans les listes de catégories est à appliquer. Ce qui fait l'obligation de l'existence de cette colonne supplémentaire 'ORDRE'.

J'espère avoir été clair maintenant.

[Artemus24]

Salut kabkab.

Je comprends mieux l'utilité de ce classement. Mais je pense que le projet a été mal pensé dès le départ.
J'aurai créé une table contenant les articles en attente de finalisation.

Pour la validation de ces articles, je les aurai associé à un rédacteur et à jour une date de finalisation.
Comme le dit Escartefigue :

La seule façon de gérer une chronologie est d'avoir une colonne d'horodatage.

cette date servira d'horodatage.
Dans ce cas vous auriez une chronologie indépendante de l'entrée dans la table des articles en attente.

Il arrive fréquemment qu'une mauvaise modélisation entraîne un problème dans la faisabilité.
D'où votre interrogation sur comment remplir la colonne "ORDRE".

@+