doris数据库和mysql dolt数据库

转载

mob64ca14017c37 2023-11-13 15:35:09

文章标签 doris数据库和mysql 数据库 git java Dolt 文章分类 MySQL 数据库

API: https://docs.dolthub.com/introduction/what-is-
仓库：dolthttps://github.com/dolthub/dolt

概述

官网解释：Dolt 是一个 SQL 数据库，您可以像 Git 存储库一样进行分叉、克隆、分支、合并、推送和拉取。就像连接任何 MySQL 数据库一样连接到 Dolt，以使用 SQL 命令运行查询或更新数据。使用命令行界面导入 CSV 文件、提交您的更改、将它们推送到远程或合并您队友的更改。
个人理解：就是一个机遇MySQL 和Git管理开发的数据库，兼容了两者的特性，对MySQL底层也做了许多修改，包括存储结构。
安装
详见API文档。

使用
Dolt服务启动及客户端连接

启动dolt服务

$ dolt sql-server -P 1234 --loglevel=debug
 Starting server with Config HP=“localhost:1234”|T=“28800000”|R=“false”|L=“debug”|S=“/tmp/mysql.sock”
 2023-03-08T13:05:06-08:00 WARN [no conn] unix socket set up failed: file already in use: /tmp/mysql.sock {}

客户端连接

$ mysql -h 127.0.0.1 -P 1234 -u root
 Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 1
 Server version: 5.7.9-VitessCopyright © 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>
分支管理

默认分支为 ‘main’

mysql> select * from dolt_branches;
 ±-----±---------------------------------±--------------------±-----------------------±------------------------±-----------------------------+
 | name | hash | latest_committer | latest_committer_email | latest_commit_date | latest_commit_message |
 ±-----±---------------------------------±--------------------±-----------------------±------------------------±-----------------------------+
 | main | 9oemsvbflg5cova8f7qrbh7kpaeevs5a | Dolt System Account | doltuser@dolthub.com | 2023-03-24 09:01:50.031 | |
 ±-----±---------------------------------±--------------------±-----------------------±------------------------±-----------------------------+
 1 rows in set (0.01 sec)

创建分支 -b 和Git分支创建的-b含义一致

mysql> call dolt_checkout(‘-b’, ‘dev’);
 ±-------+
 | status |
 ±-------+
 | 0 |
 ±-------+
 1 row in set (0.01 sec)mysql> select * from dolt_branches;
 ±-----±---------------------------------±--------------------±-----------------------±------------------------±-----------------------------+
 | name | hash | latest_committer | latest_committer_email | latest_commit_date | latest_commit_message |
 ±-----±---------------------------------±--------------------±-----------------------±------------------------±-----------------------------+
 | dev | ge62mkmsp9tu4rh6spbcrqt5tbt6n7f4 | Dolt System Account | doltuser@dolthub.com | 2023-03-24 09:02:45.274 |
 | main | 9oemsvbflg5cova8f7qrbh7kpaeevs5a | Dolt System Account | doltuser@dolthub.com | 2023-03-24 09:01:50.031 |
 ±-----±---------------------------------±--------------------±-----------------------±------------------------±-----------------------------+
 2 rows in set (0.01 sec)

切换分支

mysql> call dolt_checkout(‘dev’);
 ±-------+
 | status |
 ±-------+
 | 0 |
 ±-------+
 1 row in set (0.01 sec)

查询当前工作分支

mysql> select active_branch();
 ±----------------+
 | active_branch() |
 ±----------------+
 | main |
 ±----------------+
 1 row in set (0.00 sec)

数据提交（表、记录）

1. 切换到对应的分支

mysql> call dolt_checkout(‘dev’);
 ±-------+
 | status |
 ±-------+
 | 0 |
 ±-------+
 1 row in set (0.01 sec)

2. 创建表并插入记录：这些和MySQL保持一致

mysql> CREATE TABLE gos_table (
id varchar(90) NOT NULL COMMENT ‘主键’,
name varchar(90) DEFAULT NULL COMMENT ‘活动/礼包/自定义’,
remark varchar(255) DEFAULT NULL COMMENT ‘备注’,
 PRIMARY KEY (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
 mysql> insert into gos_table(id, name, remark) values(‘3’, ‘手动’,‘ddsf’);#3. 查询分支状态(可省略)
 mysql> select * from dolt_status;
 ±-----------±-------±----------+
 | table_name | staged | status |
 ±-----------±-------±----------+
 | gos_table | 0 | new table |
 ±-----------±-------±----------+
 1 row in set (0.00 sec)#4. 添加修改：这里‘.’代表所有修改，类似Git中的add操作，也可以指定具体的表
 mysql> call dolt_add(‘.’);
 ±-------+
 | status |
 ±-------+
 | 0 |
 ±-------+
 1 row in set (0.01 sec)#5. 提交修改： -m 同Git，‘add’为描述信息
 mysql> call dolt_commit(‘-m’, ‘add’);
 ±---------------------------------+
 | hash |
 ±---------------------------------+
 | ouldccnfbb3prr48puipp7nbtdm9oije |
 ±---------------------------------+
 1 row in set (0.02 sec)

数据查询

有两种方式

1.可以切换到对应分支，进行查询

mysql> call dolt_checkout(‘dev’);
 ±-------+
 | status |
 ±-------+
 | 0 |
 ±-------+
 1 row in set (0.01 sec)mysql> select * from gos_table;
 ±------±-----±-------+
 | id | name | remark |
 ±------±-----±-------+
 | 12232 | 手动 | ddsf |
 ±------±-----±-------+

1 row in set (0.00 sec)

2. 可以通过as of brnachName 指定要查询的分支，

注意：

这种方式只能查询到已经commit的数据

as of始终是放在表后面，而不是整个SQL 后面

mysql> select * from gos_table_1 as of ‘dev’;
 ±------±-----±-------+
 | id | name | remark |
 ±------±-----±-------+
 | 12232 | 手动 | ddsf |
 ±------±-----±-------+
 1 row in set (0.00 sec)

分支合并

获取所有变化的表

mysql> select * from dolt_diff_summary(‘main’, ‘dev’);
 ±----------------±--------------±----------±------------±--------------+
 | from_table_name | to_table_name | diff_type | data_change | schema_change |
 ±----------------±--------------±----------±------------±--------------+
 | gos_table | gos_table | modified | 1 | 1 |
 | gos_table_2 | gos_table_2 | modified | 1 | 0 |
 ±----------------±--------------±----------±------------±--------------+

查询表结构的变化

mysql> DESCRIBE gos_table as of ‘dev’;
 ±-------±-------------±-----±----±--------±------+
 | Field | Type | Null | Key | Default | Extra |
 ±-------±-------------±-----±----±--------±------+
 | id | varchar(90) | NO | PRI | NULL | |
 | name | varchar(90) | YES | | NULL | |
 | remark | varchar(255) | YES | | NULL | |
 | age | int | YES | | NULL | |
 ±-------±-------------±-----±----±--------±------+
 4 rows in set (0.00 sec)mysql> DESCRIBE gos_table as of ‘main’;
 ±-------±-------------±-----±----±--------±------+
 | Field | Type | Null | Key | Default | Extra |
 ±-------±-------------±-----±----±--------±------+
 | id | varchar(90) | NO | PRI | NULL | |
 | name | varchar(90) | YES | | NULL | |
 | remark | varchar(255) | YES | | NULL | |
 | level | int | YES | | NULL | |
 ±-------±-------------±-----±----±--------±------+
 4 rows in set (0.00 sec)

获取具体表数据的变化，select * from dolt_diff(‘fromBranch’, ‘toBranch’, ‘表名’);

mysql> select * from dolt_diff(‘main’, ‘dev’, ‘gos_table’);
 ±------±--------±----------±----------±------------------------±--------±----------±------------±------------±------------------------±----------+
 | to_id | to_name | to_remark | to_commit | to_commit_date | from_id | from_name | from_remark | from_commit | from_commit_date | diff_type |
 ±------±--------±----------±----------±------------------------±--------±----------±------------±------------±------------------------±----------+
 | 12232 | 手动 | ddsf | dev | 2023-03-24 09:59:26.103 | NULL | NULL | NULL | main | 2023-03-24 09:01:50.031 | added |
 ±------±--------±----------±----------±------------------------±--------±----------±------------±------------±------------------------±----------+
 1 row in set (0.01 sec)

注意：这里只会对比两个分支对应表的差异，不会发现冲突

通过dolt_merge发起合并

开始事务

mysql> start transaction;

合并

mysql> call dolt_merge(‘fromBranch’);

提交事务

mysql> commit;

合并时,conflicts > 0 代表存在冲突

mysql> call dolt_merge(‘dev’);
 ±-------------±----------+
 | fast_forward | conflicts |
 ±-------------±----------+
 | 0 | 1 |
 ±-------------±----------+

查询冲突的表

mysql> select * from dolt_conflicts;
 ±----------±--------------+
 | table | num_conflicts |
 ±----------±--------------+
 | gos_table | 1 |
 ±----------±--------------+

查询具体表的冲突内容：select * from dolt_conflicts_$tableName

mysql> select * from dolt_conflicts_gos_table;
 ±---------------------------------±--------±----------±------------±-------±---------±-----------±--------------±---------±-----------±-------------±----------------±-----------------------+
 | from_root_ish | base_id | base_name | base_remark | our_id | our_name | our_remark | our_diff_type | their_id | their_name | their_remark | their_diff_type | dolt_conflict_id |
 ±---------------------------------±--------±----------±------------±-------±---------±-----------±--------------±---------±-----------±-------------±----------------±-----------------------+
 | 47teoul2kv0g439atl0adtdoi7s4mlui | 1 | 手动 | ddsf | 1 | test1 | 测试 | modified | 1 | test11 | ddsf | modified | V3cii9WnC1ylQIT/9E1SGw |
 ±---------------------------------±--------±----------±------------±-------±---------±-----------±--------------±---------±-----------±-------------±----------------±-----------------------+

解决冲突：可由用户选择，保留哪一个版本

CALL DOLT_CONFLICTS_RESOLVE(‘–ours’, ); 
 
CALL DOLT_CONFLICTS_RESOLVE(‘–theirs’, ); 
 
mysql> call dolt_conflicts_resolve(‘–theirs’, ‘gos_table’);

删除冲突：解决完冲突后必须删除表的冲突记录

mysql> DELETE FROM dolt_conflicts_gos_table;

提交事务

mysql> commit;

将用户解决冲突的改动提交

mysql> call dolt_commit(‘-am’, ‘fix’);
 ±---------------------------------+
 | hash |
 ±---------------------------------+
 | 338k0b3is6ddtq60h1at5jh8r0btlg06 |
 ±---------------------------------+
 1 row in set (0.02 sec)其它
 mysql> select * from dolt_diff_stat(‘main’, ‘dev’);
 ±------------±----------------±-----------±-------------±--------------±------------±--------------±---------------±--------------±--------------±---------------±---------------+
 | table_name | rows_unmodified | rows_added | rows_deleted | rows_modified | cells_added | cells_deleted | cells_modified | old_row_count | new_row_count | old_cell_count | new_cell_count |
 ±------------±----------------±-----------±-------------±--------------±------------±--------------±---------------±--------------±--------------±---------------±---------------+
 | gos_table | 100000 | 0 | 0 | 1 | 0 | 0 | 1 | 100001 | 100001 | 300003 | 300003 |
 | gos_table_2 | 0 | 0 | 0 | 1 | 0 | 0 | 2 | 1 | 1 | 3 | 3 |
 ±------------±----------------±-----------±-------------±--------------±------------±--------------±---------------±--------------±--------------±---------------±---------------+mysql> select * from dolt_patch(‘main’, ‘dev’);
 ±----------------±---------------------------------±---------------------------------±------------±----------±----------------------------------------------------------------------------------------+
 | statement_order | from_commit_hash | to_commit_hash | table_name | diff_type | statement |
 ±----------------±---------------------------------±---------------------------------±------------±----------±----------------------------------------------------------------------------------------+
 | 1 | k0ijpong1bm66d59fp1thkkqbf9jjcc4 | evum1e80nljmbtdihtolsk44cilff48s | gos_table | data | UPDATE gos_table SET name=‘test’ WHERE id=‘00001e11-b679-47af-b9ca-c1c32ed8ffc4’; |
 | 2 | k0ijpong1bm66d59fp1thkkqbf9jjcc4 | evum1e80nljmbtdihtolsk44cilff48s | gos_table_2 | data | UPDATE gos_table_2 SET name=‘test’,remark=‘1’ WHERE id=‘1’; |
 ±----------------±---------------------------------±---------------------------------±------------±----------±----------------------------------------------------------------------------------------+mysql> select * from dolt_commit_diff_gos_table where to_commit=HASHOF(‘main’) and from_commit = HASHOF(‘dev’);
 ±------±--------±----------±---------------------------------±------------------------±--------±----------±------------±---------------------------------±------------------------±----------+
 | to_id | to_name | to_remark | to_commit | to_commit_date | from_id | from_name | from_remark | from_commit | from_commit_date | diff_type |
 ±------±--------±----------±---------------------------------±------------------------±--------±----------±------------±---------------------------------±------------------------±----------+
 | 3 | 手动 | ddsf | 338k0b3is6ddtq60h1at5jh8r0btlg06 | 2023-03-27 09:56:12.873 | 3 | test3 | ddsf | e1qibqr9a499pg5gs8lkd74c79tf5m3d | 2023-03-27 10:01:19.295 | modified |
 ±------±--------±----------±---------------------------------±------------------------±--------±----------±------------±---------------------------------±------------------------±----------+
 1 row in set (0.00 sec)mysql> select * from dolt_log as of ‘dev’;
 ±---------------------------------±--------------------±---------------------±------------------------±---------------------------+
 | commit_hash | committer | email | date | message |
 ±---------------------------------±--------------------±---------------------±------------------------±---------------------------+
 | e1qibqr9a499pg5gs8lkd74c79tf5m3d | Dolt System Account | doltuser@dolthub.com | 2023-03-27 10:01:19.295 | fix |
 | 338k0b3is6ddtq60h1at5jh8r0btlg06 | Dolt System Account | doltuser@dolthub.com | 2023-03-27 09:56:12.873 | fix |
 | qfv7iunpf7qoer0p3jpaurq2mdn4e5la | Dolt System Account | doltuser@dolthub.com | 2023-03-27 09:12:53.314 | update |
 | 47teoul2kv0g439atl0adtdoi7s4mlui | Dolt System Account | doltuser@dolthub.com | 2023-03-27 08:59:36.402 | update |
 | tvk3q62umr24v6s1k86gkjftttf3f1jk | Dolt System Account | doltuser@dolthub.com | 2023-03-27 08:57:55.58 | update |
 | lfdeb96q87bu83mb8qhej4f49vurcpns | Dolt System Account | doltuser@dolthub.com | 2023-03-27 08:55:44.032 | add |
 | aah1bp6kasjnj0267639elno34otibgk | Dolt System Account | doltuser@dolthub.com | 2023-03-27 08:53:31.213 | Initialize data repository |
 ±---------------------------------±--------------------±---------------------±------------------------±---------------------------+
 7 rows in set (0.00 sec)mysql> select * from dolt_history_gos_table as of ‘main’ order by commit_date desc;
 ±—±---------±-------±---------------------------------±--------------------±------------------------+
 | id | name | remark | commit_hash | committer | commit_date |
 ±—±---------±-------±---------------------------------±--------------------±------------------------+
 | 1 | test1 | 测试 | 1q2vn2gi5kn3c2op3h9doronvpgueq69 | Dolt System Account | 2023-03-27 10:16:57.906 |
 | 2 | testll | ddsf | 1q2vn2gi5kn3c2op3h9doronvpgueq69 | Dolt System Account | 2023-03-27 10:16:57.906 |
 | 3 | test3333 | ddsf | 1q2vn2gi5kn3c2op3h9doronvpgueq69 | Dolt System Account | 2023-03-27 10:16:57.906 |
 | 1 | test1 | 测试 | 9v2mlr9s47u995581hvo64im8uof7j4k | Dolt System Account | 2023-03-27 10:05:37.323 |
 | 2 | test22 | ddsf | 9v2mlr9s47u995581hvo64im8uof7j4k | Dolt System Account | 2023-03-27 10:05:37.323 |
 | 3 | test3333 | ddsf | 9v2mlr9s47u995581hvo64im8uof7j4k | Dolt System Account | 2023-03-27 10:05:37.323 |
 | 1 | test1 | 测试 | 338k0b3is6ddtq60h1at5jh8r0btlg06 | Dolt System Account | 2023-03-27 09:56:12.873 |
 | 2 | test22 | ddsf | 338k0b3is6ddtq60h1at5jh8r0btlg06 | Dolt System Account | 2023-03-27 09:56:12.873 |
 | 3 | 手动 | ddsf | 338k0b3is6ddtq60h1at5jh8r0btlg06 | Dolt System Account | 2023-03-27 09:56:12.873 |
 | 1 | test11 | ddsf | qfv7iunpf7qoer0p3jpaurq2mdn4e5la | Dolt System Account | 2023-03-27 09:12:53.314 |
 | 2 | test22 | ddsf | qfv7iunpf7qoer0p3jpaurq2mdn4e5la | Dolt System Account | 2023-03-27 09:12:53.314 |
 | 3 | 手动 | ddsf | qfv7iunpf7qoer0p3jpaurq2mdn4e5la | Dolt System Account | 2023-03-27 09:12:53.314 |
 | 1 | test11 | ddsf | 47teoul2kv0g439atl0adtdoi7s4mlui | Dolt System Account | 2023-03-27 08:59:36.402 |
 | 2 | 手动 | ddsf | 47teoul2kv0g439atl0adtdoi7s4mlui | Dolt System Account | 2023-03-27 08:59:36.402 |
 | 3 | 手动 | ddsf | 47teoul2kv0g439atl0adtdoi7s4mlui | Dolt System Account | 2023-03-27 08:59:36.402 |
 | 1 | test1 | 测试 | tvk3q62umr24v6s1k86gkjftttf3f1jk | Dolt System Account | 2023-03-27 08:57:55.58 |
 | 2 | 手动 | ddsf | tvk3q62umr24v6s1k86gkjftttf3f1jk | Dolt System Account | 2023-03-27 08:57:55.58 |
 | 1 | 手动 | ddsf | lfdeb96q87bu83mb8qhej4f49vurcpns | Dolt System Account | 2023-03-27 08:55:44.032 |
 | 2 | 手动 | ddsf | lfdeb96q87bu83mb8qhej4f49vurcpns | Dolt System Account | 2023-03-27 08:55:44.032 |
 ±—±---------±-------±---------------------------------±--------------------±------------------------+
 19 rows in set (0.00 sec)mysql> select * from dolt_blame_gos_table as of ‘main’;
 ±—±---------------------------------±------------------------±--------------------±---------------------±--------+
 | id | commit | commit_date | committer | email | message |
 ±—±---------------------------------±------------------------±--------------------±---------------------±--------+
 | 1 | 338k0b3is6ddtq60h1at5jh8r0btlg06 | 2023-03-27 09:56:12.873 | Dolt System Account | doltuser@dolthub.com | fix |
 | 2 | 1q2vn2gi5kn3c2op3h9doronvpgueq69 | 2023-03-27 10:16:57.906 | Dolt System Account | doltuser@dolthub.com | fix |
 | 3 | 9v2mlr9s47u995581hvo64im8uof7j4k | 2023-03-27 10:05:37.323 | Dolt System Account | doltuser@dolthub.com | fix |
 ±—±---------------------------------±------------------------±--------------------±---------------------±--------+
 3 rows in set (0.01 sec)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。