MySQL 表引擎

MySQL 引擎允许你对存储在远程 MySQL 服务器中的数据执行 SELECT 和 INSERT 查询。

创建表

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 ]
;

有关 CREATE TABLE 查询的详细说明，请参阅。表结构可以与原始 MySQL 表的结构不同：

列名应与原始 MySQL 表中的列名一致，但也可以只使用其中部分列，且顺序可以任意。
列类型可以与原始 MySQL 表中的列类型不同。ClickHouse 会尝试将值 cast 为 ClickHouse 数据类型。
external_table_functions_use_nulls 设置定义了如何处理 Nullable 列。默认值：1。如果为 0，表函数不会创建 Nullable 列，而是插入默认值来代替 null。这也适用于数组中的 NULL 值。

引擎参数

host:port — MySQL 服务器地址。
database — 远程数据库名称。
table — 远程表名，或按原样传递给 MySQL 的查询 (请参见传递查询而不是表名) 。
user — MySQL 用户。
password — 用户密码。
replace_query — 将 INSERT INTO 查询转换为 REPLACE INTO 的标志。如果 replace_query=1，则会替换该查询。
on_duplicate_clause — 添加到 INSERT 查询中的 ON DUPLICATE KEY on_duplicate_clause 表达式。示例：INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1，其中 on_duplicate_clause 为 UPDATE c2 = c2 + 1。请参阅 MySQL documentation，了解在 ON DUPLICATE KEY 子句中可以使用哪些 on_duplicate_clause。要指定 on_duplicate_clause，需要向 replace_query 参数传递 0。如果同时传递 replace_query = 1 和 on_duplicate_clause，ClickHouse 会引发异常。

参数也可以通过命名集合传递。在这种情况下，应分别指定 host 和 port。这种方式建议在生产环境中使用。简单的 WHERE 子句 (如 =, !=, >, >=, <, <=) 会在 MySQL 服务器上执行。其余条件以及 LIMIT 采样约束仅会在对 MySQL 的查询完成后由 ClickHouse 执行。

传入查询而非表名

table 参数除了可以是表名外，也可以是原样传递给 MySQL 的 SELECT 查询。表的结构会根据查询结果自动推断。该查询既可以写成子查询，也可以封装在 query 函数中：

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

这对于将 JOIN、聚合或任何其他处理下推到 MySQL 非常有用。这样的表是只读的：不允许对其执行 INSERT。mysql 表函数也支持相同的语法。

子查询形式 (SELECT ...) 会由 ClickHouse 解析，并在发送到服务器之前按 MySQL 方言 (使用反引号引用标识符) 重新序列化。因此，它必须是有效的 ClickHouse SQL。若要传递 ClickHouse 无法解析的 MySQL 特有语法，请使用 query('...') 形式，其文本会原样发送到 MySQL。外层 ClickHouse 查询中的任何 WHERE、LIMIT、聚合等，都不会下推到传入的查询中——而是会在拉取完整查询结果后由 ClickHouse 应用。要限制从 MySQL 读取的数据，请将过滤条件写在传入的查询内部。启用 external_table_strict_query = 1 时，无法下推的外层过滤条件会直接被拒绝并抛出异常，而不是在本地应用。

支持多个副本，必须用 | 列出。例如：

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

使用示例

在 MySQL 中创建表：

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)

使用常规参数在 ClickHouse 中创建表：

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

或者使用命名集合：

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

从 MySQL 表中获取数据：

SELECT * FROM mysql_table

┌─float_nullable─┬─int_id─┐
│           ᴺᵁᴸᴸ │      1 │
└────────────────┴────────┘

设置

默认设置的效率并不高，因为它们甚至不会复用连接。通过这些设置，可以提高服务器每秒可执行的查询数量。

`connection_auto_close`

允许在查询执行后自动关闭连接，即禁用连接复用。可能的值：

1 — 允许自动关闭连接，因此会禁用连接复用
0 — 不允许自动关闭连接，因此会启用连接复用

默认值：1。

`connection_max_tries`

设置启用故障转移的连接池的重试次数。可能的值：

正整数。
0 — 启用故障转移的连接池不进行重试。

默认值：3。

`connection_pool_size`

连接池大小 (如果所有连接都在使用中，查询会等待，直到有连接被释放) 。可能的值：

正整数。

默认值：16。

`connection_wait_timeout`

等待空闲连接的超时时间 (秒) (当已有 connection_pool_size 个活动连接时) ，0 表示不等待。可能的值：

正整数。

默认值：5。

`connect_timeout`

连接超时时间 (以秒为单位) 。可能的值：

正整数。

默认值：10。

`read_write_timeout`

读写超时 (单位：秒) 。可选值：

正整数。

默认值：300。

`enable_compression`

为 MySQL 协议连接启用压缩。默认值：false。此设置适用于：

MySQL 表引擎；
MySQL 数据库引擎；
mysql 表函数；
用于 MySQL 集成的命名集合。

启用后，ClickHouse 会请求对此连接启用压缩。示例：

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;

SQL 参考

创建表

传入查询而非表名

使用示例

设置

`connection_auto_close`

`connection_max_tries`

`connection_pool_size`

`connection_wait_timeout`

`connect_timeout`

`read_write_timeout`

`enable_compression`

另请参阅

​创建表

​传入查询而非表名

​使用示例

​设置

​connection_auto_close

​connection_max_tries

​connection_pool_size

​connection_wait_timeout

​connect_timeout

​read_write_timeout

​enable_compression

​另请参阅

创建表

传入查询而非表名

使用示例

设置

`connection_auto_close`

`connection_max_tries`

`connection_pool_size`

`connection_wait_timeout`

`connect_timeout`

`read_write_timeout`

`enable_compression`

另请参阅