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