clickhouse连接访问mysql

创建MySQL表

创建数据库test和表t1,并向t1表中插入几条数据

CREATE database test ;

use test;

CREATE table t1(
id int,
name varchar(100)
);

INSERT INTO t1 values (1, 'a'),(2, 'b'),(3, 'c');

SELECT * FROM t1;

ClickHouse连接访问MySQL

方式1: 数据库引擎MySQL

用该引擎创建的数据库中,可以执行SELECT、INSERT、SHOW TABLES、SHOW CREATE TABLE 命令，不能执行RENAME、CREATE TABLE、ALTER 命令

# 格式
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

注: 远程mysql中的database名称用不用单引号''包住都可以

# 示例
# 远程mysql数据库名用''包住
CREATE DATABASE test_database engine = MySQL('192.168.100.10:3306', 'test', 'root', 'xxxxx');
# 远程mysql数据库名不用''包住也可以
CREATE DATABASE test_database2 engine = MySQL('192.168.100.10:3306', test, 'root', 'xxxxx');

# 插入数据
INSERT INTO test_database.t1 values (4, 'd');

方式2: 表引擎MySQL

# 格式
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])
SETTINGS
    [ connection_pool_size=16, ]
    [ connection_max_tries=3, ]
    [ connection_wait_timeout=5, ]
    [ connection_auto_close=true, ]
    [ connect_timeout=10, ]
    [ read_write_timeout=300 ]
;

# 示例
# 创建库
CREATE database test_table;
# 字段保持一致
CREATE TABLE test_table.ck_t1
(
    id Int32,
    name String
) ENGINE = MySQL('192.168.100.10:3306', 'test', 't1', 'root', 'xxxxxx');

# 也可以少字段,但不能多字段,否则后续select和insert操作会报错
CREATE TABLE test_table.ck_t2
(
    id Int32
) ENGINE = MySQL('192.168.100.10:3306', 'test', 't1', 'root', 'xxxxxx');

# 插入数据
INSERT INTO test_table.ck_t1 values (5, 'e');
INSERT INTO test_table.ck_t2 values (6);

在MySQL中查看数据

可以发现,在ck中插入的数据, 在mysql中都能看到

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | NULL |
+------+------+
6 rows in set (0.00 sec)

MySQL和ClickHouse数据类型对应关系

MySQL	ClickHouse
UNSIGNED TINYINT	UInt8
TINYINT	Int8
UNSIGNED SMALLINT	UInt16
SMALLINT	Int16
UNSIGNED INT, UNSIGNED MEDIUMINT	UInt32
INT, MEDIUMINT	Int32
UNSIGNED BIGINT	UInt64
BIGINT	Int64
FLOAT	Float32
DOUBLE	Float64
DATE	Date
DATETIME, TIMESTAMP	DateTime
BINARY	FixedString
All other MySQL data types	String

参考

ClickHouse数据库引擎MySQL
https://clickhouse.com/docs/en/engines/database-engines/mysql

ClickHouse表引擎MySQL
https://clickhouse.com/docs/en/engines/table-engines/integrations/mysql