> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-revert-104359-revert-104251-parquet-single.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for MySQL Table Engine

# MySQL table engine

The MySQL engine allows you to perform `SELECT` and `INSERT` queries on data that is stored on a remote MySQL server.

<h2 id="creating-a-table">
  Creating a table
</h2>

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = MySQL({host:port, database, table, user, password[, replace_query, on_duplicate_clause] | named_collection[, option=value [,..]]})
SETTINGS
    [ connection_pool_size=16, ]
    [ connection_max_tries=3, ]
    [ connection_wait_timeout=5, ]
    [ connection_auto_close=true, ]
    [ connect_timeout=10, ]
    [ read_write_timeout=300, ]
    [ enable_compression=false ]
;
```

See a detailed description of the [CREATE TABLE](/reference/statements/create/table) query.

The table structure can differ from the original MySQL table structure:

* Column names should be the same as in the original MySQL table, but you can use just some of these columns and in any order.
* Column types may differ from those in the original MySQL table. ClickHouse tries to [cast](/reference/engines/database-engines/mysql#data_types-support) values to the ClickHouse data types.
* The [external\_table\_functions\_use\_nulls](/reference/settings/session-settings/external-table#external_table_functions_use_nulls) setting defines how to handle Nullable columns. Default value: 1. If 0, the table function does not make Nullable columns and inserts default values instead of nulls. This is also applicable for NULL values inside arrays.

**Engine Parameters**

* `host:port` — MySQL server address.
* `database` — Remote database name.
* `table` — Remote table name, or a query passed to MySQL as is (see [Passing a query instead of a table name](#passing-a-query)).
* `user` — MySQL user.
* `password` — User password.
* `replace_query` — Flag that converts `INSERT INTO` queries to `REPLACE INTO`. If `replace_query=1`, the query is substituted.
* `on_duplicate_clause` — The `ON DUPLICATE KEY on_duplicate_clause` expression that is added to the `INSERT` query.
  Example: `INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1`, where `on_duplicate_clause` is `UPDATE c2 = c2 + 1`. See the [MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) to find which `on_duplicate_clause` you can use with the `ON DUPLICATE KEY` clause.
  To specify `on_duplicate_clause` you need to pass `0` to the `replace_query` parameter. If you simultaneously pass `replace_query = 1` and `on_duplicate_clause`, ClickHouse generates an exception.

Arguments also can be passed using [named collections](/concepts/features/configuration/server-config/named-collections). In this case `host` and `port` should be specified separately. This approach is recommended for production environment.

Simple `WHERE` clauses such as `=, !=, >, >=, <, <=` are executed on the MySQL server.

The rest of the conditions and the `LIMIT` sampling constraint are executed in ClickHouse only after the query to MySQL finishes.

<h2 id="tls-ssl">
  TLS/SSL
</h2>

The credentials of an encrypted connection to MySQL are passed as named collection keys (or as key-value arguments):

| Parameter      | Description                                                                           |
| -------------- | ------------------------------------------------------------------------------------- |
| `ssl_ca_pem`   | Contents of the CA certificate that the MySQL server certificate is verified against. |
| `ssl_cert_pem` | Contents of the client certificate, for certificate-based authentication.             |
| `ssl_key_pem`  | Contents of the private key belonging to `ssl_cert_pem`.                              |

The values are the contents of the corresponding PEM files, which can be copied into a named collection or into a query. They are masked in logs and in `SHOW` queries, the same way passwords are.

The same credentials can also be given as paths to files on the server, in `ssl_ca`, `ssl_cert` and `ssl_key` — but **only in a named collection defined in the server configuration file**, and such a value cannot be overridden in a query. The server opens those files with its own privileges, so accepting a path from SQL would let any user who is able to define a MySQL source probe the local filesystem, and authenticate with a certificate and key they are not allowed to read themselves.

```xml theme={null}
<named_collections>
    <mysql_creds>
        <host>mysql-host</host>
        <port>3306</port>
        <user>mysql_user</user>
        <password>****</password>
        <ssl_ca>/etc/clickhouse-server/mysql-ca.crt</ssl_ca>
    </mysql_creds>
</named_collections>
```

<h2 id="passing-a-query">
  Passing a query instead of a table name
</h2>

Instead of a table name, the `table` argument can be a `SELECT` query that is passed to MySQL as is. The structure of the table is inferred from the query result. The query can be written either as a subquery, or wrapped into the `query` function:

```sql theme={null}
CREATE TABLE mysql_table ENGINE = MySQL('localhost:3306', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE mysql_table ENGINE = MySQL('localhost:3306', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');
```

This is useful to push down joins, aggregations or any other processing to MySQL. Such a table is read-only: `INSERT` into it is not allowed. The same syntax is supported by the [`mysql`](/reference/functions/table-functions/mysql) table function.

<Note>
  The subquery form `(SELECT ...)` is parsed by ClickHouse and re-serialized in the MySQL dialect (backtick identifier quoting) before being sent to the server. It must therefore be valid ClickHouse SQL. To pass MySQL-specific syntax that ClickHouse does not parse, use the `query('...')` form, whose text is sent to MySQL verbatim.

  Any outer `WHERE`, `LIMIT`, aggregation, etc. of the surrounding ClickHouse query is **not** pushed down into the passed query — it is applied in ClickHouse after the full query result is fetched. To restrict the data read from MySQL, put the filter inside the passed query. With [`external_table_strict_query = 1`](/reference/settings/session-settings/external-table#external_table_strict_query) an outer filter on the columns of the table is rejected with an exception instead of being applied locally, because it cannot be pushed into the passed query. The check covers the top-level `WHERE` predicate and each conjunct of a top-level `AND`. A `PREWHERE` on the columns of this table is not a case for this setting: this table engine do not support `PREWHERE`, and such a query is rejected with `ILLEGAL_PREWHERE` regardless of the setting. With the analyzer (the default), the check runs only where a filter could be pushed down at all: when this table is the only table of the query, on either side of an `INNER JOIN`, or on the preserving side of an outer join (the left side of a `LEFT JOIN`, the right side of a `RIGHT JOIN`). On the non-preserving side of a `LEFT`/`RIGHT JOIN` and on either side of a `FULL JOIN` nothing is pushed down and nothing is checked, so a filter on the columns of this table is applied locally after the join even in strict mode. Where the check runs, a predicate that references other tables joined in the surrounding query is not pushed down and is excluded from the check, whether it references only the joined side or mixes it with this table inside one non-`AND` expression (for example an `OR`); such a predicate keeps its usual ClickHouse evaluation point (`WHERE` after the join, `PREWHERE` before it) and is not rejected. With the old analyzer (`enable_analyzer = 0`) this scoping does not apply: the whole outer filter is checked when this table is the first table of the join tree, including a predicate on the joined side, and a joined right-hand table is not checked.
</Note>

Supports multiple replicas that must be listed by `|`. For example:

```sql theme={null}
CREATE TABLE test_replicas (id UInt32, name String, age UInt32, money UInt32) ENGINE = MySQL(`mysql{2|3|4}:3306`, 'clickhouse', 'test_replicas', 'root', 'clickhouse');
```

<h2 id="usage-example">
  Usage example
</h2>

Create table in MySQL:

```text theme={null}
mysql> CREATE TABLE `test`.`test` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `int_nullable` INT NULL DEFAULT NULL,
    ->   `float` FLOAT NOT NULL,
    ->   `float_nullable` FLOAT NULL DEFAULT NULL,
    ->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)

mysql> insert into test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)

mysql> select * from test;
+------+----------+-----+----------+
| int_id | int_nullable | float | float_nullable |
+------+----------+-----+----------+
|      1 |         NULL |     2 |           NULL |
+------+----------+-----+----------+
1 row in set (0,00 sec)
```

Create table in ClickHouse using plain arguments:

```sql theme={null}
CREATE TABLE mysql_table
(
    `float_nullable` Nullable(Float32),
    `int_id` Int32
)
ENGINE = MySQL('localhost:3306', 'test', 'test', 'bayonet', '123')
```

Or using [named collections](/concepts/features/configuration/server-config/named-collections):

```sql theme={null}
CREATE NAMED COLLECTION creds AS
        host = 'localhost',
        port = 3306,
        database = 'test',
        user = 'bayonet',
        password = '123';
CREATE TABLE mysql_table
(
    `float_nullable` Nullable(Float32),
    `int_id` Int32
)
ENGINE = MySQL(creds, table='test')
```

Retrieving data from MySQL table:

```sql theme={null}
SELECT * FROM mysql_table
```

```text theme={null}
┌─float_nullable─┬─int_id─┐
│           ᴺᵁᴸᴸ │      1 │
└────────────────┴────────┘
```

<h2 id="mysql-settings">
  Settings
</h2>

Default settings are not very efficient, since they do not even reuse connections. These settings allow you to increase the number of queries run by the server per second.

<h3 id="connection-auto-close">
  `connection_auto_close`
</h3>

Allows to automatically close the connection after query execution, i.e. disable connection reuse.

Possible values:

* 1 — Auto-close connection is allowed, so the connection reuse is disabled
* 0 — Auto-close connection is not allowed, so the connection reuse is enabled

Default value: `1`.

<h3 id="connection-max-tries">
  `connection_max_tries`
</h3>

Sets the number of retries for pool with failover.

Possible values:

* Positive integer.
* 0 — There are no retries for pool with failover.

Default value: `3`.

<h3 id="connection-pool-size">
  `connection_pool_size`
</h3>

Size of connection pool (if all connections are in use, the query will wait until some connection will be freed).

Possible values:

* Positive integer.

Default value: `16`.

<h3 id="connection-wait-timeout">
  `connection_wait_timeout`
</h3>

Timeout (in seconds) for waiting for free connection (in case of there is already connection\_pool\_size active connections), 0 - do not wait.

Possible values:

* Positive integer.

Default value: `5`.

<h3 id="connect-timeout">
  `connect_timeout`
</h3>

Connect timeout (in seconds).

Possible values:

* Positive integer.

Default value: `10`.

<h3 id="read-write-timeout">
  `read_write_timeout`
</h3>

Read/write timeout (in seconds).

Possible values:

* Positive integer.

Default value: `300`.

<h3 id="enable-compression">
  `enable_compression`
</h3>

Enables compression for the MySQL protocol connection.

Default value: `false`.

This setting applies to:

* the `MySQL` table engine;
* the `MySQL` database engine;
* the `mysql` table function;
* named collections used by MySQL integrations.

When enabled, ClickHouse requests compression for the connection.

Example:

```sql theme={null}
CREATE TABLE mysql_engine_compression
(
    id UInt32,
    name String,
    age UInt32,
    money UInt32
)
ENGINE = MySQL('mysql80:3306', 'clickhouse', 'test_table', 'root', 'password')
SETTINGS enable_compression = 1;
```

<h2 id="see-also">
  See also
</h2>

* [The mysql table function](/reference/functions/table-functions/mysql)
* [Using MySQL as a dictionary source](/reference/statements/create/dictionary/sources/mysql)