Discussion :

[rockt13]

Bonjour,
j'ai une table FRAIS qui à la structure suivante:

Code :

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

id,month,year,price,label

Ce que je souhaite faire, c'est calculé pour un couple mois/année donné le total des frais, ainsi que le détail.

Ma requête actuelle est la suivante:

Code :

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

SELECT month,year,sum(price) as cout, GROUP_CONCAT(CONCAT_WS('+',label,price)) as lbl FROM `lmdi`.`frais` AS `Frai` WHERE 1 = 1 GROUP BY month, year

Requête plutot sympa car elle me sort le rendu que je souhaite


Sauf que la colonne lbl est limitée en taille à 1024 et me tronque donc le détail quand celui-ci dépasse 1024..je suis sur un serveur mutualisé ovh je ne peux donc pas augmenter group_concat_max_len ... je cherche donc à faire une autre requete pour récupérer ce détail... avez vous une idée ?


Merci par avance.

[Artemus24]

Salut rockt13.

Sauf que la colonne lbl est limitée en taille à 1024

En effet, dans la documentation, voici ce qui est dit :

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.

--> http://dev.mysql.com/doc/refman/5.7/...n_group-concat

C'est une valeur par défaut ! Qu'est-ce qui vous empêche de modifier cette valeur ?

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
--------------
select @@group_concat_max_len
--------------
 
+------------------------+
| @@group_concat_max_len |
+------------------------+
|                   1024 |
+------------------------+
--------------
set @@group_concat_max_len = 4096
--------------
 
--------------
select @@group_concat_max_len
--------------
 
+------------------------+
| @@group_concat_max_len |
+------------------------+
|                   4096 |
+------------------------+

@+

[rockt13]

Bonjour,
tu me conseilles donc de redéfinir le group_concat_max_len avant chaque requete ?

[Artemus24]

Salut rockt13.

C'est bien ce qui vous bloque, non ?
Ou est le problème, puisque vous ne pouvez pas modifier le fichier "my.ini" puisque vous êtes sur un serveur mutualisé ovh ?

@+

[rockt13]

Ok merci je vais essayer.

En revanche le jour où mon group_concat atteint les 4096 caractères je serai obligé d'agrandir encore cette valeur? Est ce une solution vraiment perenne ?

[Artemus24]

Salut rockt13.

En revanche le jour où mon group_concat atteint les 4096 caractères je serai obligé d'agrandir encore cette valeur? Est ce une solution vraiment pérenne ?

4096 est une valeur arbitraire, juste pour indiquer que l'on peut modifier cette variable système.

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
--------------
select @@group_concat_max_len
--------------
 
+------------------------+
| @@group_concat_max_len |
+------------------------+
|                   1024 |
+------------------------+
--------------
set @@group_concat_max_len = 1073741824
--------------
 
--------------
select @@group_concat_max_len
--------------
 
+------------------------+
| @@group_concat_max_len |
+------------------------+
|             1073741824 |
+------------------------+

Est-ce vraiment pertinent de faire un group_concat aussi gigantesque ?
Et à quoi cette colonne 'lbl' va vous servir ?

Ne serait-il pas plus judicieux de revoir l'utilité et la conception de cette colonne dans une autre approche de la modélisation de votre base de données ?

@+

[rockt13]

La solution fonctionne correctement, MERCI.

[rockt13]

Salut rockt13.
Est-ce vraiment pertinent de faire un group_concat aussi gigantesque ?
Et à quoi cette colonne 'lbl' va vous servir ?

Ne serait-il pas plus judicieux de revoir l'utilité et la conception de cette colonne dans une autre approche de la modélisation de votre base de données ?

@+

C'était en effet ma question initiale, le moyen de récupérer tous les frais d'un mois et d'une année donnée, regroupé par lbl de frais...

ie:
1, janvier, 2016, dossier, 10
2,mars, 2016, foo , xx
3,janvier, 2016, dossier, 20
4,janvier, 2016, formation, 5
5, janvier, 2016, formation, 10
6,janvier, 2016, pub, 20

Je voulais avec une requête sortir par exemple le cumul pour le mois de janvier 2016:
total: 65€
puis
dossier:30
formation:15
pub:20

J'avais trouvé le moyen de faire un group_concat, puis de repasser sur ma colonne "lbl" et parser toute la ligne pour regrouper par type de frais, en fait avec ma solution j'ai:

Code :

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

dossier+10,dossier+20,formation+5,formation+10,pub+20

Je parse ensuite cette ligne pour obtenir ceci:

Code :

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

1
2
3
dossier:30
formation:15
pub:20

mais c'est clair que si je peux l'obtenir directement avec une requête sympathique... je suis preneur

[Artemus24]

Salut rockt13.

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
--------------
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 auto_increment primary key,
  `month`   char(10)          not null,
  `year`    smallint unsigned not null,
  `price`   integer  unsigned not null,
  `label`   text              not null
) ENGINE=MyIsam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`month`,`year`,`label`,`price`) values
  ('janvier', 2016, 'dossier',   10),
  ('mars',    2016, 'foo',       25),
  ('janvier', 2016, 'dossier',   20),
  ('janvier', 2016, 'formation',  5),
  ('janvier', 2016, 'formation', 10),
  ('janvier', 2016, 'pub',       20)
--------------
 
--------------
select * from test
--------------
 
+----+---------+------+-------+-----------+
| id | month   | year | price | label     |
+----+---------+------+-------+-----------+
|  1 | janvier | 2016 |    10 | dossier   |
|  2 | mars    | 2016 |    25 | foo       |
|  3 | janvier | 2016 |    20 | dossier   |
|  4 | janvier | 2016 |     5 | formation |
|  5 | janvier | 2016 |    10 | formation |
|  6 | janvier | 2016 |    20 | pub       |
+----+---------+------+-------+-----------+
--------------
select    year, month, sum(price) as price, group_concat(label) as label
    from  (
 
      select  year, month, sum(price) as price, concat(label, ':', sum(price)) as label
        from  test
    group by  year, month, label
 
) as x
group by year, month
--------------
 
+------+---------+-------+--------------------------------+
| year | month   | price | label                          |
+------+---------+-------+--------------------------------+
| 2016 | janvier |    65 | dossier:30,formation:15,pub:20 |
| 2016 | mars    |    25 | foo:25                         |
+------+---------+-------+--------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[rockt13]

Super, merci beaucoup

[clavier12AZQSWX]

bonjour,

j'ai besoin d'un avis subtil sur cette instruction cette instruction :

Code :

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

set @@group_concat_max_len = 1073741824

quand le champs long tronqué est dans le CREATE d'une VIEW et non pas dans une requête select classique,
faut-il mieux ajouter l'instruction SET dans la création de la vue ou juste en dehors dans la connexion mysql voire dans la partie php?

[Artemus24]

Salut clavier12AZQSWX.

Le "group_concat_max_len" est une variable système de MySql.
--> https://dev.mysql.com/doc/refman/8.0...concat_max_len

Si tu ne précises rien, tu as la valeur par défaut qui est de 1024.

Tu fais une view et tu as un problème de longueur.
A priori, rallonger la longueur du group_concat ne pose aucun problème.
Comme qui dirait, qui peut le moins, peu le plus.

Autant définir cette variable système dans le fichier my.ini afin de l'appliquer à ton serveur mysql.

La valeur que tu demandes est : 1073741824, soit 1G.
Si tu te trouves sur une plateforme 32 bits, tu peux mettre au maximum 4G - 1, soit 4294967295.

Lors de la création de la view, tu ne peux pas ajouter une instruction SET.
--> https://dev.mysql.com/doc/refman/8.0...eate-view.html
La syntaxe de la view ne le permet pas.

Je suppose que tu voulais dire lors de la session mysql.
Oui, on peut le faire, mais je ne vois pas trop l'intérêt de surcharger une session avec un paramétrage que l'on peut généraliser (global) au serveur mysql.
Cela se place, bien sûr, dans le script qui va exploiter la view.

Selon moi, comme je l'ai dit ci-avant, le mieux est de placer cette variable système dans le fichier my.ini.

@+

[clavier12AZQSWX]

bonjour l'autotutoyeur !

Si tu ne précises rien, tu as la valeur par défaut qui est de 1024.

sur mon serveur de dev (xamp), elle est déjà augmenté par défaut.
je me suis rendu compte que sur le serveur de prod où elle n'est que de 1024

Autant définir cette variable système dans le fichier my.ini afin de l'appliquer à ton serveur mysql.

si je l'a met en global config, ça veut dire (supposition) que chaque connexion aura plus d'emplacement mémore réservé par défaut, non ? des utilisateurs (ou des connexions) risquent de se faire allouer une qté de mémoire dont ils n'auront pas l'utilité : donc gâchi de ressource RAM pour rien.

me trompe-je ?

Comme j'ai pas la main sur le serveur pour modifier le conf, je l'ai ajouté en entête de la connexion applicatif (php...connecteur..Etc) . Ainsi, que mon appli aura ce bénéfice d'avoir autant de mémoire.

Par contre je ne sais toujours pas si ce ne serait pas mieu de la mettre juste là où elle sera utilisée, c-a-d dans le CREATE VIEW...blaba bla... SET group_concat_max_len=2048;.... SELECT ...blabla ;
car seule une colonne de cette VIEW dépasse les 1024 initiaux....

Dernière question :
Quand c'est tronqué, il n'y a pas d'alerte ou message erreur explicite ou trace dans le log ? j'ai eu la chance d'avoir une effet bug visuel, sinon je ne m'en saurai jamais aperçu.

[Artemus24]

Salut clavier12AZQSWX.

me trompe-je ?

Bonne question. Aucune idée.

Je suppose que l'allocation de cette quantité de RAM supplémentaire se fait lors de l'exécution de cette fonction.
Autrement dit, uniquement à la demande et non d'une manière permanente. Donc l'impact sera faible.

Par contre je ne sais toujours pas si ce ne serait pas mieux de la mettre juste là où elle sera utilisée, c-a-d dans le CREATE VIEW...blaba bla... SET group_concat_max_len=2048;.... SELECT ...blabla ;

Pour que l'impact soit le plus faible, le mieux est de mettre ce SET dans le script qui exécute ta view, bien sûr, juste avant l'exécution de la view, comme tu l'indiques.
Voir la syntaxe du set :
--> https://dev.mysql.com/doc/refman/5.7...-variable.html

Ce sera : "SET SESSION group_concat_max_len = 1G"

Quand le script se termine, la variable système reprendra sa valeur global.

Il n'y a pas d'alerte quand le résultat issue du group_concat est tronqué.
Utilisez-vous l'UTF-8 ? Il se peut que le problème vienne de là.
En effet, un caractère peut occuper plusieurs octets dans une chaîne.
Vous vous dites que votre chaîne contient N caractères et devrait avoir comme longueur N, alors que dans la réalité elle est beaucoup plus grande.

Sinon, pourquoi augmenter la longueur de votre group_concat ?
Avez-vous énormément de chose à récupérer ?
N'y a-t-il pas une autre façon de procéder ?

@+