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.

• Tant que le nombre de lignes est abondant, les données de contenu ne seront pas référencées, donc tout va bien.

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 |
+------+----------------------------------+

• Une chaîne de caractères est également générée à l'aide de la fonction MD5.

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

• ** Créez une table virtuelle avec des numéros de série dans MySQL ** https://blog.toshimaru.net/mysql-virtual-table/ Il est soigneusement résumé de la base à l'application, comme la génération normale du numéro de série et la méthode de doubler la valeur. En créant cet article, j'ai beaucoup fait référence à ce site.

• ** Introduction à Back MySQL Query (15) Application 3 Créer une table virtuelle avec des numéros de série dans MySQL ** https://it7c.hatenadiary.org/entry/20100713/1278950305 La méthode de génération de numéro de série par PHP est introduite. Je l'ai utilisé comme référence pour générer des numéros de série par le programme.

À propos de mysql-connector-python

• ** Connecteur MySQL / Guide du développeur Python ** https://dev.mysql.com/doc/connector-python/en/preface.html Le manuel officiel de mysql-connector-python. Obligatoire lorsque vous souhaitez en savoir plus sur les options et les fonctionnalités détaillées.

• ** Appuyez sur MySQL depuis Python 3 ** https://qiita.com/hoto17296/items/0cfe7cdd3c47b69cc892 L'utilisation de base de mysql-connector-python (* pour être exact, plus ancien mysql-connector-python-rf) est expliquée en japonais.

À propos de la procédure

• ** Quelque chose de basique sur les procédures stockées **
  https://qiita.com/setsuna82001/items/e742338eb93e3a48ba46
  Il explique attentivement, depuis les bases jusqu'à l'application, comment générer des procédures stockées dans MySQL.