Clickhouse数据库引擎Mysql

---

title: Clickhouse数据库引擎Mysql
date: 2022-03-12 14:40:59
tags: [clickhouse, mysql engine, 表引擎]
categories: clickhouse

前面介绍了clickhouse的几个表引擎，今天介绍一个数据库引擎mysql，它能够做到访问和操作mysql的数据。
官网只介绍了怎么查询、插入数据，我们这里来介绍下它的深层次的原理。

基本介绍

Mysql引擎用于将远程mysql库中的表映射到clickhouse中，并允许clickhouse对表进行select和insert操作，以方便clickhouse和mysql之间进行数据的交换。

Mysql数据库引擎会将对clickhouse的查询，转换为mysql语法并发送到mysql服务器中，因此，你可以执行对应的sql。

无法执行以下操作：
rename create table alter

创建clickhouse数据库

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

引擎参数：

1. host:port，mysql服务器的连接参数
2. user，mysql的连接用户
3. database，连接mysql的数据库
4. password，mysql的连接密码

实例

mysql

mysql> use photography;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_photography |
+-----------------------+
| app_info              |
| cat_info              |
| comment               |
| competition           |
| dim_pet               |
| feedback              |
| photography           |
| tags                  |
| user                  |
| user_pet              |
| vote                  |
+-----------------------+

mysql> create table test(
    ->   id int primary key,
    ->   name varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (1, 'chenzuoli');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | chenzuoli |
+----+-----------+

clickhouse

19c0db059cdc :) CREATE DATABASE mysql_db ENGINE = MySQL('xxxxx:3306', 'photography', 'photography', 'password')

Ok.

19c0db059cdc :) show databases;

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ mysql_db           │
│ system             │
└────────────────────┘

19c0db059cdc :) use mysql_db;

0 rows in set. Elapsed: 0.005 sec.

19c0db059cdc :) show tables;

┌─name────────┐
│ app_info    │
│ cat_info    │
│ comment     │
│ competition │
│ dim_pet     │
│ feedback    │
│ photography │
│ tags        │
│ test        │
│ user        │
│ user_pet    │
│ vote        │
└─────────────┘

好了，到这里，已经通过clickhouse连接上了mysql数据库了，接下来我们来操作下mysql表吧。

插入数据

19c0db059cdc :) select * from test;

┌─id─┬─name──────┐
│  1 │ chenzuoli │
└────┴───────────┘

19c0db059cdc :) insert into test values (2, 'zhangsan');

1 rows in set. Elapsed: 0.611 sec.

19c0db059cdc :) select * from test;

┌─id─┬─name──────┐
│  1 │ chenzuoli │
│  2 │ zhangsan  │
└────┴───────────┘

更新数据

19c0db059cdc :) update test set name = 'lisi' where id = 2;

Syntax error: failed at position 1 ('update'):

update test set name = 'lisi' where id = 2;

Expected one of: Query, Query with output, EXPLAIN, SELECT query, possibly with UNION, list of union elements, SELECT query, subquery, possibly with UNION, SELECT subquery, SELECT query, WITH, SELECT, SHOW CREATE QUOTA query, SHOW CREATE, SHOW [TEMPORARY] TABLES|DATABASES|CLUSTERS|CLUSTER 'name' [[NOT] [I]LIKE 'str'] [LIMIT expr], SHOW, EXISTS or SHOW CREATE query, EXISTS, DESCRIBE query, DESCRIBE, DESC, SHOW PROCESSLIST query, SHOW PROCESSLIST, CREATE TABLE or ATTACH TABLE query, CREATE, ATTACH, REPLACE, CREATE DATABASE query, CREATE VIEW query, CREATE DICTIONARY, CREATE LIVE VIEW query, CREATE WINDOW VIEW query, ALTER query, ALTER TABLE, ALTER LIVE VIEW, ALTER DATABASE, RENAME query, RENAME TABLE, EXCHANGE TABLES, RENAME DICTIONARY, EXCHANGE DICTIONARIES, RENAME DATABASE, DROP query, DROP, DETACH, TRUNCATE, CHECK TABLE, KILL QUERY query, KILL, OPTIMIZE query, OPTIMIZE TABLE, WATCH query, WATCH, SHOW ACCESS query, SHOW ACCESS, ShowAccessEntitiesQuery, SHOW GRANTS query, SHOW GRANTS, SHOW PRIVILEGES query, SHOW PRIVILEGES, INSERT query, INSERT INTO, USE query, USE, SET ROLE or SET DEFAULT ROLE query, SET ROLE DEFAULT, SET ROLE, SET DEFAULT ROLE, SET query, SET, SYSTEM query, SYSTEM, CREATE USER or ALTER USER query, ALTER USER, CREATE USER, CREATE ROLE or ALTER ROLE query, ALTER ROLE, CREATE ROLE, CREATE QUOTA or ALTER QUOTA query, ALTER QUOTA, CREATE QUOTA, CREATE ROW POLICY or ALTER ROW POLICY query, ALTER POLICY, ALTER ROW POLICY, CREATE POLICY, CREATE ROW POLICY, CREATE SETTINGS PROFILE or ALTER SETTINGS PROFILE query, ALTER SETTINGS PROFILE, ALTER PROFILE, CREATE SETTINGS PROFILE, CREATE PROFILE, CREATE FUNCTION query, DROP FUNCTION query, DROP access entity query, GRANT or REVOKE query, REVOKE, GRANT, EXTERNAL DDL query, EXTERNAL DDL FROM, BACKUP or RESTORE query, BACKUP, RESTORE

不支持通过clickhouse更新和删除mysql

总之，mysql数据库引擎，能够通过clickhouse访问mysql数据库，并且对mysql表进行select和insert操作，不能update和delete操作。

另外数据存储在mysql端，clickhouse端只是有对应的元数据表信息与mysql端一一对应。

计算呢？

1. where子句在mysql端执行
2. limit子句在clickhouse端执行。

---

Keep reading, Keep writing, Keep coding.

欢迎关注我的微信公众号，比较喜欢分享知识，也喜欢宠物，所以做了这2个公众号：

喜欢宠物的朋友可以关注：【电巴克宠物Pets】

一起学习，一起进步。