Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

MySQL Extension

The `mysql` extension allows DuckDB to directly read and write data from/to a running MySQL instance. The data can be queried directly from the underlying MySQL database. Data can be loaded from MySQL tables into DuckDB tables, or vice versa.

## Installing and Loading

To install the `mysql` extension, run:

    INSTALL mysql;

The extension is loaded automatically upon first use. If you prefer to load it manually, run:

    LOAD mysql;

## Reading Data from MySQL

To make a MySQL database accessible to DuckDB use the `ATTACH` command with the `MYSQL` or the `MYSQL_SCANNER` type:

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

### Configuration

The connection string determines the parameters for how to connect to MySQL as a set of `key=value` pairs. Any options not provided are replaced by their default values, as per the table below. Connection information can also be specified with environment variables. If no option is provided explicitly, the MySQL extension tries to read it from an environment variable.

Setting | Default | Environment variable
--- | --- | ---
database | NULL | MYSQL_DATABASE
host | localhost | MYSQL_HOST
password |  | MYSQL_PWD
port | 0 | MYSQL_TCP_PORT
socket | NULL | MYSQL_UNIX_PORT
user | ⟨current user⟩ | MYSQL_USER
ssl_mode | preferred |
ssl_ca |  |
ssl_capath |  |
ssl_cert |  |
ssl_cipher |  |
ssl_crl |  |
ssl_crlpath |  |
ssl_key |  |

### Configuring via Secrets

MySQL connection information can also be specified with secrets. The following syntax can be used to create a secret.

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

The information from the secret will be used when `ATTACH` is called. We can leave the connection string empty to use all of the information stored in the secret.

    ATTACH '' AS mysql_db (TYPE MYSQL);

We can use the connection string to override individual options. For example, to connect to a different database while still using the same credentials, we can override only the database name in the following manner.

    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

Named secrets can be used to manage connections to multiple MySQL database instances. Secrets can be given a name upon creation.

    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 Connections

The `ssl` connection parameters can be used to make SSL connections. Below is a description of the supported parameters.

Setting | Description
--- | ---
ssl_mode | The security state to use for the connection to the server: `disabled, required, verify_ca, verify_identity or preferred` (default: `preferred`)
ssl_ca | The path name of the Certificate Authority (CA) certificate file.
ssl_capath | The path name of the directory that contains trusted SSL CA certificate files.
ssl_cert | The path name of the client public key certificate file.
ssl_cipher | The list of permissible ciphers for SSL encryption.
ssl_crl | The path name of the file containing certificate revocation lists.
ssl_crlpath | The path name of the directory that contains files containing certificate revocation lists.
ssl_key | The path name of the client private key file.

### Reading MySQL Tables

The tables in the MySQL database can be read as if they were normal DuckDB tables, but the underlying data is read directly from MySQL at query time.

    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

It might be desirable to create a copy of the MySQL databases in DuckDB to prevent the system from re-reading the tables from MySQL continuously, particularly for large tables.

Data can be copied over from MySQL to DuckDB using standard SQL, for example:

    CREATE TABLE duckdb_table AS FROM mysqlscanner.mysql_table;

## Writing Data to MySQL

In addition to reading data from MySQL, create tables, ingest data into MySQL and make other modifications to a MySQL database using standard SQL queries.

This allows you to use DuckDB to, for example, export data that is stored in a MySQL database to Parquet, or read data from a Parquet file into MySQL.

Below is a brief example of how to create a new table in MySQL and load data into it.

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

Many operations on MySQL tables are supported. All these operations directly modify the MySQL database, and the result of subsequent operations can then be read using MySQL. Note that if modifications are not desired, `ATTACH` can be run with the `READ_ONLY` property which prevents making modifications to the underlying database. For example:

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

## Supported Operations

Below is a list of 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` and `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;

This returns:

i
42

    ROLLBACK;
    SELECT * FROM mysql_db.tmp;

This returns an empty table.

> The DDL statements are not transactional in MySQL.

## Running SQL Queries in MySQL

### The `mysql_query` Table Function

The `mysql_query` table function allows you to run arbitrary read queries within an attached database. `mysql_query` takes the name of the attached MySQL database to execute the query in, as well as the SQL query to execute. The result of the query is returned. Single-quote strings are escaped by repeating the single quote twice.

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

For example:

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

### The `mysql_execute` Function

The `mysql_execute` function allows running arbitrary queries within MySQL, including statements that update the schema and content of the database.

    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` | Whether or not to convert `BIT(1)` columns to `BOOLEAN` | `true`
`mysql_debug_show_queries` | DEBUG SETTING: print all queries sent to MySQL to stdout | `false`
`mysql_experimental_filter_pushdown` | Whether or not to use filter pushdown (currently experimental) | `false`
`mysql_tinyint1_as_boolean` | Whether or not to convert `TINYINT(1)` columns to `BOOLEAN` | `true`

## Schema Cache

To avoid having to continuously fetch schema data from MySQL, DuckDB keeps schema information – such as the names of tables, their columns, etc. – cached. If changes are made to the schema through a different connection to the MySQL instance, such as new columns being added to a table, the cached schema information might be outdated. In this case, the function `mysql_clear_cache` can be executed to clear the internal caches.

    CALL mysql_clear_cache();

##### About this page

- Visit the related GitHub repository
- Report content issue
- Edit this page on GitHub