In letzter Zeit habe ich immer mehr Möglichkeiten, MySQL zu verwenden. Unter ihnen gab es gelegentlich Fälle, in denen ich eine Seriennummer haben wollte, z. B. das Generieren von Testdaten oder das Generieren aufeinanderfolgender Daten.
Die Seriennummer hat die Funktion generate_series () für PostgreSQL und die LEVEL-Pseudospalte für Oracle. Da MySQL jedoch nicht das richtige hat, musste ich verschiedene Dinge entwickeln.
Dieses Mal möchte ich den "Einfallsreichtum" der Generierung von Seriennummern mit MySQL zusammenfassen.
Angenommen, Sie haben eine Tabelle mit den folgenden Früchten und deren Preisen:
Obsttisch
SELECT * FROM sample;
+-----------+-------+
| name | price |
+-----------+-------+
|Apfel| 100 |
|Mandarine| 80 |
|Banane| 120 |
+-----------+-------+
Gehen Sie wie folgt vor, um sie in aufsteigender Reihenfolge des Preises zu nummerieren und anzuordnen.
Geben Sie den Obsttabellen Seriennummern
SET @num=0;
SELECT
@num:=@num+1 AS num,
name,
price
FROM sample
ORDER BY price;
+------+-----------+-------+
| num | name | price |
+------+-----------+-------+
| 1 |Mandarine| 80 |
| 2 |Apfel| 100 |
| 3 |Banane| 120 |
+------+-----------+-------+
Durch Definieren der Variablen " @ </ span> num" können Sie einfach Seriennummern wie oben beschrieben zuweisen. Denken Sie daran, "SET @ </ span> num = 0;" hier zu speichern. Da die Variable " @ </ span> num" weiterhin schwankt, führt das erneute Ausführen der obigen SELECT-Anweisung zu folgendem Ergebnis.
@Führen Sie die SELECT-Anweisung erneut aus, ohne num zu initialisieren
SELECT
@num:=@num+1 AS num,
name,
price
FROM sample
ORDER BY price;
+------+-----------+-------+
| num | name | price |
+------+-----------+-------+
| 4 |Mandarine| 80 |
| 5 |Apfel| 100 |
| 6 |Banane| 120 |
+------+-----------+-------+
Seit " @ </ span> num" das letzte Mal 3 war, hat es weiter zugenommen. Daher ist eine Initialisierung mit "SET @ </ span> num = 0;" erforderlich.
Das Schöne an der Funktion zur Generierung von DB-Seriennummern ist, dass Sie problemlos Testdaten generieren können. Da MySQL jedoch nicht die richtige Funktion hat, ist ein gewisser Einfallsreichtum erforderlich.
Wie bereits erläutert, können Sie vorhandenen Tabellen problemlos Seriennummern zuweisen. Wenn MySQL über eine Tabelle verfügt, auf die jeder Benutzer leicht zugreifen kann, kann daher eine Tabelle mit nur Seriennummern erstellt werden.
Viele Leute scheinen "information_schema.COLUMNS" als solch eine bequeme Tabelle zu verwenden. So erstellen Sie beispielsweise eine Testtabelle mit einer Seriennummer von 10 Zeilen:
10 Zeilen Testdatengenerierung
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 |
+------+----------------------------------+
Die Anzahl der Zeilen in der Tabelle, die mit dieser Methode generiert werden können, hängt jedoch von der Anzahl der Zeilen in "information_schema.COLUMNS" ab. Um darüber hinaus Testdaten zu generieren, müssen Sie UNION gut verwenden oder eine eigene Tabelle mit einer sehr großen Anzahl von Zeilen erstellen.
Durch Verketten von Tabellen mit UNION können Sie eine Tabelle mit einer beliebigen Anzahl von Zeilen generieren, wie unten gezeigt.
Es ist jedoch nicht praktisch, Tausende von Zeilen manuell zu generieren. Es ist einfach, in einer Programmiersprache eine Verbindung zu MySQL herzustellen und diese zu durchlaufen.
Dieses Mal werde ich versuchen, eine Verbindung von Python mit mysql-connector-python herzustellen.
mysql_test.py
# coding:utf-8
import mysql.connector
#Verbindungsinformationswörterbuch
config = {
'user': 'root',
'password': 'password',
'host': '123.45.67.890',
'database': 'test'
}
#Verbindung
conn = mysql.connector.connect(**config)
# ------------------------------
#Testdaten SELECT-Anweisungsgenerierung
# ------------------------------
start_num = 0
end_num = 10
query_list = ["(SELECT {start_num} AS s".format(start_num=start_num)]
# 「UNION ALL SELECT {num}"Hinzufügen
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)
# ------------------------------
#Ausführung der SELECT-Anweisung
cursor = conn.cursor()
cursor.execute(query)
#Holen Sie sich das SELECT-Ergebnis
for get_row in cursor:
print(get_row[0])
#Verbindungsende
conn.close()
Als Ergebnis wird die folgende SELECT-Anweisung generiert und die Tabelle mit der Seriennummer kann abgerufen werden.
Generiertes SQL
(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
)
Sie können auf die Seriennummer verweisen, indem Sie den Seriennummernteil wie folgt zu einer Unterabfrage machen.
mysql_test2.py
# coding:utf-8
import mysql.connector
#Verbindungsinformationswörterbuch
config = {
'user': 'root',
'password': 'password',
'host': '123.45.67.890',
'database': 'test'
}
#Verbindung
conn = mysql.connector.connect(**config)
# ------------------------------
#Testdaten SELECT-Anweisungsgenerierung
# ------------------------------
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}"Hinzufügen
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)
# ------------------------------
#Ausführung der SELECT-Anweisung
cursor = conn.cursor()
cursor.execute(query)
#Holen Sie sich das SELECT-Ergebnis
for get_row in cursor:
print(get_row[0], get_row[1])
#Verbindungsende
conn.close()
Ausführungsergebnis
0 cfcd208495d565ef66e7dff9f98764da
1 c4ca4238a0b923820dcc509a6f75849b
2 c81e728d9d4c2f636f067f89cc14862c
3 eccbc87e4b5ce2fe28308fd9f2a7baf3
4 a87ff679a2f3e71d9181a67b7542122c
5 e4da3b7fbbce2345d7772b0674a318d5
6 1679091c5a880faf6fb5e6087eb1b2dc
7 8f14e45fceea167a5a36dedd4bea2543
8 c9f0f895fb98ab9159f51fd0297e236d
9 45c48cce2e2d7fbdea1afc51c7c6ad26
Generiertes SQL
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
Sie können mit nur MySQL eine große Tabelle erstellen, indem Sie eine Prozedur ohne Programm verwenden. Im Folgenden wird die Prozedur zum Generieren der Tabelle generate_series_tbl mit den im Argument übergebenen Zahlenzeilen beschrieben. Da DROP TABLE zu Beginn ausgeführt wird, wird bei jeder Ausführung eine neue Tabelle erstellt.
Testdatengenerierungsverfahren
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 ;
Ausführungsbeispiel
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 |
…(Unterlassung)…
| 97 |
| 98 |
| 99 |
+------+
Wie oben erwähnt, wurde eine Tabelle mit Seriennummern von 0 bis 99 erzeugt. Da wir die tatsächlichen Daten einfügen, dauert die Generierung der Tabelle natürlich umso länger. Da die große Tabelle unbeaufsichtigt bleibt, kann es auch sinnvoll sein, sie als temporäre Tabelle zu generieren.
** Erstellen Sie eine virtuelle Tabelle mit Seriennummern in MySQL ** https://blog.toshimaru.net/mysql-virtual-table/ Es wird sorgfältig von einfach bis angewendet zusammengefasst, wie z. B. die normale Generierung von Seriennummern und die Methode zur Verdoppelung des Werts. Bei der Erstellung dieses Artikels habe ich sehr viel auf diese Site verwiesen.
** Einführung in Back MySQL Query (15) Anwendung 3 Erstellen Sie eine virtuelle Tabelle mit Seriennummern in MySQL ** https://it7c.hatenadiary.org/entry/20100713/1278950305 Die Methode zur Generierung von Seriennummern durch PHP wird eingeführt. Ich habe es als Referenz für die Generierung von Seriennummern durch das Programm verwendet.
** MySQL Connector / Python-Entwicklerhandbuch ** https://dev.mysql.com/doc/connector-python/en/preface.html Das offizielle Handbuch für MySQL-Connector-Python. Erforderlich, wenn Sie sich über Optionen und detaillierte Funktionen informieren möchten.
** Berühre MySQL aus Python 3 ** https://qiita.com/hoto17296/items/0cfe7cdd3c47b69cc892 Die grundlegende Verwendung von MySQL-Connector-Python (* um genau zu sein, älter MySQL-Connector-Python-RF) wird auf Japanisch erklärt.
Recommended Posts