Premiers pas
Se connecter à MySQL
Le client mysql est la CLI standard de MySQL. Utilisez toujours -p (pas d'espace après) pour que le mot de passe soit demandé de façon sécurisée plutôt que visible dans l'historique du shell. -h règle l'hôte, -P (majuscule) le port. \s affiche le statut de la connexion. Utilisez -e pour lancer des requêtes ponctuelles dans les scripts.
# connect to local server (prompt for password)
mysql -u root -p
# connect to a remote host on a custom port
mysql -h 192.168.1.100 -P 3307 -u admin -p
# connect directly to a specific database
mysql -u root -p mydb
# non-interactive: run a query and exit
mysql -u root -p -e "SELECT VERSION();"
# show server status inside the client
\s
SELECT VERSION(), CURRENT_USER, DATABASE();Gestion des bases de données
Utilisez utf8mb4 (pas utf8) pour supporter l'Unicode complet y compris les emoji — le utf8 de MySQL est un sous-ensemble 3 octets incapable de stocker tous les caractères. utf8mb4_unicode_ci est la collation recommandée pour un tri correct. IF EXISTS/IF NOT EXISTS préviennent les erreurs dans les scripts. DROP DATABASE supprime instantanément toutes les tables et données.
# list all databases
SHOW DATABASES;
# create a database with charset and collation
CREATE DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
# switch to a database
USE mydb;
# show the current database
SELECT DATABASE();
# drop a database (irreversible)
DROP DATABASE IF EXISTS mydb;
# alter a database's charset
ALTER DATABASE mydb CHARACTER SET utf8mb4;Afficher et décrire les objets
Les commandes SHOW sont les outils d'introspection spécifiques à MySQL. DESCRIBE donne un aperçu rapide des colonnes ; SHOW CREATE TABLE donne le DDL complet réutilisable. Ajoutez \G au lieu de ; pour une sortie verticale — bien plus lisible pour des lignes larges comme SHOW CREATE TABLE.
# list tables in the current database
SHOW TABLES;
# show table structure (columns, types, keys)
DESCRIBE users;
# equivalent
SHOW COLUMNS FROM users;
# show create statement for a table
SHOW CREATE TABLE users\G
# list indexes on a table
SHOW INDEX FROM users;
# show stored procedures / functions
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW FUNCTION STATUS WHERE Db = 'mydb';
# list triggers
SHOW TRIGGERS\GStatut serveur et variables
Les variables MySQL ont des portées SESSION (connexion courante) et GLOBAL. SET GLOBAL modifie le serveur en cours mais se réinitialise au redémarrage — persistez plutôt les réglages dans my.cnf. @@var lit les valeurs. SHOW STATUS expose les compteurs d'exécution (connexions, uptime, débit) utiles pour le monitoring.
# view a system variable
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'version%';
# view session vs global variable
SELECT @@session.sql_mode, @@global.sql_mode;
# set a variable for the current session
SET sql_mode = 'STRICT_TRANS_TABLES';
SET @@session.foreign_key_checks = 0;
# set globally (needs SUPER/privilege, persists until restart)
SET GLOBAL max_connections = 200;
# show server status counters
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Uptime';Commentaires et bases des instructions
MySQL supporte trois styles de commentaires : -- (exige une espace finale), blocs /* */, et # jusqu'à la fin de ligne. /*! ... */ est un commentaire exécutable spécial — le code dedans ne tourne que sur MySQL et est ignoré par les autres moteurs SQL, utile pour des fichiers de schéma portables. \G exécute et affiche les résultats verticalement.
-- single-line comment (note the space after --)
SELECT 1; -- inline comment
/* multi-line
comment block */
SELECT 2;
# hash-style comment (MySQL-specific, to end of line)
SELECT 3;
# MySQL executable comments: run only on MySQL
SELECT 1 /*!50100 , 2 */; /* the part runs on MySQL >= 5.1 */
# statements end with semicolon; \G ends and prints vertically
SELECT * FROM users\GFichier de configuration (my.cnf)
my.cnf (Linux) / my.ini (Windows) contient les réglages persistants du serveur et du client organisés en sections : [mysqld] pour le serveur, [client]/[mysql] pour le client. innodb_buffer_pool_size est le levier de réglage le plus important pour InnoDB (typiquement 50-75 % de la RAM). Après édition, redémarrez le serveur. Vérifiez avec mysql --help ou SHOW VARIABLES.
# /etc/my.cnf or ~/.my.cnf (Linux/macOS)
# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini (Windows)
[mysqld]
port = 3306
datadir = /var/lib/mysql
max_connections = 200
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_buffer_pool_size = 2G
slow_query_log = 1
long_query_time = 2
[client]
default-character-set = utf8mb4
[mysql]
prompt = \u@\h [\d]>\_Opérations DDL
Créer une table
CREATE TABLE définit colonnes, types, contraintes et options de table. InnoDB est le moteur par défaut et recommandé (transactions, verrouillage de lignes, clés étrangères). BIGINT UNSIGNED pour AUTO_INCREMENT évite le débordement. DECIMAL(p,s) est exact pour l'argent. ENUM valide contre une liste fixe. TIMESTAMP DEFAULT CURRENT_TIMESTAMP se remplit automatiquement à l'insertion.
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
birth_date DATE NULL,
status ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Modifier une table (ALTER)
ALTER TABLE fait évoluer le schéma sans recréer les données. ADD COLUMN avec AFTER la place à une position précise (l'ordre des colonnes est cosmétique dans MySQL). MODIFY change type/défaut ; RENAME COLUMN (8.0+) est plus propre que CHANGE. Les grands ALTER peuvent reconstruire et verrouiller la table — utilisez pt-online-schema-change ou le DDL en ligne pour les grandes tables. RENAME TABLE est atomique.
# add a column
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) NULL AFTER email;
# add multiple columns
ALTER TABLE users
ADD COLUMN first_name VARCHAR(50) NULL,
ADD COLUMN last_name VARCHAR(50) NULL;
# modify a column type
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(30) NOT NULL;
# rename a column (MySQL 8+ preserves data)
ALTER TABLE users
RENAME COLUMN phone TO phone_number;
# rename a table
RENAME TABLE users TO members;
ALTER TABLE users RENAME TO members;DROP et TRUNCATE
DROP TABLE supprime la table entièrement ; TRUNCATE la vide mais garde la structure et réinitialise AUTO_INCREMENT à sa valeur de départ. TRUNCATE est plus rapide que DELETE car il saute la suppression et le logging ligne par ligne, mais c'est du DDL (pas de rollback, pas de triggers). Désactivez foreign_key_checks quand vous tronquez des tables référencées par d'autres.
# drop a table (removes structure and data)
DROP TABLE IF EXISTS old_logs;
# drop multiple tables
DROP TABLE IF EXISTS temp1, temp2;
# truncate: empty the table, keep structure, reset AUTO_INCREMENT
TRUNCATE TABLE session_data;
# truncate cannot be rolled back in some engines
# (InnoDB: TRUNCATE is DDL, implicitly commits)
SET foreign_key_checks = 0;
TRUNCATE TABLE child_table;
SET foreign_key_checks = 1;AUTO_INCREMENT
AUTO_INCREMENT génère des numéros séquentiels pour une clé primaire — une table n'en a qu'un seul possible. LAST_INSERT_ID() retourne l'id généré par le dernier INSERT de la session courante (par connexion, sûr en concurrence). Des trous peuvent apparaître après des suppressions, rollbacks ou inserts avec valeurs explicites. Pour réinitialiser, utilisez ALTER TABLE ... AUTO_INCREMENT = n.
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
amount DECIMAL(10,2)
);
# set the next auto-increment value
ALTER TABLE orders AUTO_INCREMENT = 1000;
# insert without specifying id
INSERT INTO orders (amount) VALUES (99.50);
# get the last inserted id
SELECT LAST_INSERT_ID();
# show current AUTO_INCREMENT value
SHOW TABLE STATUS LIKE 'orders'\GContraintes de table
Les contraintes garantissent l'intégrité des données au niveau de la base. InnoDB supporte clés primaires, UNIQUE, NOT NULL, CHECK (appliquée depuis 8.0.16) et clés étrangères. FOREIGN KEY ... ON DELETE CASCADE supprime les lignes enfants quand le parent est supprimé ; SET NULL les annule ; RESTRICT/NO ACTION bloquent la suppression. Nommez les contraintes explicitement pour une gestion plus simple. Les clés étrangères exigent des index sur les deux colonnes.
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
# unique constraint
CONSTRAINT uq_order_no UNIQUE (user_id, status),
# check constraint (MySQL 8.0.16+ enforced)
CONSTRAINT chk_total CHECK (total >= 0),
# foreign key with cascading actions
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE RESTRICT
) ENGINE=InnoDB;
# add a constraint later
ALTER TABLE orders
ADD CONSTRAINT chk_status CHECK (status IN ('paid','shipped','cancelled'));Tables temporaires
Les tables TEMPORARY n'existent que pour la session courante et sont supprimées automatiquement à sa fermeture. Elles peuvent masquer une vraie table du même nom, utile pour un refactoring sûr ou des données de staging. Deux sessions peuvent créer des tables temporaires du même nom sans conflit. Elles sont invisibles aux autres connexions et ne sont pas écrites dans le binlog par défaut.
# session-scoped, auto-dropped on disconnect
CREATE TEMPORARY TABLE tmp_active
SELECT id, username FROM users WHERE status = 'active';
# identical name shadowing a real table
CREATE TEMPORARY TABLE users
(id INT, name VARCHAR(50));
# only this session sees the temp table
SELECT * FROM users;
# explicit cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_active;Types de données
Types numériques
Utilisez DECIMAL pour les valeurs monétaires où l'exactitude compte — FLOAT/DOUBLE sont approximatifs et accumulent des erreurs d'arrondi. UNSIGNED double la plage positive mais interdit les négatifs. La largeur d'affichage (ex. INT(11)) est dépréciée en MySQL 8.0 et ignorée — elle n'a jamais limité la plage stockée. BIGINT UNSIGNED est recommandé pour AUTO_INCREMENT pour éviter le débordement. SERIAL est un raccourci pratique pour une clé de substitution.
# integers (with optional UNSIGNED)
TINYINT -- 1 byte, -128..127 or 0..255 (UNSIGNED)
SMALLINT -- 2 bytes
MEDIUMINT -- 3 bytes
INT -- 4 bytes
BIGINT -- 8 bytes (use for large AUTO_INCREMENT)
# fixed-point (exact, for money)
DECIMAL(10,2) -- 10 digits total, 2 after decimal
NUMERIC(8,4)
# floating-point (approximate)
FLOAT -- 4 bytes
DOUBLE -- 8 bytes
# other
BIT(8) -- bit-field
SERIAL -- alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUETypes chaînes et texte
VARCHAR est presque toujours préféré à CHAR (qui comble avec des espaces), sauf pour des codes courts de longueur fixe. Avec utf8mb4, chaque caractère peut prendre jusqu'à 4 octets — une colonne VARCHAR(255) peut nécessiter jusqu'à 1020 octets plus la longueur, ce qui compte pour les limites de clé d'index (3072 octets dans InnoDB). Les familles TEXT/BLOB stockent de grandes données hors page ; elles ne peuvent pas avoir de valeur par défaut et leur indexation exige une longueur de préfixe.
# fixed vs variable length
CHAR(10) -- fixed 10 chars, padded with spaces
VARCHAR(255) -- variable, up to 255 chars (stores actual length)
# large text
TINYTEXT -- up to 255 bytes
TEXT -- up to 64 KB
MEDIUMTEXT -- up to 16 MB
LONGTEXT -- up to 4 GB
# binary counterparts
BINARY(16), VARBINARY(255), BLOB, MEDIUMBLOB, LONGBLOB
# national character set
NATIONAL VARCHAR(100) -- same as VARCHAR with utf8mb4
# VARCHAR with charset counts characters, not bytes
VARCHAR(100) CHARACTER SET utf8mb4Types date et heure
DATETIME stocke une date/heure littérale sans notion de fuseau ; TIMESTAMP stocke en UTC et convertit vers le time_zone de la session à l'affichage, mieux pour les apps multi-fuseaux — mais il a une limite 2038 sur les builds 32 bits. Utilisez DATETIME(6)/TIMESTAMP(6) pour la précision microseconde. CURRENT_TIMESTAMP marche comme défaut pour les deux depuis MySQL 5.6.5. Réglez le time_zone de session par connexion avec SET time_zone = '+08:00';.
DATE -- 'YYYY-MM-DD', range 1000-01-01 .. 9999-12-31
TIME -- 'HH:MM:SS', can be negative for elapsed time
DATETIME -- 'YYYY-MM-DD HH:MM:SS', 8 bytes, no timezone
TIMESTAMP -- stored as UTC, converted to session timezone, 4 bytes
YEAR -- YEAR(4) e.g. 2024
# DATETIME with fractional seconds and auto-default
CREATE TABLE events (
ts DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
ON UPDATE CURRENT_TIMESTAMP(3)
);
# TIMESTAMP range: 1970-01-01 .. 2038-01-19 (32-bit limit)
# MySQL 8.0.28+ extends TIMESTAMP to 24 bits of rangeTypes ENUM et SET
ENUM stocke de façon compacte (1-2 octets) une valeur parmi une liste fixe et rejette les valeurs inconnues en mode strict. SET stocke toute combinaison de 64 membres au maximum en bitmask. Les deux rendent les changements de schéma coûteux (ajouter/réordonner des membres exige ALTER TABLE) et sont maladroits à interroger — beaucoup d'équipes préfèrent une table de référence ou VARCHAR avec CHECK. FIND_IN_SET aide à interroger les colonnes SET.
# ENUM: exactly one value from a list
CREATE TABLE shirts (
size ENUM('S','M','L','XL') NOT NULL DEFAULT 'M',
color ENUM('red','green','blue') NULL
);
# stored as integer index (1-based), space-efficient
INSERT INTO shirts (size) VALUES ('L'), ('XL');
# SET: zero or more values from a list (bit flags)
CREATE TABLE posts (
tags SET('news','tech','sports','fun') NOT NULL DEFAULT ''
);
INSERT INTO posts (tags) VALUES ('tech,fun');
# query SET members with FIND_IN_SET
SELECT * FROM posts WHERE FIND_IN_SET('tech', tags);Type JSON
Le type JSON (MySQL 5.7.8+) stocke le JSON en format binaire validé à l'écriture avec accès rapide aux membres. Préférable à un JSON dans une colonne TEXT car vous pouvez indexer et interroger à l'intérieur. Utilisez JSON_OBJECT(), JSON_ARRAY() et JSON_MERGE_PATCH() pour construire les valeurs. Pour interroger fréquemment quelques clés, extrayez-les dans des colonnes générées et indexez-les pour la meilleure performance.
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attrs JSON NOT NULL
);
# insert JSON literals
INSERT INTO products (id, name, attrs) VALUES
(1, 'Widget', '{"color":"red","size":42,"tags":["new","sale"]}'),
(2, 'Gadget', '{"color":"blue","in_stock":true}');
# JSON values are validated on insert and stored binary
SELECT attrs FROM products WHERE id = 1;
# pretty-print JSON
SELECT JSON_PRETTY(attrs) FROM products WHERE id = 1;Types BLOB et binaires
BLOB stocke des données binaires arbitraires (images, fichiers) mais est maladroité à interroger et ne peut pas avoir de défaut. Pour les gros fichiers, préférez stocker un chemin en base et le fichier sur disque/object storage. Stockez les UUID en BINARY(16) (16 octets) plutôt qu'en CHAR(36) (36 octets) pour l'efficacité d'index — MySQL 8.0 ajoute UUID_TO_BIN()/BIN_TO_UUID() avec swap optionnel pour la friendliness d'index.
# binary large objects
TINYBLOB -- up to 255 bytes
BLOB -- up to 64 KB
MEDIUMBLOB -- up to 16 MB
LONGBLOB -- up to 4 GB
# fixed-size binary (e.g. hashes, UUIDs)
BINARY(16) -- fixed 16 bytes, right-padded with \0
VARBINARY(255)
# store a UUID as binary for compactness
CREATE TABLE sessions (
id BINARY(16) PRIMARY KEY,
data VARBINARY(1000)
);
INSERT INTO sessions (id, data) VALUES (UUID_TO_BIN(UUID()), '...');
# retrieve as text
SELECT BIN_TO_UUID(id) FROM sessions;DML (INSERT / UPDATE / DELETE)
INSERT
L'INSERT multi-lignes est bien plus rapide que des inserts mono-ligne répétés car il amortit parsing, aller-retours réseau et mises à jour d'index. INSERT ... SELECT copie des données entre tables. La syntaxe SET est spécifique à MySQL et pratique pour une ligne. Listez toujours les colonnes cibles explicitement pour que l'instruction survive aux changements de schéma. Utilisez INSERT IGNORE ou ON DUPLICATE KEY UPDATE pour des inserts idempotents.
# single row
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');
# multiple rows in one statement
INSERT INTO users (username, email) VALUES
('bob', '[email protected]'),
('carol', '[email protected]'),
('dave', '[email protected]');
# insert from a SELECT
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE status = 'inactive';
# insert with column-order-free syntax
INSERT INTO users SET username='eve', email='[email protected]';INSERT ... ON DUPLICATE KEY UPDATE (upsert)
ON DUPLICATE KEY UPDATE implémente l'upsert — en cas de conflit de UNIQUE/PRIMARY KEY, MySQL met à jour la ligne existante au lieu d'échouer. VALUES(col) désigne la valeur qui aurait été insérée ; MySQL 8.0.19+ déprécie cela au profit des alias de ligne (AS new). Plus efficace qu'un SELECT-then-INSERT/UPDATE séparé car c'est atomique. INSERT IGNORE saute silencieusement les conflits à la place.
CREATE TABLE counters (
name VARCHAR(50) PRIMARY KEY,
hits INT NOT NULL DEFAULT 0
);
# upsert: insert or update on key conflict
INSERT INTO counters (name, hits)
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;
# reference the proposed values with VALUES()
INSERT INTO counters (name, hits) VALUES ('about', 5)
ON DUPLICATE KEY UPDATE hits = VALUES(hits) + 1;
# MySQL 8.0.20+: use an alias for the row being inserted
INSERT INTO counters (name, hits) VALUES ('home', 1) AS new
ON DUPLICATE KEY UPDATE hits = counters.hits + new.hits;UPDATE
Incluez toujours un WHERE — un UPDATE sans clause modifie chaque ligne. MySQL supporte l'UPDATE multi-tables avec JOIN, utile pour dénormaliser des valeurs calculées. ORDER BY + LIMIT permet des mises à jour par lots sûres qui évitent les longs verrous sur les tables énormes. En mode safe-updates, le client refuse UPDATE/DELETE sans clé dans le WHERE — une garde contre les changements de masse accidentels.
# basic update with WHERE (always include one!)
UPDATE users
SET status = 'banned', email = NULL
WHERE id = 42;
# update with expression
UPDATE products
SET price = price * 1.10
WHERE category = 'electronics';
# multi-table update with JOIN
UPDATE users u
JOIN orders o ON o.user_id = u.id
SET u.total_spent = u.total_spent + o.amount
WHERE o.paid = 1;
# limit and order (useful for batched updates)
UPDATE logs SET archived = 1
WHERE archived = 0
ORDER BY created_at ASC
LIMIT 1000;DELETE
DELETE supprime les lignes une à une, est transactionnel (annulable) et déclenche les triggers. Le DELETE multi-tables peut supprimer de plusieurs tables en une instruction. Pour vider une table entière, TRUNCATE est bien plus rapide et réinitialise AUTO_INCREMENT, mais n'est pas transactionnel. Le modificateur QUICK (MyISAM) saute la mise à jour des feuilles d'index — rarement nécessaire avec InnoDB. Utilisez toujours un WHERE.
# delete specific rows
DELETE FROM users WHERE status = 'banned' AND last_login < '2020-01-01';
# delete with LIMIT (batched)
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 5000;
# multi-table delete with JOIN
DELETE u, o
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 99;
# delete all rows (keeps table & AUTO_INCREMENT, unlike TRUNCATE)
DELETE FROM temp_data;
# quick delete that doesn't return row count
DELETE QUICK FROM logs WHERE year = 2020;REPLACE
REPLACE est spécifique à MySQL : si une ligne avec la même clé PK/UNIQUE existe, elle est supprimée et une nouvelle insérée ; sinon il insère simplement. Plus simple que l'upsert mais avec effets de bord — il déclenche les triggers DELETE puis INSERT (pas UPDATE), réinitialise AUTO_INCREMENT et remet les colonnes non fournies à leur défaut. Préférez INSERT ... ON DUPLICATE KEY UPDATE dans la plupart des cas.
# REPLACE deletes then inserts on key conflict
REPLACE INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]');
# REPLACE ... SET form
REPLACE INTO users SET id = 1, username = 'alice', email = '[email protected]';
# REPLACE from SELECT
REPLACE INTO daily_stats (day, visits)
SELECT CURDATE(), COUNT(*) FROM visits_log;
# caveat: REPLACE fires DELETE + INSERT triggers,
# not UPDATE, and resets columns not listedLOAD DATA INFILE
LOAD DATA INFILE est le moyen le plus rapide de charger du CSV en masse — 20x plus rapide que des INSERT car il contourne le parsing SQL. Le serveur lit les fichiers dans un répertoire sécurisé (secure_file_priv) ; LOCAL fait envoyer le fichier par le client mais est désactivé par défaut pour la sécurité. Utilisez la capture de colonnes par @var puis SET pour transformer les valeurs au chargement. SELECT ... INTO OUTFILE est l'export symétrique.
# fast bulk import from a CSV file
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email, @created)
SET created_at = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');
# LOCAL reads from the client machine (needs --local-infile)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (username, email);
# export counterpart
SELECT * FROM users INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';Requêtes SELECT
SELECT simple
Évitez SELECT * en production — il transfère des données inutiles, casse si des colonnes sont ajoutées/retirées, et empêche l'optimiseur d'utiliser des covering indexes. Nommez les colonnes dont vous avez besoin. LIMIT avec OFFSET implémente la pagination mais est lent pour de grands offsets (il scanne quand même les lignes sautées) ; la pagination keyset (WHERE id > last_id LIMIT n) passe bien mieux à l'échelle.
# select all columns
SELECT * FROM users;
# select specific columns (preferred)
SELECT id, username, email FROM users;
# column aliases
SELECT username AS name, email AS contact FROM users;
# constants and expressions
SELECT 1 + 1 AS sum, NOW() AS now, 'hello' AS greeting;
# limit rows and skip (pagination)
SELECT id, username FROM users
ORDER BY id DESC
LIMIT 10 OFFSET 20;
# MySQL shorthand: LIMIT offset, count
SELECT id, username FROM users LIMIT 20, 10;WHERE et opérateurs
Les comparaisons avec NULL donnent NULL (traité comme faux), donc utilisez toujours IS NULL / IS NOT NULL. Un NULL dans une liste IN/NOT IN peut produire des résultats vides surprenants — utilisez NOT EXISTS ou une sous-requête corrélée à la place. BETWEEN est inclusif. MySQL évalue AND avant OR, donc parenthésez les conditions mixtes. Pour la meilleure performance, mettez la condition la plus sélective d'abord et assurez-vous que les colonnes indexées sont à gauche de l'opérateur.
# comparison operators
SELECT * FROM products WHERE price < 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 150;
SELECT * FROM users WHERE id IN (1, 5, 9);
# logical operators
SELECT * FROM users
WHERE status = 'active' AND (age >= 18 OR verified = 1);
# NULL checks (never use = with NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
# BETWEEN is inclusive on both ends
SELECT * FROM orders WHERE created_at
BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';
# NOT IN with NULL traps: NULL poisons the resultORDER BY et LIMIT
ORDER BY trie les résultats ; les clés multiples s'appliquent de gauche à droite. FIELD() permet un ordre personnalisé selon une liste énumérée. Pour de grands ensembles de résultats, ORDER BY ... LIMIT peut toujours scanner et trier tout l'ensemble sans index supportant — ajoutez un index sur les colonnes du ORDER BY. Le motif de fonction de fenêtre (ROW_NUMBER OVER PARTITION BY) est la solution canonique top-N-par-groupe en MySQL 8.0+.
# ascending (default) and descending
SELECT * FROM users ORDER BY created_at;
SELECT * FROM users ORDER BY created_at DESC, username ASC;
# order by expression
SELECT product, price * stock AS value
FROM inventory
ORDER BY value DESC;
# order by FIELD() for custom ordering
SELECT * FROM tasks
ORDER BY FIELD(priority, 'high', 'medium', 'low');
# stable top-N per group via window function (8.0+)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn
FROM orders
) t WHERE rn <= 3;DISTINCT et groupement de valeurs
DISTINCT replie les lignes identiques ; avec plusieurs colonnes il déduplique la combinaison. COUNT(DISTINCT col) compte les valeurs uniques non nulles. DISTINCT est essentiellement un GROUP BY sur toutes les colonnes sélectionnées. Pour dédupliquer en gardant une ligne représentative (ex. la plus récente), utilisez ROW_NUMBER() OVER (PARTITION BY ...). DISTINCT peut être coûteux — il construit une table temporaire, préférez donc un index.
# unique values of one column
SELECT DISTINCT country FROM users;
# distinct over multiple columns
SELECT DISTINCT city, country FROM users;
# COUNT distinct values
SELECT COUNT(DISTINCT country) AS countries FROM users;
# GROUP BY returns one row per group; DISTINCT is a special case
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country;
# deduplicate while keeping the latest row (8.0+)
WITH latest AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
FROM users
)
SELECT id, email FROM latest WHERE rn = 1;LIKE et REGEXP
LIKE est simple mais les wildcards de tête (LIKE '%x') défont les index et forcent un scan complet. La collation décide de la sensibilité à la casse — utf8mb4_0900_ai_ci est insensible aux accents/casse. REGEXP/RLIKE ajoutent la puissance regex complète mais n'utilisent jamais d'index. MySQL 8.0 ajoute REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR et REGEXP_SUBSTR pour un traitement de motifs plus riche. Pour la recherche plein texte, utilisez un index FULLTEXT avec MATCH ... AGAINST.
# LIKE wildcards: % (any chars) and _ (one char)
SELECT * FROM users WHERE username LIKE 'a%'; -- starts with a
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_an'; -- 3 chars, ends 'an'
# escape a wildcard literally
SELECT * FROM users WHERE path LIKE '50\%' ESCAPE '\\';
# case-insensitive LIKE depends on column collation
# *_ci collations are case-insensitive
# REGEXP / RLIKE for regular expressions
SELECT * FROM users WHERE email REGEXP '^[a-z]+@';
SELECT * FROM products WHERE name RLIKE 'iPhone|Galaxy';
# capture groups with REGEXP_REPLACE (8.0+)
SELECT REGEXP_REPLACE(phone, '([0-9]{3})([0-9]{4})', '$1-$2')
FROM contacts;Expression CASE
CASE est l'expression conditionnelle SQL. La forme « searched » évalue les conditions WHEN de haut en bas et retourne le THEN de la première correspondance, sinon ELSE (ou NULL). CASE s'utilise dans SELECT, WHERE, ORDER BY et les agrégats — le motif SUM(CASE ...) est un pivot classique. CASE ne court-circuite jamais l'agrégation, donc chaque ligne est comptée. IF() et IFNULL() sont des raccourcis MySQL plus courts pour les cas simples.
# searched CASE (like if/else)
SELECT username,
CASE
WHEN age < 18 THEN 'minor'
WHEN age < 65 THEN 'adult'
ELSE 'senior'
END AS age_group
FROM users;
# simple CASE (compare to a value)
SELECT order_id,
CASE status
WHEN 0 THEN 'pending'
WHEN 1 THEN 'paid'
WHEN 2 THEN 'shipped'
ELSE 'unknown'
END AS status_label
FROM orders;
# CASE in aggregate (pivot)
SELECT
SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid_count,
SUM(CASE WHEN status='shipped' THEN 1 ELSE 0 END) AS shipped_count
FROM orders;Jointures (JOIN)
INNER JOIN
INNER JOIN ne retourne des lignes que s'il y a correspondance dans les deux tables — les lignes non appariées de chaque côté sont éliminées. JOIN est un raccourci d'INNER JOIN. USING (col) est une alternative concise à ON a.col = b.col quand le nom de colonne est identique et apparaît une fois dans le résultat. INNER JOIN est la jointure par défaut et la plus courante pour relier des tables normalisées.
# only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.paid = 1;
# join three tables
SELECT u.username, o.id, oi.product_id, oi.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id;
# USING clause when join columns share a name
SELECT u.username, o.id
FROM users u
JOIN orders o USING (user_id);LEFT et RIGHT JOIN
LEFT JOIN conserve chaque ligne de la table de gauche et remplit la droite avec NULL sans correspondance. L'astuce WHERE o.id IS NULL (anti-join) trouve les lignes de gauche sans correspondance à droite — souvent plus claire et rapide que NOT IN, surtout avec des NULL. RIGHT JOIN est symétrique ; la plupart réordonnent les tables et utilisent LEFT JOIN pour la lisibilité. Les NULL dans la clé de jointure ne matchent jamais.
# LEFT JOIN: all left rows, with NULLs where no match
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
# find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
# RIGHT JOIN is the mirror (rarely used; swap tables instead)
SELECT u.username, o.id
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;CROSS JOIN
CROSS JOIN retourne le produit cartésien — chaque combinaison des lignes des deux tables. Utile pour générer des combinaisons ou amorcer des matrices de dimensions, mais produit N x M lignes, donc coûteux sur de grandes tables. Une virgule entre tables (FROM a, b) est un CROSS JOIN implicite. Le motif de CTE récursive est la façon standard de générer une table de séquence/dimension en MySQL 8.0+.
# Cartesian product: every row of A paired with every row of B
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
# same effect with a comma join
SELECT s.size, c.color FROM sizes s, colors c;
# generate a series of dates (no built-in GENERATE_SERIES)
WITH RECURSIVE nums(n) AS (
SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 7
)
SELECT DATE_ADD('2024-01-01', INTERVAL n-1 DAY) AS day
FROM nums;
# build a matrix of all category x region combinations
SELECT cat.name, reg.name
FROM categories cat CROSS JOIN regions reg;Self Join
Un self join référence la même table deux fois avec des alias différents pour relier des lignes entre elles — classique pour hiérarchies manager/employé ou listes d'adjacence. La condition a.id < b.id évite les paires symétriques dupliquées. Pour les hiérarchies profondes, utilisez une CTE récursive (MySQL 8.0+), qui parcourt l'arbre sans limite de profondeur fixe et peut produire un affichage arborescent indenté avec REPEAT/CONCAT.
# employees and their managers (same table twice)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
# find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;
# hierarchical tree with recursive CTE (8.0+)
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT CONCAT(REPEAT('-- ', depth-1), name) AS tree
FROM org;Contournement FULL OUTER JOIN
MySQL n'implémente pas FULL OUTER JOIN. L'émulation standard est un LEFT JOIN UNION un RIGHT JOIN — UNION déduplique donc chaque ligne appariée apparaît une fois et les lignes non appariées de chaque côté apparaissent avec des NULL de l'autre. Pour de grands résultats, préférez UNION ALL avec une stratégie de déduplication, car le DISTINCT implicite d'UNION est coûteux. Si vous ne voulez que les lignes non appariées, utilisez deux anti-joins.
# MySQL has no FULL OUTER JOIN — emulate with LEFT + UNION + RIGHT
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;
# the UNION removes duplicates; use UNION ALL to keep them
# for an anti-union (mismatches only) add WHERE o.id IS NULL
# or u.id IS NULL in each branchNATURAL JOIN et USING
NATURAL JOIN est fragile — il joint sur tous les noms de colonnes partagés, donc ajouter une colonne peut changer les résultats en silence ; évitez-le en production. USING (col) est plus sûr et replie la colonne de jointure en une seule copie dans la sortie. STRAIGHT_JOIN force l'ordre de tables utilisé par l'optimiseur et est un contournement manuel des mauvais plans ; retirez-le une fois le vrai problème corrigé. Préférez un ON explicite pour la clarté.
# NATURAL JOIN matches all same-named columns automatically
SELECT * FROM users NATURAL JOIN profiles;
# USING matches specific shared columns, dedups them in output
SELECT u.username, user_id
FROM users u
JOIN orders o USING (user_id);
# STRAIGHT_JOIN forces the optimizer to join left-to-right
SELECT STRAIGHT_JOIN u.username, o.id
FROM users u
JOIN orders o ON o.user_id = u.id;Agrégation et groupement
Fonctions d'agrégation
Les fonctions d'agrégation replient beaucoup de lignes en une : COUNT, SUM, AVG, MIN, MAX. COUNT(*) compte les lignes ; COUNT(col) compte les valeurs non nulles de col ; COUNT(DISTINCT col) compte les valeurs uniques. AVG ignore les NULL (pas les zéros), ce qui peut biaiser — utilisez COALESCE(col,0) si vous voulez traiter NULL comme zéro. Un agrégat sans GROUP BY retourne une seule ligne même sur une table vide.
SELECT
COUNT(*) AS row_count,
COUNT(email) AS emails, -- ignores NULLs
COUNT(DISTINCT country) AS countries,
MIN(created_at) AS first_user,
MAX(created_at) AS last_user,
AVG(age) AS avg_age,
SUM(balance) AS total_balance
FROM users;
# aggregate over a filtered set
SELECT SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid';GROUP BY
GROUP BY replie les lignes partageant les valeurs des colonnes groupées en une ligne de sortie, avec agrégats calculés par groupe. MySQL 8.0 active ONLY_FULL_GROUP_BY par défaut, qui rejette les requêtes sélectionnant des colonnes absentes du GROUP BY (les versions antérieures retournaient une ligne arbitraire — un bug courant). Groupez par expressions ou alias. Pour des séries temporelles de reporting, groupez par YEAR()/MONTH()/DATE() d'un timestamp.
# one row per country
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
GROUP BY country;
# group by multiple columns
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city
ORDER BY country, users DESC;
# group by expression
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS m, COUNT(*)
FROM users
GROUP BY yr, m;
# MySQL 8.0 enforces ONLY_FULL_GROUP_BY:
# every non-aggregated SELECT column must be in GROUP BYHAVING
HAVING filtre le résultat de l'agrégation alors que WHERE filtre les lignes d'entrée avant le groupement — HAVING peut donc référencer des agrégats (SUM, COUNT) et pas WHERE. Mettez les filtres au niveau ligne dans WHERE pour l'efficacité (ils réduisent les lignes avant le groupement). HAVING accepte les alias dans MySQL (HAVING users > 100). Une requête peut combiner les deux : WHERE resserre les lignes, GROUP BY les met en seaux, HAVING resserre les seaux.
# HAVING filters groups (after aggregation); WHERE filters rows (before)
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY users DESC;
# filter on an aggregate
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;
# combine WHERE and HAVING
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
WHERE status = 'active'
GROUP BY country
HAVING avg_age >= 30;GROUP BY ROLLUP
WITH ROLLUP ajoute des lignes super-agrégées pour chaque niveau de groupement plus un grand total, où la colonne groupée devient NULL. GROUPING(col) retourne 1 sur une ligne de sous-total/total pour pouvoir étiqueter les NULL comme 'ALL'. ROLLUP fonctionne de gauche à droite sur les colonnes du GROUP BY. Pas de WITH CUBE dans MySQL — émulez avec UNION ALL de plusieurs requêtes ROLLUP si vous avez besoin de chaque combinaison de dimensions.
# ROLLUP adds subtotals and a grand total row
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;
# output includes:
# country | city | users
# 'US' | 'NYC' | 120
# 'US' | NULL | 200 <- US subtotal
# NULL | NULL | 950 <- grand total
# identify super-aggregate rows with GROUPING()
SELECT
IF(GROUPING(country)=1,'ALL',country) AS country,
IF(GROUPING(city)=1,'ALL',city) AS city,
COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;GROUP_CONCAT
GROUP_CONCAT est la fonction d'agrégation de chaînes de MySQL — elle concatène les valeurs d'un groupe en une seule chaîne. Utilisez DISTINCT pour dédupliquer, ORDER BY pour trier, et SEPARATOR pour changer le délimiteur (défaut ','). Le résultat est plafonné à group_concat_max_len (défaut 1024 octets) ; augmentez-le pour des listes longues. D'autres bases l'appellent LISTAGG/STRING_AGG — GROUP_CONCAT est l'équivalent MySQL.
# concatenate grouped values into one string
SELECT country,
GROUP_CONCAT(username) AS all_names
FROM users
GROUP BY country;
# order within the list and deduplicate
SELECT user_id,
GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ',') AS tags
FROM post_tags
GROUP BY user_id;
# the default separator is ',', max length is 1024 by default
# raise the limit:
# SET SESSION group_concat_max_len = 1000000;
SELECT department,
GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ' | ') AS ranking
FROM employees
GROUP BY department;WITH ROLLUP vs agrégats de fenêtre
ROLLUP réduit le résultat en ajoutant des lignes de sous-totaux, tandis qu'un agrégat de fenêtre (OVER) garde le même nombre de lignes et ajoute l'agrégat comme colonne supplémentaire — vous voyez donc détail et totaux ensemble. Utilisez ROLLUP pour les rapports de synthèse et les fonctions de fenêtre quand vous avez besoin du contexte par ligne à côté de l'agrégat. Les totaux cumulés sont un cas classique de SUM OVER (ORDER BY ...).
# ROLLUP gives fewer rows (subtotals mixed into the result)
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;
# a window aggregate keeps every detail row AND adds the total
SELECT
country, city,
COUNT(*) AS users_in_city,
COUNT(*) OVER (PARTITION BY country) AS users_in_country,
COUNT(*) OVER () AS users_total
FROM users;
# running total over time
SELECT created_at, amount,
SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM daily_sales;Sous-requêtes et CTE
Sous-requête scalaire
Une sous-requête scalaire retourne une seule valeur (une ligne, une colonne) et s'utilise partout où une expression est valide — liste SELECT, WHERE, HAVING. Si elle ne retourne aucune ligne, la valeur est NULL. L'optimiseur peut souvent transformer une sous-requête scalaire corrélée en jointure (matérialisation de sous-requête). Pour la clarté et parfois la performance, une CTE ou une JOIN est préférable quand la même sous-requête est référencée plusieurs fois.
# returns a single value
SELECT username, balance,
(SELECT AVG(balance) FROM users) AS avg_balance
FROM users
WHERE balance > (SELECT AVG(balance) FROM users);
# use in SELECT list, WHERE, HAVING, etc.
SELECT product, price,
price - (SELECT MIN(price) FROM products) AS above_min
FROM products;
# a scalar subquery must return exactly one row, one columnSous-requêtes IN / ANY / ALL
IN matche toute valeur retournée par la sous-requête ; NOT IN est le complément mais est empoisonné par les NULL — si la sous-requête retourne un NULL, NOT IN ne retourne aucune ligne, donc excluez toujours les NULL ou utilisez NOT EXISTS. ALL est vrai si la comparaison tient pour toutes les valeurs retournées ; ANY/SOME si elle tient pour au moins une. L'optimiseur réécrit souvent ces cas en semi-joins.
# IN: value matches any row of the subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status='active');
# NOT IN with NULLs is dangerous — prefer NOT EXISTS
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
# ANY / SOME / ALL with comparisons
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category='toys');
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category='toys');EXISTS et NOT EXISTS
EXISTS teste l'existence de lignes sans retourner de données — il s'arrête à la première correspondance, donc efficace pour des vérifications de présence. La sous-requête est corrélée (référence la requête externe). NOT EXISTS est la façon NULL-safe de trouver des lignes sans correspondance et généralement préféré à NOT IN. SELECT 1 dans EXISTS est conventionnel ; la liste de colonnes importe peu. L'optimiseur transforme souvent EXISTS en semi-join.
# EXISTS: true if the subquery returns any rows
SELECT u.username
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
# NOT EXISTS: the standard anti-semi-join (NULL-safe)
SELECT u.username
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
# correlated: the subquery references the outer row (u.id)
# EXISTS stops at the first matching row — efficient for presence checksTables dérivées
Une table dérivée est une sous-requête dans la clause FROM — elle matérialise un résultat intermédiaire sur lequel on peut sélectionner ou joindre. Alias toujours. MySQL 8.0 peut fusionner beaucoup de tables dérivées dans la requête externe (derived_merge) pour de meilleurs plans, mais les agrégats complexes ou LIMIT forcent généralement la matérialisation. Pour la lisibilité et la réutilisation dans une requête, préférez une CTE nommée (WITH) à une table dérivée en ligne.
# a subquery in the FROM clause is a derived table
SELECT t.country, t.users
FROM (
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
) t
WHERE t.users > 50;
# join a derived table
SELECT u.username, agg.order_count
FROM users u
JOIN (
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
) agg ON agg.user_id = u.id;
# derived tables must be aliasedSous-requête corrélée
Une sous-requête corrélée référence une colonne de la requête externe, donc réévaluée logiquement pour chaque ligne externe — ce qui peut être lent sur de grands ensembles. L'optimiseur peut la transformer en jointure ou utiliser un cache (subquery cache) pour atténuer. Pour les problèmes de rang/top-N (ex. n-ième salaire le plus élevé), une fonction de fenêtre comme DENSE_RANK() OVER (ORDER BY salary DESC) est plus claire et plus rapide — choisissez-la sur MySQL 8.0+.
# the subquery references the outer row (u.id) and runs per row
SELECT u.username,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;
# correlated subquery in WHERE (nth-highest salary classic)
SELECT e1.name, e1.salary
FROM employees e1
WHERE 2 = (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.salary > e1.salary
);
# prefer a window function for rank-style problems in 8.0+Common Table Expressions (CTE)
Une CTE (clause WITH) nomme une sous-requête pour la lisibilité et la réutilisation dans une instruction ; MySQL 8.0+ la supporte. RECURSIVE permet les CTE auto-référencées — essentiel pour le parcours d'arbres et la génération de séquences (UNION ALL pour récursion, UNION pour dédupliquer). Une CTE récursive exige un cas de base, un bras récursif et une garde d'arrêt. Plusieurs CTE se séparent par virgule et peuvent référencer les précédentes dans le même WITH.
# a named CTE (WITH) — readable, can be referenced multiple times
WITH active_users AS (
SELECT id, username FROM users WHERE status = 'active'
)
SELECT au.username, COUNT(o.id) AS orders
FROM active_users au
LEFT JOIN orders o ON o.user_id = au.id
GROUP BY au.username;
# RECURSIVE CTE: walk a hierarchy or generate a series
WITH RECURSIVE nums(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
# multiple CTEs, comma-separated
WITH a AS (SELECT ...), b AS (SELECT ...)
SELECT * FROM a JOIN b USING (id);Index
Créer un index
Un index est une structure de données (B+Tree dans InnoDB) qui accélère les recherches et tris au prix d'un coût d'écriture et de stockage. Créez des index sur les colonnes utilisées dans WHERE, JOIN et ORDER BY. Les index UNIQUE imposent aussi une contrainte d'unicité. Les index de préfixe sur colonnes textuelles longues économisent l'espace mais ne servent pas aux covering scans ni au ORDER BY. Chaque clé primaire est clusterisée dans InnoDB — les index secondaires stockent la valeur PK comme pointeur de ligne.
# single-column index
CREATE INDEX idx_email ON users(email);
# unique index (enforces uniqueness, allows fast lookup)
CREATE UNIQUE INDEX uq_username ON users(username);
# create with a table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255),
UNIQUE KEY uq_email (email)
);
# full-table prefix index (limited length)
CREATE INDEX idx_name ON users(last_name(20));Index composite
Les index composites sont ordonnés par leur colonne la plus à gauche d'abord, donc ils n'aident que les requêtes filtrant sur un préfixe gauche de la liste de colonnes — l'ordre des colonnes est critique. Mettez la colonne d'égalité la plus sélective d'abord, puis les plages. Un covering index contient toutes les colonnes nécessaires à la requête, permettant un scan d'index seul qui saute la consultation de table (EXPLAIN affiche 'Using index'). Ne sur-indexez pas — chaque index ralentit les écritures.
# multi-column index (column order matters!)
CREATE INDEX idx_last_first ON users(last_name, first_name);
# supports these lookups (leftmost prefix rule):
# WHERE last_name = 'X' -> uses index
# WHERE last_name = 'X' AND first_name = 'Y' -> uses index
# WHERE first_name = 'Y' -> CANNOT use index
# covering index: all needed columns are in the index
CREATE INDEX idx_cover ON orders(user_id, status, amount);
SELECT user_id, SUM(amount) FROM orders
WHERE user_id = 5 AND status = 'paid' GROUP BY user_id;
# ^ index-only scan, no table lookup neededIndex full-text
Les index FULLTEXT activent la recherche textuelle en langage naturel et booléenne, bien meilleure que LIKE '%word%'. MATCH ... AGAINST retourne un score de pertinence. Le mode booléen supporte les opérateurs : +exiger, -exclure, *wildcard, >augmenter, ~diminuer. Exige InnoDB (5.6+) ou MyISAM et une longueur minimale de mot (ft_min_word_len / innodb_ft_min_token_size, défaut 3). Pour une recherche sérieuse à grande échelle, considérez un moteur dédié comme Elasticsearch.
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;
# natural-language search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('database performance');
# boolean mode: +require -exclude ~relax
SELECT id, title
FROM articles
WHERE MATCH(title, body)
AGAINST ('+MySQL -Oracle >index' IN BOOLEAN MODE);
# query expansion (second pass adds related terms)
SELECT id FROM articles
WHERE MATCH(title, body) AGAINST ('database' WITH QUERY EXPANSION);Index spatial
Les index spatiaux (R-tree) accélèrent les requêtes géographiques sur colonnes GEOMETRY/POINT/POLYGON. MySQL 8.0 s'est standardisé sur SRID 4326 (coordonnées GPS WGS 84) et a ajouté ST_Distance_Sphere pour des distances réelles en mètres. La colonne doit être NOT NULL. Utilisez ST_Within/ST_Contains/ST_Distance pour filtrer. Exprimez toujours les coordonnées en (longitude, latitude) pour la convention X/Y. Les index spatiaux rendent les requêtes par rayon bien plus rapides.
CREATE TABLE places (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
location POINT NOT NULL SRID 4326,
SPATIAL KEY sp_location (location)
) ENGINE=InnoDB;
# insert a point (longitude, latitude)
INSERT INTO places (name, location)
VALUES ('HQ', ST_PointFromText('POINT(116.40 39.90)', 4326));
# find points within a bounding distance
SELECT name, ST_Distance_Sphere(location,
ST_PointFromText('POINT(116.40 39.90)', 4326)) AS meters
FROM places
WHERE ST_Within(location,
ST_Buffer(ST_PointFromText('POINT(116.40 39.90)', 4326), 0.01));Gestion des index
SHOW INDEX liste les index d'une table avec l'ordre des colonnes et la cardinalité (estimation des valeurs distinctes). DROP INDEX supprime un index ; renommer (8.0+) évite un drop+rebuild. OPTIMIZE TABLE reconstruit la table pour récupérer l'espace fragmenté et rafraîchir les statistiques d'index — très verrouillant, planifiez à faible trafic. Des statistiques périmées trompent l'optimiseur ; lancez ANALYZE TABLE pour les rafraîchir après de gros chargements.
# show indexes on a table
SHOW INDEX FROM users;
# drop an index
DROP INDEX idx_email ON users;
ALTER TABLE users DROP INDEX uq_username;
# rename an index (8.0+)
ALTER TABLE users RENAME INDEX idx_email TO idx_user_email;
# rebuild all indexes on a table
ALTER TABLE users ENGINE=InnoDB;
OPTIMIZE TABLE users;
# check index cardinality
SHOW INDEX FROM users;
SELECT index_name, cardinality
FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='users';Index invisibles et hints
Les index invisibles (8.0+) permettent de tester la suppression d'un index en sécurité — l'optimiseur l'ignore mais les écritures le maintiennent, donc on peut le réactiver instantanément si les requêtes régressent ; supprimez-le une fois sûr. Les hints d'optimiseur (/*+ ... */) sont préférés à l'ancienne syntaxe USE/FORCE/IGNORE INDEX et ne touchent que l'instruction étiquetée. Utilisez les hints avec parcimonie — c'est un pansement pour une distribution de données mauvaise ou des statistiques absentes.
# make an index invisible (8.0+): optimizer ignores it, still maintained
ALTER TABLE users ALTER INDEX idx_email SET INVISIBLE;
# ... observe query plans, then drop or restore:
ALTER TABLE users ALTER INDEX idx_email SET VISIBLE;
# optimizer hints (8.0+) to force / avoid indexes
SELECT /*+ INDEX(u idx_email) */ * FROM users u WHERE email LIKE 'a%';
SELECT /*+ NO_INDEX(u idx_email) */ * FROM users u;
SELECT /*+ FORCE INDEX(u idx_email) */ * FROM users u;
# index merge / IGNORE_INDEX legacy hint
SELECT * FROM users USE INDEX (idx_email) WHERE email = '[email protected]';
SELECT * FROM users IGNORE INDEX (idx_email) WHERE email = '[email protected]';Vues
Créer une vue
Une vue est une requête stockée qui se comporte comme une table virtuelle — elle simplifie les requêtes complexes, abstrait les changements de schéma et impose la visibilité des lignes/colonnes. CREATE OR REPLACE met la définition à jour en une étape. Les vues ne stockent pas de données (elles rejouent la requête sous-jacente à chaque fois, sauf matérialisation) donc pas de coût d'écriture mais pas d'accélération de lecture non plus. Les vues sont fusionnées dans la requête quand possible.
# a view is a stored SELECT
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';
# query it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';
# create or replace, with explicit columns
CREATE OR REPLACE VIEW user_summary (username, order_count, total) AS
SELECT u.username, COUNT(o.id), COALESCE(SUM(o.amount), 0)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;
# list and inspect views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW CREATE VIEW active_users\GVues updatables
Une vue sur une seule table de base sans agrégats/distinct est updatable — INSERT/UPDATE/DELETE se propagent à la table sous-jacente. Par défaut, un UPDATE déplaçant une ligne hors du WHERE de la vue réussit (la ligne disparaît juste de la vue) ; WITH CHECK OPTION bloque ces changements pour qu'une ligne ne quitte jamais le périmètre de la vue. Les vues avec JOIN, GROUP BY, DISTINCT ou sous-requêtes ne sont généralement pas updatables.
# a view over a single base table is updatable
CREATE VIEW active_users AS
SELECT id, username, email, status
FROM users
WHERE status = 'active';
# INSERT through the view inserts into the base table
INSERT INTO active_users (id, username, email, status)
VALUES (10, 'newbie', '[email protected]', 'active');
# UPDATE through the view only touches visible rows
UPDATE active_users SET email = '[email protected]' WHERE id = 10;
# WITH CHECK OPTION prevents inserts/updates that leave the view
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, status FROM users WHERE status='active'
WITH CHECK OPTION;CHECK OPTION des vues
CHECK OPTION impose que les lignes modifiées via une vue restent visibles par cette vue. LOCAL ne vérifie que le WHERE de la vue elle-même ; CASCADED (le défaut) impose aussi les WHERE des vues sous-jacentes référencées. Utilisez CASCADED en empilant des vues pour garder toute la chaîne cohérente ; LOCAL seulement si vous voulez délibérément relâcher le filtre d'une vue sous-jacente.
# LOCAL: only the defining view's WHERE is enforced
CREATE VIEW v_paid AS
SELECT * FROM orders WHERE status='paid' WITH LOCAL CHECK OPTION;
# CASCADED (default): all underlying views' checks are enforced too
CREATE VIEW v_paid_large AS
SELECT * FROM v_paid WHERE amount > 100
WITH CASCADED CHECK OPTION;
# an insert that violates v_paid's filter:
INSERT INTO v_paid_large (id, status, amount) VALUES (1, 'unpaid', 200);
# CASCADED -> rejected (status would leave v_paid)
# LOCAL -> accepted (only v_paid_large's filter checked)Gérer les vues
ALTER VIEW redéfinit une vue existante sans la supprimer (en préservant les grants). DROP VIEW IF EXISTS évite une erreur quand la vue est absente. RENAME TABLE marche aussi pour les vues mais est peu courant. information_schema.views expose la définition de chaque vue, son caractère updatable et son type de sécurité — utile pour l'audit. Une vue dépend de ses tables de base ; supprimer une table de base laisse la vue invalide jusqu'à recréation.
# alter a view's definition (CREATE OR REPLACE is simpler)
ALTER VIEW active_users AS
SELECT id, username FROM users WHERE status='active';
# drop a view
DROP VIEW IF EXISTS active_users;
# drop multiple views
DROP VIEW IF EXISTS v1, v2, v3;
# rename a view (no RENAME VIEW; recreate or use a table rename trick)
RENAME TABLE old_view TO new_view;
# view metadata
SELECT table_name, view_definition, is_updatable
FROM information_schema.views
WHERE table_schema = 'mydb';Sécurité et déterminisme des vues
SQL SECURITY DEFINER exécute la vue avec les privilèges du definer — utile pour exposer des données filtrées à des utilisateurs sans accès aux tables de base (ex. une vue de sécurité au niveau ligne). INVOKER vérifie les privilèges de l'appelant. ALGORITHM MERGE plie la vue dans la requête (plus rapide, permet les vues updatables) ; TEMPTABLE la matérialise d'abord (nécessaire pour certaines constructions mais perd l'updatabilité et peut être plus lent). UNDEFINED laisse MySQL choisir.
# DEFINER (default): runs with the view creator's privileges
CREATE VIEW secret_emails SQL SECURITY DEFINER AS
SELECT email FROM users;
# INVOKER: runs with the querying user's privileges
CREATE VIEW my_emails SQL SECURITY INVOKER AS
SELECT email FROM users WHERE id = CURRENT_USER_ID();
# ALGORITHM: MERGE (preferred) vs TEMPTABLE vs UNDEFINED
CREATE ALGORITHM=MERGE VIEW v_active AS
SELECT * FROM users WHERE status='active';Procédures stockées
Créer et appeler une procédure
Une procédure stockée est un bloc SQL nommé et précompilé. Comme les procédures contiennent des points-virgules, on change le DELIMITER pour que le CREATE entier passe en une instruction, puis on le remet. CALL exécute une procédure. Les procédures ne retournent pas de valeur directement mais peuvent retourner des result sets et modifier des paramètres OUT. Elles tournent côté serveur, économisant des aller-retours réseau pour la logique multi-étapes, mais sont plus dures à versionner et tester que le code applicatif.
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN p_id INT)
BEGIN
SELECT id, username, email FROM users WHERE id = p_id;
END //
DELIMITER ;
# call it
CALL get_user_by_id(42);
# drop it
DROP PROCEDURE IF EXISTS get_user_by_id;
# show the definition
SHOW CREATE PROCEDURE get_user_by_id\GParamètres IN / OUT / INOUT
Les paramètres sont IN (entrée en lecture seule), OUT (récrit vers l'appelant via une variable de session) ou INOUT (les deux). SELECT ... INTO affecte un résultat scalaire de requête à une variable. L'exemple enveloppe un débit/crédit dans une transaction avec SELECT FOR UPDATE pour verrouiller les lignes et éviter les mises à jour perdues. Utilisez les paramètres OUT pour retourner des codes de statut ; les result sets sont retournés directement quand on fait un SELECT dans la procédure.
DELIMITER //
CREATE PROCEDURE transfer(
IN p_from INT,
IN p_to INT,
IN p_amount DECIMAL(10,2),
OUT p_result VARCHAR(50)
)
BEGIN
DECLARE v_bal DECIMAL(10,2);
START TRANSACTION;
SELECT balance INTO v_bal FROM accounts WHERE id = p_from FOR UPDATE;
IF v_bal >= p_amount THEN
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
COMMIT;
SET p_result = 'OK';
ELSE
ROLLBACK;
SET p_result = 'INSUFFICIENT_FUNDS';
END IF;
END //
DELIMITER ;
# call with a session variable for the OUT value
CALL transfer(1, 2, 50.00, @res);
SELECT @res;Variables et flux de contrôle
Les programmes stockés supportent les variables locales (DECLARE, portée BEGIN...END), les variables utilisateur (@var, portée session) et les variables système. Le flux de contrôle inclut IF/ELSEIF/ELSE, CASE, et trois boucles : LOOP (avec LEAVE/ITERATE), WHILE (condition d'abord) et REPEAT (condition à la fin). Nommez toujours les boucles imbriquées pour que LEAVE vise la bonne. SET affecte une valeur ; SELECT ... INTO copie un scalaire de requête dans une variable.
DELIMITER //
CREATE PROCEDURE classify(IN p_age INT, OUT p_label VARCHAR(20))
BEGIN
DECLARE v_count INT DEFAULT 0;
# IF / ELSEIF / ELSE
IF p_age < 13 THEN
SET p_label = 'child';
ELSEIF p_age < 20 THEN
SET p_label = 'teen';
ELSE
SET p_label = 'adult';
END IF;
# simple loop with a counter
simple_loop: LOOP
SET v_count = v_count + 1;
IF v_count >= 5 THEN LEAVE simple_loop; END IF;
END LOOP;
# WHILE / REPEAT alternatives
# WHILE cond DO ... END WHILE;
# REPEAT ... UNTIL cond END REPEAT;
# CASE statement
CASE p_label
WHEN 'child' THEN SET p_label = CONCAT(p_label, '!');
ELSE SET p_label = CONCAT(p_label, '.');
END CASE;
END //
DELIMITER ;Curseurs
Un curseur permet à une procédure stockée d'itérer ligne par ligne sur un result set. Déclarez le curseur d'abord, puis un CONTINUE HANDLER FOR NOT FOUND pour détecter l'épuisement (mettez un flag et LEAVE la boucle). FETCH récupère une ligne dans des variables. Les curseurs sont read-only et forward-only dans MySQL. Le traitement ligne à ligne est lent comparé au SQL ensembliste — préférez un seul UPDATE/INSERT ... SELECT quand possible ; n'utilisez les curseurs que pour une logique réellement séquentielle.
DELIMITER //
CREATE PROCEDURE log_all_users()
BEGIN
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE done INT DEFAULT 0;
# declare the cursor BEFORE handlers
DECLARE cur CURSOR FOR
SELECT id, username FROM users WHERE status='active';
# continue handler for end of cursor -> set done=1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id, v_name;
IF done THEN LEAVE read_loop; END IF;
INSERT INTO audit_log(user_id, action) VALUES (v_id, CONCAT('seen:', v_name));
END LOOP;
CLOSE cur;
END //
DELIMITER ;
CALL log_all_users();Gestionnaires d'erreurs
Les handlers attrapent les erreurs dans les programmes stockés : CONTINUE exécute le corps du handler puis reprend après l'instruction fautive ; EXIT exécute le corps et sort du bloc BEGIN...END ; UNDO annule (déprécié). Attrapez SQLEXCEPTION pour toute erreur, SQLWARNING pour les avertissements, ou un SQLSTATE/errno précis (ex. 1062 clé dupliquée). RESIGNAL relance l'erreur courante après nettoyage. Les conditions nommées (CONDITION FOR) rendent les handlers auto-documentés.
DELIMITER //
CREATE PROCEDURE safe_insert(IN p_email VARCHAR(255))
BEGIN
# SQLEXCEPTION catches any error; SQLSTATE/errno catch specific ones
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL; -- re-raise so the caller sees the error
END;
DECLARE CONTINUE HANDLER FOR 1062 -- duplicate key
BEGIN
SELECT 'Duplicate skipped' AS msg;
END;
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('x', p_email);
INSERT INTO stats (email) VALUES (p_email);
COMMIT;
END //
DELIMITER ;
# named conditions make handlers readable
DECLARE dup_key CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR dup_key SELECT 'dup';Métadonnées des procédures
SHOW PROCEDURE STATUS liste les procédures ; SHOW CREATE PROCEDURE retourne le corps complet. information_schema.routines donne des métadonnées plus riches dont le déterminisme et le type de sécurité. Marquez une fonction DETERMINISTIC (READS SQL DATA / NO SQL) si sa sortie ne dépend que de ses entrées — requis pour le binary logging et la réplication des fonctions. Les procédures n'ont pas besoin du flag déterministe. Utilisez ces vues pour auditer et inventorier les routines.
# list procedures in a database
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
# show definition
SHOW CREATE PROCEDURE get_user_by_id\G
# query information_schema for routines
SELECT routine_name, routine_type, created, last_altered,
is_deterministic, security_type
FROM information_schema.routines
WHERE routine_schema = 'mydb';
# list stored functions too
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'mydb' AND routine_type='FUNCTION';Fonctions
Fonctions de chaînes
LENGTH compte les octets, CHAR_LENGTH compte les caractères (important pour l'utf8mb4 multioctet). SUBSTRING est indexé à partir de 1 ; un départ négatif compte depuis la fin. CONCAT retourne NULL si un argument est NULL — utilisez CONCAT_WS pour sauter les NULL. SUBSTRING_INDEX découpe sur un délimiteur et garde les N premières parties (N négatif garde depuis la droite). TRIM a des variantes BOTH/LEADING/TRAILING et peut rogner n'importe quel caractère, pas seulement les espaces.
# length & case
SELECT LENGTH('hello'), CHAR_LENGTH('héllo'); -- bytes vs chars
SELECT UPPER('abc'), LOWER('ABC');
# substring & position
SELECT SUBSTRING('MySQL', 3); -- 'SQL'
SELECT SUBSTRING('MySQL', 1, 2); -- 'My'
SELECT INSTR('MySQL', 'SQL'); -- 3
SELECT LOCATE('SQL', 'MySQL', 1); -- 3
# trim & pad
SELECT TRIM(' hi '), LTRIM(' hi'), RTRIM('hi ');
SELECT LPAD('5', 3, '0'), RPAD('5', 3, '-'); -- 005, 5--
# split & replace
SELECT REPLACE('a-b-c', '-', '/');
SELECT SUBSTRING_INDEX('a,b,c', ',', 2); -- 'a,b'
# concatenate with NULL-safe separator
SELECT CONCAT_WS(',', 'a', NULL, 'b'); -- 'a,b'Fonctions numériques
ROUND arrondit au demi supérieur en s'éloignant de zéro ; TRUNCATE coupe les chiffres sans arrondir. MOD et % sont interchangeables. RAND() retourne un flottant dans [0,1) ; ORDER BY RAND() est un moyen pratique mais coûteux d'échantillonner des lignes car il trie tout le résultat — pour les grandes tables, utilisez une plage de PK aléatoire ou du reservoir sampling. FORMAT ajoute les séparateurs de milliers et arrondit aux décimales demandées, en retournant une chaîne.
# rounding
SELECT ROUND(2.567, 1); -- 2.6
SELECT CEIL(2.1), FLOOR(2.9); -- 3, 2
SELECT TRUNCATE(2.567, 1); -- 2.5
# modular & power
SELECT MOD(17, 5), 17 % 5; -- 2, 2
SELECT POWER(2, 10); -- 1024
SELECT SQRT(16); -- 4
# random
SELECT RAND(); -- 0..1 float
SELECT FLOOR(RAND() * 100); -- 0..99 integer
SELECT * FROM users ORDER BY RAND() LIMIT 5; -- random sample (slow!)
# formatting
SELECT FORMAT(1234567.891, 2); -- '1,234,567.89'
SELECT ABS(-5), SIGN(-5); -- 5, -1Fonctions date et heure
NOW() retourne la date/heure courante en un appel (cohérent sur toute l'instruction) ; CURDATE/CURTIME retournent les parties date/heure. DATE_ADD/SUB avec INTERVAL est l'arithmétique de dates canonique — il gère correctement les bascules de mois/année. DATEDIFF retourne des jours (date1 - date2) ; TIMEDIFF retourne un TIME. DATE_FORMAT et STR_TO_DATE utilisent les spécificateurs %Y %m %d %H %i %s (notez i minuscule pour les minutes, s pour les secondes).
# current values
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();
# parts of a date
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), DAYNAME(NOW());
SELECT WEEK(NOW()), WEEKDAY(NOW()), DAYOFWEEK(NOW());
# arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB('2024-01-31', INTERVAL 1 MONTH); -- 2023-12-31
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365 days
SELECT TIMEDIFF('18:00:00', '09:30:00'); -- 08:30:00
# formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('31/12/2024', '%d/%m/%Y');Fonctions personnalis ées
Une fonction stockée retourne une seule valeur scalaire et s'utilise dans les expressions (contrairement aux procédures, qu'on CALL). Elle doit déclarer ses caractéristiques : DETERMINISTIC (même entrée donne toujours même sortie, requis pour la réplication statement-based), et une de READS SQL DATA / NO SQL / MODIFIES SQL DATA. Les fonctions ne peuvent pas retourner de result sets ni utiliser le contrôle de transaction. La combinaison NO SQL + DETERMINISTIC est la plus sûre pour du calcul pur.
DELIMITER //
CREATE FUNCTION full_name(p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN CONCAT_WS(' ', p_first, p_last);
END //
DELIMITER ;
# use it like a built-in
SELECT id, full_name(first_name, last_name) FROM users;
# drop it
DROP FUNCTION IF EXISTS full_name;
# functions MUST declare their nature for binary logging:
# DETERMINISTIC - same inputs -> same output (required for replication)
# READS SQL DATA - reads but doesn't modify
# NO SQL - no SQL at allFonctions de flux de contrôle
IF() est un ternaire compact ; IFNULL(a,b) retourne a sauf si a est NULL alors b (deux arguments seulement) ; COALESCE se généralise à N arguments. NULLIF(a,b) retourne NULL quand a=b, pratique pour éviter la division par zéro (x / NULLIF(y,0)). ISNULL() retourne 1/0, pas la valeur — ne le confondez pas avec IFNULL. CASE est l'expression conditionnelle la plus flexible et portable, et marche dans tout dialecte SQL.
# IF(test, true_val, false_val) - ternary
SELECT IF(age >= 18, 'adult', 'minor') FROM users;
# IFNULL / NULLIF
SELECT IFNULL(email, 'no-email') FROM users; -- coalesce one
SELECT COALESCE(nick, email, username) FROM users; -- coalesce many
SELECT NULLIF(status, 'inactive'); -- NULL if equal, else status
# CASE as a function (see SELECT section for full CASE)
SELECT CASE WHEN x>0 THEN 'pos' WHEN x<0 THEN 'neg' ELSE 'zero' END;
# ISNULL() returns 1/0 (different from IFNULL!)
SELECT ISNULL(email) FROM users;Cast et conversion
CAST et CONVERT changent le type d'une valeur — essentiel quand MySQL ferait sinon une conversion implicite qui défait les index (ex. comparer une chaîne à une colonne numérique). CAST(... AS DATE/DATETIME/SIGNED/UNSIGNED/CHAR/DECIMAL) couvre les besoins courants. Attention : CAST('123abc' AS SIGNED) retourne 123 avec un avertissement plutôt que d'échouer. Utilisez STR_TO_DATE pour parser des formats de dates personnalisés, car CAST attend un format ISO strict.
# CAST to a specific type
SELECT CAST('2024-01-15' AS DATE);
SELECT CAST(3.14 AS SIGNED); -- 3
SELECT CAST('123abc' AS SIGNED); -- 123 (warns in strict mode)
SELECT CAST(123 AS CHAR(10));
# CONVERT is the same thing with different syntax
SELECT CONVERT('2024-01-15', DATE);
SELECT CONVERT('hello' USING utf8mb4); -- charset conversion
# common: string -> date for comparison
SELECT * FROM orders
WHERE created_at >= CAST('2024-01-01' AS DATETIME);
# format a number as a zero-padded string
SELECT LPAD(CAST(id AS CHAR), 6, '0') FROM orders;Triggers
Triggers BEFORE / AFTER
Un trigger se déclenche automatiquement sur INSERT/UPDATE/DELETE, soit BEFORE (peut modifier les valeurs NEW, valider et annuler), soit AFTER (pour des effets de bord comme le logging). FOR EACH ROW signifie que le corps s'exécute une fois par ligne affectée — les triggers de ligne sont les seuls que MySQL supporte. Les triggers BEFORE conviennent au défaut/normalisation des données ; les AFTER au travail en cascade non critique. Les triggers tournent dans la même transaction que l'instruction déclenchant.
DELIMITER //
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
# normalize email to lowercase before saving
SET NEW.email = LOWER(NEW.email);
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END //
DELIMITER ;
# AFTER INSERT trigger logs the event
CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO audit_log(table_name, row_id, action, at)
VALUES ('users', NEW.id, 'INSERT', NOW());Trigger d'audit (UPDATE)
Un usage courant des triggers est l'audit des changements — capturez OLD vs NEW dans une table d'historique. La comparaison des valeurs doit gérer les NULL (OLD.email <> NEW.email est NULL si un côté est NULL), donc protégez avec des vérifications IS NULL. AFTER DELETE utilise OLD (pas de NEW en suppression). Gardez la logique d'audit légère puisqu'elle s'exécute à chaque changement de ligne. Rappelez-vous que les triggers se déclenchent aussi pour les UPDATE/DELETE en masse, ce qui peut être lent sur de grandes opérations.
DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.email <> NEW.email OR (OLD.email IS NULL) <> (NEW.email IS NULL) THEN
INSERT INTO audit_log(table_name, row_id, field, old_val, new_val, at)
VALUES ('users', NEW.id, 'email', OLD.email, NEW.email, NOW());
END IF;
END //
DELIMITER ;
# a DELETE audit trigger
CREATE TRIGGER trg_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
INSERT INTO archive_users(id, username, email, deleted_at)
VALUES (OLD.id, OLD.username, OLD.email, NOW());Valeurs OLD et NEW
NEW contient les valeurs entrantes de la ligne, OLD les précédentes. Vous pouvez modifier NEW dans BEFORE INSERT/UPDATE pour normaliser ou définir des défauts ; les triggers AFTER et DELETE les voient en lecture seule. SIGNAL SQLSTATE '45000' est la façon standard de lever une erreur personnalisée depuis un trigger ou un programme stocké (45000 signifie « exception utilisateur non gérée »). Utilisez BEFORE pour une validation qui doit annuler le changement ; AFTER pour les effets de bord.
# OLD: the row before the change (available on UPDATE/DELETE)
# NEW: the row after the change (available on INSERT/UPDATE)
# BEFORE INSERT: only NEW, you can modify it
# AFTER INSERT: only NEW, read-only
# BEFORE UPDATE: both OLD and NEW, you can modify NEW
# AFTER UPDATE: both OLD and NEW, read-only
# BEFORE DELETE: only OLD, read-only
# AFTER DELETE: only OLD, read-only
CREATE TRIGGER trg_balance_check
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Balance cannot be negative';
END IF;
END;Trigger sur plusieurs événements (FOLLOWS/PRECEDES)
Depuis 5.7.2, une table peut avoir plusieurs triggers pour le même événement et timing, ordonnés avec FOLLOWS/PRECEDES. Chaque trigger ne gère toujours qu'un seul événement (INSERT, UPDATE ou DELETE) à un timing (BEFORE/AFTER) — pour gérer plusieurs événements, créez plusieurs triggers. L'ordre compte quand les triggers dépendent des effets des autres. Pas de trigger INSTEAD OF dans MySQL (les vues sont updatables directement).
# MySQL allows multiple triggers with the same event/timing on one table (5.7.2+)
# order them with FOLLOWS / PRECEDES
DELIMITER //
CREATE TRIGGER trg_users_after_insert_2
AFTER INSERT ON users
FOR EACH ROW
FOLLOWS trg_users_after_insert
BEGIN
UPDATE user_stats SET total = total + 1 WHERE user_id = NEW.id;
END //
DELIMITER ;
# events: INSERT | UPDATE | DELETE
# timing: BEFORE | AFTER
# a trigger cannot span multiple events; create one per eventGérer les triggers
SHOW TRIGGERS liste tous les triggers (notez le backtick-quoting du nom de colonne 'Table' car réservé). SHOW CREATE TRIGGER retourne le corps complet et le definer. information_schema.triggers donne des métadonnées structurées pour l'audit. DROP TRIGGER IF EXISTS est sûr pour les scripts. Les triggers sont liés à la table — supprimer la table supprime ses triggers. Surveillez le definer : comme les procédures, les triggers tournent avec les privilèges du definer par défaut.
# list triggers
SHOW TRIGGERS\G
SHOW TRIGGERS WHERE `Table` = 'users';
# show definition
SHOW CREATE TRIGGER trg_users_before_insert\G
# query metadata
SELECT trigger_name, event_manipulation, action_timing,
event_object_table, created
FROM information_schema.triggers
WHERE trigger_schema = 'mydb';
# drop a trigger
DROP TRIGGER IF EXISTS trg_users_before_insert;
# triggers fire on the table's schema; definer is recordedTransactions et verrous
Contrôle des transactions
MySQL autocommit par défaut — chaque instruction est sa propre transaction. START TRANSACTION/BEGIN ouvre un bloc explicite clos par COMMIT (sauver) ou ROLLBACK (annuler). Seuls les moteurs transactionnels (InnoDB) supportent les transactions ; MyISAM non. Crucial : les instructions DDL (CREATE/ALTER/DROP/TRUNCATE) commitent implicitement la transaction courante et ne peuvent pas être annulées, donc ne mélangez pas DDL et DML transactionnel en espérant l'atomicité.
# explicit transaction (autocommit is on by default)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;
# alternative syntax
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 50);
UPDATE users SET balance = balance - 50 WHERE id = 1;
COMMIT;
# turn autocommit off for the session
SET autocommit = 0;
-- every statement is now part of a transaction until COMMIT/ROLLBACK
SET autocommit = 1;
# DDL (CREATE/ALTER/DROP) implicitly commits — cannot be rolled backSavepoints
SAVEPOINT marque un point nommé dans une transaction vers lequel on peut partiellement revenir sans annuler toute la transaction. ROLLBACK TO SAVEPOINT annule le travail après le savepoint ; RELEASE SAVEPOINT abandonne le marqueur (le travail reste). Les savepoints permettent d'essayer une étape optionnelle et de récupérer proprement. Ils ne sont pas visibles hors de la transaction et disparaissent au COMMIT/ROLLBACK. Les savepoints imbriqués sont supportés.
START TRANSACTION;
INSERT INTO orders (amount) VALUES (10);
SAVEPOINT sp_after_order;
INSERT INTO order_items (order_id) VALUES (LAST_INSERT_ID());
SAVEPOINT sp_after_items;
-- something risky
UPDATE inventory SET stock = stock - 1 WHERE sku='X';
-- undo just the last step, keep the order + items
ROLLBACK TO SAVEPOINT sp_after_items;
-- release a savepoint when no longer needed
RELEASE SAVEPOINT sp_after_order;
COMMIT;Niveaux d'isolation
Le niveau d'isolation échange cohérence contre concurrence. Le REPEATABLE READ par défaut de MySQL prévient lectures sales et non répétables et, contrairement au standard SQL, évite aussi la plupart des lectures fantômes via le verrouillage next-key. READ COMMITTED est populaire pour moins de verrouillage mais autorise les lectures non répétables. SERIALIZABLE transforme toutes les lectures en lectures verrouillées. InnoDB utilise MVCC donc les lecteurs ne bloquent pas les rédacteurs à aucun niveau sauf SERIALIZABLE.
# four levels, from least to most strict
# READ UNCOMMITTED - dirty reads possible (rarely used)
# READ COMMITTED - no dirty reads; non-repeatable reads possible
# REPEATABLE READ - default; consistent reads within a transaction
# SERIALIZABLE - locks everything; safest, slowest
# set for the session
SET SESSION transaction_isolation = 'REPEATABLE-READ';
# MySQL 8.0+ name; older: tx_isolation
# set globally (new connections inherit it)
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
# inspect
SELECT @@transaction_isolation;
SELECT @@GLOBAL.transaction_isolation;LOCK TABLES
LOCK TABLES prend des verrous au niveau table et est surtout une fonctionnalité de l'ère MyISAM — le verrouillage de lignes d'InnoDB est généralement préférable. Un verrou READ laisse les autres sessions lire mais bloque les écritures ; un verrou WRITE bloque tout le monde. En détenant des verrous, on ne peut accéder qu'aux tables verrouillées. LOCK TABLES commite implicitement toute transaction active, donc il ne compose pas avec les transactions. Préférez SELECT ... FOR UPDATE pour le contrôle de concurrence InnoDB.
# explicit table locks (outside transactions; MyISAM-style)
LOCK TABLES users WRITE, orders READ;
-- only these tables are accessible; users is write-locked
SELECT * FROM users;
UNLOCK TABLES;
# READ lock: this session can read; others can read but not write
# WRITE lock: only this session can read/write; others blocked entirely
# in InnoDB prefer row-level locks (SELECT FOR UPDATE) over LOCK TABLES
# LOCK TABLES implicitly commits the active transactionSELECT ... FOR UPDATE / SHARE
SELECT ... FOR UPDATE prend un verrou exclusif de ligne pour que les autres transactions bloquent si elles tentent de mettre à jour ou verrouiller la même ligne — essentiel pour les séquences read-modify-write comme les transferts. FOR SHARE prend un verrou partagé (les autres peuvent lire mais pas écrire). Les verrous se libèrent au COMMIT/ROLLBACK. 8.0 ajoute NOWAIT (erreur au lieu d'attendre) et SKIP LOCKED (sauter les lignes verrouillées) — parfaits pour des workers de file de tâches qui ne doivent pas se bloquer mutuellement.
START TRANSACTION;
# lock the row so other transactions cannot modify it until commit
SELECT balance INTO @b FROM accounts WHERE id = 1 FOR UPDATE;
IF @b >= 100 THEN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
END IF;
COMMIT; -- releases the lock
# FOR UPDATE: exclusive lock (write intent)
# FOR SHARE: shared lock (read intent, others can read)
# NOWAIT / SKIP LOCKED reduce waiting (8.0+)
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;Deadlocks et détection
InnoDB détecte les deadlocks automatiquement et annule une transaction (la victime) pour que l'autre progresse — on reçoit l'erreur 1213. SHOW ENGINE INNODB STATUS montre le dernier deadlock ; activez innodb_print_all_deadlocks pour les loguer tous. Prévenez les deadlocks en verrouillant les lignes dans un ordre cohérent entre transactions, en gardant les transactions courtes, et en indexant les colonnes verrouillées (sinon les verrous s'étendent à de grandes plages). Écrivez toujours une logique de retry autour des transactions pouvant deadlocker.
# deadlock: two transactions each hold a lock the other needs
# Tx1: locks A, wants B
# Tx2: locks B, wants A
# -> InnoDB detects it and rolls back the victim automatically
# see the last deadlock
SHOW ENGINE INNODB STATUS\G
# log all deadlocks to the error log
SET GLOBAL innodb_print_all_deadlocks = 1;
# mitigate by:
# - locking tables/rows in a consistent order across transactions
# - keeping transactions short
# - adding appropriate indexes (avoid gap locks on full scans)
# - using a lower isolation level where safeGestion des utilisateurs
Créer un utilisateur et l'authentification
Un compte MySQL est toujours 'user'@'host' — l'hôte contrôle d'où l'utilisateur peut se connecter ; '%' est un wildcard pour tout hôte. MySQL 8.0 passe par défaut à caching_sha2_password (plus sûr) que certains vieux clients ne supportent pas — retombez sur mysql_native_password pour eux. Préférez des hôtes à périmètre étroit (10.0.0.%) à '%'. CREATE USER est préféré à l'ancienne syntaxe GRANT ... IDENTIFIED BY, retirée en 8.0.
# create a user that can connect from anywhere
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass!2024';
# restrict to localhost only
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'localpass';
# MySQL 8.0 default plugin is caching_sha2_password
CREATE USER 'dev'@'10.0.%.%' IDENTIFIED WITH caching_sha2_password
BY 'devpass';
# legacy mysql_native_password (needed for some older clients)
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password
BY 'legacypass';
# rename or change host
RENAME USER 'app'@'%' TO 'appuser'@'10.0.0.%';
# drop a user
DROP USER IF EXISTS 'legacy'@'%';Privilèges GRANT
GRANT attribue des privilèges à quatre portées : globale (*.*), base (db.*), table (db.table), et colonne/routine. ALL PRIVILEGES est tout sauf GRANT OPTION. Utilisez le moindre privilège nécessaire — un utilisateur de reporting ne devrait recevoir que SELECT. WITH GRANT OPTION laisse l'utilisateur transmettre ses privilèges (dangereux). FLUSH PRIVILEGES est rarement nécessaire puisque GRANT met à jour les tables en mémoire ; utilisez-le seulement après édition manuelle des tables mysql.
# grant all on a specific database
GRANT ALL PRIVILEGES ON mydb.* TO 'app'@'%';
# grant read-only on one table
GRANT SELECT ON mydb.users TO 'reporting'@'%';
# grant specific privileges
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'editor'@'localhost';
# grant the ability to grant to others (WITH GRANT OPTION)
GRANT SELECT ON mydb.* TO 'lead'@'%' WITH GRANT OPTION;
# grant at server level (use sparingly)
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.0.5';
# reload privilege tables into memory (usually automatic)
FLUSH PRIVILEGES;Privilèges REVOKE
REVOKE retire des privilèges accordés précédemment ; la portée doit correspondre exactement. Pour tout retirer y compris GRANT OPTION, utilisez REVOKE ALL PRIVILEGES, GRANT OPTION. La révocation ne supprime pas l'utilisateur — le compte existe toujours et peut se connecter mais sans privilèges tant qu'on ne les a pas réaccordés. Utilisez SHOW GRANTS pour inspecter les privilèges effectifs d'un utilisateur ; relisez toujours avant de changer les accès. Supprimer un utilisateur entièrement, c'est DROP USER.
# remove specific privileges
REVOKE INSERT, UPDATE ON mydb.* FROM 'editor'@'localhost';
# remove all privileges (but keep the user)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';
# revoke at a specific scope
REVOKE SELECT ON mydb.users FROM 'reporting'@'%';
# see what a user currently has
SHOW GRANTS FOR 'app'@'%';
# show grants for the current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();Rôles (MySQL 8.0+)
Les rôles (8.0+) permettent de regrouper des privilèges et de les assigner à beaucoup d'utilisateurs d'un coup, simplifiant la gestion des accès. Un utilisateur qui reçoit un rôle n'obtient ses privilèges qu'une fois le rôle activé — SET DEFAULT ROLE le rend actif par défaut à la connexion. CURRENT_ROLE() montre les rôles actifs. Changez un rôle une fois et chaque utilisateur membre hérite du changement. Bien plus propre que d'accorder les mêmes privilèges à chaque utilisateur individuellement.
# a role is a named bundle of privileges
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
# grant a role to users
GRANT 'app_read' TO 'alice'@'%', 'bob'@'%';
GRANT 'app_write' TO 'carol'@'%';
# a user must activate a role to use it (default: none)
SET DEFAULT ROLE 'app_read' TO 'alice'@'%';
SET DEFAULT ROLE ALL TO 'alice'@'%';
# see active roles
SELECT CURRENT_ROLE();Gestion des mots de passe
ALTER USER est la façon moderne de gérer les mots de passe (SET PASSWORD est déprécié). PASSWORD EXPIRE force une réinitialisation à la prochaine connexion — utile pour les politiques de rotation. Le composant validate_password impose longueur/complexité à MEDIUM (casse mixte, chiffre, caractère spécial) et STRONG (plus aucun mot du dictionnaire). ACCOUNT LOCK/UNLOCK désactive temporairement un compte sans le supprimer — pratique pour suspendre un accès pendant une investigation.
# change your own password
ALTER USER USER() IDENTIFIED BY 'NewPass!2024';
# change another user's password
ALTER USER 'app'@'%' IDENTIFIED BY 'NewAppPass!2024';
# require a password to be expired (user must reset on next login)
ALTER USER 'app'@'%' PASSWORD EXPIRE;
# enforce password policy (8.0+ validate_password component)
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = 'MEDIUM'; -- 0/LOW,1/MEDIUM,2/STRONG
SET GLOBAL validate_password.length = 12;
# lock an account temporarily
ALTER USER 'app'@'%' ACCOUNT LOCK;
ALTER USER 'app'@'%' ACCOUNT UNLOCK;Afficher grants et utilisateurs
mysql.user est la table système contenant tous les comptes — interrogez-la pour inventorier les utilisateurs et les flags comme account_locked et password_expired. SHOW GRANTS est la vue décodée et plus friendly des privilèges d'un utilisateur. CURRENT_USER() est l'identité utilisée pour les vérifications de privilèges (qui peut différer de USER(), la chaîne de connexion) — important quand on se connecte via un rôle ou un proxy. Auditez-les régulièrement pour les comptes dormants ou sur-privilégiés.
# list all users (MySQL 8.0+)
SELECT user, host, account_locked, password_expired
FROM mysql.user;
# grants for a specific user
SHOW GRANTS FOR 'app'@'%';
# grants for the current session
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();
# who am I?
SELECT CURRENT_USER(), USER();
# active roles for the current session
SELECT CURRENT_ROLE();
# list global privileges
SELECT user, host, Super_priv, Process_priv, Reload_priv
FROM mysql.user;Backup et restauration
mysqldump
mysqldump est l'outil de backup logique classique — il émet des instructions SQL qui recréent la base. --single-transaction donne un snapshot InnoDB cohérent sans verrouillage via une unique transaction REPEATABLE READ. --routines/--triggers/--events incluent les programmes stockés (sinon omis). Pour les grandes bases, préférez Percona XtraBackup ou MySQL Enterprise Backup (backups physiques), bien plus rapides que mysqldump.
# dump a single database to a file
mysqldump -u root -p mydb > mydb.sql
# dump all databases
mysqldump -u root -p --all-databases > alldb.sql
# dump with routines, triggers and events
mysqldump -u root -p --routines --triggers --events mydb > mydb_full.sql
# dump schema only (no data) or data only
mysqldump -u root -p --no-data mydb > schema.sql
mysqldump -u root -p --no-create-info mydb > data.sql
# consistent dump of InnoDB in one transaction
mysqldump -u root -p --single-transaction mydb > mydb.sql
# compress on the fly
mysqldump -u root -p mydb | gzip > mydb.sql.gzRestaurer depuis un dump
Pour restaurer, redirigez le fichier de dump vers le client mysql ou utilisez SOURCE dedans. La base cible doit déjà exister (CREATE DATABASE puis USE). Pour des restaurations plus rapides, désactivez les vérifications de clé étrangère, d'unicité et l'autocommit pendant le chargement, puis réactivez — le dump contient généralement déjà ces gardes. Restaurer un dump compressé avec gunzip évite de décompresser sur disque. Testez toujours les restaurations sur un serveur de staging.
# restore a database dump (database must exist)
mysql -u root -p mydb < mydb.sql
# restore all databases (creates them)
mysql -u root -p < alldb.sql
# restore a compressed dump
gunzip < mydb.sql.gz | mysql -u root -p mydb
# restore from inside the mysql client
SOURCE /path/to/mydb.sql;
# speed up restores: disable checks and autocommit during load
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;
SOURCE /path/to/mydb.sql;
COMMIT;
SET foreign_key_checks = 1;
SET unique_checks = 1;Exporter en CSV
SELECT ... INTO OUTFILE écrit un fichier côté serveur limité au répertoire secure_file_priv (ou entièrement désactivé si secure_file_priv est NULL) et exige le privilège FILE — il ne peut pas écraser un fichier existant. --batch --raw sur le client mysql produit un fichier séparé par tabulations localement sans ces restrictions, idéal pour les cron. Les clauses FIELDS/LINES contrôlent délimiteurs, quoting et échappement pour la compatibilité CSV.
# server-side export to a file (needs FILE privilege + secure_file_priv)
SELECT id, username, email, created_at
FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
# client-side export with the mysql client (no FILE privilege needed)
mysql -u root -p -e "SELECT id, username, email FROM users" \
--batch --raw mydb > users.tsv
# include column headers with a UNION
SELECT 'id','username','email' UNION
SELECT id, username, email FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';Importer depuis CSV
LOAD DATA INFILE est le moyen le plus rapide de charger du CSV — bien plus rapide que des INSERT. La forme côté serveur lit sous un chemin de secure_file_priv ; LOCAL lit un fichier client (activé avec local_infile côté client et serveur). IGNORE 1 LINES saute un en-tête. Utilisez la capture de colonnes par @var puis SET pour transformer les valeurs (ex. parser une date). Pour de très gros chargements, désactivez clés/clés étrangères et utilisez une seule transaction.
# server-side import (needs FILE privilege)
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, username, email, @created_at)
SET created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');
# client-side import (no FILE privilege, sends file from client)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (id, username, email);
# enable LOCAL on the client: --local-infile=1
# and on the server: SET GLOBAL local_infile = 1;Binary log (mysqlbinlog)
Le binary log enregistre toutes les instructions modifiant les données pour la réplication et la restauration ponctuelle. SHOW MASTER STATUS donne le fichier et la position courants (les coordonnées de réplication). mysqlbinlog reconvertit un fichier binlog en SQL (ou événements de ligne) pour inspection ou rejeu — combinez avec des heures ou positions start/stop pour récupérer à un instant précis. PURGE BINARY LOGS récupère le disque ; laissez expire_logs_days/binlog_expire_logs_seconds automatiser cela.
# enable the binary log (my.cnf)
# [mysqld]
# log_bin = mysql-bin
# binlog_format = ROW
# server_id = 1
# show current binlog files and position
SHOW BINARY LOGS;
SHOW MASTER STATUS;
# view events in a binlog file
mysqlbinlog mysql-bin.000123
# filter by time / position
mysqlbinlog --start-datetime="2024-06-01 00:00:00" \
--stop-datetime="2024-06-01 12:00:00" \
mysql-bin.000123 > replay.sql
# replay events to recover
mysql -u root -p < replay.sql
# purge old binlogs
PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';Clone plugin et réplication
Le clone plugin (8.0+) copie physiquement un répertoire de données InnoDB bien plus vite qu'un dump logique, idéal pour provisionner des réplicas ou snapshots. Pour la réplication, pointez un réplica vers une source avec CHANGE REPLICATION SOURCE TO (8.0+ ; anciennement CHANGE MASTER TO) et START REPLICA. GTID (SOURCE_AUTO_POSITION=1) rend le positionnement de réplication robuste. SHOW REPLICA STATUS révèle lag et erreurs. Surveillez Seconds_Behind_Source pour la santé du réplica.
# install the clone plugin (8.0+) for fast physical copy
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
# clone a local data directory to another path
CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql-clone';
# clone from a remote donor (good for provisioning replicas)
CLONE INSTANCE FROM 'donor'@'10.0.0.2':3306
IDENTIFIED BY 'donorpass';
# set up replication from a donor
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.2', SOURCE_PORT=3306,
SOURCE_USER='repl', SOURCE_PASSWORD='replpass',
SOURCE_AUTO_POSITION=1;
START REPLICA; -- 8.0+ (was START SLAVE)
SHOW REPLICA STATUS\GOptimisation des performances
EXPLAIN
EXPLAIN révèle le plan de requête : quel index est utilisé, combien de lignes estimées, et si un filesort ou une table temporaire est nécessaire. La colonne 'type' classe les méthodes d'accès — const/eq_ref sont les meilleurs (lookup d'une ligne), ref/range utilisent un index, index scanne tout l'index, ALL scanne toute la table (souvent mauvais). 'Using index' signifie un scan d'index seul covering ; 'Using filesort'/'Using temporary' signalent des opérations coûteuses à optimiser.
# show the execution plan for a query
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
# extended: extra info + warnings
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;
# format as a tree (8.0+) — more readable
EXPLAIN FORMAT=TREE SELECT ...;
# the key columns to read:
# type - access method (const > eq_ref > ref > range > index > ALL)
# key - the index chosen
# rows - estimated rows scanned
# Extra - 'Using index' (good) / 'Using filesort' / 'Using temporary'EXPLAIN ANALYZE (8.0+)
EXPLAIN ANALYZE (8.0.17+) exécute réellement la requête et rapporte les comptages de lignes et timings réels par itérateur, révélant où le temps passe vraiment — bien plus précis que les estimations du simple EXPLAIN. Utilisez-le sur des requêtes SELECT en staging. Couplez avec SHOW WARNINGS pour voir la requête réécrite par l'optimiseur (après fusion de vues, pliage de constantes). Pour les requêtes d'écriture, utilisez EXPLAIN sur un équivalent en lecture seule ou inspectez avec performance_schema.
# actually runs the query and reports real per-step timing
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
# output shows actual rows, loops and per-iterator ms:
# -> Table scan on users (actual rows=1000, loops=1)
# -> Covering index lookup on o (actual rows=2.4, loops=1000)
# unlike EXPLAIN, this executes the statement (read-only queries only)
# also useful: SHOW WARNINGS after EXPLAIN to see the rewritten query
EXPLAIN SELECT ...;
SHOW WARNINGS\GHints d'optimiseur
Les hints d'optimiseur (/*+ ... */) surchargent le planificateur pour une seule instruction. Plus ciblés qu'un SET GLOBAL et survivent aux redémarrages d'instructions. Utilisez-les pour corriger un mauvais plan, mais traitez aussi la cause sous-jacente (stats périmées, index manquant, données biaisées) — les hints se dégradent à mesure que les données changent. MAX_EXECUTION_TIME plafonne les requêtes en lecture seule, protégeant le serveur des SELECT déchaînés. SET_VAR ajuste une variable de session pour une seule instruction.
# index hints (8.0+ style, after SELECT)
SELECT /*+ INDEX(u idx_email) */ *
FROM users u
WHERE email = '[email protected]';
SELECT /*+ NO_RANGE_OPTIMIZATION(u) */ *
FROM users u WHERE id BETWEEN 1 AND 1000;
# join order / algorithm hints
SELECT /*+ JOIN_ORDER(a,b,c) */ * FROM a JOIN b ON ... JOIN c ON ...;
SELECT /*+ HASH_JOIN(b) */ * FROM a JOIN b ON a.id=b.aid;
SELECT /*+ NLJ(b) */ * FROM a JOIN b ON a.id=b.aid;
# resource hints
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM big_table;
# aborts the query after 1000 ms (read-only)
# SET_VAR hint changes a variable for one statement
SELECT /*+ SET_VAR(sort_buffer_size=16M) */ ... FROM ...;Slow query log
Le slow query log capture les requêtes dépassant long_query_time (en secondes, sous-seconde accepté). Activez log_queries_not_using_indexes pour attraper les scans complets même rapides. mysqldumpslow agrège les requêtes similaires et les classe par temps/compte/lignes pour prioriser les pires. pt-query-digest de Percona offre un reporting bien plus riche (empreintes, percentiles) — l'outil standard d'analyse des slow logs. Rotez et nettoyez le log régulièrement.
# enable in my.cnf
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 2 # log queries slower than 2s
# log_queries_not_using_indexes = 1
# log_slow_admin_statements = 1
# enable at runtime (persists until restart)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
# analyze the slow log with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t sort by total time, -t 10 top 10
# -s c count, -s r rows, -s l lock time
# pt-query-digest (Percona Toolkit) gives richer analysis
pt-query-digest /var/log/mysql/slow.logSHOW PROFILE et Performance Schema
SHOW PROFILE décompose une requête en étapes (ouverture de tables, tri, envoi de données) — pratique mais déprécié. L'équivalent moderne est performance_schema, un framework d'instrumentation à faible overhead activé par défaut en 8.0. Interrogez events_statements_summary_by_digest pour trouver les requêtes consommant le plus de temps agrégé sur le serveur, puis descendez dans events_stages_history_long pour le détail par étape. C'est ainsi qu'on trouve les goulots systémiques, pas seulement les requêtes lentes isolées.
# profiling (deprecated but quick) - per-statement stage timings
SET profiling = 1;
SELECT COUNT(*) FROM huge_table;
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;
# preferred: performance_schema (8.0+, enabled by default)
# top queries by total latency
SELECT digest_text, count_star, sum_timer_wait/1e9 AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
# which stage of which query took longest
SELECT event_name, format_time(timer_wait) AS took
FROM performance_schema.events_stages_history_long
ORDER BY timer_wait DESC LIMIT 10;Conseils d'optimisation des index
La conception des index gagne la plupart des batailles de performance. Suivez la règle du préfixe gauche, évitez les wildcards de tête et n'enveloppez pas les colonnes indexées dans des fonctions (ou utilisez une colonne générée + index). Visez des covering indexes sur les requêtes chaudes. Gardez les conditions WHERE sargable (compatibles argument de recherche) pour qu'un index serve : created_at >= x AND created_at < y bat DATE(created_at)=x. ANALYZE TABLE rafraîchit les statistiques de cardinalité dont dépend l'optimiseur.
# 1. index columns used in WHERE / JOIN / ORDER BY
# with the most selective equality column first
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);
# 2. avoid leading wildcards (defeats the index)
SELECT * FROM users WHERE name LIKE 'A%'; -- OK
SELECT * FROM users WHERE name LIKE '%A'; -- full scan
# 3. don't wrap indexed columns in functions
SELECT * FROM orders WHERE DATE(created_at)='2024-01-01'; -- bad
SELECT * FROM orders WHERE created_at >= '2024-01-01'
AND created_at < '2024-01-02'; -- good
# 4. use a covering index so the query is index-only
EXPLAIN SELECT user_id, status FROM orders WHERE user_id=5;
-- 'Using index' in Extra means no table lookup
# 5. refresh statistics after big loads
ANALYZE TABLE orders;Opérations JSON
Colonne JSON et insertion
Le type JSON stocke le JSON en format binaire validé. Insérez avec un littéral de chaîne JSON ou construisez les valeurs avec JSON_OBJECT() (paires clé/valeur) et JSON_ARRAY(). Un JSON invalide est rejeté à l'insertion. Comme le JSON est stocké en arbre parsé, l'accès aux membres est rapide, mais tout le document se lit ensemble — pour des valeurs scalaires fréquemment interrogées, extrayez-les dans une colonne générée et indexez-la pour le meilleur des deux mondes.
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attrs JSON
);
# insert JSON literals
INSERT INTO products VALUES
(1, 'Widget',
'{"color":"red","size":42,"tags":["new","sale"],"stock":{"ny":5,"la":3}}'),
(2, 'Gadget', '{"color":"blue","in_stock":true}');
# build JSON with functions
INSERT INTO products VALUES (3, 'Thing',
JSON_OBJECT('color','green','tags',JSON_ARRAY('new')));JSON_EXTRACT / -> / ->>
-> extrait un membre JSON (retourne du JSON avec guillemets) ; ->> le retourne comme texte non quoté (le choix courant pour les comparaisons). Les chemins utilisent $. pour la racine, $.key pour les membres, $.arr[0] pour les indices de tableau, et $.a.b pour l'imbrication. JSON_CONTAINS_PATH vérifie l'existence d'une clé. JSON_EXTRACT est la forme fonction de ->. Pour trouver des documents efficacement par une valeur de membre, ajoutez une colonne générée + index sur attrs->>'$.color'.
# extract a member (returns JSON)
SELECT JSON_EXTRACT(attrs, '$.color') FROM products WHERE id=1; -- "red"
SELECT attrs->'$.color' FROM products WHERE id=1; -- "red"
# extract as text (unquoted)
SELECT attrs->>'$.color' FROM products WHERE id=1; -- red
# array element access
SELECT attrs->>'$.tags[0]' FROM products WHERE id=1; -- new
# nested paths
SELECT attrs->>'$.stock.ny' FROM products WHERE id=1; -- 5
# filter rows by a JSON value
SELECT id, name FROM products
WHERE attrs->>'$.color' = 'red';
# exists check
SELECT id FROM products
WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.stock');JSON_SET / INSERT / REPLACE / REMOVE
Ces fonctions retournent un document JSON modifié — il faut réassigner le résultat pour mettre à jour la colonne. JSON_SET fait un upsert (ajouter ou mettre à jour) ; INSERT n'ajoute que le manquant ; REPLACE ne met à jour que l'existant ; REMOVE supprime. JSON_MERGE_PATCH (RFC 7396) fusionne deux documents et retire les clés mises à null — la façon standard d'appliquer un patch partiel. JSON_MERGE_PRESERVE conserve les tableaux/objets existants au lieu de les remplacer.
# SET updates or adds a member (returns the modified document)
UPDATE products
SET attrs = JSON_SET(attrs, '$.color', 'green', '$.weight', 1.2)
WHERE id = 1;
# INSERT adds only if the path doesn't exist
UPDATE products
SET attrs = JSON_INSERT(attrs, '$.code', 'W-100')
WHERE id = 1;
# REPLACE updates only if the path exists
UPDATE products
SET attrs = JSON_REPLACE(attrs, '$.size', 99)
WHERE id = 1;
# REMOVE deletes a member
UPDATE products
SET attrs = JSON_REMOVE(attrs, '$.tags[0]')
WHERE id = 1;
# MERGE_PATCH merges two JSON documents (8.0+)
SET attrs = JSON_MERGE_PATCH(attrs, '{"size":50,"color":null}')JSON_TABLE (8.0+)
JSON_TABLE (8.0+) est le pont du JSON vers le relationnel — il déchiquette un document JSON en lignes qu'on peut joindre, filtrer et agréger comme toute table. La clause columns mappe les chemins JSON vers des colonnes typées. NESTED PATH déballe les tableaux imbriqués en une passe. Cela rend les colonnes JSON pratiques pour les données semi-structurées : stockez du JSON flexible, déchiquetez en relationnel avec JSON_TABLE pour l'analytique SQL, et indexez une colonne générée pour les chemins chauds.
# turn a JSON array of objects into relational rows
SELECT jt.*
FROM products p,
JSON_TABLE(p.attrs, '$.tags[*]'
COLUMNS (
tag VARCHAR(50) PATH '$' -- each element as a column
)
) AS jt
WHERE p.id = 1;
# flatten an array of objects
SELECT p.id, jt.color, jt.size
FROM products p,
JSON_TABLE(p.attrs, '$'
COLUMNS (
color VARCHAR(20) PATH '$.color',
size INT PATH '$.size'
)
) AS jt;
# nested arrays: NESTED PATH
JSON_TABLE(j, '$.stock.*'
COLUMNS (warehouse VARCHAR(5) PATH '$.wh', qty INT PATH '$.qty'))Fonctions d'agrégation JSON
JSON_ARRAYAGG collecte les valeurs en un tableau JSON par groupe ; JSON_OBJECTAGG construit un objet clé->valeur (les clés ultérieures écrasent les duplicatas antérieurs). Combinées à JSON_OBJECT, elles permettent de construire un tableau d'objets pour une réponse API directement en SQL — pratique pour assembler des payloads imbriqués sans post-traitement applicatif. Les entrées NULL sont ignorées par les deux. Surveillez la taille du résultat : un groupe énorme produit une valeur JSON énorme.
# JSON_ARRAYAGG: collect values into a JSON array
SELECT user_id,
JSON_ARRAYAGG(email) AS emails
FROM contacts
GROUP BY user_id;
-- ["[email protected]","[email protected]"]
# JSON_OBJECTAGG: key/value pairs into a JSON object
SELECT user_id,
JSON_OBJECTAGG(type, value) AS prefs
FROM user_prefs
GROUP BY user_id;
-- {"theme":"dark","lang":"en"}
# build a JSON array of objects with JSON_OBJECT + ARRAYAGG
SELECT JSON_ARRAYAGG(
JSON_OBJECT('id', id, 'name', username)
) AS users
FROM users WHERE status='active';Validation et schéma JSON
JSON_VALID vérifie la bonne formation (les insertions l'imposent déjà). JSON_PRETTY reformate pour la lisibilité ; JSON_STORAGE_SIZE rapporte les octets. JSON_SCHEMA_VALID (8.0.17+) laisse une contrainte CHECK imposer un JSON Schema (clés requises, types) — une façon légère de donner une structure aux colonnes JSON sans perdre la flexibilité. JSON_TABLE et JSON_EXTRACT acceptent les clauses ON EMPTY / ON ERROR pour contrôler le comportement quand un chemin est manquant ou invalide.
# validate a string is JSON
SELECT JSON_VALID('{"a":1}'); -- 1
SELECT JSON_VALID('{bad}'); -- 0
# pretty-print and minify
SELECT JSON_PRETTY(attrs) FROM products WHERE id=1;
SELECT JSON_STORAGE_SIZE(attrs) FROM products WHERE id=1; -- bytes
# JSON_SCHEMA_VALID (8.0.17+) enforces a schema with CHECK
CREATE TABLE events (
id INT PRIMARY KEY,
data JSON,
CHECK (JSON_SCHEMA_VALID(
'{"type":"object","required":["ts","type"],
"properties":{"ts":{"type":"string"},
"type":{"type":"string"}}}', data))
);
# JSON_TABLE with ERROR ON ERROR for strict validation
SELECT * FROM JSON_TABLE(j, '$' COLUMNS(v INT PATH '$.x')
DEFAULT NULL ON EMPTY ERROR ON ERROR) AS t;Fonctions de fenêtre
ROW_NUMBER()
ROW_NUMBER() assigne un entier séquentiel unique à chaque ligne de sa partition, ordonné comme spécifié — la façon canonique de classer, dédupliquer et choisir le top-N par groupe. Contrairement à RANK/DENSE_RANK, jamais d'égalités. Le motif de DELETE via CTE supprime les duplicatas en gardant une ligne par clé (on ne peut pas supprimer directement depuis une CTE, donc on sélectionne d'abord les IDs à supprimer). Les fonctions de fenêtre exigent MySQL 8.0+.
# unique sequential number per row within a partition
SELECT username, country,
ROW_NUMBER() OVER (PARTITION BY country ORDER BY created_at) AS rn
FROM users;
# top 3 oldest users per country
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY country ORDER BY birth_date) AS rn
FROM users
)
SELECT * FROM ranked WHERE rn <= 3;
# deduplicate: keep the latest row per email
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS rn
FROM users
)
DELETE FROM users WHERE id IN (
SELECT id FROM ranked WHERE rn > 1
);RANK() et DENSE_RANK()
RANK() donne aux égalités le même rang et saute les rangs suivants (1,1,4,5) ; DENSE_RANK() donne aux égalités le même rang sans trou (1,1,3,4) ; ROW_NUMBER() jamais d'égalités (1,2,3,4). Choisissez selon l'intention : un classement type leaderboard veut généralement RANK ou DENSE_RANK. DENSE_RANK est la façon classique de trouver le N-ième plus élevé par groupe (rank=2 retourne le deuxième salaire distinct). Les trois partagent la même syntaxe OVER().
# all three ranking functions on the same data
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM players;
# with scores 100,100,90,80 the functions return:
# rn: 1,2,3,4 (always sequential)
# rnk:1,2,4,5 (ties share a rank, next rank skips)
# drnk:1,2,3,4 (ties share a rank, no gap)
# find the 2nd-highest salary per department
WITH ranked AS (
SELECT dept, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM employees
)
SELECT DISTINCT dept, salary FROM ranked WHERE dr = 2;LAG() et LEAD()
LAG() et LEAD() jettent un œil à une autre ligne relative à la courante — LAG regarde en arrière, LEAD en avant — sans self join. Ils prennent un offset optionnel (défaut 1) et une valeur par défaut quand l'offset sort de la partition (défaut NULL). Parfaits pour les diffs période sur période, comparaisons glissantes et détection de séries/cassures dans les séquences. Spécifiez toujours un ORDER BY pour que « précédent » soit bien défini.
# compare each row to the previous/next row
SELECT day, sales,
LAG(sales) OVER (ORDER BY day) AS prev_day,
sales - LAG(sales) OVER (ORDER BY day) AS diff,
LEAD(sales) OVER (ORDER BY day) AS next_day
FROM daily_sales;
# with an explicit offset and default value
SELECT day, sales,
LAG(sales, 7, 0) OVER (ORDER BY day) AS same_day_last_week
FROM daily_sales;
# year-over-year comparison
SELECT year, region, revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS prev_year,
revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS yoy
FROM annual_revenue;Agrégats de fenêtre (SUM / AVG OVER)
Les fonctions d'agrégation avec OVER() calculent l'agrégat sur une fenêtre mobile de lignes au lieu de les replier. Sans cadre, SUM OVER (ORDER BY x) donne un total cumulé ; avec ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, c'est une moyenne mobile glissante sur 7 lignes. Un OVER() vide calcule le grand total sur toutes les lignes. PARTITION BY réinitialise la fenêtre par groupe. Les cadres utilisent des bornes ROWS (physique) ou RANGE (par valeurs).
# running total over time
SELECT day, sales,
SUM(sales) OVER (ORDER BY day) AS running_total
FROM daily_sales;
# moving average over a 7-day window
SELECT day, sales,
AVG(sales) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS ma7
FROM daily_sales;
# each row's share of its group total
SELECT user_id, amount,
amount / SUM(amount) OVER (PARTITION BY user_id) AS share
FROM orders;
# grand total alongside detail rows
SELECT username, balance,
SUM(balance) OVER () AS total_balance
FROM users;FIRST_VALUE / LAST_VALUE / NTH_VALUE
FIRST_VALUE/NTH_VALUE retournent la valeur d'une ligne précise dans le cadre. LAST_VALUE est un piège courant : le cadre par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, donc « dernier » signifie la ligne courante, pas la dernière de la partition — étendez le cadre à UNBOUNDED FOLLOWING pour obtenir la vraie dernière valeur. NTH_VALUE retourne NULL jusqu'à l'apparition de la N-ième ligne. Les cadres explicites lèvent l'ambiguïté et méritent d'être toujours spécifiés.
# first value in each partition (per ordering)
SELECT day, region, sales,
FIRST_VALUE(sales) OVER (PARTITION BY region ORDER BY day) AS first_sale
FROM daily_sales;
# LAST_VALUE needs an explicit frame to include current row!
SELECT day, region, sales,
LAST_VALUE(sales) OVER (
PARTITION BY region ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS latest_so_far
FROM daily_sales;
# the Nth value within a partition
SELECT day, region, sales,
NTH_VALUE(sales, 3) OVER (
PARTITION BY region ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third
FROM daily_sales;NTILE et PERCENT_RANK
NTILE(n) découpe les lignes ordonnées en n seaux aussi équitablement que possible (les premiers seaux reçoivent la ligne excédentaire quand ça ne divise pas rond) — pratique pour quartiles/deciles en reporting. PERCENT_RANK() est le rang relatif en fraction 0..1 ; CUME_DIST() est la distribution cumulée (fraction des lignes au-dessous ou égales à la valeur courante). Utilisez NTILE pour la mise en seaux et PERCENT_RANK/CUME_DIST pour l'analytique de type percentile.
# divide rows into N equal buckets (1..N)
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- quartile 1 = top 25%, quartile 4 = bottom 25%
# relative rank as a fraction 0..1
SELECT name, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS pct,
CUME_DIST() OVER (ORDER BY salary) AS cum
FROM employees;
-- pct = (rank-1)/(N-1), 0 = lowest, 1 = highest
-- cum = (rows up to and including current)/N
# CUME_DIST: fraction of rows with a value <= currentGestion des dates et heures
Fonctions date/heure courantes
NOW()/CURRENT_TIMESTAMP retournent la même valeur sur toute une instruction (cohérent) ; SYSDATE() reflète le temps réel à chaque appel (évitez-le — non déterministe et casse la réplication). Stockez les timestamps en UTC (UTC_TIMESTAMP) et convertissez à l'affichage en heure locale. NOW(6) donne la précision microseconde. UNIX_TIMESTAMP convertit vers/depuis des secondes epoch — pratique pour l'interop avec les langages utilisant l'epoch.
# current timestamp (date + time) in one call
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME;
# current date / time separately
SELECT CURDATE(), CURRENT_DATE; -- '2024-07-02'
SELECT CURTIME(), CURRENT_TIME; -- '14:30:00'
# UTC equivalents (store UTC, display local)
SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();
# microsecond precision with (6)
SELECT NOW(6), CURRENT_TIMESTAMP(6);
# UNIX timestamp (seconds since epoch) and back
SELECT UNIX_TIMESTAMP(); -- 1719912600
SELECT FROM_UNIXTIME(1719912600); -- '2024-07-02 12:30:00'
SELECT FROM_UNIXTIME(1719912600, '%Y-%m-%d');DATE_FORMAT et STR_TO_DATE
DATE_FORMAT formate une date/heure en chaîne selon des spécificateurs — notez %i minuscule (minutes) et %s (secondes), un piège courant. STR_TO_DATE est l'inverse, parsant une chaîne en DATE/TIME/DATETIME. Utilisez toujours STR_TO_DATE pour parser la saisie utilisateur plutôt que de compter sur la conversion implicite chaîne-vers-date, qui dépend des réglages de format et est fragile. Stockez les dates en types DATE/DATETIME, pas en chaînes.
# format a date/time to a string
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-07-02 14:30:00
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y'); -- 02/07/2024
SELECT DATE_FORMAT(NOW(), '%W, %M %e %Y'); -- Tuesday, July 2 2024
# parse a string into a date
SELECT STR_TO_DATE('02/07/2024', '%d/%m/%Y'); -- 2024-07-02
SELECT STR_TO_DATE('Jul 2, 2024 2:30 PM', '%b %e, %Y %h:%i %p');
# common specifiers:
# %Y 4-digit year %y 2-digit year
# %m month(01-12) %c month(1-12) %M month name %b abbreviated
# %d day(01-31) %e day(1-31) %j day of year
# %H hour(00-23) %h hour(01-12) %i minutes %s seconds
# %W weekday name %a abbreviated %p AM/PMDATE_ADD / DATE_SUB et INTERVAL
DATE_ADD/DATE_SUB avec INTERVAL est l'arithmétique de dates canonique ; le raccourci + INTERVAL / - INTERVAL est plus propre. INTERVAL gère correctement les bascules de mois/année (31 janv. + 1 mois = 29 févr. une année bissextile). LAST_DAY retourne le dernier jour du mois de l'argument — pratique pour la logique de fin de mois. Les unités composées comme YEAR_MONTH prennent '1-2' (1 an 2 mois). N'utilisez jamais d'arithmétique de chaînes pour les dates — elle casse sur les cas limites.
# add an interval to a date
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- 2024-02-29
SELECT DATE_ADD('2024-01-31', INTERVAL 1 DAY); -- 2024-02-01
SELECT '2024-01-31' + INTERVAL 1 MONTH; -- shorthand
# subtract
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);
SELECT NOW() - INTERVAL 1 HOUR;
# interval units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
# SECOND_MICROSECOND MINUTE_MICROSECOND ... YEAR_MONTH (compound)
# add to a datetime (keeps the time component)
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);
# last day of next month
SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));DATEDIFF / TIMESTAMPDIFF
DATEDIFF retourne des jours entiers (date1 - date2, en ignorant l'heure). TIMESTAMPDIFF retourne la différence dans l'unité choisie (unité, de, vers) et tronque vers zéro — donc TIMESTAMPDIFF(YEAR, ...) ne donne l'âge que si l'anniversaire est déjà passé cette année ; pour un âge exact, soustrayez 1 quand le mois/jour n'est pas encore arrivé. Attention à l'ordre des arguments : DATEDIFF est (a,b) = a-b ; TIMESTAMPDIFF est (unité, de, vers) = vers-de.
# DATEDIFF: difference in DAYS (date1 - date2)
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365
SELECT DATEDIFF(NOW(), birth_date) AS days_alive FROM users;
# TIMESTAMPDIFF: difference in a chosen unit (date1, date2) -> date2 - date1
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age;
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-07-02');
SELECT TIMESTAMPDIFF(MICROSECOND, '2024-07-02 09:00:00', NOW());
# units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
# (TIMESTAMPDIFF argument order is (unit, from, to))
# TIME values: TIMEDIFF returns a TIME
SELECT TIMEDIFF('18:00:00', '09:30:00'); -- 08:30:00EXTRACT et parties de date
EXTRACT(unité FROM date) retourne une seule composante numérique ; YEAR()/MONTH()/DAY() etc. sont des raccourcis pour les plus courantes ; DAYNAME/MONTHNAME retournent des noms. Grouper par YEAR()/MONTH() d'un timestamp est la façon standard de construire des rapports mensuels. Notez que DAYOFWEEK est 1=dimanche..7=samedi tandis que WEEKDAY est 0=lundi..6=dimanche — choisissez celui qui matche votre convention. WEEK() a des modes pour le jour de début de semaine et le comptage.
# pull out a single component
SELECT EXTRACT(YEAR FROM created_at) AS yr,
EXTRACT(MONTH FROM created_at) AS mon,
EXTRACT(DAY FROM created_at) AS dy
FROM orders;
# dedicated functions for each part
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at),
HOUR(created_at), MINUTE(created_at), SECOND(created_at),
DAYOFWEEK(created_at), DAYOFYEAR(created_at),
WEEK(created_at), QUARTER(created_at);
# names instead of numbers
SELECT DAYNAME(created_at), MONTHNAME(created_at);
# group orders by month for a report
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mon,
COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY yr, mon
ORDER BY yr, mon;Conversion de fuseaux horaires
Réglez le time_zone de session pour que les colonnes TIMESTAMP s'affichent en heure locale ; les zones nommées (Asia/Shanghai) exigent que les tables de fuseaux système soient chargées via mysql_tzinfo_to_sql. CONVERT_TZ convertit un datetime littéral entre zones. Bonne pratique : stockez toutes les heures en UTC (TIMESTAMP ou DATETIME en UTC), réglez le time_zone de chaque session pour l'affichage, et convertissez avec CONVERT_TZ au besoin. Cela évite l'ambiguïté entre utilisateurs de régions différentes.
# see the session time zone
SELECT @@session.time_zone, @@global.time_zone;
# set the session time zone (UTC offset or named, needs tz data loaded)
SET time_zone = '+08:00'; # Asia/Shanghai offset
SET time_zone = 'Asia/Shanghai'; # named zone (load tz tables first)
# convert a datetime between time zones
SELECT CONVERT_TZ('2024-07-02 09:00:00', '+00:00', '+08:00');
-- 2024-07-02 17:00:00
# named zones require the timezone tables to be loaded:
# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
# TIMESTAMP columns auto-convert to the session time zone on display;
# DATETIME columns do not -- they store the literal value
# store all times in UTC and convert on the way out
SELECT CONVERT_TZ(created_at, '+00:00', @@session.time_zone)
FROM orders;Snippets MySQL associés
Copy-paste ready code for common tasks.
SELECT with JOINs
Combine rows from multiple tables using JOINs.
Subqueries
Use scalar, IN, EXISTS, and derived-table subqueries.
Indexes
Create single, composite, unique, and fulltext indexes.
Transactions
Use BEGIN, COMMIT, ROLLBACK, and isolation levels.
Stored Procedures
Define reusable procedural routines with parameters.
Triggers
Run logic automatically on INSERT, UPDATE, or DELETE.
Views
Create virtual tables to simplify and secure queries.
Query Optimization
Diagnose and optimize slow queries with EXPLAIN and indexes.
Was this helpful?