MySQL-Erweiterung

Die mysql-Erweiterung ermöglicht DuckDB, Daten direkt aus einer laufenden MySQL-Instanz zu lesen und in sie zu schreiben. Die Daten können direkt aus der zugrunde liegenden MySQL-Datenbank abgefragt werden. Daten können aus MySQL-Tabellen in DuckDB-Tabellen geladen werden oder umgekehrt.

Installation und Laden

Um die mysql-Erweiterung zu installieren, führen Sie aus:

INSTALL mysql;

Die Erweiterung wird beim ersten Gebrauch automatisch geladen. Wenn Sie sie lieber manuell laden möchten, führen Sie aus:

LOAD mysql;

Daten aus MySQL lesen

Um eine MySQL-Datenbank für DuckDB zugänglich zu machen, verwenden Sie den Befehl ATTACH mit dem Typ mysql oder mysql_scanner:

ATTACH 'host=localhost user=root port=0 database=mysql' AS mysqldb (TYPE mysql);
USE mysqldb;

Konfiguration

Die Verbindungszeichenkette bestimmt die Parameter für die Verbindung zu MySQL als Menge von key=value-Paaren. Nicht angegebene Optionen werden durch ihre Standardwerte gemäß der folgenden Tabelle ersetzt. Verbindungsinformationen können auch über Umgebungsvariablen angegeben werden. Wird eine Option nicht explizit gesetzt, versucht die MySQL-Erweiterung, sie aus einer Umgebungsvariable zu lesen.

Setting	Standard	Umgebungsvariable
database	NULL	MYSQL_DATABASE
host	localhost	MYSQL_HOST
password		MYSQL_PWD
port	0	MYSQL_TCP_PORT
socket	NULL	MYSQL_UNIX_PORT
user	aktueller Benutzer	MYSQL_USER
ssl_mode	preferred
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_crl
ssl_crlpath
ssl_key

Konfiguration über Secrets

MySQL-Verbindungsinformationen können auch über Secrets angegeben werden. Die folgende Syntax kann verwendet werden, um ein Secret zu erstellen.

CREATE SECRET (
    TYPE mysql,
    HOST '127.0.0.1',
    PORT 0,
    DATABASE mysql,
    USER 'mysql',
    PASSWORD ''
);

Die Informationen aus dem Secret werden verwendet, wenn ATTACH aufgerufen wird. Wir können die Verbindungszeichenkette leer lassen, um alle im Secret gespeicherten Informationen zu verwenden.

ATTACH '' AS mysql_db (TYPE mysql);

Wir können die Verbindungszeichenkette verwenden, um einzelne Optionen zu überschreiben. Um beispielsweise eine andere Datenbank zu verbinden und dabei dieselben Anmeldedaten zu verwenden, können wir nur den Datenbanknamen wie folgt überschreiben.

ATTACH 'database=my_other_db' AS mysql_db (TYPE mysql);

Standardmäßig sind erstellte Secrets temporär. Secrets können mit dem Befehl CREATE PERSISTENT SECRET dauerhaft gespeichert werden. Persistente Secrets können sitzungsübergreifend verwendet werden.

Mehrere Secrets verwalten

Benannte Secrets können verwendet werden, um Verbindungen zu mehreren MySQL-Datenbankinstanzen zu verwalten. Secrets können bei der Erstellung einen Namen erhalten.

CREATE SECRET mysql_secret_one (
    TYPE mysql,
    HOST '127.0.0.1',
    PORT 0,
    DATABASE mysql,
    USER 'mysql',
    PASSWORD ''
);

Das Secret kann dann mit dem Parameter SECRET in ATTACH explizit referenziert werden.

ATTACH '' AS mysql_db_one (TYPE mysql, SECRET mysql_secret_one);

SSL-Verbindungen

Die ssl-Verbindungsparameter können für SSL-Verbindungen verwendet werden. Nachfolgend eine Beschreibung der unterstützten Parameter.

Setting	Beschreibung
ssl_mode	Der Sicherheitszustand für die Verbindung zum Server: disabled, required, verify_ca, verify_identity oder preferred (Standard: preferred)
ssl_ca	Der Pfadname der Certificate-Authority-(CA-)Zertifikatsdatei
ssl_capath	Der Pfadname des Verzeichnisses, das vertrauenswürdige SSL-CA-Zertifikatsdateien enthält
ssl_cert	Der Pfadname der öffentlichen Client-Schlüsselzertifikatsdatei
ssl_cipher	Die Liste zulässiger Chiffren für die SSL-Verschlüsselung
ssl_crl	Der Pfadname der Datei mit Zertifikatssperrlisten
ssl_crlpath	Der Pfadname des Verzeichnisses, das Dateien mit Zertifikatssperrlisten enthält
ssl_key	Der Pfadname der privaten Client-Schlüsseldatei

MySQL-Tabellen lesen

Die Tabellen in der MySQL-Datenbank können gelesen werden, als wären sie normale DuckDB-Tabellen, die zugrunde liegenden Daten werden jedoch zur Abfragezeit direkt aus MySQL gelesen.

SHOW ALL TABLES;

name
signed_integers

SELECT * FROM signed_integers;

t	s	m	i	b
-128	-32768	-8388608	-2147483648	-9223372036854775808
127	32767	8388607	2147483647	9223372036854775807
NULL	NULL	NULL	NULL	NULL

Es kann wünschenswert sein, eine Kopie der MySQL-Datenbanken in DuckDB anzulegen, damit das System die Tabellen nicht fortlaufend aus MySQL neu liest, insbesondere bei großen Tabellen.

Daten können mit Standard-SQL von MySQL nach DuckDB kopiert werden, zum Beispiel:

CREATE TABLE duckdb_table AS FROM mysqlscanner.mysql_table;

Daten nach MySQL schreiben

Zusätzlich zum Lesen von Daten aus MySQL können Sie Tabellen erstellen, Daten in MySQL laden und weitere Änderungen an einer MySQL-Datenbank mit Standard-SQL-Abfragen vornehmen.

So können Sie DuckDB beispielsweise verwenden, um in einer MySQL-Datenbank gespeicherte Daten nach Parquet zu exportieren oder Daten aus einer Parquet-Datei nach MySQL zu lesen.

Nachfolgend ein kurzes Beispiel, wie Sie eine neue Tabelle in MySQL erstellen und Daten darin laden.

ATTACH 'host=localhost user=root port=0 database=mysqlscanner' AS mysql_db (TYPE mysql);
CREATE TABLE mysql_db.tbl (id INTEGER, name VARCHAR);
INSERT INTO mysql_db.tbl VALUES (42, 'DuckDB');

Viele Operationen auf MySQL-Tabellen werden unterstützt. All diese Operationen ändern die MySQL-Datenbank direkt, und das Ergebnis nachfolgender Operationen kann dann mit MySQL gelesen werden. Falls keine Änderungen gewünscht sind, kann ATTACH mit der Eigenschaft READ_ONLY ausgeführt werden, die Änderungen an der zugrunde liegenden Datenbank verhindert. Zum Beispiel:

ATTACH 'host=localhost user=root port=0 database=mysqlscanner' AS mysql_db (TYPE mysql, READ_ONLY);

Unterstützte Operationen

Nachfolgend eine Liste der unterstützten Operationen.

CREATE TABLE

CREATE TABLE mysql_db.tbl (id INTEGER, name VARCHAR);

INSERT INTO

INSERT INTO mysql_db.tbl VALUES (42, 'DuckDB');

SELECT

SELECT * FROM mysql_db.tbl;

id	name
42	DuckDB

COPY

COPY mysql_db.tbl TO 'data.parquet';
COPY mysql_db.tbl FROM 'data.parquet';

Sie können auch eine vollständige Kopie der Datenbank mit der Anweisung COPY FROM DATABASE erstellen:

COPY FROM DATABASE mysql_db TO my_duckdb_db;

UPDATE

UPDATE mysql_db.tbl
SET name = 'Woohoo'
WHERE id = 42;

DELETE

DELETE FROM mysql_db.tbl
WHERE id = 42;

ALTER TABLE

ALTER TABLE mysql_db.tbl
ADD COLUMN k INTEGER;

DROP TABLE

DROP TABLE mysql_db.tbl;

CREATE VIEW

CREATE VIEW mysql_db.v1 AS SELECT 42;

CREATE SCHEMA und DROP SCHEMA

CREATE SCHEMA mysql_db.s1;
CREATE TABLE mysql_db.s1.integers (i INTEGER);
INSERT INTO mysql_db.s1.integers VALUES (42);
SELECT * FROM mysql_db.s1.integers;

i
42

DROP SCHEMA mysql_db.s1;

Transaktionen

CREATE TABLE mysql_db.tmp (i INTEGER);
BEGIN;
INSERT INTO mysql_db.tmp VALUES (42);
SELECT * FROM mysql_db.tmp;

Das liefert:

i
42

ROLLBACK;
SELECT * FROM mysql_db.tmp;

Das liefert eine leere Tabelle.

Die DDL-Anweisungen sind in MySQL nicht transaktional.

SQL-Abfragen in MySQL ausführen

Die Tabellenfunktion mysql_query

Die Tabellenfunktion mysql_query ermöglicht es, beliebige Leseabfragen in einer angehängten Datenbank auszuführen. mysql_query nimmt den Namen der angehängten MySQL-Datenbank, in der die Abfrage ausgeführt werden soll, sowie die auszuführende SQL-Abfrage entgegen. Das Ergebnis der Abfrage wird zurückgegeben. Einfache Anführungszeichen in Zeichenketten werden durch Verdopplung des einfachen Anführungszeichens escaped.

mysql_query(attached_database::VARCHAR, query::VARCHAR)

Zum Beispiel:

ATTACH 'host=localhost database=mysql' AS mysqldb (TYPE mysql);
SELECT * FROM mysql_query('mysqldb', 'SELECT * FROM cars LIMIT 3');

Die Funktion mysql_execute

Die Funktion mysql_execute ermöglicht das Ausführen beliebiger Abfragen in MySQL, einschließlich Anweisungen, die das Schema und den Inhalt der Datenbank aktualisieren.

ATTACH 'host=localhost database=mysql' AS mysqldb (TYPE mysql);
CALL mysql_execute('mysqldb', 'CREATE TABLE my_table (i INTEGER)');

Einstellungen

Name	Beschreibung	Standard
mysql_bit1_as_boolean	Ob BIT(1)-Spalten in BOOLEAN umgewandelt werden sollen	true
mysql_debug_show_queries	DEBUG-EINSTELLUNG: alle an MySQL gesendeten Abfragen auf stdout ausgeben	false
mysql_enable_filter_pushdown	Ob Filter-Pushdown verwendet werden soll (ohne Prädikatanalyse)	true
mysql_enable_transactions	Ob START TRANSACTION / COMMIT / ROLLBACK auf MySQL-Verbindungen ausgeführt werden soll	true
mysql_incomplete_dates_as_nulls	Ob DATEs mit Monat oder Tag null als NULLs zurückgegeben werden sollen	false
mysql_pool_acquire_mode	Wie Verbindungen aus dem Pool bezogen werden: force (immer verbinden, Pool-Limit ignorieren), wait (blockieren, bis eine verfügbar ist) oder try (sofort fehlschlagen, wenn keine verfügbar ist)	force
mysql_pool_connection_idle_timeout_millis	Maximale Zeit in Millisekunden, die eine Verbindung idle im Cache bleiben kann, bevor sie geschlossen wird	60000
mysql_pool_connection_max_lifetime_millis	Maximales Alter in Millisekunden einer gepoolten Verbindung seit dem ersten Öffnen; bei Überschreitung wird die Verbindung geschlossen, statt in den Cache zurückgegeben zu werden (0 deaktiviert dies)	0
mysql_pool_enable_reaper_thread	Ob ein eigener Thread laufen soll, der den Pool periodisch prüft und abgelaufene Verbindungen entfernt	true
mysql_pool_enable_thread_local_cache	Thread-lokales Verbindungs-Caching für schnellere Wiederverwendung derselben Verbindung im selben Thread aktivieren	false
mysql_pool_size	Maximale Anzahl von Verbindungen pro MySQL-Katalog	automatisch (basierend auf der CPU-Anzahl)
mysql_pool_wait_timeout_millis	Timeout in Millisekunden beim Warten auf eine Verbindung aus dem Pool	30000
mysql_session_time_zone	Sitzungszeitzone, die für neu geöffnete Verbindungen zum MySQL-Server gesetzt wird	''
mysql_time_as_time	Ob MySQL-TIME-Spalten in DuckDB-TIME umgewandelt werden sollen	false
mysql_tinyint1_as_boolean	Ob TINYINT(1)-Spalten in BOOLEAN umgewandelt werden sollen	true

Schema-Cache

Um Schema-Daten nicht fortlaufend aus MySQL holen zu müssen, hält DuckDB Schema-Informationen – etwa Tabellennamen, ihre Spalten usw. – im Cache. Werden Schema-Änderungen über eine andere Verbindung zur MySQL-Instanz vorgenommen, etwa indem einer Tabelle neue Spalten hinzugefügt werden, können die gecachten Schema-Informationen veraltet sein. In diesem Fall kann die Funktion mysql_clear_cache ausgeführt werden, um die internen Caches zu leeren.

CALL mysql_clear_cache();