MySQL 表引擎

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 执行。

​

TLS/SSL

与 MySQL 的加密连接凭据以命名集合键 (或键值参数) 的形式传递：

参数	说明
ssl_ca_pem	用于验证 MySQL 服务器证书的 CA 证书内容。
ssl_cert_pem	用于基于证书的身份验证的客户端证书内容。
ssl_key_pem	与 ssl_cert_pem 对应的私钥内容。

这些值为相应 PEM 文件的内容，可复制到命名集合或查询中。它们会像密码一样在日志和 SHOW 查询中被掩盖。

也可以在 ssl_ca、ssl_cert 和 ssl_key 中通过服务器上的文件路径提供相同的凭据——但仅限于服务器配置文件中定义的命名集合，且此类值无法在查询中被覆盖。服务器会以自身的特权打开这些文件；因此，若允许通过 SQL 接受路径，任何能够定义 MySQL 数据源的用户都可以探查本地文件系统，并使用自己无权读取的证书和密钥进行身份验证。

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

​

传入查询而非表名

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 时，无法下推到传入查询的该表列上的外层过滤条件会直接被拒绝并抛出异常，而不是在本地应用。该检查涵盖顶层 WHERE 谓词以及顶层 AND 中的每个合取项。对此表列使用 PREWHERE 不属于此设置的适用情形：此表引擎不支持 PREWHERE，无论该设置如何，此类查询都会因 ILLEGAL_PREWHERE 被拒绝。仅在过滤条件本可下推的情况下才会执行检查：即此表是查询中的唯一表、位于 INNER JOIN 的任一侧，或位于外连接的保留侧 (LEFT JOIN 的左侧、RIGHT JOIN 的右侧) 。在 LEFT/RIGHT JOIN 的非保留侧以及 FULL JOIN 的任一侧，均不会下推任何内容，也不会执行任何检查，因此即使在严格模式下，此表列上的过滤条件也会在 JOIN 后于本地应用。在执行检查的位置，引用外层查询中其他已 JOIN 表的谓词不会被下推，且不纳入检查范围；无论它只引用已 JOIN 的一侧，还是在一个非 AND 表达式 (例如 OR) 中将其与此表混用，都是如此。此类谓词会保持其通常的 ClickHouse 求值位置 (JOIN 后的 WHERE、JOIN 前的 PREWHERE) ，且不会被拒绝。

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

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;

​

另请参阅

• MySQL 表函数
• 使用 MySQL 作为字典源