[手寫系列]Go手寫db —

[手寫系列]Go手寫db

ZiyiDB是一個簡單的內存數據庫實現，支持基本的SQL操作，包含create、insert、delete、select、update、drop。目前一期暫支持int類型以及字符類型數據，后續會支持更多數據結構以及能力。本項目基于https://github.com/eatonphil/gosql靈感進行開發。

項目Github地址：https://github.com/ziyifast/ZiyiDB
請大家多多支持，也歡迎大家star??和共同維護這個項目~

項目結構

// 項目創建
mkdir ZiyiDB
cd ZiyiDB/
go mod init ziyi.db.com

ZiyiDB/
├── cmd/
│   └── main.go           # 主程序入口
├── internal/
│   ├── ast/
│   │   └── ast.go        # 抽象語法樹定義
│   ├── lexer/
│   │   ├── lexer.go      # 詞法分析器實現
│   │   └── token.go      # 詞法單元定義
│   ├── parser/
│   │   └── parser.go     # 語法分析器實現
│   └── storage/
│       └── memory.go     # 內存存儲引擎實現
├── go.mod                # Go模塊定義
└── go.sum                # 依賴版本鎖定

原理介紹

流程圖：
在這里插入圖片描述

主要包含幾大模塊：

cmd/main.go：

程序入口點
實現交互式命令行界面
處理用戶輸入
顯示執行結果

internal/ast/ast.go：

定義抽象語法樹節點
定義 SQL 語句結構
定義表達式結構

internal/lexer/token.go：

定義詞法單元類型
定義 SQL 關鍵字
定義運算符和分隔符

internal/lexer/lexer.go：

實現詞法分析器
將輸入文本轉換為標記序列
處理標識符和字面量

internal/parser/parser.go：

實現語法分析器
將標記序列轉換為抽象語法樹
處理各種 SQL 語句

internal/storage/memory.go：

實現內存存儲引擎
處理數據存儲和檢索
實現索引和約束

具體實現

模塊一：詞法分析器 (Lexer)

詞法分析器 (Lexer)：SQL轉token序列

①定義標記類型token.go

思路

新建ziyi-db/internal/lexer/token.go文件,完成詞法分析器（Lexer）中的標記（Token）定義部分，用于將 SQL 語句分解成基本的語法單元。

定義詞法單元以及關鍵字：

包含常見的SQL關鍵字，如：select、update等
包含符號關鍵字：=、>、<
包含字段類型：INT、字符型（TEXT）
包含標識符：INDENT，解析出來的SQL列名、表名

type TokenType stringconst (SELECT  TokenType = "SELECT"FROM    TokenType = "FROM"IDENT   TokenType = "IDENT"  // 標識符（如列名、表名）INT_LIT TokenType = "INT"    // 整數字面量STRING  TokenType = "STRING" // 字符串字面量EQ TokenType = "=" // 等于GT TokenType = ">" // 大于LT TokenType = "<" // 小于....
)// Token 詞法單元
// Type：標記的類型（如 SELECT、IDENT 等）
// Literal：標記的實際值（如具體的列名、數字等）
type Token struct {Type    TokenType // 標記類型Literal string    // 標記的實際值
}

示例：

SELECT id, name FROM users WHERE age > 18;該SQL 語句會被下面的詞法分析器lexer.go分解成以下標記序列：
{Type: SELECT, Literal: "SELECT"}
{Type: IDENT, Literal: "id"}
{Type: COMMA, Literal: ","}
{Type: IDENT, Literal: "name"}
{Type: FROM, Literal: "FROM"}
{Type: IDENT, Literal: "users"}
{Type: WHERE, Literal: "WHERE"}
{Type: IDENT, Literal: "age"}
{Type: GT, Literal: ">"}
{Type: INT_LIT, Literal: "18"}
{Type: SEMI, Literal: ";"}
解析后的標記隨后會被傳遞給語法分析器（Parser）進行進一步處理，構建抽象語法樹（AST）。

全部代碼

// internal/lexer/token.go
package lexer// TokenType 表示詞法單元類型
type TokenType stringconst (// 特殊標記EOF   TokenType = "EOF"   // 文件結束標記ERROR TokenType = "ERROR" // 錯誤標記// 關鍵字SELECT  TokenType = "SELECT"FROM    TokenType = "FROM"WHERE   TokenType = "WHERE"CREATE  TokenType = "CREATE"TABLE   TokenType = "TABLE"INSERT  TokenType = "INSERT"INTO    TokenType = "INTO"VALUES  TokenType = "VALUES"UPDATE  TokenType = "UPDATE"SET     TokenType = "SET"DELETE  TokenType = "DELETE"DROP    TokenType = "DROP"PRIMARY TokenType = "PRIMARY"KEY     TokenType = "KEY"INT     TokenType = "INT"TEXT    TokenType = "TEXT"LIKE    TokenType = "LIKE"// 標識符和字面量IDENT   TokenType = "IDENT"  // 標識符（如列名、表名）INT_LIT TokenType = "INT"    // 整數字面量STRING  TokenType = "STRING" // 字符串字面量// 運算符EQ TokenType = "="GT TokenType = ">"LT TokenType = "<"// 標識符COMMA    TokenType = ","SEMI     TokenType = ";"LPAREN   TokenType = "("RPAREN   TokenType = ")"ASTERISK TokenType = "*"
)// Token 詞法單元
// Type：標記的類型（如 SELECT、IDENT 等）
// Literal：標記的實際值（如具體的列名、數字等）
type Token struct {Type    TokenType // 標記類型Literal string    // 標記的實際值
}

② 實現詞法分析器lexer.go

思路

新建ziyi-db/internal/lexer/lexer.go文件，這是詞法分析器（Lexer）的核心實現，負責將輸入的 SQL 語句分解成標記（Token）序列。

詞法分析器lexer.go：讀取SQL到內存中并進行解析，將字符轉換為對應關鍵字

示例：

SELECT id, name FROM users WHERE age > 18;處理過程：
跳過空白字符
讀取 "SELECT" 并識別為關鍵字
讀取 "id" 并識別為標識符
讀取 "," 并識別為分隔符
讀取 "name" 并識別為標識符
讀取 "FROM" 并識別為關鍵字
讀取 "users" 并識別為標識符
讀取 "WHERE" 并識別為關鍵字
讀取 "age" 并識別為標識符
讀取 ">" 并識別為運算符
讀取 "18" 并識別為數字
讀取 ";" 并識別為分隔符
這個詞法分析器是 SQL 解析器的第一步，它將輸入的 SQL 語句分解成標記序列，為后續的語法分析提供基礎該SQL 語句會被詞法分析器分解成以下標記序列：
{Type: SELECT, Literal: "SELECT"}
{Type: IDENT, Literal: "id"}
{Type: COMMA, Literal: ","}
{Type: IDENT, Literal: "name"}
{Type: FROM, Literal: "FROM"}
{Type: IDENT, Literal: "users"}
{Type: WHERE, Literal: "WHERE"}
{Type: IDENT, Literal: "age"}
{Type: GT, Literal: ">"}
{Type: INT_LIT, Literal: "18"}
{Type: SEMI, Literal: ";"}
解析后的標記隨后會被傳遞給語法分析器（Parser）進行進一步處理，構建抽象語法樹（AST）。

全部代碼

// internal/lexer/lexer.go
package lexerimport ("bufio""bytes""io""strings""unicode"
)// Lexer 詞法分析器
// reader：使用 bufio.Reader 進行高效的字符讀取
// ch：存儲當前正在處理的字符
type Lexer struct {reader *bufio.Reader // 用于讀取輸入ch     rune          // 當前字符
}// NewLexer 創建一個新的 詞法分析器
// 初始化 reader 并讀取第一個字符
func NewLexer(r io.Reader) *Lexer {l := &Lexer{reader: bufio.NewReader(r),}l.readChar()return l
}// 讀取字符
func (l *Lexer) readChar() {ch, _, err := l.reader.ReadRune()if err != nil {l.ch = 0 // 遇到錯誤或EOF時設置為0} else {l.ch = ch}
}// NextToken 獲取下一個詞法單元
// 識別并返回下一個標記
// 處理各種類型的標記：運算符、分隔符、標識符、數字、字符串等
func (l *Lexer) NextToken() Token {var tok Token// 跳過空白字符l.skipWhitespace()switch l.ch {case '=':tok = Token{Type: EQ, Literal: "="}case '>':tok = Token{Type: GT, Literal: ">"}case '<':tok = Token{Type: LT, Literal: "<"}case ',':tok = Token{Type: COMMA, Literal: ","}case ';':tok = Token{Type: SEMI, Literal: ";"}case '(':tok = Token{Type: LPAREN, Literal: "("}case ')':tok = Token{Type: RPAREN, Literal: ")"}case '*':tok = Token{Type: ASTERISK, Literal: "*"}case '\'':tok.Type = STRING// 讀取字符串字面量tok.Literal = l.readString()return tokcase 0:tok = Token{Type: EOF, Literal: ""}default:if isLetter(l.ch) {// 讀取標識符（表名、列名等）tok.Literal = l.readIdentifier()// 將讀取到的標識符轉換為對應的標記類型(轉換為對應tokenType)tok.Type = l.lookupIdentifier(tok.Literal)return tok} else if isDigit(l.ch) {tok.Type = INT_LIT// 讀取數字tok.Literal = l.readNumber()return tok} else {tok = Token{Type: ERROR, Literal: string(l.ch)}}}l.readChar()return tok
}func (l *Lexer) skipWhitespace() {for unicode.IsSpace(l.ch) {l.readChar()}
}// 讀取標識符，如：列名、表名
func (l *Lexer) readIdentifier() string {var ident bytes.Bufferfor isLetter(l.ch) || isDigit(l.ch) {ident.WriteRune(l.ch)l.readChar()}return ident.String()
}func (l *Lexer) readNumber() string {var num bytes.Bufferfor isDigit(l.ch) {num.WriteRune(l.ch)l.readChar()}return num.String()
}// 讀取字符串字面量
func (l *Lexer) readString() string {var str bytes.Bufferl.readChar() // 跳過開始的引號for l.ch != '\'' && l.ch != 0 {str.WriteRune(l.ch)l.readChar()}l.readChar() // 跳過結束的引號return str.String()
}func (l *Lexer) peekChar() rune {ch, _, err := l.reader.ReadRune()if err != nil {return 0}l.reader.UnreadRune()return ch
}// lookupIdentifier 查找標識符類型
// 將標識符轉換為對應的標記類型
// 識別 SQL 關鍵字
func (l *Lexer) lookupIdentifier(ident string) TokenType {switch strings.ToUpper(ident) {case "SELECT":return SELECTcase "FROM":return FROMcase "WHERE":return WHEREcase "CREATE":return CREATEcase "TABLE":return TABLEcase "INSERT":return INSERTcase "INTO":return INTOcase "VALUES":return VALUEScase "UPDATE":return UPDATEcase "SET":return SETcase "DELETE":return DELETEcase "DROP":return DROPcase "PRIMARY":return PRIMARYcase "KEY":return KEYcase "INT":return INTcase "TEXT":return TEXTcase "LIKE":return LIKEdefault:return IDENT}
}// 判斷字符是否為字母或下劃線
func isLetter(ch rune) bool {return unicode.IsLetter(ch) || ch == '_'
}// 判斷字符是否為數字
func isDigit(ch rune) bool {return unicode.IsDigit(ch)
}

模塊二：抽象語法樹 (AST)

思路

抽象語法樹用于表示 SQL 語句的語法結構。我們需要為每種 SQL 語句定義相應的節點類型。

我們新建internal/ast/ast.go。

ast.go構建不同SQL語句的結構，以及查詢結果等。
這個 AST 定義文件是 SQL 解析器的核心部分，它：

定義了所有 SQL 語句的語法結構
提供了類型安全的方式來表示 SQL 語句
支持復雜的表達式和條件
便于后續的語義分析和執行
通過這個 AST，我們可以：

驗證 SQL 語句的語法正確性
進行語義分析
生成執行計劃
執行 SQL 語句

示例：

SELECT id, name FROM users WHERE age > 18;交給語法分析器parser解析后的AST結構為：SelectStatement
├── Fields
│   ├── Identifier{Value: "id"}
│   └── Identifier{Value: "name"}
├── TableName: "users"
└── Where└── BinaryExpression├── Left: Identifier{Value: "age"}├── Operator: ">"└── Right: IntegerLiteral{Value: "18"}

全部代碼

package astimport ("cursor-db/internal/lexer""fmt"
)// Node 表示AST中的節點
type Node interface {TokenLiteral() string
}// Statement 表示SQL語句
type Statement interface {NodestatementNode()
}// Expression 表示表達式
type Expression interface {NodeexpressionNode()
}// Program 表示整個SQL程序
type Program struct {Statements []Statement
}// SelectStatement 表示SELECT語句
type SelectStatement struct {Token     lexer.TokenFields    []ExpressionTableName stringWhere     Expression
}func (ss *SelectStatement) statementNode()       {}
func (ss *SelectStatement) TokenLiteral() string { return ss.Token.Literal }// CreateTableStatement 表示CREATE TABLE語句
type CreateTableStatement struct {Token     lexer.TokenTableName stringColumns   []ColumnDefinition
}func (cts *CreateTableStatement) statementNode()       {}
func (cts *CreateTableStatement) TokenLiteral() string { return cts.Token.Literal }// InsertStatement 表示INSERT語句
type InsertStatement struct {Token     lexer.TokenTableName stringValues    []Expression
}func (is *InsertStatement) statementNode()       {}
func (is *InsertStatement) TokenLiteral() string { return is.Token.Literal }// ColumnDefinition 表示列定義
type ColumnDefinition struct {Name     stringType     stringPrimary  boolNullable bool
}// Cell 表示數據單元格
type Cell struct {Type      CellTypeIntValue  int32TextValue string
}// CellType 表示單元格類型
type CellType intconst (CellTypeInt CellType = iotaCellTypeText
)// AsText 返回單元格的文本值
func (c *Cell) AsText() string {switch c.Type {case CellTypeInt:s := fmt.Sprintf("%d", c.IntValue)return scase CellTypeText:return c.TextValuedefault:return "NULL"}
}// AsInt 返回單元格的整數值
func (c *Cell) AsInt() int32 {if c.Type == CellTypeInt {return c.IntValue}return 0
}// String 返回單元格的字符串表示
func (c Cell) String() string {switch c.Type {case CellTypeInt:return fmt.Sprintf("%d", c.IntValue)case CellTypeText:return c.TextValuedefault:return "NULL"}
}// Results 表示查詢結果
type Results struct {Columns []ResultColumnRows    [][]Cell
}// ResultColumn 表示結果列
type ResultColumn struct {Name stringType string
}// StarExpression 表示星號表達式,如：select * from users;
type StarExpression struct{}func (se *StarExpression) expressionNode()      {}
func (se *StarExpression) TokenLiteral() string { return "*" }// LikeExpression 表示LIKE表達式, 如 LIKE '%b'
type LikeExpression struct {Token   lexer.TokenLeft    ExpressionPattern string
}func (le *LikeExpression) expressionNode()      {}
func (le *LikeExpression) TokenLiteral() string { return le.Token.Literal }// BinaryExpression 表示二元表達式,如比較運算,大于小于比較等
type BinaryExpression struct {Token    lexer.TokenLeft     ExpressionOperator stringRight    Expression
}func (be *BinaryExpression) expressionNode()      {}
func (be *BinaryExpression) TokenLiteral() string { return be.Token.Literal }// IntegerLiteral 表示整數字面量
type IntegerLiteral struct {Token lexer.TokenValue string
}func (il *IntegerLiteral) expressionNode()      {}
func (il *IntegerLiteral) TokenLiteral() string { return il.Token.Literal }// StringLiteral 表示字符串字面量
type StringLiteral struct {Token lexer.TokenValue string
}func (sl *StringLiteral) expressionNode()      {}
func (sl *StringLiteral) TokenLiteral() string { return sl.Token.Literal }// Identifier 表示標識符（如列名）
type Identifier struct {Token lexer.TokenValue string
}func (i *Identifier) expressionNode()      {}
func (i *Identifier) TokenLiteral() string { return i.Token.Literal }// UpdateStatement 表示UPDATE語句
type UpdateStatement struct {Token     lexer.TokenTableName stringSet       []SetClauseWhere     Expression
}func (us *UpdateStatement) statementNode()       {}
func (us *UpdateStatement) TokenLiteral() string { return us.Token.Literal }// SetClause 表示SET子句
type SetClause struct {Column stringValue  Expression
}// DeleteStatement 表示DELETE語句
type DeleteStatement struct {Token     lexer.TokenTableName stringWhere     Expression
}func (ds *DeleteStatement) statementNode()       {}
func (ds *DeleteStatement) TokenLiteral() string { return ds.Token.Literal }// DropTableStatement 表示DROP TABLE語句
type DropTableStatement struct {Token     lexer.TokenTableName string
}func (ds *DropTableStatement) statementNode()       {}
func (ds *DropTableStatement) TokenLiteral() string { return ds.Token.Literal }

模塊三：語法分析器 (Parser)

思路

語法分析器負責將詞法分析器生成的標記序列轉換為抽象語法樹。將token序列構建成ast。

SQL 解析器（Parser）的實現，負責將詞法分析器（Lexer）產生的標記（Token）序列轉換為抽象語法樹（AST）。
語法分析器SQL 數據庫系統的關鍵組件，負責：

驗證 SQL 語句的語法正確性
構建抽象語法樹
為后續的語義分析和執行提供基礎

我們新建internal/parser/parser.go。

示例：

CREATE TABLE users (id INT PRIMARY KEY,name TEXT
);解析過程：
1. 識別 CREATE 關鍵字
2. 解析 TABLE 關鍵字
3. 解析表名 "users"
4. 解析列定義：列名 "id"，類型 INT，主鍵列名 "name"，類型 TEXT
5. 生成 CREATE TABLE 語句的 AST

全部代碼

package parserimport ("fmt""ziyi.db.com/internal/ast""ziyi.db.com/internal/lexer"
)// Parser 表示語法分析器
// 維護當前和下一個標記，實現向前查看（lookahead）
// 記錄解析過程中的錯誤
type Parser struct {l         *lexer.Lexer // 詞法分析器curToken  lexer.Token  // 當前標記peekToken lexer.Token  // 下一個標記errors    []string     // 錯誤信息
}// NewParser 創建新的語法分析器
// 初始化解析器
// 預讀兩個標記
func NewParser(l *lexer.Lexer) *Parser {p := &Parser{l:      l,errors: []string{},}// 讀取兩個token，設置curToken和peekTokenp.nextToken()p.nextToken()return p
}// nextToken 移動到下一個詞法單元
func (p *Parser) nextToken() {p.curToken = p.peekTokenp.peekToken = p.l.NextToken()
}// ParseProgram 解析整個程序
// 解析整個 SQL 程序
// 循環解析每個語句直到結束
func (p *Parser) ParseProgram() (*ast.Program, error) {program := &ast.Program{Statements: []ast.Statement{},}for p.curToken.Type != lexer.EOF {stmt, err := p.parseStatement()if err != nil {return nil, err}if stmt != nil {program.Statements = append(program.Statements, stmt)}p.nextToken()}return program, nil
}// parseStatement 解析語句
// 根據當前標記類型選擇相應的解析方法
func (p *Parser) parseStatement() (ast.Statement, error) {switch p.curToken.Type {case lexer.CREATE:return p.parseCreateTableStatement()case lexer.INSERT:return p.parseInsertStatement()case lexer.SELECT:return p.parseSelectStatement()case lexer.UPDATE:return p.parseUpdateStatement()case lexer.DELETE:return p.parseDeleteStatement()case lexer.DROP:return p.parseDropTableStatement()case lexer.SEMI:return nil, nildefault:return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Type)}
}// parseCreateTableStatement 解析CREATE TABLE語句
// 解析表名
// 解析列定義
// 處理主鍵約束
func (p *Parser) parseCreateTableStatement() (*ast.CreateTableStatement, error) {stmt := &ast.CreateTableStatement{Token: p.curToken}if !p.expectPeek(lexer.TABLE) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}if !p.expectPeek(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}stmt.TableName = p.curToken.Literalif !p.expectPeek(lexer.LPAREN) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}// 解析列定義for !p.peekTokenIs(lexer.RPAREN) {p.nextToken()if !p.curTokenIs(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Literal)}col := ast.ColumnDefinition{Name: p.curToken.Literal,}if !p.expectPeek(lexer.INT) && !p.expectPeek(lexer.TEXT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}col.Type = string(p.curToken.Type)if p.peekTokenIs(lexer.PRIMARY) {p.nextToken()if !p.expectPeek(lexer.KEY) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}col.Primary = true}stmt.Columns = append(stmt.Columns, col)if p.peekTokenIs(lexer.COMMA) {p.nextToken()}}if !p.expectPeek(lexer.RPAREN) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}return stmt, nil
}// parseInsertStatement 解析INSERT語句
// 解析表名
// 解析 VALUES 子句
// 解析插入的值
func (p *Parser) parseInsertStatement() (*ast.InsertStatement, error) {stmt := &ast.InsertStatement{Token: p.curToken}if !p.expectPeek(lexer.INTO) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}if !p.expectPeek(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}stmt.TableName = p.curToken.Literalif !p.expectPeek(lexer.VALUES) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}if !p.expectPeek(lexer.LPAREN) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}// 解析值列表for !p.peekTokenIs(lexer.RPAREN) {p.nextToken()expr, err := p.parseExpression()if err != nil {return nil, err}stmt.Values = append(stmt.Values, expr)if p.peekTokenIs(lexer.COMMA) {p.nextToken()}}if !p.expectPeek(lexer.RPAREN) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}return stmt, nil
}// parseSelectStatement 解析SELECT語句
// 解析選擇列表
// 解析 FROM 子句
// 解析 WHERE 子句
func (p *Parser) parseSelectStatement() (*ast.SelectStatement, error) {stmt := &ast.SelectStatement{Token: p.curToken}// 解析選擇列表for !p.peekTokenIs(lexer.FROM) {p.nextToken()if p.curToken.Type == lexer.ASTERISK {stmt.Fields = append(stmt.Fields, &ast.StarExpression{})break}expr, err := p.parseExpression()if err != nil {return nil, err}stmt.Fields = append(stmt.Fields, expr)if p.peekTokenIs(lexer.COMMA) {p.nextToken()}}if !p.expectPeek(lexer.FROM) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}if !p.expectPeek(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}stmt.TableName = p.curToken.Literal// 解析WHERE子句if p.peekTokenIs(lexer.WHERE) {p.nextToken()p.nextToken()// 解析左操作數（列名）if !p.curTokenIs(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Literal)}left := &ast.Identifier{Token: p.curToken,Value: p.curToken.Literal,}// 解析操作符p.nextToken()operator := p.curToken// 處理LIKE操作符if p.curTokenIs(lexer.LIKE) {p.nextToken()if !p.curTokenIs(lexer.STRING) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Literal)}// 移除字符串字面量的引號pattern := p.curToken.Literalif len(pattern) >= 2 && (pattern[0] == '\'' || pattern[0] == '"') {pattern = pattern[1 : len(pattern)-1]}stmt.Where = &ast.LikeExpression{Token:   operator,Left:    left,Pattern: pattern,}return stmt, nil}// 處理其他操作符if !p.curTokenIs(lexer.EQ) && !p.curTokenIs(lexer.GT) && !p.curTokenIs(lexer.LT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", operator.Type)}// 解析右操作數p.nextToken()right, err := p.parseExpression()if err != nil {return nil, err}stmt.Where = &ast.BinaryExpression{Token:    operator,Left:     left,Operator: operator.Literal,Right:    right,}}return stmt, nil
}// parseUpdateStatement 解析UPDATE語句
// 解析表名
// 解析 SET 子句
// 解析 WHERE 子句
func (p *Parser) parseUpdateStatement() (*ast.UpdateStatement, error) {stmt := &ast.UpdateStatement{Token: p.curToken}if !p.expectPeek(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}stmt.TableName = p.curToken.Literalif !p.expectPeek(lexer.SET) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}// 解析SET子句for {p.nextToken()if !p.curTokenIs(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Literal)}column := p.curToken.Literalif !p.expectPeek(lexer.EQ) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}p.nextToken()value, err := p.parseExpression()if err != nil {return nil, err}stmt.Set = append(stmt.Set, ast.SetClause{Column: column,Value:  value,})if !p.peekTokenIs(lexer.COMMA) {break}p.nextToken()}// 解析WHERE子句if p.peekTokenIs(lexer.WHERE) {p.nextToken()p.nextToken()// 解析左操作數（列名）if !p.curTokenIs(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Literal)}left := &ast.Identifier{Token: p.curToken,Value: p.curToken.Literal,}// 解析操作符p.nextToken()operator := p.curTokenif !p.curTokenIs(lexer.EQ) && !p.curTokenIs(lexer.GT) && !p.curTokenIs(lexer.LT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", operator.Type)}// 解析右操作數p.nextToken()right, err := p.parseExpression()if err != nil {return nil, err}stmt.Where = &ast.BinaryExpression{Token:    operator,Left:     left,Operator: operator.Literal,Right:    right,}}return stmt, nil
}// parseDeleteStatement 解析DELETE語句
// 解析表名
// 解析 WHERE 子句
func (p *Parser) parseDeleteStatement() (*ast.DeleteStatement, error) {stmt := &ast.DeleteStatement{Token: p.curToken}if !p.expectPeek(lexer.FROM) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}if !p.expectPeek(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}stmt.TableName = p.curToken.Literal// 解析WHERE子句if p.peekTokenIs(lexer.WHERE) {p.nextToken()p.nextToken()// 解析左操作數（列名）if !p.curTokenIs(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Literal)}left := &ast.Identifier{Token: p.curToken,Value: p.curToken.Literal,}// 解析操作符p.nextToken()operator := p.curTokenif !p.curTokenIs(lexer.EQ) && !p.curTokenIs(lexer.GT) && !p.curTokenIs(lexer.LT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", operator.Type)}// 解析右操作數p.nextToken()right, err := p.parseExpression()if err != nil {return nil, err}stmt.Where = &ast.BinaryExpression{Token:    operator,Left:     left,Operator: operator.Literal,Right:    right,}}return stmt, nil
}// parseDropTableStatement 解析DROP TABLE語句
func (p *Parser) parseDropTableStatement() (*ast.DropTableStatement, error) {stmt := &ast.DropTableStatement{Token: p.curToken}if !p.expectPeek(lexer.TABLE) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}if !p.expectPeek(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.peekToken.Literal)}stmt.TableName = p.curToken.Literalreturn stmt, nil
}// parseExpression 解析表達式(字面量int、string類型，標識符列名、表名等)
// 解析各種類型的表達式
// 支持字面量、標識符等
func (p *Parser) parseExpression() (ast.Expression, error) {switch p.curToken.Type {case lexer.INT_LIT:return &ast.IntegerLiteral{Token: p.curToken,Value: p.curToken.Literal,}, nilcase lexer.STRING:return &ast.StringLiteral{Token: p.curToken,Value: p.curToken.Literal,}, nilcase lexer.IDENT:return &ast.Identifier{Token: p.curToken,Value: p.curToken.Literal,}, nildefault:return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Type)}
}// curTokenIs 檢查當前token是否為指定類型
func (p *Parser) curTokenIs(t lexer.TokenType) bool {return p.curToken.Type == t
}// peekTokenIs 檢查下一個token是否為指定類型
func (p *Parser) peekTokenIs(t lexer.TokenType) bool {return p.peekToken.Type == t
}// expectPeek 檢查下一個詞法單元是否為預期類型
func (p *Parser) expectPeek(t lexer.TokenType) bool {if p.peekTokenIs(t) {p.nextToken()return true}return false
}// parseWhereClause 解析WHERE子句
func (p *Parser) parseWhereClause() (ast.Expression, error) {p.nextToken()// 解析左操作數（列名）if !p.curTokenIs(lexer.IDENT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Literal)}left := &ast.Identifier{Token: p.curToken,Value: p.curToken.Literal,}// 解析操作符p.nextToken()operator := p.curToken// 處理LIKE操作符if p.curTokenIs(lexer.LIKE) {p.nextToken()if !p.curTokenIs(lexer.STRING) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", p.curToken.Literal)}// 移除字符串字面量的引號pattern := p.curToken.Literalif len(pattern) >= 2 && (pattern[0] == '\'' || pattern[0] == '"') {pattern = pattern[1 : len(pattern)-1]}return &ast.LikeExpression{Token:   operator,Left:    left,Pattern: pattern,}, nil}// 處理其他操作符if !p.curTokenIs(lexer.EQ) && !p.curTokenIs(lexer.GT) && !p.curTokenIs(lexer.LT) {return nil, fmt.Errorf("You have an error in your SQL syntax; check the manual that corresponds to your db server version for the right syntax to use near '%s'", operator.Type)}// 解析右操作數p.nextToken()right, err := p.parseExpression()if err != nil {return nil, err}return &ast.BinaryExpression{Token:    operator,Left:     left,Operator: operator.Literal,Right:    right,}, nil
}

模塊四：存儲引擎 (Storage)

思路

存儲引擎負責實際的數據存儲和檢索操作,執行引擎中的數據操作CURD。

我們需要新建internal/storage/memory.go文件。

這是內存存儲引擎的實現，負責處理 SQL 語句的實際執行和數據存儲。

本期存儲引擎實現了：

完整的數據操作（CRUD）
主鍵約束
索引支持
類型檢查
條件評估
模式匹配

它是 SQL 數據庫系統的核心組件，負責：

數據存儲和管理
查詢執行
數據完整性維護
性能優化（通過索引）

原理解析：

-- 創建表
CREATE TABLE users (id INT PRIMARY KEY,name TEXT
);-- 插入數據
INSERT INTO users VALUES (1, 'Alice');-- 查詢數據
SELECT * FROM users WHERE name LIKE 'A%';-- 更新數據
UPDATE users SET name = 'Bob' WHERE id = 1;-- 刪除數據
DELETE FROM users WHERE id = 1;存儲引擎會根據解析后的語法分析器，創建出對應的數據結構（如：在內存中），以及對外暴露對該數據的操作（CRUD）

全部代碼

// internal/storage/memory.go
package storageimport ("fmt""regexp""strconv""strings""ziyi.db.com/internal/ast"
)// MemoryBackend 內存存儲引擎，管理所有表
type MemoryBackend struct {tables map[string]*Table
}// Table 數據表，包含列定義、數據行和索引
type Table struct {Name    stringColumns []ast.ColumnDefinitionRows    [][]ast.CellIndexes map[string]*Index // 值到行索引的映射
}// Index 索引，用于加速查詢
type Index struct {Column stringValues map[string][]int // 值到行索引的映射
}// NewMemoryBackend 創建新的內存存儲引擎
func NewMemoryBackend() *MemoryBackend {return &MemoryBackend{tables: make(map[string]*Table),}
}// CreateTable 創建表
// 驗證表名唯一性
// 創建表結構
// 為主鍵列創建索引
func (b *MemoryBackend) CreateTable(stmt *ast.CreateTableStatement) error {if _, exists := b.tables[stmt.TableName]; exists {return fmt.Errorf("Table '%s' already exists", stmt.TableName)}table := &Table{Name:    stmt.TableName,Columns: stmt.Columns,Rows:    make([][]ast.Cell, 0),Indexes: make(map[string]*Index),}// 為主鍵創建索引for _, col := range stmt.Columns {if col.Primary {table.Indexes[col.Name] = &Index{Column: col.Name,Values: make(map[string][]int),}}}b.tables[stmt.TableName] = tablereturn nil
}// Insert 插入數據
// 驗證表存在性
// 檢查數據完整性
// 處理主鍵約束
// 維護索引
func (b *MemoryBackend) Insert(stmt *ast.InsertStatement) error {table, exists := b.tables[stmt.TableName]if !exists {return fmt.Errorf("Table '%s' doesn't exist", stmt.TableName)}if len(stmt.Values) != len(table.Columns) {return fmt.Errorf("Column count doesn't match value count at row 1")}// 轉換值row := make([]ast.Cell, len(stmt.Values))for i, expr := range stmt.Values {value, err := evaluateExpression(expr)if err != nil {return err}switch v := value.(type) {case string:if table.Columns[i].Type == "INT" {// 嘗試將字符串轉換為整數intVal, err := strconv.ParseInt(v, 10, 32)if err != nil {return fmt.Errorf("Incorrect integer value: '%s' for column '%s'", v, table.Columns[i].Name)}row[i] = ast.Cell{Type: ast.CellTypeInt, IntValue: int32(intVal)}} else {row[i] = ast.Cell{Type: ast.CellTypeText, TextValue: v}}case int32:row[i] = ast.Cell{Type: ast.CellTypeInt, IntValue: v}default:return fmt.Errorf("Unsupported value type: %T for column '%s'", value, table.Columns[i].Name)}}// 檢查主鍵約束for i, col := range table.Columns {if col.Primary {key := row[i].String()if _, exists := table.Indexes[col.Name].Values[key]; exists {return fmt.Errorf("Duplicate entry '%s' for key '%s'", key, col.Name)}}}// 插入數據rowIndex := len(table.Rows)table.Rows = append(table.Rows, row)// 更新索引for i, col := range table.Columns {if col.Primary {key := row[i].String()table.Indexes[col.Name].Values[key] = append(table.Indexes[col.Name].Values[key], rowIndex)}}return nil
}// Select 查詢數據
// 支持 SELECT * 和指定列
// 處理 WHERE 條件
// 返回查詢結果
func (b *MemoryBackend) Select(stmt *ast.SelectStatement) (*ast.Results, error) {table, exists := b.tables[stmt.TableName]if !exists {return nil, fmt.Errorf("Table '%s' doesn't exist", stmt.TableName)}results := &ast.Results{Columns: make([]ast.ResultColumn, 0),Rows:    make([][]ast.Cell, 0),}// 處理選擇列表if len(stmt.Fields) == 1 && stmt.Fields[0].(*ast.StarExpression) != nil {// SELECT *for _, col := range table.Columns {results.Columns = append(results.Columns, ast.ResultColumn{Name: col.Name,Type: col.Type,})}} else {// 處理指定的列for _, expr := range stmt.Fields {switch e := expr.(type) {case *ast.Identifier:// 查找列found := falsefor _, col := range table.Columns {if col.Name == e.Value {results.Columns = append(results.Columns, ast.ResultColumn{Name: col.Name,Type: col.Type,})found = truebreak}}if !found {return nil, fmt.Errorf("Unknown column '%s' in 'field list'", e.Value)}default:return nil, fmt.Errorf("Unsupported select expression type")}}}// 處理WHERE子句for _, row := range table.Rows {if stmt.Where != nil {match, err := evaluateWhereCondition(stmt.Where, row, table.Columns)if err != nil {return nil, err}if !match {continue}}// 構建結果行resultRow := make([]ast.Cell, len(results.Columns))for j, col := range results.Columns {// 查找列在原始行中的位置for k, tableCol := range table.Columns {if tableCol.Name == col.Name {resultRow[j] = row[k]break}}}results.Rows = append(results.Rows, resultRow)}return results, nil
}// Update 執行UPDATE操作
// 驗證表和列存在性
// 處理 WHERE 條件
// 更新符合條件的行
func (mb *MemoryBackend) Update(stmt *ast.UpdateStatement) error {table, ok := mb.tables[stmt.TableName]if !ok {return fmt.Errorf("Table '%s' doesn't exist", stmt.TableName)}// 獲取列索引columnIndices := make(map[string]int)for i, col := range table.Columns {columnIndices[col.Name] = i}// 驗證所有要更新的列是否存在for _, set := range stmt.Set {if _, ok := columnIndices[set.Column]; !ok {return fmt.Errorf("Unknown column '%s' in 'field list'", set.Column)}}// 更新符合條件的行for i := range table.Rows {if stmt.Where != nil {// 評估WHERE條件result, err := evaluateWhereCondition(stmt.Where, table.Rows[i], table.Columns)if err != nil {return err}if !result {continue}}// 更新行for _, set := range stmt.Set {colIndex := columnIndices[set.Column]value, err := evaluateExpression(set.Value)if err != nil {return err}switch v := value.(type) {case int32:table.Rows[i][colIndex] = ast.Cell{Type: ast.CellTypeInt, IntValue: v}case string:table.Rows[i][colIndex] = ast.Cell{Type: ast.CellTypeText, TextValue: v}default:return fmt.Errorf("Unsupported value type: %T for column '%s'", value, set.Column)}}}return nil
}// Delete 執行DELETE操作
// 驗證表存在性
// 處理 WHERE 條件
// 刪除符合條件的行
func (mb *MemoryBackend) Delete(stmt *ast.DeleteStatement) error {table, ok := mb.tables[stmt.TableName]if !ok {return fmt.Errorf("Table '%s' doesn't exist", stmt.TableName)}// 找出要刪除的行rowsToDelete := make([]int, 0)for i := range table.Rows {if stmt.Where != nil {// 評估WHERE條件result, err := evaluateWhereCondition(stmt.Where, table.Rows[i], table.Columns)if err != nil {return err}if !result {continue}}rowsToDelete = append(rowsToDelete, i)}// 從后向前刪除行，以避免索引變化for i := len(rowsToDelete) - 1; i >= 0; i-- {rowIndex := rowsToDelete[i]table.Rows = append(table.Rows[:rowIndex], table.Rows[rowIndex+1:]...)}return nil
}// DropTable 刪除表
// 驗證表是否存在
// 從存儲引擎中刪除表
func (mb *MemoryBackend) DropTable(stmt *ast.DropTableStatement) error {if _, exists := mb.tables[stmt.TableName]; !exists {return fmt.Errorf("Unknown table '%s'", stmt.TableName)}delete(mb.tables, stmt.TableName)return nil
}// evaluateExpression 評估表達式的值
// 計算表達式的值
// 處理不同類型的數據
func evaluateExpression(expr ast.Expression) (interface{}, error) {switch e := expr.(type) {case *ast.IntegerLiteral:val, err := strconv.ParseInt(e.Value, 10, 32)if err != nil {return nil, fmt.Errorf("Incorrect integer value: '%s'", e.Value)}return int32(val), nilcase *ast.StringLiteral:return e.Value, nilcase *ast.Identifier:return nil, fmt.Errorf("Cannot evaluate identifier: '%s'", e.Value)default:return nil, fmt.Errorf("Unknown expression type: %T", expr)}
}// matchLikePattern 檢查字符串是否匹配LIKE模式
func matchLikePattern(str, pattern string) bool {// 將SQL LIKE模式轉換為正則表達式regexPattern := "^"for i := 0; i < len(pattern); i++ {switch pattern[i] {case '%':regexPattern += ".*"case '_':regexPattern += "."case '\\':if i+1 < len(pattern) {regexPattern += "\\" + string(pattern[i+1])i++}default:// 轉義正則表達式特殊字符if strings.ContainsAny(string(pattern[i]), ".+*?^$()[]{}|") {regexPattern += "\\" + string(pattern[i])} else {regexPattern += string(pattern[i])}}}regexPattern += "$"// 編譯正則表達式re, err := regexp.Compile(regexPattern)if err != nil {return false}// 執行匹配return re.MatchString(str)
}// evaluateWhereCondition 評估WHERE條件
// 評估 WHERE 條件
// 支持比較運算符和 LIKE 操作符
func evaluateWhereCondition(expr ast.Expression, row []ast.Cell, columns []ast.ColumnDefinition) (bool, error) {switch e := expr.(type) {case *ast.BinaryExpression:// 獲取左操作數的值leftValue, err := getColumnValue(e.Left, row, columns)if err != nil {return false, err}// 獲取右操作數的值rightValue, err := getColumnValue(e.Right, row, columns)if err != nil {return false, err}// 根據操作符比較值switch e.Operator {case "=":return compareValues(leftValue, rightValue, "=")case ">":return compareValues(leftValue, rightValue, ">")case "<":return compareValues(leftValue, rightValue, "<")default:return false, fmt.Errorf("Unknown operator: '%s'", e.Operator)}case *ast.LikeExpression:// 獲取左操作數的值leftValue, err := getColumnValue(e.Left, row, columns)if err != nil {return false, err}// 確保左操作數是字符串類型strValue, ok := leftValue.(string)if !ok {return false, fmt.Errorf("LIKE operator requires string operand")}// 執行LIKE匹配return matchLikePattern(strValue, e.Pattern), nildefault:return false, fmt.Errorf("Unknown expression type: %T", expr)}
}// compareValues 比較兩個值
func compareValues(left, right interface{}, operator string) (bool, error) {switch l := left.(type) {case int32:if r, ok := right.(int32); ok {switch operator {case "=":return l == r, nilcase ">":return l > r, nilcase "<":return l < r, nil}}case string:if r, ok := right.(string); ok {switch operator {case "=":return l == r, nilcase ">":return l > r, nilcase "<":return l < r, nil}}}return false, fmt.Errorf("Cannot compare values of different types: %T and %T", left, right)
}// getColumnValue 獲取列的值
func getColumnValue(expr ast.Expression, row []ast.Cell, columns []ast.ColumnDefinition) (interface{}, error) {switch e := expr.(type) {case *ast.Identifier:// 查找列索引for i, col := range columns {if col.Name == e.Value {switch row[i].Type {case ast.CellTypeInt:return row[i].IntValue, nilcase ast.CellTypeText:return row[i].TextValue, nildefault:return nil, fmt.Errorf("Unknown cell type: %v", row[i].Type)}}}return nil, fmt.Errorf("Unknown column '%s' in 'where clause'", e.Value)case *ast.IntegerLiteral:val, err := strconv.ParseInt(e.Value, 10, 32)if err != nil {return nil, fmt.Errorf("Incorrect integer value: '%s'", e.Value)}return int32(val), nilcase *ast.StringLiteral:return e.Value, nildefault:return nil, fmt.Errorf("Unknown expression type: %T", expr)}
}//后續拓展新的存儲引擎，如落地到文件...

模塊五：REPL 交互界面

思路

最后，我們需要實現一個交互式的命令行界面，讓用戶可以輸入 SQL 命令并查看結果。
這是 ZiyiDB 的主程序，實現了一個交互式的 SQL 命令行界面。

為了實現客戶端可以上下翻找之前執行的命令以及cli客戶端的美觀，我們這里使用"github.com/c-bata/go-prompt"庫

// 安裝依賴
go get "github.com/c-bata/go-prompt"

我們需要新建cmd/main.go文件。

主要實現：

交互式命令行界面
SQL 命令解析和執行
命令歷史記錄
查詢結果格式化
錯誤處理和提示

全部代碼

package mainimport ("fmt""github.com/c-bata/go-prompt""os""strings""ziyi.db.com/internal/ast""ziyi.db.com/internal/lexer""ziyi.db.com/internal/parser""ziyi.db.com/internal/storage"
)var history []string               // 存儲命令歷史
var backend *storage.MemoryBackend // 存儲引擎實例
var historyIndex int               // 當前歷史記錄索引// 處理用戶輸入的命令
func executor(t string) {t = strings.TrimSpace(t)if t == "" {return}// 添加到歷史記錄history = append(history, t)historyIndex = len(history) // 重置歷史記錄索引// 處理退出命令if strings.ToLower(t) == "exit" {fmt.Println("Bye!")os.Exit(0)}// 創建詞法分析器l := lexer.NewLexer(strings.NewReader(t))// 創建語法分析器p := parser.NewParser(l)// 解析SQL語句stmt, err := p.ParseProgram()if err != nil {fmt.Printf("Parse error: %v\n", err)return}// 執行SQL語句for _, statement := range stmt.Statements {switch s := statement.(type) {case *ast.CreateTableStatement:if err := backend.CreateTable(s); err != nil {fmt.Printf("Error: %v\n", err)} else {fmt.Println("Table created successfully")}case *ast.InsertStatement:if err := backend.Insert(s); err != nil {fmt.Printf("Error: %v\n", err)} else {fmt.Println("1 row inserted")}case *ast.SelectStatement:results, err := backend.Select(s)if err != nil {fmt.Printf("Error: %v\n", err)} else {// 計算每列的最大寬度colWidths := make([]int, len(results.Columns))for i, col := range results.Columns {colWidths[i] = len(col.Name)}for _, row := range results.Rows {for i, cell := range row {cellLen := len(cell.String())if cellLen > colWidths[i] {colWidths[i] = cellLen}}}// 打印表頭fmt.Print("+")for _, width := range colWidths {fmt.Print(strings.Repeat("-", width+2))fmt.Print("+")}fmt.Println()// 打印列名fmt.Print("|")for i, col := range results.Columns {fmt.Printf(" %-*s |", colWidths[i], col.Name)}fmt.Println()// 打印分隔線fmt.Print("+")for _, width := range colWidths {fmt.Print(strings.Repeat("-", width+2))fmt.Print("+")}fmt.Println()// 打印數據行for _, row := range results.Rows {fmt.Print("|")for i, cell := range row {fmt.Printf(" %-*s |", colWidths[i], cell.String())}fmt.Println()}// 打印底部邊框fmt.Print("+")for _, width := range colWidths {fmt.Print(strings.Repeat("-", width+2))fmt.Print("+")}fmt.Println()// 打印行數統計fmt.Printf("%d rows in set\n", len(results.Rows))}case *ast.UpdateStatement:if err := backend.Update(s); err != nil {fmt.Printf("Error: %v\n", err)} else {fmt.Println("Query OK, 1 row affected")}case *ast.DeleteStatement:if err := backend.Delete(s); err != nil {fmt.Printf("Error: %v\n", err)} else {fmt.Println("Query OK, 1 row affected")}case *ast.DropTableStatement:if err := backend.DropTable(s); err != nil {fmt.Printf("Error: %v\n", err)} else {fmt.Println("Table dropped successfully")}default:fmt.Printf("Unsupported statement type: %T\n", s)}}
}// 提供命令補全功能
func completer(d prompt.Document) []prompt.Suggest {s := []prompt.Suggest{}return prompt.FilterHasPrefix(s, d.GetWordBeforeCursor(), true)
}func main() {// 初始化存儲引擎backend = storage.NewMemoryBackend()historyIndex = 0fmt.Println("Welcome to ZiyiDB!")fmt.Println("Type your SQL commands (type 'exit' to quit)")p := prompt.New(executor,completer,prompt.OptionTitle("ZiyiDB: A Simple SQL Database"),prompt.OptionPrefix("ziyidb> "),prompt.OptionHistory(history),prompt.OptionLivePrefix(func() (string, bool) {return "ziyidb> ", true}),//實現方向鍵上下翻閱歷史命令// 上鍵綁定prompt.OptionAddKeyBind(prompt.KeyBind{Key: prompt.Up,Fn: func(buf *prompt.Buffer) {if historyIndex > 0 {historyIndex--buf.DeleteBeforeCursor(len(buf.Text()))buf.InsertText(history[historyIndex], false, true)}},}),// 下鍵綁定prompt.OptionAddKeyBind(prompt.KeyBind{Key: prompt.Down,Fn: func(buf *prompt.Buffer) {if historyIndex < len(history)-1 {historyIndex++buf.DeleteBeforeCursor(len(buf.Text()))buf.InsertText(history[historyIndex], false, true)} else if historyIndex == len(history)-1 {historyIndex++buf.DeleteBeforeCursor(len(buf.Text()))}},}),)p.Run()
}

整體測試

編寫完第一版后，現在我們來整體測試一下。

測試腳本

test_cast.sql：

-- 1. 創建表
CREATE TABLE users (id INT PRIMARY KEY,name TEXT ,age INT);-- 2. 插入用戶數據
INSERT INTO users VALUES (1, 'Alice', 20);
INSERT INTO users VALUES (2, 'Bob', 25);
INSERT INTO users VALUES (3, 'Charlie', 30);
INSERT INTO users VALUES (4, 'David', 35);
INSERT INTO users VALUES (5, 'Eve', 40);-- 3. 測試主鍵沖突
INSERT INTO users VALUES (1, 'Tomas', 21);-- 4. 基本查詢測試
-- 4.1 查詢所有數據
SELECT * FROM users;-- 4.2 查詢特定列
SELECT id, name FROM users;-- 5. WHERE 子句測試
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age < 30;-- 6. LIKE 操作符測試
-- 6.1 基本模式匹配
SELECT * FROM users WHERE name LIKE 'A%';  -- 以 A 開頭
SELECT * FROM users WHERE name LIKE '%e';  -- 以 e 結尾-- 6.2 轉義字符測試
INSERT INTO users VALUES (6, 'Bob%Smith', 45);
SELECT * FROM users WHERE name LIKE 'Bob\%Smith';-- 7. 更新操作測試
-- 7.1 更新單個字段
UPDATE users SET age = 21 WHERE name = 'Alice';-- 7.2 更新多個字段
UPDATE users SET name = 'Robert', age = 8 WHERE id = 2;-- 8. 刪除操作測試
DELETE FROM users WHERE age > 30;-- 9. 清理測試數據
DROP TABLE users;-- 10. 驗證表已刪除
SELECT * FROM users;    -- 應該失敗todo::1. 實現!= >= <=等運算符2. 支持更多數據類型3. 支持更多函數4. 優化查詢結果展示5. 支持更多索引類型6. 支持null值等7. 支持數據落地本地文件8. 支持事務操作等