[Résolu] Calcul automatique d'un champ en SQL

Bonjour à tous
Ma base de données MySQL contient une table "Addresses". Chaque adresse a un champ "zip" (code postal) et un champ "region"
Les "régions" qui m'intéressent sont:
1) paris
2) ile de france
3) autres régions
Si l'adresse n'est pas en France, le champ "region" est NULL
Actuellement la valeur de "region" est calculée en PHP avant la création ou la mise à jour de la table Address à partir du champ "zip".
J'ai cru comprendre que l'on pouvait faire ça automatiquement dans MySQL, mais je n'ai pas trouvé comment faire.
Auriez vous des informations à me communiquer?
Merci de votre aide

Et l'eau,

Ton explication n'est pas très claire. Peux tu indiquer sur le forum la structure de ta table ou tes tables ?

Sinon, ceci (peut être ?)

SELECT 
    un_champ, 
    un_autre_champ,
    etc 
FROM 
    adresses 
WHERE 
    region IN ('Paris', 'ile de france', 'autres régions')

Modifié par niuxe (19 Jun 2020 - 13:17)

Pour un select :

SELECT 
    street,
    zip,
    SUBSTR(zip, 0,3) AS dpt,
    town,
    country, 
    CASE 
        WHEN SUBSTR(zip, 0,3) = '75' THEN 'paris' 
        WHEN  SUBSTR(zip, 0,3) = '77' 
		OR 
			SUBSTR(zip, 0,3) = '78' 
		OR 
			SUBSTR(zip, 0,3) BETWEEN '91' AND '95' 
		THEN 'idf'  
        ELSE 'other'
    END
FROM 
    adresses;

pour un update :

UPDATE  
    adresses 
SET 
    regionid= (
    CASE 
        WHEN SUBSTR(zip, 0,3) = '75' THEN 'paris' 
        WHEN SUBSTR(zip, 0,3) = '77' 
		OR 
			SUBSTR(zip, 0,3) = '78' 
		OR 
			SUBSTR(zip, 0,3) BETWEEN '91' AND '95' 
		THEN 'idf'  
        ELSE 'other'
    END
);

Attention à l'update, je n'ai pas mis de clause WHERE. De toute manière, la règle est simple : avant de faire un update ou un delete, on fait toujours un select afin de vérifier ce que l'on va supprimer ou mettre à jour

Chaque fois, je me trompe dans le between. Je ne sais plus s'il prend les critères désignés ou seulement ceux à l'intérieur. Il me semble que oui
Modifié par niuxe (19 Jun 2020 - 17:52)

Merci de ta réponse.
Ce que tu me dis veut sans doute dire que j'avais compris de travers.
Dans ton SELECT on part d'une table qui n'a pas de champ "regionID" et on le met dans la réponse.
Dans ton UPDATE, tu pars d'une table qui a un champ "regionID" et tu le remplis à chaque UPDATE.
Mais ça veut dire que toute commande UPDATE doit contenir ce code, c'est à dire que tu fais en SQL ce que je fais en PHP.
Ce que j'avais cru comprendre c'est qu'on pouvait mettre quelque part dans la dabatase une fonction enregistrée qui faisait ça sans qu'on ait besoin de l'appeler spécifiquement à chaque UPDATE.
Il y a un mécanisme de "trigger" qui doit permettre de faire ça, mais ça ne doit pas exister dans MySQL

PapyJP a écrit :

Ce que tu me dis veut sans doute dire que j'avais compris de travers.

oui je pense

PapyJP a écrit :

Dans ton SELECT on part d'une table qui n'a pas de champ "regionID" et on le met dans la réponse.

oui. exemple le champ créé à la volée : dpt

PapyJP a écrit :

Dans ton UPDATE, tu pars d'une table qui a un champ "regionID" et tu le remplis à chaque UPDATE.
Mais ça veut dire que toute commande UPDATE doit contenir ce code, c'est à dire que tu fais en SQL ce que je fais en PHP.

oui et c'est nettement plus rapide puisque php/python/ruby/etc. et apache/nginx n’interprètent pas.

PapyJP a écrit :

Ce que j'avais cru comprendre c'est qu'on pouvait mettre quelque part dans la dabatase une fonction enregistrée qui faisait ça sans qu'on ait besoin de l'appeler spécifiquement à chaque UPDATE.
Il y a un mécanisme de "trigger" qui doit permettre de faire ça, mais ça ne doit pas exister dans MySQL

Que ce soit les trigger ou procédure stocké, je ne connais pas trop puisque j'en ai jamais eu besoin. Là de toute évidence ta table n'est pas normalisée. Il est toujours préférable d'avoir peu de champ et beaucoup de lignes que l'inverse.

Après, je ne sais pas ce que tu veux faire : select ou update ? Si c'est un select, regarde les VIEW

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition; 

Modifié par niuxe (19 Jun 2020 - 18:05)

niuxe a écrit :


Que tu aies :
- id unique (tiens... un oxymore)
- qu'un id ne soit pas composite (je le vois souvent. Or c'est une hérésie... Edgar Codd sauterait au plafond voyant ça)
- que tes champs soient atomiques
- que tu aies des index bien placés
- etc.
- etc.

relis bien mon message précédent, j'ai ajouté une note

Pour moi c'est le cas, mais personne n'est parfait.
A propos des varchar la doc dit:
"Avant Mysql version 5.0.3, le type de données Varchar peut stocker 255 caractères, mais à partir de la version 5.0.3, il peut contenir 65 535 caractères. MAIS il a une limitation de taille maximale de 65 535 octets. Cela signifie que toutes les colonnes ne doivent pas dépasser 65 535 octets."

A propos de "DEFAUT NULL" c'est phpMySql qui génère ça, moi j'ai simplement dit que je considérais que ce champ pouvait être null et n'avait pas de valeur par défaut.
Modifié par PapyJP (19 Jun 2020 - 21:30)

PapyJP a écrit :
Je ne comprends pas très bien de quoi tu parles. Je regarderai demain. Je pense qu’un schéma de données qui marche sans problème depuis 21 ans ne peut pas être totalement à jeter.

Vraisemblablement, ton schema est équivalent à ton fichier plat :
| id | addressID | street | zip | town | country | regionID |

Or, déjà on peut scinder cette table en 3 ou en 4. Ton schema peut fonctionner pendant plusieurs années. Mais tes requêtes seront moins performantes et pour étendre ton schéma sans avoir de doublons, bonne chance.

PapyJP a écrit :

Ce qui me pose un problème c’est de devoir utiliser une base de données SQL pour y stocker des objets.

Je vois pas de quoi tu parles. Quel objet ?

PapyJP a écrit :

Il se trouve que j’ai travaillé sur ce sujet dans les années 90, il y avait des travaux très intéressants sur les bases de données objet, mais elles ont été anéanties par qui nous savons.

Merise existe depuis les années 70-80. Tout comme le SQL. Edgar Codd et son acolyte dont j'ai oublié le nom ont travaillé pour sortir quelque chose de cohérent et surtout efficace. Il faut comprendre que dans les années 70, le SQL n'était pas du tout prédominant. Le nom du langage ne me revient pas. D'ailleurs, il était largement utilisé dans les databases de cette époque (exemple : Ingres papa de Postgresql).

Bob Miner, Larry Ellison et Ed Oates ont vu les travaux d'Edgar et ont décidé de révolutionner le secteur.... (Ils l'ont révolutionné) Si tu fais allusion à Bill Gates, bah non, il n' pas fait grand chose. SQLServer n'est qu'une database à la sauce microsoft (donc un sql légèrement différent du standard).
Modifié par niuxe (20 Jun 2020 - 18:30)

J’ai eu le désagréable privilège de travailler sous les ordres d’un des concepteurs de Merise qui publiait des bouquins à ce sujet. Le Merise de l’époque conduisait à des modèles de données fondés sur des processus figés. Quelques modifications des processus suffisaient à casser le modèle.
J’espère que les évolutions de Merise ont permis de changer cela. Mais je n’ai aucune envie de me plonger à nouveau dans ce marécage.
Je n’avais pas non plus envie de faire du SQL, mais j’y suis contraint. Pour autant que je comprenne SQL conduit à éclater les données, choses complètement contraire à ma façon de travailler depuis 50 ans. Que cela conduise à un modèle de données hérétique à tes yeux ne m’étonne pas.