Utilisation des expressions de table communes MySQL

Dans ce didacticiel, vous apprendrez à utiliser les expressions de table communes MySQL (CTE) et à écrire vos propres expressions de table communes dans MySQL.

Dans votre travail quotidien en tant qu'ingénieur logiciel ou administrateur de base de données, vous devrez probablement écrire de longues requêtes complexes, souvent avec des sous-requêtes.

Au fil du temps, ces requêtes deviennent moins performantes, difficiles à lire et à comprendre, et encore plus difficiles à gérer. Et personne ne veut faire le travail difficile de les refactoriser, alors ils continuent à vivre.

Ou vous avez probablement dû récupérer des données similaires sur la base d'un ensemble de données ou de paramètres. Pour ce faire, vous écrivez de nombreuses sous-requêtes similaires, parfois exactement identiques, et vous les rassemblez à l'aide du mot-clé UNION.

Eh bien, vous pouvez vous simplifier la vie et résoudre efficacement ces problèmes en utilisant une expression de table commune.

Une expression de table commune (CTE) est un jeu de résultats temporaire nommé qui existe dans la portée d'une seule instruction et qui peut être référencé ultérieurement dans cette instruction, éventuellement plusieurs fois. – MySQL.com

À l'aide d'une expression de table commune, vous pouvez écrire très facilement des requêtes plus lisibles et plus performantes. Il est en fait plus facile que d'écrire plusieurs sous-requêtes qui pourraient rendre vos requêtes illisibles et moins performantes.

Vous utiliserez principalement une expression de table commune pour deux raisons :

• Pour écrire des requêtes sans utiliser de sous-requêtes (ou en utilisant moins de sous-requêtes)
• Pour écrire des fonctions récursives

Dans ce didacticiel, je vais vous montrer comment écrire vos propres expressions de table courantes.

Comment créer une expression de table commune

Vous pouvez créer une expression de table commune (CTE) à l'aide du WITHmot-clé. Vous pouvez spécifier plusieurs expressions de table communes en même temps en séparant par des virgules les requêtes constituant chaque expression de table commune.

La forme générale d'une expression de table commune est la suivante :

WITH cte_name AS (query)

-- Multiple CTEs
WITH
    cte_name1 AS (
        -- Query here
    ),
    cte_name2 AS (
        -- Query here
    )

Structure d'un CTE de base

Le WITHmot-clé est suivi du nom CTE. Après le nom, vous introduisez la requête à exécuter dans le CTE à l'aide du ASmot-clé. Vous devez mettre la requête must entre parenthèses. Le CTE ne peut pas être suivi d'un point-virgule comme les autres requêtes SQL. Au lieu de cela, il est suivi d'une autre requête qui l'utilise.

Après avoir créé un CTE, vous pouvez facilement utiliser le résultat des requêtes exécutées dans le CTE en référençant le CTE dans d'autres requêtes, d'autres CTE, ou même en lui-même.

Exemple de CTE

Si vous avez une table de joueurs world_cup, par exemple, vous pouvez créer un CTE comme ceci :

WITH
    barca_players AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    *
FROM
    barca_players;

Ici, nous avons créé un CTE nommé barca_players. Ce CTE renverra le nom, la position, l'âge et la nationalité de chaque joueur de Barcelone qui était à la coupe du monde. Il contient la sous-requête :

SELECT
    id,
    player_name,
    nationality,
    position,
    TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
FROM
    wc_players
WHERE
    club = 'Barcelona';

Cette sous-requête est ce qui produit le résultat CTE. Ensuite, il est suivi d'une requête qui utilise ce résultat. Vous pouvez voir le résultat de la sélection de chaque enregistrement dans le CTE ci-dessous.

Vous pouvez également sélectionner uniquement des champs spécifiques du CTE, par exemple :

WITH
    barca_players AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    player_name,
    position
FROM
    barca_players;

Cette requête est presque la même que la première, sauf qu'elle ne sélectionne que les noms et positions des joueurs dans la liste.

Comment utiliser des expressions de table courantes avec des paramètres

Vous pouvez également transmettre des arguments au CTE. Ce sont des alias que vous pouvez utiliser pour référencer les colonnes des résultats de la requête. Le nombre de paramètres transmis au CTE doit être le même que le nombre de colonnes sélectionnées dans sa sous-requête. En effet, les colonnes sont mises en correspondance avec les alias une par une, l'une après l'autre.

Par exemple, dans le barca_playersCTE créé ci-dessus, vous pouvez décider de faire référence à la nationalitycolonne comme country, et positioncomme role:

WITH
    barca_players (id, player_name, country, role, age) AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    player_name,
    role
FROM
    barca_players;

Notez que dans la sous-requête CTE, vous utilisez toujours les noms de colonne corrects. Mais dans la SELECTrequête externe, vous utilisez les nouveaux alias spécifiés en tant que paramètres du CTE.

Expressions de table communes récursives

Lorsque vous faites référence à une expression de table commune dans elle-même, elle devient une expression de table commune récursive.

Une expression de table commune récursive, comme son nom l'indique, est une expression de table commune qui peut exécuter une sous-requête plusieurs fois, tant qu'une condition est remplie. Il itère continuellement jusqu'à ce qu'il atteigne un point d'arrêt, lorsque la condition cesse d'être vraie.

Pour définir un CTE récursif, le RECURSIVEmot-clé doit figurer dans son nom. Sans ce mot-clé, MySQL génère une erreur.

Par exemple, vous pouvez écrire une expression de table commune qui imprime les nombres 1 à 10 et leurs carrés comme ceci :

WITH RECURSIVE
    numbers_list (n, square) AS (
        SELECT
            1,
            1
        UNION ALL
        SELECT
            n + 1,
            (n + 1) * (n + 1)
        FROM
            numbers_list
        WHERE
            n < 10
    )
SELECT
    *
FROM
    numbers_list;

Examinons ce qui se passe ici :

Dans les deux premières lignes, l'expression de table commune récursive est définie avec deux paramètres, l'un représentant la colonne pour le nombre et l'autre représentant la colonne pour le carré :

WITH RECURSIVE
    numbers_list (n, square) AS (

Ensuite, la sous-requête. La sous-requête est en deux parties, jointes par le UNION ALLmot-clé pour n'en former qu'une. Vous pouvez également joindre ces sous-requêtes par le UNIONmot-clé si vous n'avez pas besoin d'enregistrements en double.

La première partie de la sous-requête est un élément clé des expressions de table communes récursives. C'est la requête de base, le premier ensemble de résultats, l'itération initiale. Cette requête est le point de départ de toutes les itérations.

Dans cet exemple, il est statique, car aucun enregistrement n'est récupéré.

SELECT
    1,
    1

Après cette première requête, le tableau de résultats comporte une ligne et ressemble à ceci :

La deuxième partie de la sous-requête est l'endroit où l'itération se produit réellement.

Dans cette requête, le CTE est référencé en lui-même et ses colonnes peuvent être utilisées. Lorsqu'un nom de colonne est mentionné, la valeur la plus récente de cette colonne est prise.

Ainsi, au début de l'itération, nest 1 et squareest également 1. Cela signifie, n + 1est 2, et (n + 1) * (n + 1)est 2 * 2 qui est 4. 2 et 4 sont ajoutés au tableau des résultats et deviennent ensuite les valeurs les plus récentes du tableau. ndevient 2 et squaredevient 4.

Cela continue jusqu'à ce que la condition dans le WHEREmot clé is cesse d'être vraie.

Le WHEREmot-clé dans la requête spécifie le point d'arrêt du CTE. Tant que la condition spécifiée n'est pas remplie, la requête continue d'être exécutée. Dans ce cas, après chaque itération, la requête vérifie si nest inférieur à 10.

Si une condition qui sera toujours évaluée à vrai est définie, cela crée une boucle sans fin et vous obtenez une erreur commeRecursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

SELECT
    n + 1,
    (n + 1) * (n + 1)
FROM
    numbers_list
WHERE
    n < 10

Maintenant, vous pourriez penser, "Si la condition vérifie n < 10, comment se fait-il que 10 soit toujours dans la table finale ?".

Eh bien, la raison en est qu'en SQL, la WHEREpartie mot-clé d'une requête est évaluée en premier avant les autres parties. Ainsi, quand n = 9est la dernière ligne, la requête s'exécute une fois de plus, et avant l'insertion ou quoi que ce soit, elle vérifie si 9 est inférieur à 10. Puisque 9 est inférieur à 10, elle ajoute ce qui n + 1est 10 à la liste. Ensuite, à l'itération suivante, 10 est l'enregistrement le plus récent et il n'est pas inférieur à lui-même, donc la boucle se termine.

Gardez à l'esprit qu'une expression de table commune récursive se compose d'une SELECTrequête récursive et d'une requête non récursive SELECT.

Règles d'expression de table commune récursive simple

• Vous ne pouvez pas utiliser le GROUP BYmot-clé. En effet, vous ne pouvez regrouper qu'une collection, mais dans une expression de table commune récursive, les enregistrements sont gérés et évalués individuellement. D'autres mots clés tels que ORDER BY, DISTINCTet les fonctions d'agrégation telles que SUMne peuvent pas non plus être utilisés.
• Vous ne pouvez pas utiliser les fonctions de fenêtre.

Ces règles s'appliquent à la partie récursive d'une expression de table commune récursive.

Cas d'utilisation pour les CTE récursifs

Suite de Fibonacci

La suite de Fibonacci est une suite dans laquelle chaque nombre est la somme des deux précédents. La séquence commence généralement à partir de 0 et 1, bien que certains auteurs commencent la séquence à partir de 1 et 1 ou parfois à partir de 1 et 2. ( source )

Vous pouvez facilement générer une séquence de Fibonacci de n'importe quelle longueur à l'aide d'une expression de table commune récursive. Par exemple, voici une requête qui obtiendra les 20 premiers nombres d'une suite de Fibonacci à partir de 0 et 1.

WITH RECURSIVE
    fibonacci (n, fib_n, next_fib_n) AS (
        /*
        * n - Number of iterations
        * fib_n - Currennt Fibonnaci number. Starts at 0
        * next_fib_n - Next Fibonnaci number. Starts at 1
        */
        SELECT
            1,
            0,
            1
        UNION ALL
        SELECT
            n + 1,
            next_fib_n,
            fib_n + next_fib_n
        FROM
            fibonacci
        WHERE
            n < 20
    )
SELECT
    *
FROM
    fibonacci;

Traversée hiérarchique des données

Dans de nombreuses bases de données d'application, vous constaterez que les données hiérarchiques sont stockées dans la même table.

Par exemple, un categoriestableau contiendra généralement des catégories principales et des sous-catégories faisant référence à leur catégorie parente. Une employeestable contiendra les employés réguliers avec leurs manager_id, ainsi que leurs managers ou superviseurs, car ce sont aussi des employés.

Si vous aviez un categoriestableau comme celui-ci, avec 4 enregistrements, 1 catégorie principale et une chaîne de sous-catégories :

CREATE TABLE
    categories (
        id int,
        cat_name varchar(100),
        parent_category_id int DEFAULT NULL
    );

INSERT INTO
    categories
VALUES
    (1, 'Mens', NULL),
    (2, 'Tops', 1),
    (3, 'Jerseys', 2),
    (4, 'England', 3);

Vous pouvez récupérer chaque catégorie, avec sa catégorie parent attachée facilement comme ceci :

WITH RECURSIVE
    category_tree AS (
        SELECT
            id,
            cat_name,
            parent_category_id,
            cat_name AS full_name
        FROM
            categories
        WHERE
            parent_category_id IS NULL
        UNION ALL
        SELECT
            c.id,
            c.cat_name,
            c.parent_category_id,
            CONCAT (ct.full_name, ' > ', c.cat_name)
        FROM
            categories c
            JOIN category_tree ct ON c.parent_category_id = ct.id
    )
SELECT
    full_name
FROM
    category_tree;

Dans cet exemple, la requête de base sélectionne la catégorie racine, où parent_category_id IS NULL. Ensuite, il recherche une catégorie où parent_category_idest le idde la catégorie actuelle en utilisant un JOIN. Il répète cela jusqu'à ce qu'il arrive à la catégorie finale. Le résultat de cette requête est le suivant :

Résumé

J'espère que vous comprenez maintenant comment utiliser MySQL Common Table Expressions, leurs variations (régulières et récursives) et quand les utiliser pour pouvoir écrire de meilleures requêtes. Vous pouvez en savoir plus sur les expressions de table courantes dans la documentation ici .

Si vous avez des questions ou des conseils pertinents, n'hésitez pas à me contacter pour les partager.

Source : https://www.freecodecamp.org

#mysql #sql 

What is GEEK

Buddha Community

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

How to Upload and Store Images in MySQL using Node.js and Express

Today we are going to explore the basic usage of Express-FileUpload. In addition to this, I will show you how you can save/update a user record with a profile image that you can upload.

Chapters:
0:00 Introduction:
1:16 NPM Project Setup
3:54 Creating Express Server
5:51 Setting up Layouts & Routes
9:46 Express Upload Form
21:50 User Card
33:40 Database
52:05 Ending

Source Files:
https://raddy.co.uk/blog/upload-and-store-images-in-mysql-using-node-js-express-express-fileupload-express-handlebars/

#node.js #express #express-fileupload #express-handlebars #mysql #upload and store images

How to alter tables in production when records are in millions

As a developer, I have experienced changes in app when it is in production and the records have grown up to millions. In this specific case if you want to alter a column using simple migrations that will not work because of the following reasons:

It is not so easy if your production servers are under heavy load and the database tables have 100 million rows. Because such a migration will run for some seconds or even minutes and the database table can be locked for this time period – a no-go on a zero-downtime environment.

In this specific case you can use MySQL’s algorithms: Online DDL operations. That’s how you can do it in Laravel.

First of all create migration. For example I want to modify a column’s name the traditional migration will be:

Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('name', 'first_name');
        });

Run the following command php artisan migrate –pretend this command will not run the migration rather it will print out it’s raw sql:

ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL

Copy that raw sql, remove following code:

Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('name', 'first_name');
        });

Replace it with following in migrations up method:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL');

Add desired algorithm, in my case query will look like this:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;');

#laravel #mysql #php #alter heavy tables in production laravel #alter table in production laravel #alter tables with million of records in laravel #how to alter heavy table in production laravel #how to alter table in production larave #mysql online ddl operations

Utilisation des expressions de table communes MySQL

Dans ce didacticiel, vous apprendrez à utiliser les expressions de table communes MySQL (CTE) et à écrire vos propres expressions de table communes dans MySQL.

Dans votre travail quotidien en tant qu'ingénieur logiciel ou administrateur de base de données, vous devrez probablement écrire de longues requêtes complexes, souvent avec des sous-requêtes.

Au fil du temps, ces requêtes deviennent moins performantes, difficiles à lire et à comprendre, et encore plus difficiles à gérer. Et personne ne veut faire le travail difficile de les refactoriser, alors ils continuent à vivre.

Ou vous avez probablement dû récupérer des données similaires sur la base d'un ensemble de données ou de paramètres. Pour ce faire, vous écrivez de nombreuses sous-requêtes similaires, parfois exactement identiques, et vous les rassemblez à l'aide du mot-clé UNION.

Eh bien, vous pouvez vous simplifier la vie et résoudre efficacement ces problèmes en utilisant une expression de table commune.

Une expression de table commune (CTE) est un jeu de résultats temporaire nommé qui existe dans la portée d'une seule instruction et qui peut être référencé ultérieurement dans cette instruction, éventuellement plusieurs fois. – MySQL.com

À l'aide d'une expression de table commune, vous pouvez écrire très facilement des requêtes plus lisibles et plus performantes. Il est en fait plus facile que d'écrire plusieurs sous-requêtes qui pourraient rendre vos requêtes illisibles et moins performantes.

Vous utiliserez principalement une expression de table commune pour deux raisons :

• Pour écrire des requêtes sans utiliser de sous-requêtes (ou en utilisant moins de sous-requêtes)
• Pour écrire des fonctions récursives

Dans ce didacticiel, je vais vous montrer comment écrire vos propres expressions de table courantes.

Comment créer une expression de table commune

Vous pouvez créer une expression de table commune (CTE) à l'aide du WITHmot-clé. Vous pouvez spécifier plusieurs expressions de table communes en même temps en séparant par des virgules les requêtes constituant chaque expression de table commune.

La forme générale d'une expression de table commune est la suivante :

WITH cte_name AS (query)

-- Multiple CTEs
WITH
    cte_name1 AS (
        -- Query here
    ),
    cte_name2 AS (
        -- Query here
    )

Structure d'un CTE de base

Le WITHmot-clé est suivi du nom CTE. Après le nom, vous introduisez la requête à exécuter dans le CTE à l'aide du ASmot-clé. Vous devez mettre la requête must entre parenthèses. Le CTE ne peut pas être suivi d'un point-virgule comme les autres requêtes SQL. Au lieu de cela, il est suivi d'une autre requête qui l'utilise.

Après avoir créé un CTE, vous pouvez facilement utiliser le résultat des requêtes exécutées dans le CTE en référençant le CTE dans d'autres requêtes, d'autres CTE, ou même en lui-même.

Exemple de CTE

Si vous avez une table de joueurs world_cup, par exemple, vous pouvez créer un CTE comme ceci :

WITH
    barca_players AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    *
FROM
    barca_players;

Ici, nous avons créé un CTE nommé barca_players. Ce CTE renverra le nom, la position, l'âge et la nationalité de chaque joueur de Barcelone qui était à la coupe du monde. Il contient la sous-requête :

SELECT
    id,
    player_name,
    nationality,
    position,
    TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
FROM
    wc_players
WHERE
    club = 'Barcelona';

Cette sous-requête est ce qui produit le résultat CTE. Ensuite, il est suivi d'une requête qui utilise ce résultat. Vous pouvez voir le résultat de la sélection de chaque enregistrement dans le CTE ci-dessous.

Vous pouvez également sélectionner uniquement des champs spécifiques du CTE, par exemple :

WITH
    barca_players AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    player_name,
    position
FROM
    barca_players;

Cette requête est presque la même que la première, sauf qu'elle ne sélectionne que les noms et positions des joueurs dans la liste.

Comment utiliser des expressions de table courantes avec des paramètres

Vous pouvez également transmettre des arguments au CTE. Ce sont des alias que vous pouvez utiliser pour référencer les colonnes des résultats de la requête. Le nombre de paramètres transmis au CTE doit être le même que le nombre de colonnes sélectionnées dans sa sous-requête. En effet, les colonnes sont mises en correspondance avec les alias une par une, l'une après l'autre.

Par exemple, dans le barca_playersCTE créé ci-dessus, vous pouvez décider de faire référence à la nationalitycolonne comme country, et positioncomme role:

WITH
    barca_players (id, player_name, country, role, age) AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    player_name,
    role
FROM
    barca_players;

Notez que dans la sous-requête CTE, vous utilisez toujours les noms de colonne corrects. Mais dans la SELECTrequête externe, vous utilisez les nouveaux alias spécifiés en tant que paramètres du CTE.

Expressions de table communes récursives

Lorsque vous faites référence à une expression de table commune dans elle-même, elle devient une expression de table commune récursive.

Une expression de table commune récursive, comme son nom l'indique, est une expression de table commune qui peut exécuter une sous-requête plusieurs fois, tant qu'une condition est remplie. Il itère continuellement jusqu'à ce qu'il atteigne un point d'arrêt, lorsque la condition cesse d'être vraie.

Pour définir un CTE récursif, le RECURSIVEmot-clé doit figurer dans son nom. Sans ce mot-clé, MySQL génère une erreur.

Par exemple, vous pouvez écrire une expression de table commune qui imprime les nombres 1 à 10 et leurs carrés comme ceci :

WITH RECURSIVE
    numbers_list (n, square) AS (
        SELECT
            1,
            1
        UNION ALL
        SELECT
            n + 1,
            (n + 1) * (n + 1)
        FROM
            numbers_list
        WHERE
            n < 10
    )
SELECT
    *
FROM
    numbers_list;

Examinons ce qui se passe ici :

Dans les deux premières lignes, l'expression de table commune récursive est définie avec deux paramètres, l'un représentant la colonne pour le nombre et l'autre représentant la colonne pour le carré :

WITH RECURSIVE
    numbers_list (n, square) AS (

Ensuite, la sous-requête. La sous-requête est en deux parties, jointes par le UNION ALLmot-clé pour n'en former qu'une. Vous pouvez également joindre ces sous-requêtes par le UNIONmot-clé si vous n'avez pas besoin d'enregistrements en double.

La première partie de la sous-requête est un élément clé des expressions de table communes récursives. C'est la requête de base, le premier ensemble de résultats, l'itération initiale. Cette requête est le point de départ de toutes les itérations.

Dans cet exemple, il est statique, car aucun enregistrement n'est récupéré.

SELECT
    1,
    1

Après cette première requête, le tableau de résultats comporte une ligne et ressemble à ceci :

La deuxième partie de la sous-requête est l'endroit où l'itération se produit réellement.

Dans cette requête, le CTE est référencé en lui-même et ses colonnes peuvent être utilisées. Lorsqu'un nom de colonne est mentionné, la valeur la plus récente de cette colonne est prise.

Ainsi, au début de l'itération, nest 1 et squareest également 1. Cela signifie, n + 1est 2, et (n + 1) * (n + 1)est 2 * 2 qui est 4. 2 et 4 sont ajoutés au tableau des résultats et deviennent ensuite les valeurs les plus récentes du tableau. ndevient 2 et squaredevient 4.

Cela continue jusqu'à ce que la condition dans le WHEREmot clé is cesse d'être vraie.

Le WHEREmot-clé dans la requête spécifie le point d'arrêt du CTE. Tant que la condition spécifiée n'est pas remplie, la requête continue d'être exécutée. Dans ce cas, après chaque itération, la requête vérifie si nest inférieur à 10.

Si une condition qui sera toujours évaluée à vrai est définie, cela crée une boucle sans fin et vous obtenez une erreur commeRecursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

SELECT
    n + 1,
    (n + 1) * (n + 1)
FROM
    numbers_list
WHERE
    n < 10

Maintenant, vous pourriez penser, "Si la condition vérifie n < 10, comment se fait-il que 10 soit toujours dans la table finale ?".

Eh bien, la raison en est qu'en SQL, la WHEREpartie mot-clé d'une requête est évaluée en premier avant les autres parties. Ainsi, quand n = 9est la dernière ligne, la requête s'exécute une fois de plus, et avant l'insertion ou quoi que ce soit, elle vérifie si 9 est inférieur à 10. Puisque 9 est inférieur à 10, elle ajoute ce qui n + 1est 10 à la liste. Ensuite, à l'itération suivante, 10 est l'enregistrement le plus récent et il n'est pas inférieur à lui-même, donc la boucle se termine.

Gardez à l'esprit qu'une expression de table commune récursive se compose d'une SELECTrequête récursive et d'une requête non récursive SELECT.

Règles d'expression de table commune récursive simple

• Vous ne pouvez pas utiliser le GROUP BYmot-clé. En effet, vous ne pouvez regrouper qu'une collection, mais dans une expression de table commune récursive, les enregistrements sont gérés et évalués individuellement. D'autres mots clés tels que ORDER BY, DISTINCTet les fonctions d'agrégation telles que SUMne peuvent pas non plus être utilisés.
• Vous ne pouvez pas utiliser les fonctions de fenêtre.

Ces règles s'appliquent à la partie récursive d'une expression de table commune récursive.

Cas d'utilisation pour les CTE récursifs

Suite de Fibonacci

La suite de Fibonacci est une suite dans laquelle chaque nombre est la somme des deux précédents. La séquence commence généralement à partir de 0 et 1, bien que certains auteurs commencent la séquence à partir de 1 et 1 ou parfois à partir de 1 et 2. ( source )

Vous pouvez facilement générer une séquence de Fibonacci de n'importe quelle longueur à l'aide d'une expression de table commune récursive. Par exemple, voici une requête qui obtiendra les 20 premiers nombres d'une suite de Fibonacci à partir de 0 et 1.

WITH RECURSIVE
    fibonacci (n, fib_n, next_fib_n) AS (
        /*
        * n - Number of iterations
        * fib_n - Currennt Fibonnaci number. Starts at 0
        * next_fib_n - Next Fibonnaci number. Starts at 1
        */
        SELECT
            1,
            0,
            1
        UNION ALL
        SELECT
            n + 1,
            next_fib_n,
            fib_n + next_fib_n
        FROM
            fibonacci
        WHERE
            n < 20
    )
SELECT
    *
FROM
    fibonacci;

Traversée hiérarchique des données

Dans de nombreuses bases de données d'application, vous constaterez que les données hiérarchiques sont stockées dans la même table.

Par exemple, un categoriestableau contiendra généralement des catégories principales et des sous-catégories faisant référence à leur catégorie parente. Une employeestable contiendra les employés réguliers avec leurs manager_id, ainsi que leurs managers ou superviseurs, car ce sont aussi des employés.

Si vous aviez un categoriestableau comme celui-ci, avec 4 enregistrements, 1 catégorie principale et une chaîne de sous-catégories :

CREATE TABLE
    categories (
        id int,
        cat_name varchar(100),
        parent_category_id int DEFAULT NULL
    );

INSERT INTO
    categories
VALUES
    (1, 'Mens', NULL),
    (2, 'Tops', 1),
    (3, 'Jerseys', 2),
    (4, 'England', 3);

Vous pouvez récupérer chaque catégorie, avec sa catégorie parent attachée facilement comme ceci :

WITH RECURSIVE
    category_tree AS (
        SELECT
            id,
            cat_name,
            parent_category_id,
            cat_name AS full_name
        FROM
            categories
        WHERE
            parent_category_id IS NULL
        UNION ALL
        SELECT
            c.id,
            c.cat_name,
            c.parent_category_id,
            CONCAT (ct.full_name, ' > ', c.cat_name)
        FROM
            categories c
            JOIN category_tree ct ON c.parent_category_id = ct.id
    )
SELECT
    full_name
FROM
    category_tree;

Dans cet exemple, la requête de base sélectionne la catégorie racine, où parent_category_id IS NULL. Ensuite, il recherche une catégorie où parent_category_idest le idde la catégorie actuelle en utilisant un JOIN. Il répète cela jusqu'à ce qu'il arrive à la catégorie finale. Le résultat de cette requête est le suivant :

Résumé

J'espère que vous comprenez maintenant comment utiliser MySQL Common Table Expressions, leurs variations (régulières et récursives) et quand les utiliser pour pouvoir écrire de meilleures requêtes. Vous pouvez en savoir plus sur les expressions de table courantes dans la documentation ici .

Si vous avez des questions ou des conseils pertinents, n'hésitez pas à me contacter pour les partager.

Source : https://www.freecodecamp.org

#mysql #sql 

Exploring MySQL Binlog Server - Ripple

MySQL does not limit the number of slaves that you can connect to the master server in a replication topology. However, as the number of slaves increases, they will have a toll on the master resources because the binary logs will need to be served to different slaves working at different speeds. If the data churn on the master is high, the serving of binary logs alone could saturate the network interface of the master.

A classic solution for this problem is to deploy a binlog server – an intermediate proxy server that sits between the master and its slaves. The binlog server is set up as a slave to the master, and in turn, acts as a master to the original set of slaves. It receives binary log events from the master, does not apply these events, but serves them to all the other slaves. This way, the load on the master is tremendously reduced, and at the same time, the binlog server serves the binlogs more efficiently to slaves since it does not have to do any other database server processing.

Ripple is an open source binlog server developed by Pavel Ivanov. A blog post from Percona, titled MySQL Ripple: The First Impression of a MySQL Binlog Server, gives a very good introduction to deploying and using Ripple. I had an opportunity to explore Ripple in some more detail and wanted to share my observations through this post.

1. Support for GTID based replication

Ripple supports only GTID mode, and not file and position-based replication. If your master is running in non-GTID mode, you will get this error from Ripple:

Failed to read packet: Got error reading packet from server: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON.

You can specify Server_id and UUID for the ripple server using the cmd line options: -ripple_server_id and -ripple_server_uuid

Both are optional parameters, and if not specified, Ripple will use the default server_id=112211 and uuid will be auto generated.

2. Connecting to the master using replication user and password

While connecting to the master, you can specify the replication user and password using the command line options:

-ripple_master_user and -ripple_master_password

3. Connection endpoint for the Ripple server

You can use the command line options -ripple_server_ports and -ripple_server_address to specify the connection end points for the Ripple server. Ensure to specify the network accessible hostname or IP address of your Ripple server as the -rippple_server_address. Otherwise, by default, Ripple will bind to localhost and hence you will not be able to connect to it remotely.

4. Setting up slaves to the Ripple server

You can use the CHANGE MASTER TO command to connect your slaves to replicate from the Ripple server.

To ensure that Ripple can authenticate the password that you use to connect to it, you need to start Ripple by specifying the option -ripple_server_password_hash

For example, if you start the ripple server with the command:

rippled -ripple_datadir=./binlog_server -ripple_master_address= <master ip> -ripple_master_port=3306 -ripple_master_user=repl -ripple_master_password='password' -ripple_server_ports=15000 -ripple_server_address='172.31.23.201' -ripple_server_password_hash='EF8C75CB6E99A0732D2DE207DAEF65D555BDFB8E'

you can use the following CHANGE MASTER TO command to connect from the slave:

CHANGE MASTER TO master_host='172.31.23.201', master_port=15000, master_password=’XpKWeZRNH5#satCI’, master_user=’rep’

Note that the password hash specified for the Ripple server corresponds to the text password used in the CHANGE MASTER TO command. Currently, Ripple does not authenticate based on the usernames and accepts any non-empty username as long as the password matches.

Exploring MySQL Binlog Server - Ripple

CLICK TO TWEET

5. Ripple server management

It’s possible to monitor and manage the Ripple server using the MySQL protocol from any standard MySQL client. There are a limited set of commands that are supported which you can see directly in the source code on the mysql-ripple GitHub page.

Some of the useful commands are:

• SELECT @@global.gtid_executed; – To see the GTID SET of the Ripple server based on its downloaded binary logs.
• STOP SLAVE; – To disconnect the Ripple server from the master.
• START SLAVE; – To connect the Ripple server to the master.

#cloud #database #developer #high availability #mysql #performance #binary logs #gtid replication #mysql binlog #mysql protocol #mysql ripple #mysql server #parallel threads #proxy server #replication topology #ripple server