# MySQL

Allows SELECT and INSERT queries to be performed on data that is stored on a remote MySQL server.

# Syntax

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

Arguments

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 queries to REPLACE INTO. Possible values:
- 0 - The query is executed as INSERT INTO.
- 1 - The query is executed as REPLACE INTO.
on_duplicate_clause — The ON DUPLICATE KEY expression that is added to the INSERT query. Can be specified only with replace_query = 0 (if you simultaneously pass replace_query = 1 and on_duplicate_clause, MyScale generates an exception).

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

Here, 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.
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 MyScale only after the query to MySQL finishes.

Supports multiple replicas that must be listed by |. For example:

SELECT name FROM mysql(`mysql_host{1|2|3}:3306`, '[mysql_database]', '[mysql_table]', '[mysql_user]', '[mysql_password]');

SELECT name FROM mysql(`mysql_host1:3306|mysql_host2:3306|mysql_host3:3306`, '[mysql_database]', '[mysql_table]', '[mysql_user]', '[mysql_password]');

# Returned Value

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

# Examples

# Table in MySQL

# CREATE DATABASE
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
# CREATE TABLE
mysql> CREATE TABLE `test`.`test` (
    ->  `int_id` INT NOT NULL AUTO_INCREMENT,
    ->  `float` FLOAT NOT NULL,
    ->  PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0.01 sec)
# INSERT
mysql> INSERT INTO test.test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0.00 sec)
# SELECT
mysql> SELECT * FROM test.test;
+--------+-------+
| int_id | float |
+--------+-------+
|      1 |     2 |
+--------+-------+
1 row in set (0.00 sec)

# Selecting data from MySQL

SELECT * FROM mysql('[host:port]', 'test', 'test', '[mysql_user]', '[mysql_password]');

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

# Replacing and Inserting

# INSERT
INSERT INTO FUNCTION mysql('[host:port]', 'test', 'test', '[mysql_user]', '[mysql_password]') (int_id, float) VALUES (2, 3);
# REPLACE
INSERT INTO TABLE FUNCTION mysql('[host:port]', 'test', 'test', '[mysql_user]', '[mysql_password]', 0, 'UPDATE int_id = int_id + 1') (int_id, float) VALUES (2, 4);
# SELECT
SELECT * FROM mysql('[host:port]', 'test', 'test', '[mysql_user]', '[mysql_password]');

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

# Copying data from MySQL table into MyScale table

# CREATE TABLE
CREATE TABLE mysql_copy
(
  `id` UInt64 NOT NULL,
  `score` FLOAT NOT NULL,
)
ENGINE = MergeTree
ORDER BY (id);
# INSERT
INSERT INTO mysql_copy
SELECT * FROM mysql('[host:port]', 'test', 'test', '[mysql_user]', '[mysql_password]');
# SELECT
SELECT * from mysql_copy;

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

# Copying only an incremental batch from MySQL based on the max current id

# INSERT
INSERT INTO mysql_copy
SELECT * FROM mysql('[host:port]', 'test', 'test', '[mysql_user]', '[mysql_password]');
WHERE int_id > (SELECT max(id) from mysql_copy);
# SELECT
SELECT * FROM mysql_copy;

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