《手摸手带你学ClickHouse》之MySQL表引擎

墨蓝 2023-09-30 10:04 25阅读 0赞

#### 文章目录 ####

*   *   *   *  1、基本使用
             *  2、结合物化视图使用

前文回顾：  
[《手摸手带你学ClickHouse》之安装部署][ClickHouse]  
[《手摸手带你学ClickHouse》之访问接口][ClickHouse 1]  
[《手摸手带你学ClickHouse》之导入导出数据][ClickHouse 2]  
[《手摸手带你学ClickHouse》之MergeTree系列表引擎][ClickHouse_MergeTree]  
[《手摸手带你学ClickHouse》之ReplacingMergeTree表引擎][ClickHouse_ReplacingMergeTree]  
[《手摸手带你学ClickHouse》之CollapsingMergeTree表引擎][ClickHouse_CollapsingMergeTree]  
[《手摸手带你学ClickHouse》之VersionedCollapsingMergeTree表引擎][ClickHouse_VersionedCollapsingMergeTree]  
[《手摸手带你学ClickHouse》之SummingMergeTree表引擎][ClickHouse_SummingMergeTree]  
[《手摸手带你学ClickHouse》之AggregatingMergeTree表引擎][ClickHouse_AggregatingMergeTree]

--------------------

##### 1、基本使用 #####

MySQL 表引擎主要是**与 MySQL 数据库建立映射**，远程执行 SQL，包括 SELECT 和 INSERT 。如下

创建表：

CREATE TABLE tb_mysql(
      id Int8,
      user_id String,
      message String,
      send_date DateTime,
      channel String,
      sendUser String,
      receiver String,
      contentType Int8,
      pictureUrl String,
      isRead Int8,
      msgType Int8,
      readTime DateTime,
      create_time DateTime,
      state Int8,
      batch String
    )ENGINE=MySQL('192.168.1.120:3306','czim','message','root','root');

查询数据：

select id,user_id,message,sendUser,receiver,contentType,msgType,create_time,batch from tb_mysql;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56iL5bqP5ZGY5aSn5L2s6LaF_size_20_color_FFFFFF_t_70_g_se_x_16]

然后插入数据，也是可以写入到MySQL中。

##### 2、结合物化视图使用 #####

这张表具备读写能力后，还可以利用物化视图来搭配使用，如下

--创建物化视图搭配MySQL引擎使用
    CREATE MATERIALIZED VIEW view_mysql
    ENGINE=MergeTree()
    PARTITION BY user_id
    ORDER BY user_id
    AS
    SELECT * FROM tb_mysql;

当通过MySQL引擎向远端MySQL写入数据时，物化视图也会同步更新数据。  
![在这里插入图片描述][4918e7963bb94471b4b71080a5fda32a.png]

> 后续将继续更新该系列，大佬超手摸手带你学ClickHouse，敬请关注！！！

--------------------

觉得有帮助点个赞吧！！！  
原创不易，转载请注明出处。

[ClickHouse]: https://blog.csdn.net/xch_yang/article/details/122309471
[ClickHouse 1]: https://blog.csdn.net/xch_yang/article/details/122370442
[ClickHouse 2]: https://blog.csdn.net/xch_yang/article/details/122817778
[ClickHouse_MergeTree]: https://blog.csdn.net/xch_yang/article/details/122855242
[ClickHouse_ReplacingMergeTree]: https://blog.csdn.net/xch_yang/article/details/123047197
[ClickHouse_CollapsingMergeTree]: https://blog.csdn.net/xch_yang/article/details/123125210
[ClickHouse_VersionedCollapsingMergeTree]: https://blog.csdn.net/xch_yang/article/details/123137895
[ClickHouse_SummingMergeTree]: https://blog.csdn.net/xch_yang/article/details/123591740
[ClickHouse_AggregatingMergeTree]: https://blog.csdn.net/xch_yang/article/details/123610328
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56iL5bqP5ZGY5aSn5L2s6LaF_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/6afbace85b30434a85a758c05ec91199.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56iL5bqP5ZGY5aSn5L2s6LaF,size_20,color_FFFFFF,t_70,g_se,x_16
[4918e7963bb94471b4b71080a5fda32a.png]: https://img-blog.csdnimg.cn/4918e7963bb94471b4b71080a5fda32a.png