Mon wiki - informatique:databases:mysql

changement_du_mot_de_passe_root_ubuntu_16.04

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:52:27 +0000

Changement du mot de passe root (Ubuntu 16.04)

Sur Ubuntu 16.04, le mécanisme de gestion des mots de passe ne permet pas d'avoir un mot de passe vide lors de l'installation. Pour pouvoir quand même utiliser un mot de passe vide, il faut réaliser les quelques manipulations suivantes :

# Arrêt de mysql
sudo service mysql stop
 
# Démarrage de mysql en "safe-mode"
sudo mysqld_safe --skip-grant-tables &
 
# Mise à jour du mot de passe
mysql -u root -e 'update user set authentication_string="" where User="root"' mysql
 
# Mise à jour du mécanisme de gestion des mots de passe
mysql -u root -e 'update user set plugin="mysql_native_password" where User="root"' mysql
 
# Remise à zéro des privilèges
mysql -u root -e 'FLUSH PRIVILEGES' mysql
 
# Démarrage de mysql
sudo service mysql start

Cette manipulation n'est pas recommandée et sera à refaire à chaque mise à jour de MySql

comments

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:37:36 +0000

Commentaires

Pour un code de qualité, il faut commenter et le faire de manière intelligente. L'environnement de base de données n'échappe pas à cette règle.

Dans les options de table

Il est possible d'ajouter un commentaire dans les options d'une table. Il est possible d'ajouter ce commentaire à sa création ou à sa mise à jour. Attention, la taille du commentaire ne peut pas dépasser 60 caractères.

ALTER TABLE <table>
COMMENT = '<comment>'

Dans les options de colonne

Il est possible d'ajouter un commentaire dans les options d'une colonne. Il est possible d'ajouter ce commentaire à sa création ou à sa mise à jour. Attention, la taille du commentaire ne peut pas dépasser 255 caractères.

ALTER TABLE <table>
MODIFY COLUMN <column> <datatype> COMMENT '<comment>'

Dans les options de procédure stockée

Il est possible d'ajouter un commentaire dans les options d'une procédure stockée. Il est possible d'ajouter ce commentaire à sa création ou à sa mise à jour.

ALTER PROCEDURE <proc>
COMMENT '<comment>'

Dans la procédure stockée

Il est également recommandé d'ajouter des commentaires dans les procédures stockés et les scripts. Il existe trois syntaxes pour cela.

# Première syntaxe
# <comment>
 
-- Deuxième syntaxe
-- <comment>
 
/* Troisième syntaxe, peut être affiché sur plusieurs lignes */
/* <comment> */

Dans la requête

Il est possible d'ajouter un commentaire directement dans une requête SQL. Ce qui peut s'avérer très pratique lors de l'affichage des requêtes en cours d'exécution.

-- Première syntaxe
SELECT
/* <comment> */
<column>
FROM <table>
 
-- Deuxième syntaxe
SELECT
-- <comment>
<column>
FROM <table>

export_de_donnees

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:50:12 +0000

Export de données

Export d'une base de données

Il est possible de le faire avec PhpMyAdmin mais c'est très long et pas forcément fiable. Il vaut mieux utiliser l'outil mysqldump installé en même temps que mysql. Voici un exemple d'utilisation :

mysqldump 
    --add-drop-database
    --skip-comments
    --complete-insert
    --disable-keys
    --extended-insert
    --routines
    --host=<hostname>
    --password
    --user=<username>
    --result-file=<filename>
    --databases <database>

--add-drop-database ajoute une requête de suppression de base avant chaque requête de création de base.
--skip-comments n'ajoute pas les commentaires dans le fichier généré.
--complete-insert génère des requêtes d'insertion contenant les noms de colonnes.
--disable-keys ajoute une requête pour désactiver les clés avant la requête d'insertion et une requête pour activer les clés après.
--extended-insert utilise la syntaxe multi-lignes pour les requêtes d'insertion.
--routines extrait les procédures stockées et les fonctions des bases de données sauvegardées.
--host indique l'hôte de la base de donnée à utiliser.
--password indique le mot de passe à utiliser.
--user indique l'utilisateur à utiliser.
--result-file indique le nom du fichier à générer.
--databases indique que les arguments sont des bases et ajoute les requêtes de création de base.

Pour plus d'option, il faut regarder dans la documentation officielle.

Export d'une table

Comme pour l'exportation d'une base de données, il est possible de le faire avec PhpMyAdmin mais il vaut mieux utiliser directement mysql. Voici un exemple d'utilisation :

mysqldump 
    --skip-comments
    --complete-insert
    --disable-keys
    --extended-insert
    --host=<hostname>
    --password
    --user=<username>
    --result-file=<filename>
    --single-transaction
    <database>
    <table 1> [<table 2> <table 3>]

procedures_et_fonctions

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:37:36 +0000

Procédures et fonctions

Interruption de l'exécution d'une procedure

-- Avec l'utilisation d'une étiquette
CREATE PROCEDURE <nom de la procédure>()
this_proc:BEGIN
    IF <condition> THEN
        LEAVE this_proc;
    END IF;
    ...
END;
 
-- Avec l'utilisation d'un bloc IF
CREATE PROCEDURE <nom de la procédure>()
BEGIN
    IF <condition> THEN
        ...
    END IF;
END;

La première méthode est plus intéressante car elle permet de garder un code propre, même quand le nombre de d'interruption augmente.
Avec la seconde méthode, il sera nécessaire d'imbriquer de multiples blocs IF, le rendant moins lisible.

Définition d'une procédure

Utilisateur de création

S'il n'est pas explicitement mentionné, l'utilisateur utilisé pour la définition de la procédure sera utilisé.
Il est possible de spécifier l'utilisateur à utiliser :

CREATE DEFINER = `<utilisateur>`@`<hôte>` PROCEDURE <nom de la procédure>()
BEGIN
   ...
END

Note : il n'est pas nécessaire que l'utilisateur existe sur la base de données.

Type de sécurité appliquée

Il existe 2 types de sécurité pour l'exécution des procédures :

DEFINER : utilise les droits de l'utilisateur utiliser pour la définition de la procédure,
INVOKER : utilise les droits de l'utilisateur appelant la procédure.

S'il n'est pas explicitement mentionné, le type de sécurité appliqué sera DEFINER.
Lors de la création d'une procédure, il est possible de définir le type de sécurité à appliquer.

CREATE PROCEDURE <nom de la procédure>() SQL SECURITY INVOKER
BEGIN
   ...
END

Note : si l'utilisateur n'existe pas et que le type de sécurité appliquée est DEFINER, l'erreur suivante sera générée:

ERROR 1449 (HY000): The user specified as a definer ('<utilisateur>'@'%') does not exist

Commentaires

CREATE PROCEDURE <nom de la procédure>()
COMMENT '<commentaire>'
BEGIN
    ...
END

Fonctions utiles

Ajout d'une durée normalisée à une date

Selon la norme ISO 8601, on peut normaliser les durées utilisées pour le calcul de dates. MySQL permet d'ajouter des durées à des dates mais en utilisant une syntaxe n'utilisant pas les durées normalisées. La fonction ¹⁾qui suit permet de faire ça mais uniquement pour les jours, les semaines, les mois et les années.

CREATE FUNCTION ADD_ISO_DURATION(StartDate DATE, Duration VARCHAR(45))
RETURNS DATE
BEGIN
  DECLARE Pos INTEGER;
 
  IF StartDate IS NULL OR Duration IS NULL OR LENGTH(Duration) = 0 THEN
    RETURN StartDate;
  END IF;
 
  IF Duration REGEXP '^P[0-9]+W$' THEN
    SET StartDate = DATE_ADD(StartDate, INTERVAL SUBSTR(Duration, 2, LENGTH(Duration) - 2) WEEK);
    RETURN StartDate;
  END IF;
 
  IF Duration NOT REGEXP '^P([0-9]+Y)?([0-9]+M)?([0-9]+D)?$' THEN
    RETURN NULL;
  END IF;
 
  SET Duration = SUBSTR(Duration, 2);
 
  SET Pos = LOCATE('Y', Duration);
  IF Pos <> 0 THEN
    SET StartDate = DATE_ADD(StartDate, INTERVAL SUBSTR(Duration, 1, Pos - 1) YEAR);
    SET Duration = SUBSTR(Duration, Pos + 1);
  END IF;
 
  SET Pos = LOCATE('M', Duration);
  IF Pos <> 0 THEN
    SET StartDate = DATE_ADD(StartDate, INTERVAL SUBSTR(Duration, 1, Pos - 1) MONTH);
    SET Duration = SUBSTR(Duration, Pos + 1);
  END IF;
 
  SET Pos = LOCATE('D', Duration);
  IF Pos <> 0 THEN
    SET StartDate = DATE_ADD(StartDate, INTERVAL SUBSTR(Duration, 1, Pos - 1) DAY);
  END IF;
 
  RETURN StartDate;
END

¹⁾

Largement inspirée de iso8601 - MySQL - Convert ISO-8601 Duration to Interval for DATE_ADD - Stack Overflow

requetes_administration

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:37:36 +0000

Requêtes d'administration

Affichage des erreurs

SHOW ERRORS

Plus d'informations ici

Affichage des alertes

SHOW WARNINGS

Plus d'informations ici

Affichage de la description d'une table

DESC <table>

Plus d'informations ici

Purge des caches

/* Ferme les tables ouvertes, force la fermeture des tables utilisées, purge le tampon de requêtes */
FLUSH TABLES
 
/* Purge le cache des hôtes lorsqu'ils changent d'IP ou lorsqu'ils génèrent trop de connexions */
FLUSH HOSTS

Plus d'informations ici

Changement de mot de passe

SET PASSWORD = PASSWORD('<password>')

Changer les droits d'un utilisateur

/* Pour donner les droits à tous les hôtes, il faut utiliser % */
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'<host>';
 
FLUSH PRIVILEGES;

Récupérer la liste des index d'une base de données

SELECT DISTINCT s.INDEX_SCHEMA
, s.INDEX_NAME
, s.TABLE_NAME
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
AND s.INDEX_NAME = t.CONSTRAINT_NAME
WHERE t.CONSTRAINT_NAME IS NULL
AND s.TABLE_SCHEMA = '<database>'

Gérer les processus en cours

-- Affiche la liste des processus en cours
SHOW processlist;
 
-- Arrête un processus en le sélectionnant par son id
KILL <id>;

Comparer le schéma de deux tables

SELECT COLUMN_NAME
, ORDINAL_POSITION
, DATA_TYPE
, COLUMN_TYPE
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN ('<table1>', '<table2>')
GROUP BY COLUMN_NAME
, ORDINAL_POSITION
, DATA_TYPE
, COLUMN_TYPE
HAVING COUNT(1)=1

La requête précédente est un peu violente car elle valide que les champs sont à la même position. En voici une qui est moins restrictive car elle ne fait pas cette vérification :

SELECT COLUMN_NAME
, DATA_TYPE
, COLUMN_TYPE
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN ('<table1>', '<table2>')
GROUP BY COLUMN_NAME
, DATA_TYPE
, COLUMN_TYPE
HAVING COUNT(1)=1

Récupérer la liste des tables avec une colonne manquante

Dans certaines tables, on travaille avec les couples de colonnes createdAt/createdBy, updatedAt/updatedBy et/ou deletedAt/deletedBy. Mais parfois, on oublie l'une des deux colonnes. Trouver ces tables n'est pas un vrai problème quand on n'a que quelques tables mais avec plusieurs centaines, il vaut mieux écrire une requête appropriée.

SELECT @tableSchema := 'database'
, @columnName := 'deleted';
 
-- Première écriture de la requête (naïve). Le temps d'exécution est très long (≈ 10 secondes).
SELECT t.TABLE_NAME
FROM information_schema.TABLES AS t
WHERE t.TABLE_SCHEMA = @tableSchema
AND EXISTS (
	SELECT 1
	FROM information_schema.`COLUMNS` AS c
	WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA
	AND c.TABLE_NAME = t.TABLE_NAME
	AND c.COLUMN_NAME = CONCAT(@columnName, 'At')
)
AND NOT EXISTS (
	SELECT 1
	FROM information_schema.`COLUMNS` AS c
	WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA
	AND c.TABLE_NAME = t.TABLE_NAME
	AND c.COLUMN_NAME = CONCAT(@columnName, 'By')
);
 
-- Seconde écriture de la requête. Le temps d'exécution est nettement meilleur (≈ 30 milli-secondes) pour un résultat identique.
SELECT c.TABLE_NAME
FROM information_schema.`COLUMNS` AS c
WHERE c.TABLE_SCHEMA = @tableSchema
AND c.COLUMN_NAME LIKE CONCAT(@columnName, '%')
GROUP BY c.TABLE_NAME
HAVING COUNT(1) = 1;

Calculer la taille des données

-- Afficher la taille globale de l'ensemble des bases de données
SELECT TABLE_SCHEMA AS "Nom de la base de données"
, CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 0), " MB") AS "Taille de la base de données"
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
 
-- Afficher le détail de la taille des tables d'une base de données
SELECT TABLE_NAME AS "Nom de la table"
, TABLE_ROWS AS "Nombre de lignes"
, CONCAT(ROUND(DATA_LENGTH / 1024 / 1024, 2), " MB") AS "Taille des données"
, CONCAT(ROUND(INDEX_LENGTH / 1024 / 1024, 2), " MB") AS "Taille de l'index"
, CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2), " MB") AS "Taille de la table"
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "resamania"
ORDER BY (DATA_LENGTH + INDEX_LENGTH)
, TABLE_NAME

Logguer les requêtes

-- Définition de la destination (mysql.general_log)
SET GLOBAL log_output = 'TABLE';
-- Activation du logging
SET GLOBAL general_log = 'ON';
-- Désactivation du logging
SET GLOBAL general_log = 'OFF';
-- Purge de la table (uniquement quand le logging est arrêté)
TRUNCATE TABLE mysql.general_log

Pour logguer dans un fichier :

-- Définition de la destination
SET GLOBAL log_output = 'FILE';
-- Définition du fichier
SET GLOBAL general_log_file = "</path/to/file>";

requetes_amusantes

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:54:10 +0000

Requêtes amusantes

Multiplier le nombre de lignes d'une table

Cette requête permet de multiplier le nombre d'enregistrements d'une table en fonction d'un multiplicateur de cette même table. Dans l'exemple suivant, on ne pourra pas générer plus de 9 lignes. Il suffit d'augmenter le nombre de UNION pour pouvoir générer plus d'enregistrements.

SELECT id
, multiplier
FROM <table>
, (
    SELECT 0 AS `rank`
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
) AS multiplier
WHERE multiplier.`rank` < <table>.multiplier

Il est possible de se passer des UNION en remplaçant la sous-requête par une table contenant les nombres de 0 à n.

SELECT id
, multiplier
FROM <table>
, multiplier
WHERE multiplier.`rank` < <table>.multiplier

Trier des numéros de version

Cette requête permet de trier des numéros de versions (5, 4.1, 3.2.5, …) entre eux.

SELECT version
, @version := IF(LOCATE('.',version) = 0, CONCAT(version, '.0.0'), version) -- Ajoute les versions mineure et hotfix quand on a qu'une version majeure
, @version := IF(@version REGEXP '^.+\..+\..+$' = 1, @version , CONCAT(@version, '.0')) -- Ajoute la version hotfix quand on a qu'une version mineure
, CONCAT(
 LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(@version, '.', 1), '.', -1), 10, '0'), -- Extrait la version majeure et la préfixe avec des 0
 LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(@version, '.', 2), '.', -1), 10, '0'), -- Extrait la version mineure et la préfixe avec des 0
 LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(@version, '.', 3), '.', -1), 10, '0')  -- Extrait la version hotfix et la préfixe avec des 0
) AS concat
FROM docs
ORDER BY concat DESC

Limitations :

Cette requête ne permet de gérer que des versions dont le format correspond au versionnage sémantique.
Seules les versions ne contenant que des chiffres sont supportées.
Les sections du numéro de version ne peuvent pas excéder 10 caractères.

Récupération des "trous" dans une séquence

Récupération sous la forme de plages d'identifiants

Cette requête ne permet pas de récupérer le premier “trou” de séquence si celui-ci commence au premier identifiant.

SELECT range_start
, range_end
FROM (
	SELECT  @rownum1 := @rownum1 + 1 AS rownum_end
	, table1.id - 1 AS range_end
	FROM <table> AS table1
	JOIN (SELECT @rownum1 := 0) AS dummy
	LEFT JOIN <table> AS table2 ON table2.id = table1.id - 1
	WHERE table2.id IS NULL
	AND table1.id <> (SELECT MIN(id) FROM <table>)
) AS e
, (
	SELECT @rownum2 := @rownum2 + 1 AS rownum_start
	, table1.id + 1 AS range_start
	FROM <table> AS table1
	JOIN (SELECT @rownum2 := 0) AS dummy
	LEFT JOIN <table> AS table2 ON table2.id = table1.id + 1
	WHERE table2.id IS NULL
	AND table1.id <> (SELECT MAX(id) FROM <table>)
) AS s
WHERE rownum_end = rownum_start

Récupération de la liste complète des identifiants

SELECT dummy.`row`
FROM (
	SELECT @ROW := @ROW + 1 AS `row`
	FROM 
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy1,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy2,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy3,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy4,
		(SELECT @ROW := 0) numbers
	WHERE @ROW <= (SELECT MAX(id) FROM <table>)
) dummy
LEFT JOIN <table> AS table1 ON table1.id = dummy.`row`
WHERE table1.id IS NULL

Générer une liste de 1000 dates

Cette méthode peut être étendue pour générer plus de dates ou au contraire restreinte pour en générer moins. Dans l'exemple suivant, la date de départ sera la plus petite date, mais il est possible qu'elle devienne la plus grande en changeant le signe de l'opération. Pour que les dates soient ordonnées, la requête units doit impérativement être avant la requête tens qui elle même doit impérativement être avant la requête hundreds.

SELECT '2013-04-01' + INTERVAL (hundreds.val + tens.val + units.val) DAY AS DAY
FROM (
    SELECT 0 AS val
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
) AS units
CROSS JOIN (
    SELECT 0 AS val
    UNION ALL SELECT 10
    UNION ALL SELECT 20
    UNION ALL SELECT 30
    UNION ALL SELECT 40
    UNION ALL SELECT 50
    UNION ALL SELECT 60
    UNION ALL SELECT 70
    UNION ALL SELECT 80
    UNION ALL SELECT 90
) AS tens
CROSS JOIN (
    SELECT 0 AS val 
    UNION ALL SELECT 100
    UNION ALL SELECT 200
    UNION ALL SELECT 300
    UNION ALL SELECT 400
    UNION ALL SELECT 500
    UNION ALL SELECT 600
    UNION ALL SELECT 700
    UNION ALL SELECT 800
    UNION ALL SELECT 900
) AS hundreds

Générer une liste de nombre

Cette requête permet de générer une liste de 10000 valeurs consécutives. Pour avoir plus de valeur, il faut ajouter une ligne identique aux lignes dummy existantes.

SELECT @ROW := @ROW + 1 AS `row`
FROM 
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy1,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy2,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy3,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy4,
	(SELECT @ROW := 0) numbers

Quine

Une quine est un programme sans entrée qui génère son propre code source.

SELECT REPLACE(REPLACE('SELECT REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS Quine',CHAR(34),CHAR(39)),CHAR(36),'SELECT REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS Quine') AS Quine

Code ASCII	Caractère
34	“
36	$
39	'

requetes_donnees

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:54:05 +0000

Requêtes de manipulation de données

Utiliser le caractère d'échappement dans une requête

Lorqu'on utilise le caractère d'échappement (\) dans une requête, il faut prendre quelques précautions. En effet, il faut le doubler pour pouvoir l'utiliser car le parseur en enlève un.

-- Ne fonctionne pas
SELECT 1 FROM <table> WHERE <column> = '1\1'
INSERT INTO <table> (<column>) VALUES ('1\1')
-- Fonctionne
SELECT 1 FROM <table> WHERE <column> = '1\\1'
INSERT INTO <table> (<column>) VALUES ('1\\1')

Lorsqu'on utilise la méthode de recherche LIKE, il faut prendre des précautions supplémentaires. En effet, il faut le quadrupler car le parseur en enlève un et le motif également. On peut utiliser un caractère d'échappement différent et dans ce cas, il ne faut que le doubler.

-- Ne fonctionne pas
SELECT 1 FROM <table> WHERE <column> LIKE '%\%'
SELECT 1 FROM <table> WHERE <column> LIKE '%\\%'
SELECT 1 FROM <table> WHERE <column> LIKE '%\\\%'
-- Fonctionne
SELECT 1 FROM <table> WHERE <column> LIKE '%\\\\%'
SELECT 1 FROM <table> WHERE <column> LIKE '%\\%' ESCAPE '|'

Extrait de la documentation :

Because MySQL uses C escape syntax in strings (for example, \n to represent a newline character), you must double any \ that you use in LIKE strings. For example, to search for \n, specify it as \\n. To search for \, specify it as \\\\; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Calculer une moyenne géométrique

Il n'existe pas de fonction d'agrégation pour réaliser cela facilement. Cependant, en utilisant les mathématiques décrites dans cet article, on peut la calculer nous même.

SELECT EXP(AVG(LN(<column>)))
FROM <table>

Afficher le numéro de ligne

Il n'existe pas de pseudo-colonne pour numéroter les lignes comme il en existe sur la base de données Oracle. On peut cependant recréer cette pseudo-colonne. Ça surcharge la requête, mais ça peut être utile si on veut utiliser le résultat tel quel.

SELECT @rownum := @rownum + 1 AS rownum
, <column>
FROM <table>
, (SELECT @rownum := 0) AS <alias>

Pour plus d'information, voir ici

Il est également possible d'introduire des sauts dans la numérotation pour, par exemple, numéroter les lignes par groupes :

SELECT a
, b
, @num := IF(@a = a, @num + 1, 1) AS rownum
, @a := a
FROM <table>
, (SELECT @a := '', @num := 1) AS <alias>

Pour plus d'information, voir ici

Lors de l'écriture de ce genre de requêtes dans Talend, il faut faire attention à choisir des noms de variables en minuscule.

Mise à jour d'une table avec des données variables

Quand on veut mettre à jour une table avec des données provenant d'une requête, voici la structure de la requête à utiliser. On retrouve facilement cette syntaxe en cherchant sur le web ou en testant rapidement, mais c'est encore mieux de savoir où trouver l'information rapidement quand ce n'est pas quelque chose que l'on fait régulièrement.

UPDATE <table-1>
SET <table-1>.<column-1> = (
    SELECT <table-2>.<column-1>
    FROM <table-2>
    WHERE <table-1>.id = <table-2>.id
)
WHERE EXISTS (
    SELECT 1
    FROM <table-2>
    WHERE <table-1>.id = <table-2>.id
)

Après avoir utilisé cette syntaxe depuis pas mal de temps, je viens de me rendre compte qu'il existe une syntaxe beaucoup plus facile et surtout qui autorise la mise à jour de plus d'un champ à la fois. La cerise sur le gâteau est qu'elle autorise l'utilisation d'alias de tables.

UPDATE <table-1>, <table-2>
SET <table-1>.<column-1> = <table-2>.<column-1>
, <table-1>.<column-2> = <table-2>.<column-2>
WHERE <table-1>.id = <table-2>.id

Créer une table à partir d'une autre table

Méthode 1 :

-- Copie de la structure et des données
CREATE TABLE <new_table>
AS
SELECT *
FROM <old_table>
 
-- Copie de la structure uniquement
CREATE TABLE <new_table>
AS
SELECT *
FROM <old_table>
LIMIT 0

Méthode 2 :

-- Copie de la structure uniquement
CREATE TABLE <new_table>
LIKE <old_table>

Lors de l'utilisation de la méthode 1, on perd l'ensemble des index de la table ainsi que certaines propriétés (auto_increment, comment). La méthode 2 n'a pas ce problème.

Changer la base d'un nombre

/* De binaire en octal */
SELECT CONV( '101101' , 2, 8 ) -- Retourne 55
 
/* De binaire en décimal */
SELECT CONV( '101101' , 2, 10 ) -- Retourne 45
 
/* De binaire en héxadécimal */
SELECT CONV( '101101' , 2, 16 ) -- Retourne 2D

La fonction CONV supporte les bases comprises entre 2 et 36.

Générer les requêtes pour déplacer les tables d'une base à une autre

Pour déplacer des tables d'une base à une autre, on peut passer par un export des données de la base source puis par un import des données dans la base cible. Mais ces actions sont très longues. Si les bases source et cible peuvent communiquer entre-elles (par exemple, elles sont sur le même serveur), il est possible de renommer les tables en ne changeant que le nom de la base de données.

RENAME TABLE `<source>`.`<table>`
TO `<destination>`.`<table>`

Si la table de la base d'origine existe déjà dans la base cible, celle-ci n'est pas déplacée.

Si le nombre de tables à déplacer est élevé, il est intéressant de générer la liste les requêtes dynamiquement. Par exemple, pour déplacer toutes les tables d'une base vers une autre :

-- Initialisation des variables
SET @orig:='<source>';
SET @dest:='<destination>';
 
-- Génération des requêtes
SELECT CONCAT('RENAME TABLE ', REPLACE(NAME, CONCAT(@orig, '/') ,CONCAT('`', @orig, '`.`')), '` TO ', REPLACE(NAME, CONCAT(@orig, '/'), CONCAT('`', @dest, '`.`')),'`;')
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME LIKE CONCAT(@orig, '/%');

Cette méthode de déplacement de table ne fonctionne qu'en innoDB.

Filtre multiple

SELECT * FROM <table1>
WHERE (<table1>.<column1>, <table1>.<column2>) IN (
    SELECT <table2>.<column1>, <table2>.<column2> FROM <table2>
)

Filtrer des données accentuées

-- Retourne l'ensemble des valeurs contenant "e", "é", "è", "E", "É", "È", …
SELECT *
FROM <table>
WHERE <column> LIKE '%e%'
 
-- Retourne uniquement les valeurs contenant "e" et "E"
SELECT *
FROM <table>
WHERE LOWER(<field>) LIKE '%e%' COLLATE utf8_bin
 
-- Retourne uniquement les valeurs contenant "e"
SELECT *
FROM <table>
WHERE <field> LIKE '%e%' COLLATE utf8_bin

stockage_utilisation_hierarchie

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:37:36 +0000

Stockage et utilisation d'une hiérarchie de données

Cette page contient une traduction partielle d'un article proposé sur mysql.com. Malheureusement, celui-ci a été supprimé mais on peut encore en trouver une copie sur archive.org. Cette traduction a été faite en utilisant DeepL, par conséquent, il se peux que certaines traductions soient approximatives. J'ai essayé de reprendre la plus part du texte généré mais il se peut que j'ai oublié certains passages. Certaines parties ont été complètement supprimées car elle étaient soit obsolètes soit inutiles.

Introduction

La plupart des utilisateurs ont, à un moment ou à un autre, traité des données hiérarchiques dans une base de données SQL et ont sans doute appris que la gestion de données hiérarchiques n'est pas ce à quoi une base de données relationnelle est destinée. Les tableaux d'une base de données relationnelle ne sont pas hiérarchiques (comme XML), mais sont simplement une liste plate. Les données hiérarchiques ont une relation parent-enfant qui n'est pas naturellement représentée dans une table de base de données relationnelle.

Pour nos besoins, les données hiérarchiques sont une collection de données où chaque élément a un seul parent et zéro ou plusieurs enfants (à l'exception de l'élément racine, qui n'a pas de parent). Les données hiérarchiques peuvent être trouvées dans une variété d'applications de base de données, y compris les fils de discussion des forums et des listes de diffusion, les organigrammes d'entreprises, les catégories de gestion de contenu et les catégories de produits. Pour nos besoins, nous utiliserons la hiérarchie de catégories de produits suivante, provenant d'un magasin d'électronique fictif :

Ces catégories forment une hiérarchie à peu près comme les autres exemples cités ci-dessus.

Modèle de liste adjacente

Généralement, les catégories d'exemple présentées ci-dessus seront stockées dans un tableau comme le suivant :

adjacency.list.tree.sql

CREATE TABLE category(
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    parent INT DEFAULT NULL
);
 
INSERT INTO category VALUES
    (1, 'ELECTRONICS', NULL),
    (2, 'TELEVISIONS', 1),
    (3, 'TUBE', 2),
    (4, 'LCD', 2),
    (5, 'PLASMA', 2),
    (6, 'PORTABLE ELECTRONICS', 1),
    (7, 'MP3 PLAYERS', 6),
    (8, 'FLASH', 7),
    (9, 'CD PLAYERS', 6),
    (10, '2 WAY RADIOS', 6);
 
SELECT *
FROM category
ORDER BY category_id;

category_id	name	parent
1	ELECTRONICS	NULL
2	TELEVISIONS	1
3	TUBE	2
4	LCD	2
5	PLASMA	2
6	PORTABLE ELECTRONICS	1
7	MP3 PLAYERS	6
8	FLASH	7
9	CD PLAYERS	6
10	2 WAY RADIOS	6

Dans le modèle de liste adjacente, chaque élément du tableau contient un pointeur vers son parent. L'élément le plus haut, dans ce cas l'électronique, a une valeur nulle pour son parent. Le modèle de liste adjacente a l'avantage d'être assez simple, il est facile de voir que FLASH est un enfant des lecteurs mp3, qui est un enfant de l'électronique portable, qui est un enfant de l'électronique. Alors que le modèle de liste adjacente peut être traité assez facilement dans le code côté client, travailler avec ce modèle peut être plus problématique en SQL pur.

Récupération d'un arbre complet

La première tâche courante lorsqu'on traite des données hiérarchiques est l'affichage de l'arbre entier, généralement avec une certaine forme d'indentation. La façon la plus courante de le faire en SQL pur est d'utiliser une auto-jointure :

SELECT t1.name AS lev1
, t2.name AS lev2
, t3.name AS lev3
, t4.name AS lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

lev1	lev2	lev3	lev4
ELECTRONICS	TELEVISIONS	TUBE	NULL
ELECTRONICS	TELEVISIONS	LCD	NULL
ELECTRONICS	TELEVISIONS	PLASMA	NULL
ELECTRONICS	PORTABLE ELECTRONICS	MP3 PLAYERS	FLASH
ELECTRONICS	PORTABLE ELECTRONICS	CD PLAYERS	NULL
ELECTRONICS	PORTABLE ELECTRONICS	2 WAY RADIOS	NULL

Trouver tous les nœuds feuille

Nous pouvons trouver tous les nœuds feuilles de notre arbre (ceux qui n'ont pas d'enfants) en utilisant une requête avec une jointure gauche :

SELECT t1.name
FROM category AS t1
LEFT JOIN category AS t2 ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

name
TUBE
LCD
PLASMA
FLASH
CD PLAYERS
2 WAY RADIOS

Retrouver un chemin unique

L'auto-jointure nous permet également de voir le chemin complet à travers nos hiérarchies :

SELECT t1.name AS lev1
, t2.name AS lev2
, t3.name AS lev3
, t4.name AS lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

lev1	lev2	lev3	lev4
ELECTRONICS	PORTABLE ELECTRONICS	MP3 PLAYERS	FLASH

La principale limite d'une telle approche est qu'il faut une auto-jointure pour chaque niveau de la hiérarchie et les performances se dégraderont naturellement avec chaque niveau ajouté à mesure que la jointure deviendra plus complexe.

Limites du modèle de liste adjacente

Travailler avec le modèle de liste adjacente en SQL pur peut être difficile. Avant de pouvoir voir le chemin complet d'une catégorie, nous devons connaître le niveau auquel elle se trouve. En outre, il faut être particulièrement attentif lors de la suppression de nœuds en raison du risque de création d'orphelin d'un sous-arbre entier au cours du processus (supprimer la catégorie de l'électronique portable rend tous ses enfants orphelins). Certaines de ces limitations peuvent être résolues par l'utilisation de codes côté client ou de procédures stockées. Avec un langage procédural, nous pouvons commencer au bas de l'arbre et itérer vers le haut pour revenir à l'arbre complet ou à un seul chemin. Nous pouvons également utiliser la programmation procédurale pour supprimer des nœuds sans rendre orphelins des sous-arbres entiers en favorisant un élément enfant et en réordonnant les enfants restants pour qu'ils pointent vers le nouveau parent.

Modèle d'ensembles imbriqués

Dans le modèle d'ensembles imbriqués, nous pouvons considérer notre hiérarchie d'une nouvelle manière, non pas comme des nœuds et des lignes, mais comme des conteneurs imbriqués. Essayez d'imaginer nos catégories électroniques de cette manière :

Remarquez comment notre hiérarchie est toujours maintenue, puisque les catégories de parents enveloppent leurs enfants. Nous représentons cette forme de hiérarchie dans un tableau en utilisant des valeurs de gauche et de droite pour représenter l'emboîtement de nos nœuds :

nested.set.tree.sql

CREATE TABLE nested_category (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    lft INT NOT NULL,
    rgt INT NOT NULL
);
 
INSERT INTO nested_category VALUES
    (1, 'ELECTRONICS', 1, 20),
    (2, 'TELEVISIONS', 2, 9),
    (3, 'TUBE', 3, 4),
    (4, 'LCD', 5, 6),
    (5, 'PLASMA', 7, 8),
    (6, 'PORTABLE ELECTRONICS', 10, 19),
    (7, 'MP3 PLAYERS', 11, 14),
    (8, 'FLASH', 12, 13),
    (9, 'CD PLAYERS', 15, 16),
    (10, '2 WAY RADIOS', 17, 18);
 
SELECT *
FROM nested_category
ORDER BY category_id;

category_id	name	lft	rgt
1	ELECTRONICS	1	20
2	TELEVISIONS	2	9
3	TUBE	3	4
4	LCD	5	6
5	PLASMA	7	8
6	PORTABLE ELECTRONICS	10	19
7	MP3 PLAYERS	11	14
8	FLASH	12	13
9	CD PLAYERS	15	16
10	2 WAY RADIOS	17	18

Nous utilisons lft et rgt parce que left et right sont des mots réservés à MySQL, voir la liste complète des mots réservés.

Alors comment déterminer les valeurs de gauche et de droite ? Nous commençons à numéroter du côté le plus à gauche du nœud extérieur et nous continuons vers la droite :

Cette conception peut également être appliquée à un arbre classique :

Lorsque l'on travaille avec un arbre, on travaille de gauche à droite, une couche à la fois, en descendant vers les enfants de chaque nœud avant d'attribuer un numéro à droite et de se déplacer vers la droite. Cette approche est appelée l'algorithme de traversée d'arbre pré-ordonné modifié.

Récupération d'un arbre complet

Nous pouvons récupérer l'arbre complet grâce à l'utilisation d'un auto-jointure qui relie les parents aux nœuds sur la base que la valeur lft d'un nœud apparaîtra toujours entre les valeurs lft et rgt de son parent :

SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

name
ELECTRONICS
TELEVISIONS
TUBE
LCD
PLASMA
PORTABLE ELECTRONICS
MP3 PLAYERS
FLASH
CD PLAYERS
2 WAY RADIOS

Contrairement à nos précédents exemples avec le modèle de liste adjacente, cette requête fonctionnera quelle que soit la profondeur de l'arbre. Nous ne nous préoccupons pas de la valeur rgt du nœud dans notre clause BETWEEN, car la valeur rgt sera toujours comprise dans le même parent que les valeurs lft.

Trouver tous les nœuds feuille

Trouver tous les nœuds de feuilles dans le modèle d'ensemble imbriqué est encore plus simple que la méthode utilisée dans le modèle de liste adjacente. Si vous regardez le tableau, vous pouvez remarquer que les valeurs lft et rgt pour les nœuds de feuilles sont des nombres consécutifs.

SELECT name
FROM nested_category
WHERE rgt = lft + 1;

name
TUBE
LCD
PLASMA
FLASH
CD PLAYERS
2 WAY RADIOS

Retrouver un chemin unique

Avec le modèle d'ensemble imbriqué, nous pouvons retrouver un seul chemin sans avoir de multiples auto-jointures :

SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft;

name
ELECTRONICS
PORTABLE ELECTRONICS
MP3 PLAYERS
FLASH

Trouver la profondeur des nœuds

Nous avons déjà examiné comment montrer l'arbre entier, mais que faire si nous voulons également montrer la profondeur de chaque nœud de l'arbre, pour mieux identifier comment chaque nœud s'insère dans la hiérarchie ? Cela peut être fait en ajoutant une fonction COUNT et une clause GROUP BY à notre requête existante pour afficher l'arbre entier :

SELECT node.name
, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

name	depth
ELECTRONICS	0
TELEVISIONS	1
TUBE	2
LCD	2
PLASMA	2
PORTABLE ELECTRONICS	1
MP3 PLAYERS	2
FLASH	3
CD PLAYERS	2
2 WAY RADIOS	2

Nous pouvons utiliser la valeur de profondeur pour indenter nos noms de catégorie avec les fonctions de chaîne CONCAT et REPEAT :

SELECT CONCAT( REPEAT('…', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

name
ELECTRONICS
…TELEVISIONS
……TUBE
……LCD
……PLASMA
…PORTABLE ELECTRONICS
……MP3 PLAYERS
………FLASH
……CD PLAYERS
……2 WAY RADIOS

Bien entendu, dans une application cliente, vous serez plus enclin à utiliser directement la valeur de profondeur pour afficher votre hiérarchie. Les développeurs web pourraient faire une boucle dans l'arborescence, en ajoutant les balises <li> et <ul> à mesure que le nombre de profondeur augmente et diminue.

Trouver la profondeur d'un sous-arbre

Lorsque nous avons besoin d'informations de profondeur pour un sous-arbre, nous ne pouvons limiter ni le nœud ni les tables parentes dans notre auto-jointure car cela corromprait nos résultats. Au lieu de cela, nous ajoutons une troisième auto-jointure, ainsi qu'une sous-requête pour déterminer la profondeur qui sera le nouveau point de départ de notre sous-arbre :

SELECT node.name
, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name
        , (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'PORTABLE ELECTRONICS'
        GROUP BY node.name
        ORDER BY node.lft
    ) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;

name	depth
PORTABLE ELECTRONICS	0
MP3 PLAYERS	1
FLASH	2
CD PLAYERS	1
2 WAY RADIOS	1

Cette fonction peut être utilisée avec n'importe quel nom de nœud, y compris le nœud racine. Les valeurs de profondeur sont toujours relatives au nœud nommé.

Trouver les enfants immédiats d'un nœud

Imaginez que vous présentez une catégorie de produits électroniques sur le site web d'un détaillant. Lorsqu'un utilisateur clique sur une catégorie, vous souhaitez afficher les produits de cette catégorie, ainsi que la liste de ses sous-catégories immédiates, mais pas l'arbre entier des catégories. Pour cela, nous devons afficher le nœud et ses sous-nœuds immédiats uniquement. Par exemple, lorsque nous affichons la catégorie ÉLECTRONIQUE PORTABLE, nous voulons afficher les catégories LECTEURS MP3, LECTEURS CD et RADIOS 2 VOIES, mais pas la catégorie FLASH.

Cela peut être facilement réalisé en ajoutant une clause HAVING à notre requête précédente :

SELECT node.name
, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name
        , (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'PORTABLE ELECTRONICS'
        GROUP BY node.name
        ORDER BY node.lft
    ) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

name	depth
PORTABLE ELECTRONICS	0
MP3 PLAYERS	1
CD PLAYERS	1
2 WAY RADIOS	1

Si vous ne souhaitez pas afficher le nœud parent, changez la ligne HAVING depth <= 1 par HAVING depth = 1.

Fonctions d’agrégation dans un ensemble imbriqué

Ajoutons une table de produits que nous pouvons utiliser avec des fonctions agrégées :

CREATE TABLE product (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40),
    category_id INT NOT NULL
);
 
INSERT INTO product (name, category_id) VALUES
    ('20" TV', 3),
    ('36" TV' ,3),
    ('Super-LCD 42"', 4),
    ('Ultra-Plasma 62"', 5),
    ('Value Plasma 38"', 5),
    ('Power-MP3 5gb', 7),
    ('Super-Player 1gb', 8),
    ('Porta CD', 9),
    ('CD To go!', 9),
    ('Family Talk 360', 10);
 
SELECT *
FROM product;

product_id	name	category_id
1	20“ TV	3
2	36” TV	3
3	Super-LCD 42“	4
4	Ultra-Plasma 62”	5
5	Value Plasma 38“	5
6	Power-MP3 128mb	7
7	Super-Shuffle 1gb	8
8	Porta CD	9
9	CD To go!	9
10	Family Talk 360	10

Produisons maintenant une requête qui récupère notre arbre de catégories, ainsi que le nombre de produits pour chaque catégorie :

SELECT parent.name
, COUNT(product.name)
FROM nested_category AS node,
nested_category AS parent,
product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;

name	COUNT(product.name)
ELECTRONICS	10
TELEVISIONS	5
TUBE	2
LCD	1
PLASMA	2
PORTABLE ELECTRONICS	5
MP3 PLAYERS	2
FLASH	1
CD PLAYERS	2
2 WAY RADIOS	1

C'est notre requête classique de récupération d'arbre entier avec l'ajout de COUNT et GROUP BY et d'une jointure entre le nœud et la table de produits dans la clause WHERE. Comme vous pouvez le voir, il y a un nombre pour chaque catégorie et le nombre de sous-catégories est reflété dans les catégories parentes.

Ajout d'un nœud

Maintenant que nous avons appris à interroger notre arbre, nous devrions examiner comment le mettre à jour en y ajoutant un nouveau nœud. Examinons à nouveau notre diagramme d'ensembles imbriqués :

Si nous voulions ajouter un nouveau nœud entre les nœuds TELEVISIONS et ELECTRONIQUE PORTABLE, le nouveau nœud aurait des valeurs lft et rgt de 10 et 11, et tous les nœuds à sa droite auraient leurs valeurs lft et rgt augmentées de deux. Nous ajouterions alors le nouveau nœud avec les valeurs lft et rgt appropriées. Bien que cela puisse être fait avec une procédure stockée dans MySQL 5, ~~je vais supposer pour le moment que la plupart des lecteurs utilisent la version 4.1, car c'est la dernière version stable~~, je vais isoler mes requêtes avec une instruction LOCK TABLES à la place :

-- Verrouillage en écriture de la table
LOCK TABLE nested_category WRITE;
 
-- Récupération des informations de l'élément parent
SELECT @myRight := rgt
FROM nested_category
WHERE name = 'TELEVISIONS';
 
-- Mise à jour des bornes droites des éléments concernés
UPDATE nested_category
SET rgt = rgt + 2
WHERE rgt > @myRight;
 
-- Mise à jour des bornes gauches des éléments concernés
UPDATE nested_category
SET lft = lft + 2
WHERE lft > @myRight;
 
-- Insertion du nouvel élément
INSERT INTO nested_category (name, lft, rgt) VALUES
    ('GAME CONSOLES', @myRight + 1, @myRight + 2);
 
-- Suppression des verrous
UNLOCK TABLES;

Nous pouvons alors vérifier notre arborescence grâce à notre requête d'arbre avec indentation :

SELECT CONCAT( REPEAT( '…', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

name
ELECTRONICS
…TELEVISIONS
……TUBE
……LCD
……PLASMA
…GAME CONSOLES
…PORTABLE ELECTRONICS
……MP3 PLAYERS
………FLASH
……CD PLAYERS
……2 WAY RADIOS

Si nous voulons plutôt ajouter un nœud en tant qu'enfant d'un nœud qui n'a pas d'enfant existant, nous devons modifier légèrement notre procédure. Ajoutons un nouveau nœud FRS sous le nœud 2 WAY RADIOS :

LOCK TABLE nested_category WRITE;
 
SELECT @myLeft := lft
FROM nested_category
 
WHERE name = '2 WAY RADIOS';
 
UPDATE nested_category
SET rgt = rgt + 2 WHERE rgt > @myLeft;
 
UPDATE nested_category
SET lft = lft + 2 WHERE lft > @myLeft;
 
INSERT INTO nested_category(name, lft, rgt) VALUES
    ('FRS', @myLeft + 1, @myLeft + 2);
 
UNLOCK TABLES;

Dans cet exemple, nous développons tout à droite du numéro de gauche de notre nouveau nœud parent, puis nous plaçons le nœud à droite de la valeur de gauche. Comme vous pouvez le voir, notre nouveau nœud est maintenant correctement imbriqué :

SELECT CONCAT( REPEAT( '…', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

name
ELECTRONICS
…TELEVISIONS
……TUBE
……LCD
……PLASMA
…GAME CONSOLES
…PORTABLE ELECTRONICS
……MP3 PLAYERS
………FLASH
……CD PLAYERS
……2 WAY RADIOS
………FRS

Suppression d'un nœud

La dernière tâche de base impliquée dans le travail avec des ensembles imbriqués est la suppression des nœuds. La marche à suivre pour supprimer un nœud dépend de la position du nœud dans la hiérarchie ; il est plus facile de supprimer des nœuds feuilles que des nœuds enfants, car nous devons nous occuper des nœuds orphelins.

Lors de la suppression d'un nœud de feuille, le processus est tout à fait inverse à celui de l'ajout d'un nouveau nœud ; nous supprimons le nœud et sa largeur de chaque nœud à sa droite :

-- Verrouillage en écriture de la table
LOCK TABLE nested_category WRITE;
 
-- Récupération des informations de l'élément à supprimer
SELECT @myLeft := lft
, @myRight := rgt
, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';
 
-- Suppression de l'élément
DELETE FROM nested_category
WHERE lft BETWEEN @myLeft AND @myRight;
 
-- Mise à jour des bornes droites des éléments concernés
UPDATE nested_category
SET rgt = rgt - @myWidth
WHERE rgt > @myRight;
 
-- Mise à jour des bornes gauches des éléments concernés
UPDATE nested_category
SET lft = lft - @myWidth
WHERE lft > @myRight;
 
-- Suppression des verrous
UNLOCK TABLES;

Et une fois de plus, nous exécutons notre requête d'arbre avec indentation pour confirmer que notre nœud a été supprimé sans corrompre la hiérarchie :

SELECT CONCAT( REPEAT( '…', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

name
ELECTRONICS
…TELEVISIONS
……TUBE
……LCD
……PLASMA
…PORTABLE ELECTRONICS
……MP3 PLAYERS
………FLASH
……CD PLAYERS
……2 WAY RADIOS
………FRS

Cette approche fonctionne tout aussi bien pour supprimer un nœud et tous ses enfants :

LOCK TABLE nested_category WRITE;
 
SELECT @myLeft := lft
, @myRight := rgt
, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';
 
DELETE FROM nested_category
WHERE lft BETWEEN @myLeft AND @myRight;
 
UPDATE nested_category
SET rgt = rgt - @myWidth 
WHERE rgt > @myRight;
 
UPDATE nested_category
SET lft = lft - @myWidth
WHERE lft > @myRight;
 
UNLOCK TABLES;

Et une fois de plus, nous exécutons notre requête pour valider que nous avons réussi à supprimer un sous-arbre entier :

SELECT CONCAT( REPEAT( '…', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

name
ELECTRONICS
…TELEVISIONS
……TUBE
……LCD
……PLASMA
…PORTABLE ELECTRONICS
……CD PLAYERS
……2 WAY RADIOS
………FRS

L'autre scénario que nous devons traiter est la suppression d'un nœud parent mais pas des enfants. Dans certains cas, il est possible de changer le nom en place jusqu'à ce qu'un remplaçant soit présenté, par exemple lorsqu'un superviseur est licencié. Dans d'autres cas, les nœuds enfants doivent tous être déplacés jusqu'au niveau du parent supprimé :

LOCK TABLE nested_category WRITE;
 
SELECT @myLeft := lft
, @myRight := rgt
, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';
 
DELETE FROM nested_category
WHERE lft = @myLeft;
 
UPDATE nested_category
SET rgt = rgt - 1
, lft = lft - 1
WHERE lft BETWEEN @myLeft AND @myRight;
 
UPDATE nested_category
SET rgt = rgt - 2
WHERE rgt > @myRight;
 
UPDATE nested_category
SET lft = lft - 2
WHERE lft > @myRight;
 
UNLOCK TABLES;

Dans ce cas, nous en soustrayons deux de tous les éléments à droite du nœud (puisque sans les enfants, il aurait une largeur de deux), et un des nœuds qui sont ses enfants (pour combler l'écart créé par la perte de la valeur gauche du parent). Une fois de plus, nous pouvons confirmer que nos éléments ont été promus :

SELECT CONCAT( REPEAT( '…', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

name
ELECTRONICS
…TELEVISIONS
……TUBE
……LCD
……PLASMA
…CD PLAYERS
…2 WAY RADIOS
……FRS

D'autres scénarios de suppression de nœuds incluent la promotion d'un des enfants en position parentale et le déplacement des nœuds enfants sous un frère ou une sœur du nœud parent, mais par souci d'espace, ces scénarios ne seront pas couverts dans cet article.

Autres ressources

Manipulations de données supplémentaires

Ces manipulations ne sont pas abordées dans l'article traduit, c'est pourquoi elles sont regroupées ici.

Retrouver un chemin unique sans le nœud

SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
AND node.category_id <> parent.category_id
ORDER BY parent.lft;

name
ELECTRONICS
PORTABLE ELECTRONICS
MP3 PLAYERS

Retrouver le parent immédiat d'un nœud

SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
AND node.category_id <> parent.category_id
ORDER BY parent.lft DESC
LIMIT 1;

name
MP3 PLAYERS

Déplacement d'un nœud

Ici, on ne couvre que le cas du déplacement d'un nœud vers un nœud qui n'est pas un descendant du nœud traité.

-- Verrouillage en écriture de la table
LOCK TABLE nested_category WRITE;
 
-- Récupération des informations de l'élément à déplacer
SELECT @myLeft := lft
, @myRight := rgt
, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'CD PLAYERS';
 
-- Récupération des informations du nouvel élément parent
SELECT @myNewParentLeft := lft
, @myNewParentRight := rgt
FROM nested_category
WHERE name = 'ELECTRONICS';
 
-- Calcul de la valeur de déplacement de l'élément à déplacer
SELECT @offsetWidth := @myNewParentRight - @myRight - 1 + IF( SIGN( @myNewParentRight - @myRight ) < 1 , @myWidth , 0 );
 
-- Calcul de la borne gauche de déplacement des autres éléments
SELECT @offsetLeft := IF( SIGN( @offsetWidth ) = 1 , @myRight , @myNewParentRight );
 
-- Calcul de la borne droite de déplacement des autres éléments
SELECT @offsetRight := IF( SIGN( @offsetWidth ) = 1 , @myNewParentRight - 1 , @myLeft - 1 );
 
-- Calcul de la valeur de déplacement des autres éléments
SELECT @signedNodeWidth := SIGN( @offsetWidth ) * @myWidth;
 
-- Exclusion temporaire de l'élément à déplacer
UPDATE nested_category
SET lft = -lft
, rgt = -rgt
WHERE lft >= @myLeft
AND rgt <= @myRight;
 
-- Mise à jour des bornes droites des éléments concernés
UPDATE nested_category
SET rgt = rgt - @signedNodeWidth
WHERE rgt BETWEEN @offsetLeft AND @offsetRight;
 
-- Mise à jour des bornes gauches des éléments concernés
UPDATE nested_category
SET lft = lft - @signedNodeWidth
WHERE lft BETWEEN @offsetLeft AND @offsetRight;
 
-- Repositionnement de l'élément à déplacer
UPDATE nested_category
SET lft = -lft + @offsetWidth
, rgt = -rgt + @offsetWidth
WHERE rgt < 0;
 
-- Suppression des verrous
UNLOCK TABLES;

name
ELECTRONICS
…TELEVISIONS
……TUBE
……LCD
……PLASMA
…GAME CONSOLES
…PORTABLE ELECTRONICS
……MP3 PLAYERS
………FLASH
……2 WAY RADIOS
………FRS
…CD PLAYERS

Transformation d'un nœud en nœud racine

Cela implique de déplacer un nœud ainsi que l'ensemble de ses descendants. Le nœud sélectionné pour être un nouveau nœud racine n'aura, de ce fait, plus de parent. À la fin de la transformation, on aura donc deux arborescences côte à cote.

-- Verrouillage en écriture de la table
LOCK TABLE nested_category WRITE;
 
-- Récupération des informations de l'élément à déplacer
SELECT @myLeft := lft
, @myRight := rgt
, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';
 
-- Récupération des informations de déplacement de l'élément
SELECT @offsetWidth := MAX(rgt) - @myRight
FROM nested_category;
 
-- Exclusion temporaire de l'élément à déplacer
UPDATE nested_category
SET lft = -lft
, rgt = -rgt
WHERE lft >= @myLeft
AND rgt <= @myRight;
 
-- Mise à jour des bornes droites des éléments concernés
UPDATE nested_category
SET rgt = rgt - @myWidth
WHERE rgt >= @myRight;
 
-- Mise à jour des bornes gauches des éléments concernés
UPDATE nested_category
SET lft = lft - @myWidth
WHERE lft >= @myRight;
 
-- Repositionnement de l'élément à déplacer
UPDATE nested_category
SET lft = -lft + @offsetWidth
, rgt = -rgt + @offsetWidth
WHERE rgt < 0;
 
-- Suppression des verrous
UNLOCK TABLES;

name
ELECTRONICS
…TELEVISIONS
……TUBE
……LCD
……PLASMA
…GAME CONSOLES
PORTABLE ELECTRONICS
…MP3 PLAYERS
……FLASH
…CD PLAYERS
…2 WAY RADIOS
……FRS

utilisation_du_client

anonymous@undisclosed.example.com (Anonymous) — Fri, 22 Nov 2024 02:51:33 +0000

Utilisation du client

Affichage des options utilisées par défaut

mysql --print-defaults

Ouverture d'une connexion

mysql
    --user=<username>
    --password
    --host=<hostname>

Depuis la version 5.6.7, l'option secure-auth est activé par défaut.
Il faut la désactiver cette option en utilisant l'option skip-secure-auth si l'erreur suivante apparait :

ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

S'il y a des options inconnues dans les fichiers de configuration, l'ouverture de la connexion échouera. Pour ne pas utiliser ces options, il faut utiliser l'option no-defaults.
Il faut noter que le fichier .mylogin.cnf est chargé quoiqu'il arrive.

Import de base de données

Comme pour l'exportation d'une base de données, il est possible de le faire avec PhpMyAdmin mais il vaut mieux utiliser directement mysql. Voici un exemple d'utilisation :

# avec une redirection
mysql --password --user=<username> --database=<database> < <filename>
 
# avec un "pipe"
cat <filename> | mysql --password --user=<username> --database=<database>

--password indique le mot de passe à utiliser.
--user indique l'utilisateur à utiliser.
--database indique la base de données à utiliser.