Comment gérer des valeurs consécutives dans MySQL

introduction

Récemment, j'ai de plus en plus d'opportunités d'utiliser MySQL. Parmi eux, il y avait des occasions où je voulais un numéro de série, comme générer des données de test ou générer des dates consécutives.

Le numéro de série a la fonction generate_series () pour PostgreSQL et la pseudo-colonne LEVEL pour Oracle. Cependant, MySQL n'a pas le bon, alors j'ai dû concevoir diverses choses.

Donc, cette fois, je voudrais résumer «l'ingéniosité» de générer des numéros de série avec MySQL.

Ajouter un numéro de série à une table existante

Par exemple, supposons que vous ayez une table avec les fruits suivants et leurs prix:

Table de fruits


SELECT * FROM sample;

+-----------+-------+
| name      | price |
+-----------+-------+
|pomme|   100 |
|Mandarine|    80 |
|banane|   120 |
+-----------+-------+

Pour les numéroter et les classer par ordre croissant de prix, procédez comme suit.

Donner des numéros de série aux tables de fruits


SET @num=0;
SELECT
  @num:=@num+1 AS num,
  name,
  price
FROM sample
ORDER BY price;

+------+-----------+-------+
| num  | name      | price |
+------+-----------+-------+
|    1 |Mandarine|    80 |
|    2 |pomme|   100 |
|    3 |banane|   120 |
+------+-----------+-------+

En définissant la variable " @ </ span> num", vous pouvez facilement attribuer des numéros de série comme décrit ci-dessus. N'oubliez pas de vous souvenir de "SET @ </ span> num = 0;" ici. Étant donné que la variable « @ </ span> num» continue de fluctuer, l'exécution de l'instruction SELECT ci-dessus produit à nouveau le résultat suivant.

@Réexécutez l'instruction SELECT sans initialiser num


SELECT
  @num:=@num+1 AS num,
  name,
  price
FROM sample
ORDER BY price;

+------+-----------+-------+
| num  | name      | price |
+------+-----------+-------+
|    4 |Mandarine|    80 |
|    5 |pomme|   100 |
|    6 |banane|   120 |
+------+-----------+-------+

Depuis que " @ </ span> num" était 3 la dernière fois, il a encore augmenté. Par conséquent, une initialisation par "SET @ </ span> num = 0;" est requise.

Génération de numéro de série pour les données de test

L'avantage de la fonction de génération de numéro de série DB est que vous pouvez facilement générer des données de test. Cependant, MySQL n'a pas la bonne fonction, donc une certaine ingéniosité est requise.

Manière simple

Comme expliqué précédemment, vous pouvez facilement attribuer des numéros de série aux tables existantes. Par conséquent, si MySQL a une table facilement accessible par n'importe quel utilisateur, il est possible de créer une table avec uniquement des numéros de série.

Beaucoup de gens semblent utiliser "information_schema.COLUMNS" comme une table pratique. Par exemple, pour créer une table de test avec un numéro de série de 10 lignes:

10 lignes de génération de données de test


SET @num=0;
SELECT
  @num:=@num+1 AS num,
  MD5(@num) AS txt
FROM information_schema.COLUMNS
LIMIT 10;

+------+----------------------------------+
| num  | txt                              |
+------+----------------------------------+
|    1 | c4ca4238a0b923820dcc509a6f75849b |
|    2 | c81e728d9d4c2f636f067f89cc14862c |
|    3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
|    4 | a87ff679a2f3e71d9181a67b7542122c |
|    5 | e4da3b7fbbce2345d7772b0674a318d5 |
|    6 | 1679091c5a880faf6fb5e6087eb1b2dc |
|    7 | 8f14e45fceea167a5a36dedd4bea2543 |
|    8 | c9f0f895fb98ab9159f51fd0297e236d |
|    9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
|   10 | d3d9446802a44259755d38e6d163e820 |
+------+----------------------------------+

Cependant, le nombre de lignes de la table qui peuvent être générées par cette méthode dépend du nombre de lignes dans "information_schema.COLUMNS". Pour générer des données de test au-delà de cela, vous devez bien utiliser UNION ou créer votre propre table avec un très grand nombre de lignes.

Méthode programmatique

En concaténant des tables avec UNION, vous pouvez générer une table avec n'importe quel nombre de lignes, comme indiqué ci-dessous.

Cependant, il n'est pas pratique de générer manuellement des milliers de lignes. Il est facile de se connecter à MySQL dans un langage de programmation et de le parcourir.

Cette fois, je vais essayer de me connecter à partir de python en utilisant mysql-connector-python.

mysql_test.py


# coding:utf-8
import mysql.connector

#Dictionnaire des informations de connexion
config = {
    'user': 'root',
    'password': 'password',
    'host': '123.45.67.890',
    'database': 'test'
}

#Lien
conn = mysql.connector.connect(**config)

# ------------------------------
#Génération de l'instruction SELECT de données de test
# ------------------------------
start_num = 0
end_num = 10
query_list = ["(SELECT {start_num} AS s".format(start_num=start_num)]
# 「UNION ALL SELECT {num}"Ajouter
for i in range(start_num + 1, end_num):
    query_list.append(" UNION ALL SELECT {num}".format(num=i))
query_list.append(")")
query = "\n".join(query_list)
# ------------------------------

#Exécution de l'instruction SELECT
cursor = conn.cursor()
cursor.execute(query)

#Obtenir le résultat SELECT
for get_row in cursor:
    print(get_row[0])

#Fin de connexion
conn.close()

Par conséquent, l'instruction SELECT suivante est générée et la table avec le numéro de série peut être obtenue.

SQL généré


(SELECT 0 AS s
 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
)

Vous pouvez faire référence au numéro de série en faisant de la partie du numéro de série une sous-requête comme suit.

mysql_test2.py


# coding:utf-8
import mysql.connector

#Dictionnaire des informations de connexion
config = {
    'user': 'root',
    'password': 'password',
    'host': '123.45.67.890',
    'database': 'test'
}

#Lien
conn = mysql.connector.connect(**config)

# ------------------------------
#Génération de l'instruction SELECT de données de test
# ------------------------------
start_num = 0
end_num = 10
query_list = ["SELECT serial_num.s, md5(serial_num.s) FROM", "(SELECT {start_num} AS s".format(start_num=start_num)]
# 「UNION ALL SELECT {num}"Ajouter
for i in range(start_num + 1, end_num):
    query_list.append(" UNION ALL SELECT {num}".format(num=i))
query_list.append(") AS serial_num")
query = "\n".join(query_list)
# ------------------------------

#Exécution de l'instruction SELECT
cursor = conn.cursor()
cursor.execute(query)

#Obtenir le résultat SELECT
for get_row in cursor:
    print(get_row[0], get_row[1])

#Fin de connexion
conn.close()

Résultat d'exécution


0 cfcd208495d565ef66e7dff9f98764da
1 c4ca4238a0b923820dcc509a6f75849b
2 c81e728d9d4c2f636f067f89cc14862c
3 eccbc87e4b5ce2fe28308fd9f2a7baf3
4 a87ff679a2f3e71d9181a67b7542122c
5 e4da3b7fbbce2345d7772b0674a318d5
6 1679091c5a880faf6fb5e6087eb1b2dc
7 8f14e45fceea167a5a36dedd4bea2543
8 c9f0f895fb98ab9159f51fd0297e236d
9 45c48cce2e2d7fbdea1afc51c7c6ad26

SQL généré


SELECT serial_num.s, md5(serial_num.s) FROM
(SELECT 0 AS s
 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 serial_num

Créer une procédure pour la génération de données de test

Il est possible de générer une table énorme uniquement avec MySQL en utilisant une procédure sans utiliser de programme. Voici la procédure pour générer la table generate_series_tbl avec le nombre de lignes passées dans l'argument. Puisque DROP TABLE est fait au début, une nouvelle table est créée chaque fois qu'elle est exécutée.

Procédure de génération des données de test


DELIMITER //
CREATE PROCEDURE generate_series (IN max INT)
BEGIN
  DECLARE i INT;
  SET i = 0;
  DROP TABLE IF EXISTS generate_series_tbl;
  CREATE TABLE generate_series_tbl (num INT);
  WHILE i < max DO
    INSERT INTO generate_series_tbl VALUES (i);
    SET i = i + 1;
  END WHILE;
END
//
DELIMITER ;

Exemple d'exécution


CALL generate_series(100);

Query OK, 1 row affected (1.61 sec)


SELECT count(*) FROM generate_series_tbl;

+----------+
| count(*) |
+----------+
|      100 |
+----------+


SELECT * FROM generate_series_tbl;
+------+
| num  |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
…(Omission)…
|   97 |
|   98 |
|   99 |
+------+

Comme mentionné ci-dessus, un tableau avec des numéros de série de 0 à 99 a été généré. Bien sûr, puisque nous INSÉRONS les données réelles, plus la table est grande, plus il faudra de temps pour la générer. De plus, étant donné que l'énorme table sera laissée sans surveillance, il peut être bon de la générer en tant que table temporaire.

Lien de référence

À propos de la génération du numéro de série

À propos de mysql-connector-python

À propos de la procédure

Recommended Posts

Comment gérer des valeurs consécutives dans MySQL
Comment gérer une session dans SQLAlchemy
Comment utiliser Mysql avec python
Comment gérer le japonais avec Python
Comment gérer le type datetime dans sqlite3 de python
Comment développer en Python
Comment gérer les trames de données
[Python] Comment afficher les valeurs de liste dans l'ordre
Comment gérer JSON en Ruby, Python, JavaScript, PHP
[Django] Comment donner des valeurs d'entrée à l'avance avec ModelForm
Comment obtenir toutes les clés et valeurs du dictionnaire
[Blender] Comment gérer les événements de souris et de clavier dans les scripts Blender
Comment utiliser des valeurs discrètes comme variables dans Scipy Optimize
Comment rechercher par chaîne pour utiliser les json_contains de mysql dans SQL Alchemy
[Python] Comment faire PCA avec Python
Comment utiliser les classes dans Theano
Comment écrire sobrement avec des pandas
Comment collecter des images en Python
Comment mettre à jour Spyder dans Anaconda
Comment refléter CSS dans Django
Comment tuer des processus en vrac
[Python] Comment permuter les valeurs de tableau
Comment envelopper C en Python
Comment utiliser ChemSpider en Python
Comment utiliser PubChem avec Python
Comment exécuter du code TensorFlow 1.0 en 2.0
Comment se connecter à Docker + NGINX
Comment appeler PyTorch dans Julia
Comment obtenir toutes les valeurs possibles dans une expression régulière
<Pandas> Comment gérer les données de séries chronologiques dans le tableau croisé dynamique
Comment créer une grande quantité de données de test dans MySQL? ??
Comment utiliser les colonnes calculées dans CASTable
[Introduction à Python] Comment utiliser la classe en Python?
Comment supprimer l'erreur d'affichage dans matplotlib
Comment définir dynamiquement des variables en Python
Comment faire R chartr () en Python
Mettre ensemble des valeurs consécutives dans la liste
Comment supprimer des sessions expirées dans Django
[Itertools.permutations] Comment créer une séquence en Python
Comment utiliser Google Test en langage C
Comment implémenter un sérialiseur imbriqué avec drf-flex-fields
Comment exécuter des commandes avec Jupyter Notebook
Comment faire 'git fetch --tags' dans GitPython
Comment obtenir stacktrace en python
Comment afficher la table quatre-vingt-dix-neuf en python
Comment extraire une zone de polygone en Python
Comment réattribuer un index dans pandas dataframe
Comment vérifier la version d'opencv avec python
Comment activer SSL (TLS) dans Apache
Comment utiliser l'interpréteur d'Anaconda avec PyCharm
Comment spécifier une cible non vérifiée dans Flake8
Comment changer de version de Python dans cloud9
Comment régler le contraste de l'image en Python
Comment utiliser __slots__ dans la classe Python
Comment remplir dynamiquement des zéros avec Python
Comment faire des événements envoyés par le serveur dans Django
Comment utiliser les expressions régulières en Python