JZTXT

clickhouse连接访问mysql

发布时间 2023-10-09 21:24:43作者: theSummerDay

创建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)

参考

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

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