mysql

    Syntax

    Arguments

    • host:port — MySQL server address.

    • database — Remote database name.

    • table — Remote table name.

    • user — MySQL user.

    • password — User password.

      • 0 - The query is executed as INSERT INTO.
      • 1 - The query is executed as REPLACE INTO.

    • on_duplicate_clause — The ON DUPLICATE KEY on_duplicate_clause expression that is added to the INSERT query. Can be specified only with (if you simultaneously pass replace_query = 1 and on_duplicate_clause, ClickHouse generates an exception).

      Example: INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1;

      on_duplicate_clause here is UPDATE c2 = c2 + 1. See the MySQL documentation to find which on_duplicate_clause you can use with the ON DUPLICATE KEY clause.

    Simple WHERE clauses such as =, !=, >, >=, <, <= are currently 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.

    Returned Value

    A table object with the same columns as the original MySQL table.

    In the INSERT query to distinguish table function mysql(...) from table name with column names list, you must use keywords FUNCTION or TABLE FUNCTION. See examples below.

    Examples

    Table in MySQL:

    1.     ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    3.     ->   PRIMARY KEY (`int_id`));
    5. mysql> INSERT INTO test (`int_id`, `float`) VALUES (1,2);
    7. mysql> SELECT * FROM test;
    8. +--------+-------+
    9. | int_id | float |
    10. |      1 |     2 |
    11. +--------+-------+

    Selecting data from ClickHouse:

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

    Replacing and inserting:

    1. ┌─int_id─┬─float─┐
    2. │      1 │     3 │
    3. │      2 │     4 │

    See Also