Passer au contenu principal
Base de données, Logiciel

SQL avec MySQL/MariaDB : requêtes, tables, droits et transactions

Les bases de données relationnelles ont quelque chose de rassurant.

Des tables.

Des colonnes.

Des lignes.

Des contraintes.

Une logique apparemment implacable.

Puis quelqu’un exécute :

DELETE FROM clients;

et toute cette sérénité conceptuelle prend soudainement une dimension beaucoup plus personnelle.

SQL est le langage utilisé pour décrire, interroger et modifier les données de nombreux systèmes de gestion de bases de données relationnelles.

Dans cet article, nous allons nous concentrer sur les fondamentaux communs à :

  • MySQL ;
  • MariaDB.

Les deux systèmes sont historiquement très proches et partagent une grande partie de leur syntaxe.

Mais ils ont évolué séparément et ne doivent plus être considérés comme parfaitement interchangeables.

SQL est relativement facile à commencer. La difficulté n’est pas d’écrire SELECT. C’est de savoir exactement quelles données votre requête va toucher avant d’appuyer sur Entrée.

SQL, MySQL et MariaDB : ne mélangeons pas tout

Trois termes reviennent constamment :

SQL
MySQL
MariaDB

Ils ne désignent pas la même chose.

Terme Définition
SQL Langage permettant de travailler avec des bases de données relationnelles
MySQL Système de gestion de bases de données relationnelles développé aujourd’hui par Oracle
MariaDB SGBD issu historiquement de MySQL et développé séparément

Une requête comme :

SELECT nom, prix
FROM produits
WHERE prix < 100;

est du SQL.

MySQL ou MariaDB est le logiciel serveur qui reçoit cette requête, la comprend, l’optimise et l’exécute.

Qu’est-ce qu’une base de données relationnelle ?

Une base relationnelle organise principalement les informations dans des tables.

Chaque table représente généralement un type d’entité.

Par exemple :

clients
produits
commandes
factures

Base de données

Dans MySQL et MariaDB, une base de données constitue un espace logique contenant notamment :

  • des tables ;
  • des vues ;
  • des procédures ;
  • des fonctions ;
  • des triggers ;
  • des événements ;
  • d’autres objets.

Pour un petit projet :

boutique

peut par exemple contenir :

boutique.clients
boutique.produits
boutique.commandes

Table

Une table contient des lignes organisées selon des colonnes définies.

Par exemple :

id nom prix en_stock
1 Couteau suisse 49.99 1
2 Lampe frontale 29.90 1
3 Boussole 14.50 0

La comparaison avec une feuille de calcul est utile pour débuter.

Mais une base relationnelle ajoute notamment :

  • des types de données ;
  • des contraintes ;
  • des index ;
  • des clés primaires ;
  • des clés étrangères ;
  • des transactions ;
  • un contrôle d’accès ;
  • un moteur d’optimisation des requêtes.

Disons qu’Excel et SQL peuvent tous deux afficher des cellules.

La ressemblance commence à diminuer sérieusement après cela.

Ligne, enregistrement et colonne

Une ligne représente une occurrence.

Par exemple :

1 | Couteau suisse | 49.99 | 1

correspond à un produit.

Une colonne représente une propriété :

nom
prix
en_stock

Le terme :

champ

est souvent utilisé dans le langage courant, même si colonne est généralement plus précis lorsqu’on décrit la structure d’une table.

Les grandes familles de commandes SQL

On classe traditionnellement les instructions SQL en plusieurs familles.

Famille Rôle Exemples
DDL Définition de la structure CREATE, ALTER, DROP
DML Manipulation des données INSERT, UPDATE, DELETE
DQL Interrogation SELECT
DCL Contrôle des droits GRANT, REVOKE
TCL Contrôle transactionnel COMMIT, ROLLBACK

Cette classification est utile pédagogiquement, même si les frontières exactes et la terminologie varient selon les documentations.

Se connecter à MySQL ou MariaDB

Avec le client MySQL :

mysql -u utilisateur -p

Avec le client MariaDB :

mariadb -u utilisateur -p

L’option :

-u

indique l’utilisateur.

L’option :

-p

demande au client de réclamer le mot de passe.

Celui-ci n’est normalement pas affiché pendant la saisie.

Ce comportement est volontaire.

Votre clavier fonctionne encore.

Ne mettez pas le mot de passe directement dans la commande

Évitez :

mysql -u paul -pMonSuperMotDePasse

Le mot de passe peut alors apparaître :

  • dans l’historique du shell ;
  • dans certains outils d’observation ;
  • dans des scripts ;
  • dans les captures d’écran de documentation envoyées à cinquante collègues.

Préférez :

mysql -u paul -p

puis saisissez le secret lorsqu’il est demandé.

Connexion locale root avec MariaDB sous Debian

Sur de nombreuses installations Debian de MariaDB, le compte :

'root'@'localhost'

utilise l’authentification par socket Unix.

La commande naturelle est alors souvent :

sudo mariadb

plutôt que :

mysql -u root -p

Le principe est que le compte root du système local est déjà authentifié par Linux.

MariaDB peut donc utiliser cette identité lorsqu’il se connecte par le socket Unix.

Le comportement exact dépend néanmoins de l’installation et de la configuration d’authentification.

Connexion à un serveur distant

Pour spécifier un serveur :

mysql -h db.example.com -u paul -p

ou :

mariadb -h db.example.com -u paul -p

Le :

-h

désigne l’hôte distant.

Attention : permettre les connexions distantes implique aussi de considérer :

  • l’écoute réseau du serveur ;
  • le pare-feu ;
  • le compte SQL et sa partie hôte ;
  • le chiffrement TLS ;
  • la segmentation réseau.

Créer :

'paul'@'%'

n’ouvre pas magiquement Internet vers le serveur.

Mais cela peut considérablement élargir les endroits depuis lesquels ce compte est accepté si le réseau laisse passer la connexion.

Le prompt SQL

Une fois connecté, vous obtenez quelque chose ressemblant à :

mysql>

ou :

MariaDB [(none)]>

Les instructions SQL se terminent généralement par :

;

Par exemple :

SELECT NOW();

Si vous oubliez le point-virgule, le client attend simplement la suite.

Ce n’est pas forcément un crash.

Le serveur attend patiemment que vous terminiez votre phrase.

Afficher les bases disponibles

SHOW DATABASES;

Le résultat dépend évidemment des privilèges du compte connecté.

Un utilisateur restreint ne voit pas nécessairement tout ce qui existe sur le serveur.

Créer une base de données

CREATE DATABASE boutique;

Une variante pratique est :

CREATE DATABASE IF NOT EXISTS boutique;

Elle évite une erreur si la base existe déjà.

Jeu de caractères : pensez utf8mb4

Pour une application moderne, on peut créer explicitement :

CREATE DATABASE boutique
    CHARACTER SET utf8mb4;

utf8mb4 permet de représenter correctement l’ensemble des caractères Unicode pris en charge par MySQL/MariaDB dans ce contexte.

Cela inclut notamment les caractères que votre application découvrira seulement après sa mise en production.

Typiquement :

é
漢
🙂

Le dernier étant souvent celui qui révèle que quelqu’un avait choisi un encodage « suffisant » en 2009.

Utiliser une base

USE boutique;

À partir de là, les noms non qualifiés seront généralement recherchés dans cette base.

Par exemple :

SELECT * FROM produits;

équivaut dans ce contexte à travailler avec :

boutique.produits

Quelle base suis-je en train d’utiliser ?

SELECT DATABASE();

Une commande particulièrement intéressante juste avant de lancer :

DROP TABLE quelque_chose;

La connaissance de son environnement améliore nettement la qualité de vie.

Créer une table

Construisons une table plus robuste que l’exemple minimal :

CREATE TABLE produits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    prix DECIMAL(10,2) NOT NULL,
    en_stock BOOLEAN NOT NULL DEFAULT TRUE,
    cree_le TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

Décomposons cette définition

id

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

sert d’identifiant numérique.

  • BIGINT : entier de grande capacité ;
  • UNSIGNED : pas de valeurs négatives ;
  • NOT NULL : valeur obligatoire ;
  • AUTO_INCREMENT : génération automatique des nouvelles valeurs.

nom

nom VARCHAR(100) NOT NULL

stocke une chaîne de longueur variable jusqu’à la limite définie.

prix

prix DECIMAL(10,2) NOT NULL

DECIMAL représente un nombre décimal à précision fixe.

Ici :

10 = nombre total de chiffres
2  = chiffres après la virgule décimale

C’est généralement plus approprié qu’un type à virgule flottante pour des montants monétaires exigeant une précision décimale déterministe.

en_stock

en_stock BOOLEAN NOT NULL DEFAULT TRUE

Dans MySQL/MariaDB, le concept booléen possède une particularité.

Dans MariaDB, notamment :

BOOLEAN

est un synonyme de :

TINYINT(1)

et :

TRUE
FALSE

correspondent respectivement à :

1
0

Il ne faut donc pas imaginer un type booléen totalement séparé comme dans certains autres SGBD.

Afficher les tables

SHOW TABLES;

Afficher la structure d’une table

DESCRIBE produits;

ou :

DESC produits;

Vous obtenez notamment :

  • nom des colonnes ;
  • type ;
  • acceptation de NULL ;
  • index ;
  • valeurs par défaut ;
  • informations complémentaires.

Afficher la vraie commande CREATE TABLE

Pour voir précisément comment le serveur représente la table :

SHOW CREATE TABLE produits;

C’est souvent plus instructif que :

DESCRIBE

lorsqu’on cherche :

  • les index ;
  • les contraintes ;
  • le moteur ;
  • le charset ;
  • la collation.

La clé primaire

Une :

PRIMARY KEY

identifie de manière unique chaque ligne.

Ici :

PRIMARY KEY (id)

garantit qu’il ne peut pas exister deux produits avec le même identifiant.

Une bonne clé primaire doit être :

  • unique ;
  • non NULL ;
  • stable autant que possible.

AUTO_INCREMENT

Avec :

AUTO_INCREMENT

le serveur attribue automatiquement une valeur lorsqu’on ne fournit pas explicitement l’identifiant.

Par exemple :

INSERT INTO produits (nom, prix, en_stock)
VALUES ('Couteau suisse', 49.99, TRUE);

L’ID est créé automatiquement.

Récupérer le dernier identifiant généré

Dans la même connexion :

SELECT LAST_INSERT_ID();

est très utile après une insertion automatique.

Insérer plusieurs lignes

On peut effectuer plusieurs insertions dans une seule instruction :

INSERT INTO produits (nom, prix, en_stock)
VALUES
    ('Couteau suisse', 49.99, TRUE),
    ('Lampe frontale', 29.90, TRUE),
    ('Boussole', 14.50, FALSE);

C’est généralement plus efficace que d’envoyer trois requêtes séparées.

NULL : absence de valeur

SQL possède la valeur spéciale :

NULL

Elle représente essentiellement :

« valeur absente ou inconnue ».

Elle n’est pas équivalente à :

0
''
FALSE

Tester NULL

N’écrivez pas :

WHERE colonne = NULL

Utilisez :

WHERE colonne IS NULL

ou :

WHERE colonne IS NOT NULL

La logique à trois valeurs de SQL est l’un des premiers endroits où la phrase :

« Pourtant c’est évident. »

commence à perdre de son autorité.

Lire les données avec SELECT

La requête fondamentale :

SELECT * FROM produits;

renvoie toutes les colonnes des lignes sélectionnées.

Le :

*

signifie :

« toutes les colonnes ».

Préférez souvent les colonnes explicites

Dans du code applicatif, ceci :

SELECT id, nom, prix
FROM produits;

est souvent préférable à :

SELECT *
FROM produits;

Vous indiquez précisément :

  • les données nécessaires ;
  • leur ordre ;
  • le contrat attendu par l’application.

Et vous évitez de transférer quinze colonnes simplement parce qu’une table en possédait trois lorsque le développeur écrivit la requête.

WHERE : filtrer les lignes

SELECT id, nom, prix
FROM produits
WHERE en_stock = TRUE;

Pour un prix :

SELECT nom, prix
FROM produits
WHERE prix < 50;

Pour une plage :

SELECT nom, prix
FROM produits
WHERE prix BETWEEN 20 AND 50;

Combiner les conditions

Avec :

AND

les deux conditions doivent être vraies :

SELECT nom, prix
FROM produits
WHERE en_stock = TRUE
  AND prix < 50;

Avec :

OR

une des conditions suffit :

SELECT nom, prix
FROM produits
WHERE prix < 20
   OR prix > 100;

Utilisez des parenthèses lorsque la logique devient ambiguë

Par exemple :

SELECT *
FROM produits
WHERE en_stock = TRUE
  AND (prix < 20 OR prix > 100);

Les parenthèses coûtent très peu.

Les erreurs de logique métier généralement davantage.

IN

Au lieu de :

WHERE id = 1
   OR id = 4
   OR id = 7

utilisez :

WHERE id IN (1, 4, 7)

LIKE

Pour rechercher un motif :

SELECT *
FROM produits
WHERE nom LIKE 'Lampe%';

Le :

%

représente une séquence de caractères.

Exemple :

%lampe%

cherche le motif quelque part dans la valeur, selon la collation utilisée.

Attention à LIKE ‘%mot%’

Une recherche commençant par un joker :

LIKE '%lampe%'

empêche souvent l’utilisation efficace d’un index B-tree classique pour localiser directement le début de la valeur.

Sur dix produits, personne ne s’en soucie.

Sur cent millions de lignes, le serveur commence à développer une opinion.

ORDER BY

Pour trier :

SELECT id, nom, prix
FROM produits
ORDER BY prix ASC;

ASC signifie croissant.

Pour décroissant :

SELECT id, nom, prix
FROM produits
ORDER BY prix DESC;

Trier sur plusieurs colonnes

SELECT nom, prix, en_stock
FROM produits
ORDER BY en_stock DESC, prix ASC;

Le serveur trie d’abord selon :

en_stock

puis utilise :

prix

pour départager les lignes correspondantes.

LIMIT

Pour limiter le nombre de résultats :

SELECT id, nom, prix
FROM produits
ORDER BY prix DESC
LIMIT 10;

Très utile lorsque la table contient :

48 000 000 lignes

et que votre objectif était simplement de vérifier si trois produits existent.

LIMIT avec décalage

SELECT id, nom
FROM produits
ORDER BY id
LIMIT 20 OFFSET 40;

Cette requête saute les quarante premières lignes du résultat ordonné puis en renvoie vingt.

Cette méthode est courante pour la pagination simple.

Sur de très grands offsets, d’autres stratégies peuvent devenir plus efficaces.

DISTINCT

Pour obtenir les valeurs différentes :

SELECT DISTINCT en_stock
FROM produits;

Sur une table booléenne, le résultat ne sera probablement pas le suspense de l’année.

Mais le principe est utile.

Compter les lignes

SELECT COUNT(*)
FROM produits;

Avec une condition :

SELECT COUNT(*)
FROM produits
WHERE en_stock = TRUE;

Fonctions d’agrégation

SQL fournit notamment :

COUNT()
SUM()
AVG()
MIN()
MAX()

Par exemple :

SELECT
    COUNT(*) AS nombre_produits,
    MIN(prix) AS prix_minimum,
    MAX(prix) AS prix_maximum,
    AVG(prix) AS prix_moyen
FROM produits;

Les alias avec AS

Le :

AS

permet de donner un nom plus lisible au résultat :

SELECT COUNT(*) AS total
FROM produits;

GROUP BY

Imaginons une colonne :

categorie_id

On peut compter les produits par catégorie :

SELECT categorie_id, COUNT(*) AS total
FROM produits
GROUP BY categorie_id;

HAVING

WHERE filtre généralement les lignes avant l’agrégation.

HAVING permet de filtrer les groupes produits.

Par exemple :

SELECT categorie_id, COUNT(*) AS total
FROM produits
GROUP BY categorie_id
HAVING COUNT(*) > 10;

Mettre à jour des données

Exemple :

UPDATE produits
SET prix = 39.99
WHERE id = 1;

Le produit numéro 1 reçoit le nouveau prix.

Préférez une clé unique au nom lorsque c’est possible

L’original :

UPDATE produits
SET prix = 39.99
WHERE nom = 'Couteau suisse';

fonctionne.

Mais que se passe-t-il si trois produits portent le même nom ?

Ils sont tous mis à jour.

Si vous voulez précisément une ligne, utilisez une condition réellement unique :

WHERE id = 1

UPDATE sans WHERE

Cette requête :

UPDATE produits
SET prix = 39.99;

est parfaitement valide.

Elle applique :

39.99

à tous les produits.

Le serveur n’interprète pas l’absence de WHERE comme :

« L’administrateur a probablement oublié quelque chose. »

Il l’interprète comme :

« Toutes les lignes. Très bien. »

Prévisualisez les lignes avant un UPDATE

Avant :

UPDATE produits
SET prix = prix * 0.90
WHERE categorie_id = 4;

commencez par :

SELECT id, nom, prix
FROM produits
WHERE categorie_id = 4;

Si le résultat correspond exactement à ce que vous souhaitez modifier, la suite devient déjà plus sereine.

Supprimer des lignes

DELETE FROM produits
WHERE en_stock = FALSE;

Cette instruction supprime les lignes correspondant à la condition.

DELETE sans WHERE

Cette requête :

DELETE FROM produits;

supprime toutes les lignes de la table.

Elle ne supprime pas nécessairement la table elle-même.

Sa structure reste présente.

Prévisualiser avant DELETE

Avant :

DELETE FROM produits
WHERE en_stock = FALSE;

faites :

SELECT *
FROM produits
WHERE en_stock = FALSE;

Le conseil paraît excessivement prudent jusqu’au jour où la clause devait être :

WHERE client_id = 4281

mais devint :

WHERE client_id <> 4281

Le symbole était petit.

La sauvegarde beaucoup plus volumineuse.

DELETE, TRUNCATE et DROP : trois niveaux différents

Commande Effet général
DELETE FROM produits WHERE ... Supprime certaines lignes
DELETE FROM produits Supprime toutes les lignes via DELETE
TRUNCATE TABLE produits Vide rapidement la table selon les règles du SGBD
DROP TABLE produits Supprime la table elle-même

TRUNCATE n’est pas simplement un DELETE plus rapide

TRUNCATE TABLE appartient au domaine DDL dans MySQL/MariaDB.

Il possède donc des comportements différents concernant notamment :

  • transactions ;
  • réinitialisation de certaines informations internes ;
  • contraintes ;
  • triggers selon le SGBD.

Ne remplacez pas automatiquement :

DELETE FROM table;

par :

TRUNCATE TABLE table;

uniquement parce que le second semble plus énergique.

Modifier une table avec ALTER TABLE

Ajouter une colonne :

ALTER TABLE produits
ADD COLUMN description TEXT NULL;

Ajouter une colonne obligatoire avec valeur par défaut :

ALTER TABLE produits
ADD COLUMN actif BOOLEAN NOT NULL DEFAULT TRUE;

Supprimer une colonne

ALTER TABLE produits
DROP COLUMN description;

Cette opération détruit les données contenues dans cette colonne.

Ce n’est donc pas le meilleur endroit pour vérifier si le nom de colonne était correctement orthographié.

Renommer une table

RENAME TABLE produits TO catalogue_produits;

La syntaxe exacte et les possibilités peuvent varier selon les versions et systèmes.

Clés étrangères : relier les tables

Le mot :

relationnelle

prend tout son intérêt lorsque les tables se référencent.

Créons des catégories :

CREATE TABLE categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_categories_nom (nom)
);

Puis une table produits :

CREATE TABLE produits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    categorie_id BIGINT UNSIGNED NULL,
    nom VARCHAR(100) NOT NULL,
    prix DECIMAL(10,2) NOT NULL,
    en_stock BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY (id),
    CONSTRAINT fk_produits_categories
        FOREIGN KEY (categorie_id)
        REFERENCES categories(id)
);

À quoi sert la clé étrangère ?

Elle exprime que :

produits.categorie_id

doit faire référence à :

categories.id

lorsque la valeur n’est pas NULL.

Elle contribue ainsi à l’intégrité référentielle.

Sans elle, rien n’empêcherait l’application d’enregistrer :

categorie_id = 8472

alors que la catégorie 8472 n’existe absolument nulle part.

JOIN : réunir les données

Supposons :

categories
-----------
1 | Outils
2 | Éclairage

produits
--------
1 | 1 | Couteau suisse
2 | 2 | Lampe frontale

Une jointure :

SELECT
    p.id,
    p.nom,
    p.prix,
    c.nom AS categorie
FROM produits AS p
INNER JOIN categories AS c
    ON c.id = p.categorie_id;

permet d’obtenir :

1 | Couteau suisse | 49.99 | Outils
2 | Lampe frontale | 29.90 | Éclairage

INNER JOIN

INNER JOIN conserve les lignes pour lesquelles une correspondance répond à la condition de jointure.

LEFT JOIN

Pour garder tous les produits, même ceux sans catégorie :

SELECT
    p.nom,
    c.nom AS categorie
FROM produits AS p
LEFT JOIN categories AS c
    ON c.id = p.categorie_id;

La catégorie sera alors :

NULL

si aucune correspondance n’existe.

Les alias de tables

Dans :

FROM produits AS p
JOIN categories AS c

p et c sont des alias.

Ils permettent d’écrire :

p.nom
c.nom

plutôt que :

produits.nom
categories.nom

Très utile lorsque :

  • plusieurs tables possèdent des colonnes du même nom ;
  • la requête contient plusieurs jointures ;
  • le développeur souhaite conserver un peu de cartilage dans ses doigts.

Les index

Un index permet au moteur de retrouver certaines données plus efficacement.

Par exemple :

CREATE INDEX idx_produits_nom
ON produits(nom);

Le moteur peut alors utiliser cet index pour certaines requêtes.

N’ajoutez pas un index à chaque colonne utilisée dans WHERE

La règle :

« Toute colonne utilisée dans WHERE ou JOIN doit avoir un index. »

est trop simpliste.

Un index :

  • consomme de l’espace ;
  • doit être maintenu lors des INSERT ;
  • doit être maintenu lors de certains UPDATE ;
  • peut être inutile si la sélectivité est faible ;
  • peut être redondant avec un autre index ;
  • peut ne jamais être choisi par l’optimiseur.

L’indexation doit correspondre aux requêtes réelles.

Index composite

On peut indexer plusieurs colonnes :

CREATE INDEX idx_produits_stock_prix
ON produits(en_stock, prix);

Cet index peut être intéressant pour des requêtes comme :

SELECT id, nom, prix
FROM produits
WHERE en_stock = TRUE
ORDER BY prix;

Mais l’ordre des colonnes dans un index composite est important.

Un index :

(en_stock, prix)

n’est pas nécessairement équivalent à :

(prix, en_stock)

UNIQUE

Un index unique impose l’unicité selon les règles du moteur.

Par exemple :

CREATE TABLE clients (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    nom VARCHAR(150) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_clients_email (email)
);

Deux clients ne pourront alors pas recevoir la même adresse e-mail selon cette contrainte et sa collation.

Une contrainte vaut mieux qu’une promesse applicative

On entend parfois :

« Notre application vérifie déjà qu’il n’y a pas de doublon. »

Très bien.

Une contrainte :

UNIQUE

permet également au serveur de défendre cette règle lorsque :

  • deux requêtes arrivent simultanément ;
  • un script d’administration contourne l’application ;
  • une nouvelle application accède à la même base.

Les règles critiques gagnent généralement à être exprimées le plus près possible des données.

EXPLAIN : demander au serveur son plan

Pour analyser une requête :

EXPLAIN
SELECT id, nom, prix
FROM produits
WHERE en_stock = TRUE
ORDER BY prix;

Le résultat fournit des informations sur la stratégie envisagée par l’optimiseur.

Selon le système et la version, on peut observer notamment :

  • les tables utilisées ;
  • les index possibles ;
  • l’index choisi ;
  • le nombre estimé de lignes ;
  • le type d’accès.

Lorsque quelqu’un affirme :

« J’ai ajouté un index, donc forcément ça va plus vite. »

EXPLAIN offre une manière relativement diplomatique de demander confirmation au serveur.

Les transactions

Une transaction permet de regrouper plusieurs opérations pour les traiter comme une unité logique.

Exemple classique :

START TRANSACTION;

UPDATE comptes
SET solde = solde - 100.00
WHERE id = 1;

UPDATE comptes
SET solde = solde + 100.00
WHERE id = 2;

COMMIT;

Nous transférons 100 entre deux comptes.

Il est préférable que :

débit du compte A

et :

crédit du compte B

soient traités ensemble.

Sinon nous venons d’inventer une banque possédant un modèle économique inhabituellement simple.

ROLLBACK

Si quelque chose ne va pas avant le commit :

ROLLBACK;

annule les modifications transactionnelles de la transaction courante lorsque le moteur et les opérations concernées permettent ce rollback.

Exemple prudent avec contrôle

START TRANSACTION;

UPDATE produits
SET prix = prix * 0.90
WHERE categorie_id = 4;

SELECT id, nom, prix
FROM produits
WHERE categorie_id = 4;

Si le résultat est correct :

COMMIT;

Sinon :

ROLLBACK;

Cette méthode est particulièrement confortable pendant une opération manuelle sensible.

Autocommit

MySQL et MariaDB travaillent généralement avec :

autocommit = 1

par défaut.

Cela signifie qu’en dehors d’une transaction explicitement ouverte, une instruction transactionnelle terminée correctement est normalement validée automatiquement.

Ainsi :

UPDATE produits
SET prix = 0;

n’attend pas nécessairement que vous réalisiez votre erreur avant de devenir permanente.

Voir l’état autocommit

SELECT @@autocommit;

Désactiver autocommit

SET autocommit = 0;

Mais pour une série ponctuelle de commandes, préférez généralement une transaction explicite :

START TRANSACTION;

Elle exprime beaucoup plus clairement l’intention.

Attention : toutes les commandes ne sont pas annulables

C’est une nuance essentielle.

De nombreuses commandes DDL comme :

CREATE DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE TABLE

provoquent un commit implicite dans MySQL/MariaDB selon les règles correspondantes.

Ce scénario :

START TRANSACTION;

DROP TABLE clients;

ROLLBACK;

ne doit donc absolument pas être interprété comme :

« Aucun problème, ROLLBACK va ressusciter la table. »

Ce serait une interprétation extrêmement optimiste de la gestion transactionnelle.

Les moteurs de stockage comptent

Les transactions dépendent aussi du moteur de stockage.

InnoDB est le moteur transactionnel largement utilisé pour les tables MySQL/MariaDB modernes.

Pour voir le moteur d’une table :

SHOW TABLE STATUS LIKE 'produits';

ou :

SHOW CREATE TABLE produits;

Les propriétés ACID

Les transactions relationnelles sont souvent expliquées par :

ACID
Lettre Concept
A Atomicité
C Cohérence
I Isolation
D Durabilité

Atomicité

Une transaction est traitée comme une unité logique : ses changements sont validés ou annulés selon son résultat.

Cohérence

Les règles d’intégrité définies doivent rester respectées entre états valides.

Isolation

Les transactions concurrentes doivent interagir selon un niveau d’isolation défini.

Durabilité

Une fois validés, les changements doivent survivre aux événements couverts par les garanties du système.

Les transactions ne remplacent pas les sauvegardes

ROLLBACK peut sauver :

une opération actuelle non validée

Il ne remonte pas automatiquement :

la table telle qu'elle était mardi dernier à 14 h 07

Pour cela, il faut une vraie stratégie :

  • sauvegardes ;
  • journaux binaires selon architecture ;
  • réplication ;
  • restauration testée ;
  • éventuellement point-in-time recovery.

Créer un utilisateur SQL

Exemple :

CREATE USER 'paul'@'localhost'
IDENTIFIED BY 'UnePhraseSecreteLongueEtUnique';

L’identité possède deux parties :

'utilisateur'@'hote'

Ainsi :

'paul'@'localhost'

et :

'paul'@'192.168.10.%'

peuvent représenter des comptes différents dans la logique du serveur.

localhost est important

Avec :

'paul'@'localhost'

vous autorisez le compte correspondant à se connecter selon la correspondance d’hôte locale prévue.

Ce n’est pas simplement un commentaire décoratif ajouté après le nom.

La partie hôte participe à l’identité du compte.

Évitez ‘%’ lorsqu’il n’est pas nécessaire

Une définition comme :

'paul'@'%'

peut permettre une correspondance beaucoup plus large.

Préférez une origine limitée lorsque l’architecture le permet.

Par exemple :

'application'@'192.168.10.%'

ou mieux encore une architecture réseau et une méthode d’authentification adaptées au contexte réel.

Accorder des privilèges

L’exemple original :

GRANT ALL PRIVILEGES
ON boutique.*
TO 'paul'@'localhost';

fonctionne mais accorde énormément de droits dans cette base.

Le principe de sécurité préférable est :

accorder uniquement ce qui est nécessaire.

Exemple pour une application classique

Si une application doit seulement lire et modifier ses données :

GRANT SELECT, INSERT, UPDATE, DELETE
ON boutique.*
TO 'application'@'localhost';

Elle n’obtient pas automatiquement :

DROP
ALTER
CREATE USER
GRANT OPTION

dont elle n’a probablement aucune utilité.

Pourquoi éviter ALL PRIVILEGES par défaut ?

Si l’application est compromise, un compte limité à :

SELECT
INSERT
UPDATE
DELETE

offre moins de possibilités qu’un compte pouvant également :

DROP TABLE
ALTER TABLE
CREATE TABLE

Le moindre privilège ne rend pas une application invulnérable.

Il réduit le nombre de choses qu’elle peut casser lorsqu’elle cesse de l’être.

FLUSH PRIVILEGES n’est normalement pas nécessaire

Après :

CREATE USER ...;
GRANT ...;

vous n’avez normalement pas besoin d’exécuter :

FLUSH PRIVILEGES;

Les instructions de gestion des comptes comme :

CREATE USER
GRANT
REVOKE

mettent directement à jour le système de privilèges.

FLUSH PRIVILEGES sert notamment lorsque les tables de privilèges ont été modifiées directement ou dans certains scénarios administratifs particuliers.

Ne modifiez pas directement les tables système des privilèges

Évitez :

UPDATE mysql.user ...

pour administrer les comptes.

Utilisez les instructions prévues :

CREATE USER
ALTER USER
DROP USER
GRANT
REVOKE

La base système possède déjà suffisamment de responsabilités sans que vous commenciez à pratiquer la chirurgie directement dans ses tables internes.

Afficher les privilèges d’un utilisateur

SHOW GRANTS FOR 'paul'@'localhost';

C’est une commande essentielle avant de conclure :

« Il a forcément tous les droits, j’ai fait GRANT quelque chose l’année dernière. »

Retirer un privilège

REVOKE INSERT
ON boutique.*
FROM 'paul'@'localhost';

Pour plusieurs :

REVOKE INSERT, UPDATE, DELETE
ON boutique.*
FROM 'paul'@'localhost';

Supprimer un utilisateur

DROP USER 'paul'@'localhost';

Cela supprime le compte SQL correspondant.

Cela ne supprime pas automatiquement :

  • les fichiers personnels de Paul ;
  • son compte Linux ;
  • ses contributions Git ;
  • ses souvenirs.

MariaDB reste heureusement assez spécialisé.

Modifier un mot de passe

On peut utiliser :

ALTER USER 'paul'@'localhost'
IDENTIFIED BY 'NouvellePhraseSecreteLongue';

La syntaxe exacte de certaines options d’authentification peut varier davantage entre MySQL et MariaDB.

Pour une administration avancée des comptes, consultez donc la documentation du serveur réellement utilisé.

Les applications ne devraient pas utiliser root

Un site Web ne devrait normalement pas se connecter avec :

root

simplement parce que cela évite les :

Access denied

Créez un compte dédié :

wordpress_prod
erp_app
api_boutique

avec uniquement les privilèges nécessaires.

SQL et injection SQL

Un des risques les plus connus est l’injection SQL.

Code conceptuellement dangereux :

requete = "SELECT * FROM utilisateurs WHERE nom = '" + utilisateur + "'";

Si les données fournies par l’utilisateur sont concaténées directement dans la requête, elles peuvent modifier sa structure SQL.

Utilisez les requêtes préparées

Le bon modèle consiste à séparer :

structure SQL
+
valeurs

Par exemple conceptuellement :

SELECT id, nom
FROM utilisateurs
WHERE email = ?;

puis transmettre la valeur comme paramètre via le pilote de base de données.

N’échappez pas artisanalement les entrées utilisateur lorsque votre bibliothèque sait déjà utiliser des paramètres préparés.

Le compte SQL limité complète les requêtes préparées

Les protections se cumulent :

requêtes préparées
+
validation applicative
+
compte SQL à privilèges limités
+
segmentation réseau
+
sauvegardes

Aucune de ces couches ne remplace les autres.

Dates et heures

Date et heure actuelle :

SELECT NOW();

Date actuelle :

SELECT CURRENT_DATE;

Heure actuelle :

SELECT CURRENT_TIME;

Types DATE, DATETIME et TIMESTAMP

On rencontre notamment :

DATE
DATETIME
TIMESTAMP
TIME

Ils ne sont pas totalement interchangeables.

Le comportement concernant :

  • plage ;
  • fuseau horaire ;
  • conversion ;
  • valeur par défaut ;

dépend du type et du serveur.

Pour un système critique, ne stockez pas toutes les dates dans :

VARCHAR(255)

au motif que :

« Comme ça on peut mettre ce qu’on veut. »

C’est précisément le problème.

Un exemple de table commandes

CREATE TABLE commandes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    client_id BIGINT UNSIGNED NOT NULL,
    statut VARCHAR(30) NOT NULL,
    total DECIMAL(12,2) NOT NULL,
    cree_le DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_commandes_client (client_id),
    INDEX idx_commandes_statut_date (statut, cree_le)
);

Sous-requêtes

SQL permet d’imbriquer des requêtes.

Par exemple :

SELECT nom
FROM produits
WHERE prix > (
    SELECT AVG(prix)
    FROM produits
);

Cette requête récupère les produits dont le prix dépasse la moyenne.

EXISTS

On peut aussi tester l’existence d’une ligne :

SELECT c.id, c.nom
FROM clients AS c
WHERE EXISTS (
    SELECT 1
    FROM commandes AS co
    WHERE co.client_id = c.id
);

Le choix entre :

  • jointure ;
  • sous-requête ;
  • EXISTS ;

dépend de la logique et du plan d’exécution.

CREATE VIEW

Une vue permet d’enregistrer une requête comme objet logique.

Par exemple :

CREATE VIEW produits_disponibles AS
SELECT id, nom, prix
FROM produits
WHERE en_stock = TRUE;

Puis :

SELECT *
FROM produits_disponibles;

Une vue n’est pas nécessairement une copie physique des données.

Elle représente généralement une requête enregistrée dont le comportement dépend du SGBD et de sa définition.

Transactions et verrous

Lorsque plusieurs utilisateurs travaillent simultanément, le serveur doit assurer une cohérence suffisante.

Une transaction peut donc acquérir différents verrous selon :

  • les requêtes ;
  • les index ;
  • le niveau d’isolation ;
  • le moteur.

Une transaction laissée ouverte pendant vingt minutes peut ainsi bloquer d’autres opérations.

Le problème n’est parfois pas :

« La base est lente. »

mais :

« Quelqu’un a ouvert une transaction avant d’aller déjeuner. »

Deadlocks

Un deadlock peut apparaître lorsque plusieurs transactions attendent mutuellement des ressources.

Exemple conceptuel :

Transaction A :
verrouille ligne 1
attend ligne 2

Transaction B :
verrouille ligne 2
attend ligne 1

Le moteur peut détecter cette situation et annuler une des transactions.

Une application sérieuse doit donc être capable de gérer certains échecs transactionnels et éventuellement recommencer l’opération.

Les commentaires SQL

On peut écrire notamment :

-- Ceci est un commentaire

SELECT *
FROM produits;

ou :

/* commentaire
   sur plusieurs lignes */

Très pratique pour les scripts.

Un commentaire comme :

-- NE PAS SUPPRIMER, IMPORTANT

est cependant moins utile qu’une explication de ce qui est important et pourquoi.

Scripts SQL

Un fichier :

initialisation.sql

peut contenir :

CREATE DATABASE IF NOT EXISTS boutique;

USE boutique;

CREATE TABLE IF NOT EXISTS produits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    prix DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id)
);

Il peut être exécuté depuis le shell :

mysql -u paul -p boutique < initialisation.sql

ou avec le client MariaDB :

mariadb -u paul -p boutique < initialisation.sql

Attention à la réexécution des scripts

Un script d’installation peut être conçu pour être :

idempotent

c’est-à-dire pouvoir être exécuté plusieurs fois sans produire de dégâts inattendus.

Des clauses comme :

IF EXISTS
IF NOT EXISTS

peuvent aider.

Mais elles ne suffisent pas toujours à garantir une migration correcte.

Sauvegarder avant les opérations destructives

Pour MySQL, on rencontre notamment :

mysqldump

Pour MariaDB :

mariadb-dump

Exemple simple :

mariadb-dump -u sauvegarde -p boutique > boutique.sql

ou selon le client installé :

mysqldump -u sauvegarde -p boutique > boutique.sql

Mais une commande de dump n’est que le début d’une politique de sauvegarde.

Une sauvegarde doit être restaurable

Une stratégie crédible doit répondre à :

  • où sont les sauvegardes ?
  • combien de temps sont-elles conservées ?
  • sont-elles chiffrées ?
  • sont-elles isolées du serveur principal ?
  • peut-on les restaurer ?
  • combien de temps prend la restauration ?
  • quelles données seront perdues entre deux sauvegardes ?

Un fichier :

backup.sql

créé tous les soirs mais jamais restauré depuis quatre ans est une sauvegarde.

Ou une œuvre de fiction.

La distinction mérite un test.

Ne testez pas une restauration sur la base de production

Créez une base dédiée :

boutique_restore_test

et restaurez-y régulièrement les sauvegardes.

Le meilleur moment pour découvrir :

ERROR 1064

dans le dump n’est pas après la perte de la base originale.

Quelques commandes d’inspection utiles

Version du serveur

SELECT VERSION();

Utilisateur présenté

SELECT USER();

Compte réellement utilisé pour les privilèges

SELECT CURRENT_USER();

Ces deux valeurs peuvent différer selon la manière dont l’authentification et les correspondances de comptes sont résolues.

Base actuelle

SELECT DATABASE();

Date et heure

SELECT NOW();

Tables

SHOW TABLES;

Structure

DESCRIBE produits;

Définition complète

SHOW CREATE TABLE produits;

Privilèges

SHOW GRANTS;

Quitter le client

EXIT;

ou :

QUIT;

Vous retournez alors au shell.

La base continue de fonctionner sans vous.

Ce qui peut constituer une expérience d’humilité pour certains DBA.

Cas pratique complet : petite boutique

Créons une base :

CREATE DATABASE boutique
CHARACTER SET utf8mb4;

USE boutique;

Catégories

CREATE TABLE categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_categories_nom (nom)
);

Produits

CREATE TABLE produits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    categorie_id BIGINT UNSIGNED NULL,
    nom VARCHAR(100) NOT NULL,
    prix DECIMAL(10,2) NOT NULL,
    en_stock BOOLEAN NOT NULL DEFAULT TRUE,
    cree_le TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_produits_categorie (categorie_id),
    CONSTRAINT fk_produits_categories
        FOREIGN KEY (categorie_id)
        REFERENCES categories(id)
);

Ajouter les catégories

INSERT INTO categories (nom)
VALUES
    ('Outils'),
    ('Éclairage'),
    ('Orientation');

Ajouter les produits

INSERT INTO produits
    (categorie_id, nom, prix, en_stock)
VALUES
    (1, 'Couteau suisse', 49.99, TRUE),
    (2, 'Lampe frontale', 29.90, TRUE),
    (3, 'Boussole', 14.50, FALSE);

Afficher le catalogue

SELECT
    p.id,
    p.nom,
    c.nom AS categorie,
    p.prix,
    p.en_stock
FROM produits AS p
LEFT JOIN categories AS c
    ON c.id = p.categorie_id
ORDER BY p.prix DESC;

Afficher uniquement les produits disponibles

SELECT
    p.nom,
    p.prix
FROM produits AS p
WHERE p.en_stock = TRUE
ORDER BY p.prix;

Augmenter les prix de 5 %

D’abord vérifier :

SELECT id, nom, prix
FROM produits
WHERE categorie_id = 1;

Puis :

START TRANSACTION;

UPDATE produits
SET prix = ROUND(prix * 1.05, 2)
WHERE categorie_id = 1;

SELECT id, nom, prix
FROM produits
WHERE categorie_id = 1;

Si tout est correct :

COMMIT;

Sinon :

ROLLBACK;

Cas pratique : compte applicatif

Créons un compte spécifique :

CREATE USER 'boutique_app'@'localhost'
IDENTIFIED BY 'UnePhraseSecreteUniqueEtLongue';

Donnons-lui uniquement les droits nécessaires :

GRANT SELECT, INSERT, UPDATE, DELETE
ON boutique.*
TO 'boutique_app'@'localhost';

Vérifions :

SHOW GRANTS FOR 'boutique_app'@'localhost';

Aucun :

FLUSH PRIVILEGES;

n’est nécessaire après ces commandes normales de gestion de comptes.

Cas pratique : utilisateur en lecture seule

CREATE USER 'audit'@'localhost'
IDENTIFIED BY 'AutrePhraseSecreteLongue';

GRANT SELECT
ON boutique.*
TO 'audit'@'localhost';

Le compte pourra consulter les données couvertes par ce privilège sans recevoir automatiquement :

INSERT
UPDATE
DELETE
DROP

Les erreurs SQL classiques

Oublier WHERE sur UPDATE

UPDATE clients
SET actif = FALSE;

Résultat :

vous venez de désactiver tout le monde.

Y compris probablement le directeur qui découvrira avec beaucoup d’intérêt le concept de transaction.

Oublier WHERE sur DELETE

DELETE FROM commandes;

Résultat :

historique commercial minimaliste.

Confondre DELETE et DROP

DELETE FROM produits;

supprime les lignes.

DROP TABLE produits;

supprime la table.

La différence tient en quatre lettres.

L’opération de restauration, parfois beaucoup plus.

Penser que ROLLBACK annule tout

Les commandes DDL peuvent provoquer des commits implicites.

Une transaction n’est pas une machine à remonter le temps universelle.

Utiliser root pour l’application

Une faille applicative devient alors potentiellement une faille disposant de privilèges administratifs sur la base.

Donner ALL PRIVILEGES par habitude

Accordez les droits réellement nécessaires.

Faire FLUSH PRIVILEGES après chaque GRANT

Inutile dans l’administration normale des comptes.

Mettre un index sur chaque colonne

Les index accélèrent certains accès mais ralentissent aussi certaines écritures et consomment de l’espace.

Mesurez.

Stocker les prix en FLOAT

Pour des valeurs monétaires exigeant une représentation décimale exacte, préférez généralement :

DECIMAL

Stocker toutes les dates dans VARCHAR

Vous perdez une grande partie des avantages des types temporels.

Comparer NULL avec =

N’écrivez pas :

WHERE valeur = NULL

mais :

WHERE valeur IS NULL

Construire les requêtes par concaténation

Utilisez des requêtes préparées pour les données fournies par les utilisateurs.

Utiliser SELECT * partout

Pratique pour explorer.

Souvent moins souhaitable dans une application durable.

Lancer une modification sans SELECT préalable

Avant :

UPDATE ... WHERE ...

ou :

DELETE ... WHERE ...

exécutez souvent le même :

WHERE

avec :

SELECT

pour voir exactement ce qui sera touché.

Checklist avant un UPDATE important

  1. Vérifier la base courante avec SELECT DATABASE();
  2. Vérifier la condition avec un SELECT.
  3. Compter les lignes concernées.
  4. Vérifier qu’une sauvegarde adaptée existe.
  5. Utiliser une transaction lorsque l’opération et le moteur le permettent.
  6. Exécuter l’UPDATE.
  7. Contrôler le résultat avant COMMIT.

Exemple

SELECT DATABASE();

SELECT COUNT(*)
FROM produits
WHERE categorie_id = 4;

SELECT id, nom, prix
FROM produits
WHERE categorie_id = 4;

START TRANSACTION;

UPDATE produits
SET prix = prix * 1.05
WHERE categorie_id = 4;

SELECT id, nom, prix
FROM produits
WHERE categorie_id = 4;

COMMIT;

Checklist avant un DELETE

  1. Vérifier la base.
  2. Faire le SELECT correspondant.
  3. Compter les lignes.
  4. Vérifier les dépendances et clés étrangères.
  5. Vérifier la sauvegarde.
  6. Utiliser une transaction lorsque cela est possible.
  7. Supprimer uniquement les lignes prévues.

Les commandes fondamentales à retenir

Commande Rôle
SHOW DATABASES Lister les bases accessibles
CREATE DATABASE Créer une base
DROP DATABASE Supprimer une base
USE Choisir la base courante
SHOW TABLES Lister les tables
CREATE TABLE Créer une table
ALTER TABLE Modifier sa structure
DROP TABLE Supprimer une table
DESCRIBE Afficher sa structure simplifiée
SHOW CREATE TABLE Afficher sa définition détaillée
INSERT Ajouter des lignes
SELECT Lire des données
UPDATE Modifier des lignes
DELETE Supprimer des lignes
TRUNCATE Vider une table selon le mécanisme DDL correspondant
CREATE INDEX Créer un index
EXPLAIN Examiner le plan d’une requête
CREATE USER Créer un compte SQL
GRANT Accorder des privilèges
REVOKE Retirer des privilèges
SHOW GRANTS Afficher les privilèges
DROP USER Supprimer un compte SQL
START TRANSACTION Commencer une transaction explicite
COMMIT Valider les modifications transactionnelles
ROLLBACK Annuler les modifications transactionnelles non validées

Les clauses essentielles d’un SELECT

Clause Rôle
FROM Source des données
JOIN Relier plusieurs sources
WHERE Filtrer les lignes
GROUP BY Créer des groupes
HAVING Filtrer les groupes
ORDER BY Trier le résultat
LIMIT Limiter le nombre de lignes renvoyées

Une requête complète

SELECT
    c.nom AS categorie,
    COUNT(*) AS nombre,
    AVG(p.prix) AS prix_moyen
FROM produits AS p
INNER JOIN categories AS c
    ON c.id = p.categorie_id
WHERE p.en_stock = TRUE
GROUP BY c.id, c.nom
HAVING COUNT(*) >= 2
ORDER BY prix_moyen DESC
LIMIT 10;

Cette seule requête contient une grande partie des concepts fondamentaux :

  • projection des colonnes ;
  • jointure ;
  • filtrage ;
  • agrégation ;
  • filtrage des groupes ;
  • tri ;
  • limitation du résultat.

Une méthode saine pour apprendre SQL

Commencez par les opérations de lecture :

SELECT
WHERE
ORDER BY
LIMIT

Puis :

JOIN
GROUP BY
COUNT
SUM
AVG

Ensuite :

INSERT
UPDATE
DELETE

avec transactions.

Puis :

CREATE TABLE
ALTER TABLE
INDEX
FOREIGN KEY

Enfin :

utilisateurs
privilèges
optimisation
transactions concurrentes
sauvegardes

Apprendre :

DROP DATABASE

en premier n’offre pas énormément de possibilités pédagogiques après la première démonstration.

Quelques réflexes professionnels

  • Utilisez des comptes applicatifs dédiés.
  • Appliquez le principe du moindre privilège.
  • Utilisez des types adaptés aux données.
  • Définissez des clés primaires.
  • Utilisez les contraintes pour protéger l’intégrité.
  • Ajoutez des index à partir des besoins réels.
  • Analysez les requêtes avec EXPLAIN.
  • Utilisez des requêtes préparées dans les applications.
  • Prévisualisez UPDATE et DELETE avec SELECT.
  • Utilisez les transactions lorsque le moteur et les instructions le permettent.
  • Ne supposez pas que ROLLBACK annule le DDL.
  • Sauvegardez et testez les restaurations.

Le principe le plus important : savoir combien de lignes vous allez toucher

Avant :

UPDATE
DELETE

une excellente habitude consiste à poser deux questions au serveur :

SELECT COUNT(*)
...

puis :

SELECT ...
...

avec exactement la même condition.

Si vous pensiez toucher :

1 ligne

et que :

COUNT(*)

répond :

3 841 972

vous venez probablement d’économiser une partie non négligeable de votre soirée.

Conclusion : SQL est simple à lire, mais littéral dans ses conséquences

Les bases du SQL tiennent finalement dans quelques verbes :

CREATE
SELECT
INSERT
UPDATE
DELETE
ALTER
DROP
GRANT
REVOKE
COMMIT
ROLLBACK

Mais leur puissance vient de la précision avec laquelle ils peuvent cibler les données.

La différence entre :

DELETE FROM utilisateurs
WHERE id = 42;

et :

DELETE FROM utilisateurs;

tient à :

WHERE id = 42

Trois mots, un opérateur et un nombre.

Potentiellement toute votre base.

Retenez surtout :

  • MySQL et MariaDB sont proches mais désormais distincts ;
  • sur Debian/MariaDB, root peut être authentifié par socket plutôt que par mot de passe ;
  • CREATE USER, GRANT et REVOKE ne nécessitent normalement pas FLUSH PRIVILEGES ;
  • BOOLEAN correspond essentiellement à une représentation entière dans MySQL/MariaDB ;
  • DECIMAL convient généralement mieux aux montants monétaires que FLOAT ;
  • les clés primaires identifient les lignes et les clés étrangères protègent les relations ;
  • les index doivent correspondre aux requêtes réelles ;
  • les transactions sont essentielles mais ne rendent pas toutes les commandes réversibles ;
  • de nombreuses commandes DDL provoquent des commits implicites ;
  • les applications doivent utiliser des comptes limités et des requêtes préparées ;
  • une sauvegarde n’est crédible que lorsqu’on sait la restaurer ;
  • et un UPDATE ou DELETE sans WHERE n’est pas une erreur de syntaxe.

Cette dernière règle mérite d’être répétée.

Le serveur ne vous empêchera pas d’écrire :

DELETE FROM clients;

Il ne vous demandera pas :

« Vous vouliez peut-être ajouter une clause WHERE ? »

Il exécutera la requête.

Parce qu’une base de données possède beaucoup de qualités.

Le doute envers les décisions de son administrateur n’en fait généralement pas partie.

En SQL, la puissance vient de la précision.
Et lorsque la précision manque, la sauvegarde prend le relais.

Post Views: 12