mysql

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

语法

mysql({host:port, database, table, user, password[, replace_query, on_duplicate_clause] | named_collection[, option=value [,..]]})

参数

参数	描述
`host:port`	MySQL 服务器地址。
`database`	远程数据库名称。
`table`	远程表名，或原样传递给 MySQL 的查询 (请参见传递查询而不是表名) 。
`user`	MySQL 用户。
`password`	用户密码。
`replace_query`	用于将 `INSERT INTO` 查询转换为 `REPLACE INTO` 的标志。可能的值： - `0` - 以 `INSERT INTO` 执行该查询。 - `1` - 以 `REPLACE INTO` 执行该查询。
`on_duplicate_clause`	添加到 `INSERT` 查询中的 `ON DUPLICATE KEY on_duplicate_clause` 表达式。只能在 `replace_query = 0` 时指定 (如果同时传入 `replace_query = 1` 和 `on_duplicate_clause`，ClickHouse 会抛出异常) 。示例：`INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1;` 此处的 `on_duplicate_clause` 为 `UPDATE c2 = c2 + 1`。请参阅 MySQL 文档，了解 `ON DUPLICATE KEY` 子句支持哪些 `on_duplicate_clause`。

参数也可以通过命名集合传递。在这种情况下，需要分别指定 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 source 的用户都可以探测本地文件系统，并使用其无权自行读取的证书和密钥进行身份验证。

传递查询而非表名

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

SELECT * FROM mysql('localhost:3306', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
SELECT * FROM mysql('localhost:3306', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

这对于将 joins、聚合或其他任何处理下推到 MySQL 很有用。此类表为只读：不允许对其执行 INSERT。MySQL 表引擎也支持相同的语法。

子查询形式 (SELECT ...) 会先由 ClickHouse 解析，再在发送到服务器之前按 MySQL 方言 (使用反引号引用标识符) 重新序列化。因此，它必须是有效的 ClickHouse SQL。若要传递 ClickHouse 无法解析的 MySQL 特定语法，请使用 query('...') 形式，其文本会原样发送给 MySQL。外层 ClickHouse 查询中的任何 WHERE、LIMIT、聚合等都不会下推到传入的查询中，而是在拉取完整查询结果后由 ClickHouse 应用。要限制从 MySQL 读取的数据，请将过滤条件放在传入的查询内部。启用 external_table_strict_query = 1 时，针对该表函数列的外层过滤器会直接抛出异常，而不是在本地应用，因为它无法下推到传入的查询中。该检查涵盖顶层 WHERE predicate 以及顶层 AND 的每个合取项。针对此表列的 PREWHERE 不属于此设置的适用范围：该表引擎不支持 PREWHERE，无论该设置如何，此类查询都会以 ILLEGAL_PREWHERE 被拒绝。该检查仅在过滤器确实有可能被下推的场景下才会执行：当此表是查询中唯一的表、位于 INNER JOIN 的任一侧，或位于外连接的保留侧 (LEFT JOIN 的左侧、RIGHT JOIN 的右侧) 时。在 LEFT/RIGHT JOIN 的非保留侧以及 FULL JOIN 的任一侧，不会下推任何内容，也不会执行任何检查，因此即使在严格模式下，针对此表列的过滤器也会在 join 之后于本地应用。在执行检查的场景中，引用了外层查询中其他被 join 表的 predicate 不会被下推，并且会被排除在检查之外，无论它仅引用被 join 的一侧，还是在某个非 AND 表达式 (例如 OR) 中将其与此表混合引用；此类 predicate 会保持其通常的 ClickHouse 求值位置 (WHERE 在 join 之后，PREWHERE 在 join 之前)，并且不会被拒绝。

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

SELECT name FROM mysql(`mysql{1|2|3}:3306`, 'mysql_database', 'mysql_table', 'user', 'password');

或

SELECT name FROM mysql(`mysql1:3306|mysql2:3306|mysql3:3306`, 'mysql_database', 'mysql_table', 'user', 'password');

返回值

一个表对象，其列与原始 MySQL 表的列相同。

MySQL 的某些数据类型可映射为不同的 ClickHouse 类型——可通过查询级设置 mysql_datatypes_support_level 来处理这一点。

在 INSERT 查询中，为了将表函数 mysql(...) 与带列名列表的表名区分开来，必须使用关键字 FUNCTION 或 TABLE FUNCTION。请参见下面的示例。

示例

MySQL 中的表：

mysql> CREATE TABLE `test`.`test` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `float` FLOAT NOT NULL,
    ->   PRIMARY KEY (`int_id`));

mysql> INSERT INTO test (`int_id`, `float`) VALUES (1,2);

mysql> SELECT * FROM test;
+--------+-------+
| int_id | float |
+--------+-------+
|      1 |     2 |
+--------+-------+

从 ClickHouse 查询数据：

SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123');

或者使用命名集合：

CREATE NAMED COLLECTION creds AS
        host = 'localhost',
        port = 3306,
        database = 'test',
        user = 'bayonet',
        password = '123';
SELECT * FROM mysql(creds, table='test');

┌─int_id─┬─float─┐
│      1 │     2 │
└────────┴───────┘

`enable_compression`

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

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

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

SELECT *
FROM mysql(
    'mysql80:3306',
    'clickhouse',
    'test_table',
    'root',
    'password',
    SETTINGS enable_compression = 1
);

替换与插入：

INSERT INTO FUNCTION mysql('localhost:3306', 'test', 'test', 'bayonet', '123', 1) (int_id, float) VALUES (1, 3);
INSERT INTO TABLE FUNCTION mysql('localhost:3306', 'test', 'test', 'bayonet', '123', 0, 'UPDATE int_id = int_id + 1') (int_id, float) VALUES (1, 4);
SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123');

┌─int_id─┬─float─┐
│      1 │     3 │
│      2 │     4 │
└────────┴───────┘

将 MySQL 表中的数据复制到 ClickHouse 表中：

CREATE TABLE mysql_copy
(
   `id` UInt64,
   `datetime` DateTime('UTC'),
   `description` String,
)
ENGINE = MergeTree
ORDER BY (id,datetime);

INSERT INTO mysql_copy
SELECT * FROM mysql('host:port', 'database', 'table', 'user', 'password');

或者，如果只想根据当前的最大 ID 从 MySQL 复制一个增量批次：

INSERT INTO mysql_copy
SELECT * FROM mysql('host:port', 'database', 'table', 'user', 'password')
WHERE id > (SELECT max(id) FROM mysql_copy);

SQL 参考文档

数据类型

引擎

函数

格式

接口

设置

系统表

数据湖

语法

参数

TLS/SSL

传递查询而非表名

返回值

示例

`enable_compression`

相关

​语法

​参数

​TLS/SSL

​传递查询而非表名

​返回值

​示例

​enable_compression

​相关

语法

参数

TLS/SSL

传递查询而非表名

返回值

示例

`enable_compression`

相关