如何用Java實現(xiàn)支持MySQL協(xié)議的數(shù)據(jù)庫

想不想自已手動擼一個MySQL數(shù)據(jù)庫?，那么一起來吧

1. 簡介

本項目主要實現(xiàn)一個Java版本的'MySQL', 即支持MySQL協(xié)議苇羡， 以mysql-client腋逆、jdbc等形式訪問數(shù)據(jù)庫蒿褂，目前進度如下:

  • 簡單實現(xiàn)在MySQL連接協(xié)議
  • 支持建表、建db吊输、show tables
  • 支持查詢
  • 支持查看邏輯執(zhí)行計劃
  • 簡單實現(xiàn)了RBO與CBO
  • 實現(xiàn)了物理執(zhí)行計劃Operator(MPP)

項目地址: https://github.com/yuqi1129/schema/tree/master/mysql-protocol, 歡迎小伙伴們一起參加

目前正在進行中

  • 數(shù)據(jù)持久化(表數(shù)據(jù)持久化已完成, 表元數(shù)據(jù)todo中)與存儲優(yōu)化
  • 支持插入語法與功能(done)
  • 列類型豐富

2.如何啟動該數(shù)據(jù)庫

2.1. 準備元數(shù)據(jù)數(shù)據(jù)庫

本項目采用MySQL存放元數(shù)據(jù), 建表語句如下:

create database sloth;

use sloth;

-- store db infomation
CREATE TABLE `schemata` (
  `CATALOG_NAME` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `SCHEMA_NAME` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `DEFAULT_CHARACTER_SET_NAME` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `DEFAULT_COLLATION_NAME` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `SQL_PATH` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


-- store table info
CREATE TABLE `tables` (
  `TABLE_CATALOG` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `ENGINE` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `TABLE_SHARD` int(11) NOT NULL DEFAULT '1' COMMENT 'shard數(shù)目'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

-- store column info
CREATE TABLE `columns` (
  `TABLE_CATALOG` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext COLLATE utf8mb4_unicode_ci,
  `IS_NULLABLE` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `COLLATION_NAME` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `COLUMN_TYPE` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `COLUMN_KEY` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `EXTRA` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `GENERATION_EXPRESSION` longtext COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

啟動數(shù)據(jù)庫，并替換MySQL相應的配置, 請參考MysqlConnection

說明:
如果沒有配置MySQL用來存儲元數(shù)據(jù), 所有的db铁追、table數(shù)據(jù)均存放在內存之中季蚂，無法在重啟后恢復

2.2. 編譯項目

mvn clean package

2.3. 啟動

2.3.1 本地調試

在IDE中找到FrontEndMain, 直接啟動main函數(shù)即可

2.3.2 服務部署

打包項目后， 解壓mysql-protocol-1.0-SNAPSHOT-RELEASE.tar.gz, 執(zhí)行:

  • bin/start.sh start 啟動
  • bin/start.sh stop 停止

3.連接

3.1 連接地址

 mysql -h127.0.0.1 -uroot -psxx -P3016

目前沒有對用戶名與密碼進行驗證琅束，任何用戶名與密碼登陸都沒有問題

3.2 使用范例

mysql -h127.0.0.1 -uroot -psxx -P3016
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.22 Yuqi version

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> show databases;
Empty set (0.01 sec)

mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| db1      |
+----------+
1 row in set (0.00 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> use db1;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table sales(id bigint, person_id bigint, addr_id bigint, money double, desc varchar);
Query OK, 0 rows affected (0.01 sec)

mysql> create table person(id bigint, name varchar);
Query OK, 0 rows affected (0.00 sec)

mysql> create table addr(id bigint, name varchar, detail varchar);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_In_db1 |
+---------------+
| person        |
| addr          |
| sales         |
+---------------+
3 rows in set (0.01 sec)

mysql> explain
    -> select t3.sales_id, desc, t3.person_name, t4.name as addr_name from
    -> (
    -> select addr_id, t1.id as sales_id, desc, name as person_name from sales t1 inner join
    -> person t2 on t1.person_id = t2.id
    -> ) t3 inner join addr t4 on t3.addr_id = t4.id where t4.name in ('hubei', 'hunan')\G
*************************** 1. row ***************************
Plan:
SlothProject(sales_id=[$1], desc=[$2], person_name=[$3], addr_name=[$5]): rowcount = 5625.0, cumulative cost = {8175.0 rows, 3298.0 cpu, 0.0 io}, id = 337
  SlothJoin(condition=[=($0, $4)], joinType=[inner]): rowcount = 5625.0, cumulative cost = {7612.5 rows, 1048.0 cpu, 0.0 io}, id = 336
    SlothProject(addr_id=[$2], sales_id=[$0], desc=[$3], person_name=[$5]): rowcount = 1500.0, cumulative cost = {1860.0 rows, 842.0 cpu, 0.0 io}, id = 333
      SlothJoin(condition=[=($1, $4)], joinType=[inner]): rowcount = 1500.0, cumulative cost = {1710.0 rows, 242.0 cpu, 0.0 io}, id = 332
        SlothProject(id=[$0], person_id=[$1], addr_id=[$2], desc=[$4]): rowcount = 100.0, cumulative cost = {110.0 rows, 141.0 cpu, 0.0 io}, id = 331
          SlothTableScan(table=[[db1, sales]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 22
        SlothTableScan(table=[[db1, person]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 24
    SlothProject(id=[$0], name=[$1]): rowcount = 25.0, cumulative cost = {127.5 rows, 206.0 cpu, 0.0 io}, id = 335
      SlothFilter(condition=[OR(=($1, 'hubei'), =($1, 'hunan'))]): rowcount = 25.0, cumulative cost = {125.0 rows, 201.0 cpu, 0.0 io}, id = 334
        SlothTableScan(table=[[db1, addr]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 27

1 row in set (1.28 sec)

mysql> explain select * from personn where name like 'xx%'\G
ERROR 1064 (HY000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from personn where name like 'xx%''
mysql> explain select * from person where name like 'xx%'\G
*************************** 1. row ***************************
Plan:
SlothFilter(condition=[LIKE($1, 'xx%')]): rowcount = 25.0, cumulative cost = {125.0 rows, 201.0 cpu, 0.0 io}, id = 361
  SlothTableScan(table=[[db1, person]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 342

1 row in set (0.06 sec)

mysql> select 1 + 1, 2 * 2 + 1, 102 * 100;
+-------+-----------+-----------+
| 1 + 1 | 2 * 2 + 1 | 102 * 100 |
+-------+-----------+-----------+
| 2     | 5         | 10200     |
+-------+-----------+-----------+
1 row in set (0.16 sec)

mysql> explain select t1.id, t1.money, t1.desc, t2.name from sales t1 right join person t2 on t1.person_id = t2.id limit 2\G
*************************** 1. row ***************************
Plan:
SlothProject(id=[$0], money=[$2], desc=[$3], name=[$5]): rowcount = 2.0, cumulative cost = {244.2 rows, 306.8 cpu, 0.0 io}, id = 167
  SlothSort(fetch=[2]): rowcount = 2.0, cumulative cost = {244.0 rows, 306.0 cpu, 0.0 io}, id = 166
    SlothJoin(condition=[=($1, $4)], joinType=[right]): rowcount = 30.0, cumulative cost = {242.0 rows, 258.0 cpu, 0.0 io}, id = 165
      SlothProject(id=[$0], person_id=[$1], money=[$3], desc=[$4]): rowcount = 100.0, cumulative cost = {110.0 rows, 141.0 cpu, 0.0 io}, id = 163
        SlothTableScan(table=[[db1, sales]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 129
      SlothSort(fetch=[2]): rowcount = 2.0, cumulative cost = {102.0 rows, 117.0 cpu, 0.0 io}, id = 164
        SlothTableScan(table=[[db1, person]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 133

1 row in set (0.39 sec)

mysql> select t1.id, t1.money, t1.desc, t2.name from sales t1 right join person t2 on t1.person_id = t2.id limit 2\G
*************************** 1. row ***************************
   id: NULL
money: NULL
 desc: NULL
 name: hello
*************************** 2. row ***************************
   id: 101
money: 32.5
 desc: lisi
 name: good
2 rows in set (0.62 sec)

mysql> select t1.id, t1.money, t1.desc, t2.name from sales t1 right join person t2 on t1.person_id = t2.id order by money desc limit 3\G
*************************** 1. row ***************************
   id: 101
money: 32.5
 desc: lisi
 name: good
*************************** 2. row ***************************
   id: 100
money: 25.5
 desc: zhangsan
 name: nice
*************************** 3. row ***************************
   id: NULL
money: NULL
 desc: NULL
 name: hello
3 rows in set (0.21 sec)

mysql> explain select t1.id, t1.money, t1.desc, t2.name from sales t1 right join person t2 on t1.person_id = t2.id where money > 30 order by money desc limit 3\G
*************************** 1. row ***************************
Plan:
SlothProject(id=[$0], money=[$2], desc=[$3], name=[$5]): rowcount = 3.0, cumulative cost = {1008.3 rows, 402.3000847841039 cpu, 0.0 io}, id = 494
  SlothSort(sort0=[$2], dir0=[DESC], fetch=[3]): rowcount = 3.0, cumulative cost = {1008.0 rows, 401.1000847841039 cpu, 0.0 io}, id = 493
    SlothJoin(condition=[=($1, $4)], joinType=[inner]): rowcount = 750.0, cumulative cost = {1005.0 rows, 322.0 cpu, 0.0 io}, id = 492
      SlothProject(id=[$0], person_id=[$1], money=[$3], desc=[$4]): rowcount = 50.0, cumulative cost = {155.0 rows, 221.0 cpu, 0.0 io}, id = 491
        SlothFilter(condition=[>($3, 30)]): rowcount = 50.0, cumulative cost = {150.0 rows, 201.0 cpu, 0.0 io}, id = 490
          SlothTableScan(table=[[db1, sales]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 373
      SlothTableScan(table=[[db1, person]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 377

1 row in set (0.25 sec)

mysql> select t1.id, t1.money, t1.desc, t2.name from sales t1 right join person t2 on t1.person_id = t2.id where money > 30 order by money desc limit 3\G
*************************** 1. row ***************************
   id: 101
money: 32.5
 desc: lisi
 name: good
1 row in set (0.15 sec)


mysql> use db2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t1;
Empty set (0.63 sec)

mysql> insert into t1 values(1, 'hanngzhou'),(2, 'wuhan'),(3, 'nanjing'),(4, 'jiujiang');
Query OK, 4 rows affected (0.15 sec)

mysql> select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | hanngzhou |
|    2 | wuhan     |
|    3 | nanjing   |
|    4 | jiujiang  |
+------+-----------+
4 rows in set (0.05 sec)

mysql> select * from t1 where id > 2;
+------+----------+
| id   | name     |
+------+----------+
|    3 | nanjing  |
|    4 | jiujiang |
+------+----------+
2 rows in set (0.06 sec)

mysql> explain select * from t1 where id > 2;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan                                                                                                                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
SlothFilter(condition=[>($0, 2)]): rowcount = 50.0, cumulative cost = {150.0 rows, 201.0 cpu, 0.0 io}, id = 138
  SlothTableScan(table=[[db2, t1]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 133
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>
最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末扭屁，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子涩禀，更是在濱河造成了極大的恐慌料滥，老刑警劉巖，帶你破解...
    沈念sama閱讀 206,839評論 6贊 482
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件艾船，死亡現(xiàn)場離奇詭異葵腹，居然都是意外死亡，警方通過查閱死者的電腦和手機屿岂，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,543評論 2贊 382
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門践宴，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人爷怀，你說我怎么就攤上這事阻肩。” “怎么了运授？”我有些...
    開封第一講書人閱讀 153,116評論 0贊 344
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵烤惊，是天一觀的道長。 經(jīng)常有香客問我吁朦，道長撕氧，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 55,371評論 1贊 279
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任喇完，我火速辦了婚禮伦泥，結果婚禮上剥啤，老公的妹妹穿的比我還像新娘。我一直安慰自己不脯，他們只是感情好府怯，可當我...
    茶點故事閱讀 64,384評論 5贊 374
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著防楷，像睡著了一般牺丙。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上复局，一...
    開封第一講書人閱讀 49,111評論 1贊 285
  • 城市分裂傳說
    那天冲簿，我揣著相機與錄音，去河邊找鬼亿昏。 笑死峦剔，一個胖子當著我的面吹牛，可吹牛的內容都是我干的角钩。 我是一名探鬼主播吝沫，決...
    沈念sama閱讀 38,416評論 3贊 400
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼递礼！你這毒婦竟也來了惨险？” 一聲冷哼從身側響起，我...
    開封第一講書人閱讀 37,053評論 0贊 259
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤脊髓，失蹤者是張志新（化名）和其女友劉穎辫愉，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體将硝，經(jīng)...
    沈念sama閱讀 43,558評論 1贊 300
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡一屋，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 36,007評論 2贊 325
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了袋哼。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片冀墨。...
    茶點故事閱讀 38,117評論 1贊 334
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖涛贯，靈堂內的尸體忽然破棺而出诽嘉，到底是詐尸還是另有隱情，我是刑警寧澤弟翘，帶...
    沈念sama閱讀 33,756評論 4贊 324
  • ?日本核電站爆炸內幕
    正文 年R本政府宣布虫腋，位于F島的核電站，受9級特大地震影響稀余，放射性物質發(fā)生泄漏悦冀。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 39,324評論 3贊 307
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一睛琳、第九天 我趴在偏房一處隱蔽的房頂上張望盒蟆。 院中可真熱鬧踏烙，春花似錦、人聲如沸历等。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,315評論 0贊 19
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽寒屯。三九已至荐捻，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間寡夹，已是汗流浹背处面。 一陣腳步聲響...
    開封第一講書人閱讀 31,539評論 1贊 262
  • 情欲美人皮
    我被黑心中介騙來泰國打工， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留菩掏，地道東北人魂角。 一個月前我還...
    沈念sama閱讀 45,578評論 2贊 355
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像患蹂，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子砸紊，可洞房花燭夜當晚...
    茶點故事閱讀 42,877評論 2贊 345