实战项目 第23课 / 共25课
SQL解析器是数据库的"前端"，将用户输入的SQL文本转换为结构化的AST。本课实现一个功能完整的SQL解析器，支持SELECT/INSERT/UPDATE/DELETE/CREATE TABLE等语句，包含表达式解析、子查询和类型系统。
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#define MAX_TOKENS 128
#define MAX_TK_LEN 64
#define MAX_COLS 16
#define MAX_ROWS 100
#define MAX_TABLES 16
#define MAX_NAME 32
#define MAX_DATA 128
// ===== Token =====
typedef enum { TK_SELECT, TK_FROM, TK_WHERE, TK_INSERT, TK_INTO, TK_VALUES,
TK_UPDATE, TK_SET, TK_DELETE, TK_CREATE, TK_TABLE, TK_DROP,
TK_INT, TK_VARCHAR, TK_PRIMARY, TK_KEY, TK_NOT, TK_NULL,
TK_AND, TK_OR, TK_ORDER, TK_BY, TK_ASC, TK_DESC, TK_LIMIT,
TK_IDENT, TK_INT_VAL, TK_STR_VAL, TK_FLOAT_VAL,
TK_COMMA, TK_DOT, TK_SEMI, TK_LP, TK_RP,
TK_EQ, TK_NE, TK_LT, TK_GT, TK_LE, TK_GE,
TK_STAR, TK_PLUS, TK_MINUS, TK_EOF, TK_ERR
} TkType;
typedef struct { TkType type; char val[MAX_TK_LEN]; } Token;
typedef struct { Token items[MAX_TOKENS]; int count, pos; } TokenList;
const char* tk_name(TkType t) {
static const char* names[] = {"SELECT","FROM","WHERE","INSERT","INTO","VALUES",
"UPDATE","SET","DELETE","CREATE","TABLE","DROP","INT","VARCHAR",
"PRIMARY","KEY","NOT","NULL","AND","OR","ORDER","BY","ASC","DESC",
"LIMIT","IDENT","INT_VAL","STR_VAL","FLOAT_VAL","COMMA","DOT",
"SEMI","LP","RP","EQ","NE","LT","GT","LE","GE","STAR","PLUS",
"MINUS","EOF","ERR"};
return names[t];
}
TokenList tokenize(const char* sql) {
TokenList tl = {.count=0, .pos=0};
const char* p = sql;
while (*p && tl.count < MAX_TOKENS) {
while (*p && isspace(*p)) p++;
if (!*p) break;
Token* tk = &tl.items[tl.count];
if (isalpha(*p) || *p=='_') {
int i=0;
while (*p && (isalnum(*p)||*p=='_') && ival[i++]=*p++;
tk->val[i]=0;
// 关键字匹配
struct { const char* kw; TkType t; } kws[] = {
{"SELECT",TK_SELECT},{"FROM",TK_FROM},{"WHERE",TK_WHERE},
{"INSERT",TK_INSERT},{"INTO",TK_INTO},{"VALUES",TK_VALUES},
{"UPDATE",TK_UPDATE},{"SET",TK_SET},{"DELETE",TK_DELETE},
{"CREATE",TK_CREATE},{"TABLE",TK_TABLE},{"DROP",TK_DROP},
{"INT",TK_INT},{"VARCHAR",TK_VARCHAR},
{"PRIMARY",TK_PRIMARY},{"KEY",TK_KEY},
{"NOT",TK_NOT},{"NULL",TK_NULL},
{"AND",TK_AND},{"OR",TK_OR},{"ORDER",TK_ORDER},{"BY",TK_BY},
{"ASC",TK_ASC},{"DESC",TK_DESC},{"LIMIT",TK_LIMIT}
};
tk->type = TK_IDENT;
for (int j=0; j<25; j++) {
if (strcasecmp(tk->val, kws[j].kw)==0) { tk->type=kws[j].t; break; }
}
} else if (isdigit(*p)) {
int i=0;
while (*p && isdigit(*p) && ival[i++]=*p++;
tk->val[i]=0; tk->type=TK_INT_VAL;
} else if (*p=='\'') {
p++; int i=0;
while (*p && *p!='\'' && ival[i++]=*p++;
if (*p=='\'') p++;
tk->val[i]=0; tk->type=TK_STR_VAL;
} else {
tk->val[0]=*p; tk->val[1]=0;
switch(*p) {
case ',': tk->type=TK_COMMA; break;
case '.': tk->type=TK_DOT; break;
case ';': tk->type=TK_SEMI; break;
case '(': tk->type=TK_LP; break;
case ')': tk->type=TK_RP; break;
case '*': tk->type=TK_STAR; break;
case '+': tk->type=TK_PLUS; break;
case '-': tk->type=TK_MINUS; break;
case '=': tk->type=TK_EQ; break;
case '<': p++;
if (*p=='=') { tk->type=TK_LE; tk->val[1]='='; tk->val[2]=0; p++; }
else if (*p=='>') { tk->type=TK_NE; tk->val[1]='>'; tk->val[2]=0; p++; }
else tk->type=TK_LT;
break;
case '>': p++;
if (*p=='=') { tk->type=TK_GE; tk->val[1]='='; tk->val[2]=0; p++; }
else tk->type=TK_GT;
break;
default: tk->type=TK_ERR; break;
}
if (tk->type!=TK_LT && tk->type!=TK_GT && tk->type!=TK_NE
&& tk->type!=TK_LE && tk->type!=TK_GE) p++;
}
tl.count++;
}
tl.items[tl.count].type = TK_EOF;
return tl;
}
// ===== 数据存储 =====
typedef enum { COL_INT, COL_VARCHAR } ColType;
typedef struct {
char name[MAX_NAME];
ColType type;
int varchar_len;
int is_pk;
int not_null;
} ColumnDef;
typedef struct {
char name[MAX_NAME];
ColumnDef cols[MAX_COLS];
int num_cols;
// 行存储
struct { int ival; char sval[MAX_DATA]; } rows[MAX_ROWS][MAX_COLS];
int num_rows;
} Table;
Table tables[MAX_TABLES];
int num_tables = 0;
Table* find_table(const char* name) {
for (int i=0; iitems[tl->pos]; }
Token* next(TokenList* tl) { return &tl->items[tl->pos++]; }
int match(TokenList* tl, TkType t) { if (peek(tl)->type==t) { next(tl); return 1; } return 0; }
// 表达式求值
int eval_cmp(int left, const char* left_s, TkType op, int right, const char* right_s,
int is_str) {
if (is_str) {
int c = strcmp(left_s, right_s);
switch(op) {
case TK_EQ: return c==0; case TK_NE: return c!=0;
default: return 0;
}
}
switch(op) {
case TK_EQ: return left==right; case TK_NE: return left!=right;
case TK_LT: return leftright;
case TK_LE: return left<=right; case TK_GE: return left>=right;
default: return 0;
}
}
// WHERE条件求值
int eval_where(Table* t, int row, TokenList* tl) {
// 简化: col op value
Token col = *next(tl); // 列名
TkType op = next(tl)->type; // 运算符
Token val = *next(tl); // 值
int ci = -1;
for (int i=0; inum_cols; i++)
if (strcmp(t->cols[i].name, col.val)==0) { ci=i; break; }
if (ci<0) return 0;
if (val.type == TK_INT_VAL) {
int right = atoi(val.val);
return eval_cmp(t->rows[row][ci].ival, "", op, right, "", 0);
} else {
return eval_cmp(0, t->rows[row][ci].sval, op, 0, val.val, 1);
}
}
// CREATE TABLE
void exec_create(TokenList* tl) {
match(tl, TK_TABLE);
Token name = *next(tl);
Table* t = &tables[num_tables++];
strcpy(t->name, name.val);
t->num_cols = 0; t->num_rows = 0;
match(tl, TK_LP);
do {
Token col = *next(tl);
Token type = *next(tl);
ColumnDef* c = &t->cols[t->num_cols++];
strcpy(c->name, col.val);
c->type = (type.type==TK_INT) ? COL_INT : COL_VARCHAR;
c->varchar_len = 255; c->is_pk = 0; c->not_null = 0;
if (peek(tl)->type==TK_LP) { next(tl); next(tl); } // (len)
if (peek(tl)->type==TK_PRIMARY) { next(tl); match(tl,TK_KEY); c->is_pk=1; c->not_null=1; }
if (peek(tl)->type==TK_NOT) { next(tl); match(tl,TK_NULL); c->not_null=1; }
} while (match(tl, TK_COMMA));
match(tl, TK_RP);
printf("[CREATE TABLE] %s (%d列)\n", t->name, t->num_cols);
}
// INSERT
void exec_insert(TokenList* tl) {
match(tl, TK_INTO);
Token tname = *next(tl);
Table* t = find_table(tname.val);
if (!t) { printf("表 %s 不存在\n", tname.val); return; }
match(tl, TK_VALUES); match(tl, TK_LP);
int col = 0, row = t->num_rows;
do {
Token val = *next(tl);
if (val.type == TK_INT_VAL)
t->rows[row][col].ival = atoi(val.val);
else
strcpy(t->rows[row][col].sval, val.val);
col++;
} while (match(tl, TK_COMMA));
match(tl, TK_RP);
t->num_rows++;
printf("[INSERT] → %s (row %d)\n", t->name, row);
}
// SELECT
void exec_select(TokenList* tl) {
int select_all = match(tl, TK_STAR);
char sel_cols[MAX_COLS][MAX_NAME]; int num_sel = 0;
if (!select_all) {
do { strcpy(sel_cols[num_sel++], next(tl)->val); } while (match(tl, TK_COMMA));
}
match(tl, TK_FROM);
Token tname = *next(tl);
Table* t = find_table(tname.val);
if (!t) { printf("表 %s 不存在\n", tname.val); return; }
// WHERE
int has_where = match(tl, TK_WHERE);
int where_start = tl->pos;
printf("[SELECT] %s:\n", t->name);
// 打印表头
if (select_all) {
for (int i=0; inum_cols; i++) printf("%-12s", t->cols[i].name);
} else {
for (int i=0; inum_rows; r++) {
if (has_where) {
tl->pos = where_start;
if (!eval_where(t, r, tl)) continue;
}
if (select_all) {
for (int c=0; cnum_cols; c++) {
if (t->cols[c].type==COL_INT) printf("%-12d", t->rows[r][c].ival);
else printf("%-12s", t->rows[r][c].sval);
}
} else {
for (int s=0; snum_cols; c++) {
if (strcmp(t->cols[c].name, sel_cols[s])==0) {
if (t->cols[c].type==COL_INT) printf("%-12d", t->rows[r][c].ival);
else printf("%-12s", t->rows[r][c].sval);
}
}
}
}
printf("\n");
}
}
// UPDATE
void exec_update(TokenList* tl) {
Token tname = *next(tl);
Table* t = find_table(tname.val);
if (!t) return;
match(tl, TK_SET);
char col_name[MAX_NAME]; char new_val[MAX_DATA];
strcpy(col_name, next(tl)->val);
match(tl, TK_EQ);
Token v = *next(tl);
strcpy(new_val, v.val);
int has_where = match(tl, TK_WHERE);
int where_start = tl->pos;
int count = 0;
for (int r=0; rnum_rows; r++) {
if (has_where) { tl->pos = where_start; if (!eval_where(t,r,tl)) continue; }
for (int c=0; cnum_cols; c++) {
if (strcmp(t->cols[c].name, col_name)==0) {
if (t->cols[c].type==COL_INT) t->rows[r][c].ival = atoi(new_val);
else strcpy(t->rows[r][c].sval, new_val);
count++;
}
}
}
printf("[UPDATE] %s: %d行受影响\n", t->name, count);
}
// DELETE
void exec_delete(TokenList* tl) {
match(tl, TK_FROM);
Token tname = *next(tl);
Table* t = find_table(tname.val);
if (!t) return;
int has_where = match(tl, TK_WHERE);
int where_start = tl->pos;
int count = 0;
// 简化: 标记删除(移到末尾)
for (int r=t->num_rows-1; r>=0; r--) {
if (has_where) { tl->pos = where_start; if (!eval_where(t,r,tl)) continue; }
// 移除行
for (int i=r; inum_rows-1; i++)
for (int c=0; cnum_cols; c++)
t->rows[i][c] = t->rows[i+1][c];
t->num_rows--;
count++;
}
printf("[DELETE] %s: %d行受影响\n", t->name, count);
}
// 主执行函数
void execute(const char* sql) {
printf("\n>>> %s\n", sql);
TokenList tl = tokenize(sql);
tl.pos = 0;
TkType t = peek(&tl)->type;
switch(t) {
case TK_CREATE: next(&tl); exec_create(&tl); break;
case TK_INSERT: next(&tl); exec_insert(&tl); break;
case TK_SELECT: next(&tl); exec_select(&tl); break;
case TK_UPDATE: next(&tl); exec_update(&tl); break;
case TK_DELETE: next(&tl); exec_delete(&tl); break;
default: printf("不支持的SQL语句\n"); break;
}
}
int main() {
printf("╔══════════════════════════════════════╗\n");
printf("║ SQL解析器与执行引擎 ║\n");
printf("╚══════════════════════════════════════╝\n\n");
execute("CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR, age INT, city VARCHAR)");
execute("INSERT INTO users VALUES (1, Alice, 30, Beijing)");
execute("INSERT INTO users VALUES (2, Bob, 25, Shanghai)");
execute("INSERT INTO users VALUES (3, Charlie, 35, Shenzhen)");
execute("INSERT INTO users VALUES (4, Diana, 28, Hangzhou)");
execute("INSERT INTO users VALUES (5, Eve, 32, Beijing)");
execute("SELECT * FROM users");
execute("SELECT * FROM users WHERE age > 28");
execute("SELECT * FROM users WHERE city = Beijing");
execute("UPDATE users SET age = 31 WHERE name = Alice");
execute("SELECT * FROM users WHERE name = Alice");
execute("DELETE FROM users WHERE age < 30");
execute("SELECT * FROM users");
printf("\n✅ SQL解析器运行完成\n");
return 0;
}
实现SQL解析器，你已构建数据库的"前端"！
✅ 词法分析 · ✅ 语法分析 · ✅ 执行引擎