MySQL Extension

Search Shortcut cmd + k | ctrl + k

Documentation / Extensions

MySQL Extension

mysql 扩展允许 DuckDB 直接从运行的 MySQL 实例中读取和写入数据。数据可以直接从底层的 MySQL 数据库中查询。数据可以从 MySQL 表加载到 DuckDB 表中，反之亦然。

Installing and Loading

要安装mysql扩展，请运行：

INSTALL mysql;

扩展在首次使用时自动加载。如果您希望手动加载，请运行：

LOAD mysql;

从MySQL读取数据

为了使MySQL数据库对DuckDB可访问，使用ATTACH命令与MYSQL或MYSQL_SCANNER类型：

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

Configuration

连接字符串决定了如何连接到MySQL的参数，作为一组key=value对。任何未提供的选项将根据下表替换为其默认值。连接信息也可以通过环境变量指定。如果没有明确提供选项，MySQL扩展会尝试从环境变量中读取。

设置	默认	环境变量
数据库	NULL	MYSQL_DATABASE
主机	localhost	MYSQL_HOST
密码		MYSQL_PWD
端口	0	MYSQL_TCP_PORT
socket	NULL	MYSQL_UNIX_PORT
用户	⟨当前用户⟩	MYSQL_USER
ssl_mode	preferred
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_crl
ssl_crlpath
ssl_key

Configuring via Secrets

MySQL连接信息也可以通过secrets来指定。以下语法可用于创建secret。

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

当调用ATTACH时，将使用来自secret的信息。我们可以将连接字符串留空，以使用存储在secret中的所有信息。

ATTACH '' AS mysql_db (TYPE MYSQL);

我们可以使用连接字符串来覆盖个别选项。例如，为了连接到不同的数据库，同时仍然使用相同的凭据，我们可以仅以下列方式覆盖数据库名称。

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

By default, created secrets are temporary. Secrets can be persisted using the CREATE PERSISTENT SECRET command. Persistent secrets can be used across sessions.

Managing Multiple Secrets

命名的密钥可用于管理与多个MySQL数据库实例的连接。密钥在创建时可以指定名称。

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

The secret can then be explicitly referenced using the SECRET parameter in the ATTACH.

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

SSL 连接

ssl 连接参数可以用于建立SSL连接。以下是支持的参数描述。

设置	描述
ssl_mode	用于连接到服务器的安全状态：`disabled, required, verify_ca, verify_identity or preferred`（默认：`preferred`）
ssl_ca	证书颁发机构（CA）证书文件的路径名。
ssl_capath	包含受信任的SSL CA证书文件的目录路径名。
ssl_cert	客户端公钥证书文件的路径名。
ssl_cipher	SSL加密允许的密码列表。
ssl_crl	包含证书吊销列表的文件路径名。
ssl_crlpath	包含证书吊销列表文件的目录的路径名。
ssl_key	客户端私钥文件的路径名称。

读取MySQL表

MySQL数据库中的表可以像普通的DuckDB表一样读取，但底层数据在查询时直接从MySQL读取。

SHOW ALL TABLES;

name
有符号整数

SELECT * FROM signed_integers;

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

可能需要在DuckDB中创建MySQL数据库的副本，以防止系统不断从MySQL重新读取表，特别是对于大型表。

数据可以使用标准SQL从MySQL复制到DuckDB，例如：

CREATE TABLE duckdb_table AS FROM mysqlscanner.mysql_table;

将数据写入MySQL

除了从MySQL读取数据外，还可以创建表、将数据导入MySQL，并使用标准SQL查询对MySQL数据库进行其他修改。

这允许您使用DuckDB，例如，将存储在MySQL数据库中的数据导出到Parquet，或将数据从Parquet文件读取到MySQL中。

以下是一个简要示例，展示如何在MySQL中创建新表并将数据加载到其中。

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');

支持对MySQL表的许多操作。所有这些操作都会直接修改MySQL数据库，然后可以使用MySQL读取后续操作的结果。请注意，如果不希望进行修改，可以使用ATTACH命令并设置READ_ONLY属性，这样可以防止对底层数据库进行修改。例如：

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

Supported Operations

以下是支持的操作列表。

`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';

You may also create a full copy of the database using the COPY FROM DATABASE statement:

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` 和 `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;

Transactions

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

这将返回：

i
42

ROLLBACK;
SELECT * FROM mysql_db.tmp;

这将返回一个空表。

DDL语句在MySQL中不是事务性的。

在MySQL中运行SQL查询

The `mysql_query` 表函数

mysql_query 表函数允许您在附加的数据库中运行任意读取查询。mysql_query 接受要执行查询的附加 MySQL 数据库的名称以及要执行的 SQL 查询。查询的结果将被返回。单引号字符串通过重复单引号两次来转义。

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

例如：

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

The `mysql_execute` 函数

mysql_execute 函数允许在 MySQL 中运行任意查询，包括更新数据库模式和内容的语句。

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

Settings

Name	Description	Default
`mysql_bit1_as_boolean`	是否将 `BIT(1)` 列转换为 `BOOLEAN`	`true`
`mysql_debug_show_queries`	调试设置：将所有发送到MySQL的查询打印到标准输出	`false`
`mysql_experimental_filter_pushdown`	是否使用过滤器下推（目前为实验性功能）	`false`
`mysql_tinyint1_as_boolean`	是否将`TINYINT(1)`列转换为`BOOLEAN`	`true`

Schema Cache

为了避免不断从MySQL获取模式数据，DuckDB会将模式信息（如表名、列名等）缓存起来。如果通过不同的连接对MySQL实例的模式进行了更改，例如向表中添加了新列，缓存的模式信息可能会过时。在这种情况下，可以执行函数mysql_clear_cache来清除内部缓存。

CALL mysql_clear_cache();