go-mysql-server:基于内存的go实现mysql server

golang算法架构leetcode技术php 2023-04-26

1755

https://github.com/dolthub/go-mysql-server是用golang实现的基于内存的mysql server。主要应用于两个场景：作为golang单测的mysql替换，作为内置的基于内存的数据库。MySQL Fake 常见实现方式有两种：SQLite 以及 go-mysql-server。因为大家都基本遵循了 SQL 92 标准，在没有用到比较复杂的，或者 MySQL 特有的一些语法特性时，我们可以用 SQLite 来作为一个平替。但是SQLite底层是C语言实现的，github.com/mattn/go-sqlite3仅仅做了cgo的一层封装。用 SQLite 来存取数据时，你会发现跟 MySQL 不同，它只依赖一个文件进行读取和写入，非常轻量级。我们可以在单测执行结束的时候清理掉这个文件即可。SQLite 还提供了内存的模式，这样我们就能完全不依赖存储，直接用 SQLite 来验证我们的语句是否正确。

go-mysql-server 是一个用 Golang 实现的，和 MySQL 完全兼容的数据库，能够用于golang的测试环境，它可以启动一个内存级别的mysql db，初始化一些数据，可以让被测试对象的db连接指向该内存db。这样做测试的好处是:没有很夸张的mock成本;不用担心产生的脏数据问题;能顺带着测出 DAL 层sql不符合预期的问题。和 SQLite 相比，它进一步规避了很多 SQLite 和 MySQL 语法不兼容的问题（虽然哪怕是 MySQL 自身，5.7 和 8.0 的语法也不相同）。下面体验下

package main


import (
  "time"


  sqle "github.com/dolthub/go-mysql-server"
  "github.com/dolthub/go-mysql-server/memory"
  "github.com/dolthub/go-mysql-server/server"
  "github.com/dolthub/go-mysql-server/sql"
  "github.com/dolthub/go-mysql-server/sql/information_schema"
)


// Example of how to implement a MySQL server based on a Engine:
//
// ```
// > mysql --host=127.0.0.1 --port=3306 -u root mydb -e "SELECT * FROM mytable"
// +----------+-------------------+-------------------------------+---------------------+
// | name     | email             | phone_numbers                 | created_at          |
// +----------+-------------------+-------------------------------+---------------------+
// | John Doe | john@doe.com      | ["555-555-555"]               | 2018-04-18 09:41:13 |
// | John Doe | johnalt@doe.com   | []                            | 2018-04-18 09:41:13 |
// | Jane Doe | jane@doe.com      | []                            | 2018-04-18 09:41:13 |
// | Evil Bob | evilbob@gmail.com | ["555-666-555","666-666-666"] | 2018-04-18 09:41:13 |
// +----------+-------------------+-------------------------------+---------------------+
// ```
func main() {
  engine := sqle.NewDefault(
    sql.NewDatabaseProvider(
      createTestDatabase(),
      information_schema.NewInformationSchemaDatabase(),
    ))
  engine.Analyzer.Catalog.MySQLDb.AddRootAccount()
  config := server.Config{
    Protocol: "tcp",
    Address:  "localhost:3306",
  }
  s, err := server.NewDefaultServer(config, engine)
  if err != nil {
    panic(err)
  }
  s.Start()
}


func createTestDatabase() *memory.Database {
  const (
    dbName    = "mydb"
    tableName = "mytable"
  )
  db := memory.NewDatabase(dbName)
  table := memory.NewTable(tableName, sql.NewPrimaryKeySchema(sql.Schema{
    {Name: "name", Type: sql.Text, Nullable: false, Source: tableName},
    {Name: "email", Type: sql.Text, Nullable: false, Source: tableName},
    {Name: "phone_numbers", Type: sql.JSON, Nullable: false, Source: tableName},
    // {Name: "phone_numbers", Type: sql, Nullable: false, Source: tableName},
    {Name: "created_at", Type: sql.Timestamp, Nullable: false, Source: tableName},
  }), &memory.ForeignKeyCollection{})


  db.AddTable(tableName, table)
  ctx := sql.NewEmptyContext()
  table.Insert(ctx, sql.NewRow("John Doe", "john@doe.com", sql.JSONDocument{Val: []string{"555-555-555"}}, time.Now()))
  table.Insert(ctx, sql.NewRow("John Doe", "johnalt@doe.com", sql.JSONDocument{Val: []string{}}, time.Now()))
  table.Insert(ctx, sql.NewRow("Jane Doe", "jane@doe.com", sql.JSONDocument{Val: []string{}}, time.Now()))
  table.Insert(ctx, sql.NewRow("Evil Bob", "evilbob@gmail.com", sql.JSONDocument{Val: []string{"555-666-555", "666-666-666"}}, time.Now()))
  return db
}

官方的例子会有问题，原因详见https://github.com/dolthub/go-mysql-server/issues/1357

panic: Actual Value Type: []string, Expected Value Type: sql.JSONValue

解决后，我们就可以启动我们的server

% go run ./main.go
INFO[0007] NewConnection                                 DisableClientMultiStatements=false connectionID=1

尝试连接

%  mysql --host=127.0.0.1 --port=3306 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess


Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
+--------------------+
3 rows in set (0.01 sec)

数据查询

mysql> use mydb;
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> SELECT * FROM mytable;
+----------+-------------------+-------------------------------+----------------------------+
| name     | email             | phone_numbers                 | created_at                 |
+----------+-------------------+-------------------------------+----------------------------+
| John Doe | john@doe.com      | ["555-555-555"]               | 2023-04-16 00:43:21.392759 |
| John Doe | johnalt@doe.com   | []                            | 2023-04-16 00:43:21.392781 |
| Jane Doe | jane@doe.com      | []                            | 2023-04-16 00:43:21.392784 |
| Evil Bob | evilbob@gmail.com | ["555-666-555","666-666-666"] | 2023-04-16 00:43:21.392786 |
+----------+-------------------+-------------------------------+----------------------------+
4 rows in set (0.00 sec)

go mysql数据库 mysql sqlite

文章转载自golang算法架构leetcode技术php，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

go-mysql-server:基于内存的go实现mysql server

评论