Help wanted!

The following content of this documentation page has been machine-translated. But unlike other websites, it is not done on the fly. This translated text lives on GitHub repository alongside main ClickHouse codebase and waits for fellow native speakers to make it more human-readable. You can also use the original English version as a reference.

Help ClickHouse documentation by editing this page

mysql

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

  1. mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

参数

  • host:port — MySQL server address.

  • database — Remote database name.

  • table — Remote table name.

  • user — MySQL user.

  • password — User password.

  • replace_query — Flag that converts INSERT INTO 查询到 REPLACE INTO. 如果 replace_query=1，查询被替换。

  • on_duplicate_clause — The ON DUPLICATE KEY on_duplicate_clause 表达式被添加到 INSERT 查询。

    1. 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 to find which `on_duplicate_clause` you can use with the `ON DUPLICATE KEY` clause.
    3. 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.

简单 WHERE 条款如 =, !=, >, >=, <, <= 当前在MySQL服务器上执行。

其余的条件和 LIMIT 只有在对MySQL的查询完成后，才会在ClickHouse中执行采样约束。

返回值

与原始MySQL表具有相同列的table对象。

用法示例

MySQL中的表:

  1. mysql> CREATE TABLE `test`.`test` (
  2.     ->   `int_id` INT NOT NULL AUTO_INCREMENT,
  3.     ->   `int_nullable` INT NULL DEFAULT NULL,
  4.     ->   `float` FLOAT NOT NULL,
  5.     ->   `float_nullable` FLOAT NULL DEFAULT NULL,
  6.     ->   PRIMARY KEY (`int_id`));
  7. Query OK, 0 rows affected (0,09 sec)
  9. mysql> insert into test (`int_id`, `float`) VALUES (1,2);
  10. Query OK, 1 row affected (0,00 sec)
  12. mysql> select * from test;
  13. +------+----------+-----+----------+
  14. | int_id | int_nullable | float | float_nullable |
  15. +------+----------+-----+----------+
  16. |      1 |         NULL |     2 |           NULL |
  17. +------+----------+-----+----------+
  18. 1 row in set (0,00 sec)

从ClickHouse中选择数据:

  1. SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123')
  1. ┌─int_id─┬─int_nullable─┬─float─┬─float_nullable─┐
  2. │      1 │         ᴺᵁᴸᴸ │     2 │           ᴺᵁᴸᴸ │
  3. └────────┴──────────────┴───────┴────────────────┘

另请参阅

原始文章