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 的语法也不相同）。下面体验下
代码语言：javascript复制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
代码语言：javascript复制panic: Actual Value Type: []string, Expected Value Type: sql.JSONValue解决后，我们就可以启动我们的server
代码语言：javascript复制% go run ./main.go
INFO[0007] NewConnection DisableClientMultiStatements=false connectionID=1尝试连接
代码语言：javascript复制% 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)数据查询
代码语言：javascript复制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)
