数据库操作

本页面介绍Magic-API中的数据库操作方法。

数据源说明

系统预配置的数据源：

数据源	说明	使用方式
master	主数据库	db.xxx()
slave	从数据库	db.slave.xxx()

查询操作

查询列表

// 查询所有
var users = db.select("SELECT * FROM eb_user");

// 带条件查询
var activeUsers = db.select("SELECT * FROM eb_user WHERE status = 1");

// 带参数查询（推荐，防SQL注入）
var user = db.select("SELECT * FROM eb_user WHERE id = ?", 123);

// 多参数
var orders = db.select(
    "SELECT * FROM eb_store_order WHERE mer_id = ? AND status = ?",
    1,
    2
);

查询单条

// 查询单条记录
var user = db.selectOne("SELECT * FROM eb_user WHERE id = ?", 123);

if (!user) {
    exit 404, "用户不存在";
}

return user;

查询单值

// 查询数量
var count = db.selectInt("SELECT COUNT(*) FROM eb_user WHERE status = 1");

// 查询字符串
var name = db.selectString("SELECT nickname FROM eb_user WHERE id = ?", 123);

// 查询单个值
var value = db.selectValue("SELECT balance FROM eb_user WHERE id = ?", 123);

分页查询

var page = param.page || 1;
var limit = param.limit || 10;

// 使用page方法
var pageResult = db.page("""
    SELECT * FROM eb_user WHERE status = 1 ORDER BY id DESC
""", page, limit);

return {
    list: pageResult.list,
    total: pageResult.total,
    page: page,
    limit: limit,
    totalPage: Math.ceil(pageResult.total / limit)
};

新增操作

单条插入

// 方式1：使用insert
var id = db.insert("""
    INSERT INTO custom_table (name, value, create_time)
    VALUES (?, ?, NOW())
""", body.name, body.value);

return { id: id };

// 方式2：使用对象插入
var data = {
    name: body.name,
    value: body.value,
    create_time: new Date()
};

var id = db.table('custom_table').insert(data);

return { id: id };

批量插入

var items = body.items;

var values = items.map(item => [item.name, item.value, new Date()]);

var count = db.batchInsert("""
    INSERT INTO custom_table (name, value, create_time) VALUES (?, ?, ?)
""", values);

return { insertCount: count };

更新操作

单条更新

// 方式1：使用update
var affected = db.update("""
    UPDATE eb_user SET nickname = ?, update_time = NOW() WHERE id = ?
""", body.nickname, body.id);

if (affected == 0) {
    exit 404, "用户不存在";
}

return { success: true };

// 方式2：使用对象更新
var affected = db.table('eb_user')
    .where('id', body.id)
    .update({
        nickname: body.nickname,
        update_time: new Date()
    });

条件更新

// 批量更新状态
var affected = db.update("""
    UPDATE eb_store_order
    SET status = 2, update_time = NOW()
    WHERE status = 1 AND create_time < DATE_SUB(NOW(), INTERVAL 30 MINUTE)
""");

return { updatedCount: affected };

删除操作

物理删除

// 使用delete
var affected = db.delete("DELETE FROM custom_table WHERE id = ?", body.id);

return { deleted: affected > 0 };

逻辑删除

// 推荐使用逻辑删除
var affected = db.update("""
    UPDATE eb_user SET is_del = 1, update_time = NOW() WHERE id = ?
""", body.id);

return { success: affected > 0 };

事务处理

手动事务

// 开启事务
db.beginTransaction();

try {
    // 扣减库存
    var stockResult = db.update("""
        UPDATE eb_product SET stock = stock - ? WHERE id = ? AND stock >= ?
    """, body.num, body.productId, body.num);

    if (stockResult == 0) {
        throw new Error("库存不足");
    }

    // 创建订单
    var orderId = db.insert("""
        INSERT INTO custom_order (product_id, num, create_time) VALUES (?, ?, NOW())
    """, body.productId, body.num);

    // 提交事务
    db.commit();

    return { orderId: orderId };

} catch (e) {
    // 回滚事务
    db.rollback();
    log.error("下单失败: {}", e.message);
    exit 500, e.message;
}

动态SQL

条件拼接

var keyword = param.keyword;
var status = param.status;
var startDate = param.startDate;
var endDate = param.endDate;

var sql = "SELECT * FROM eb_user WHERE is_del = 0";
var params = [];

if (keyword) {
    sql += " AND (nickname LIKE ? OR phone LIKE ?)";
    params.push('%' + keyword + '%', '%' + keyword + '%');
}

if (status != null) {
    sql += " AND status = ?";
    params.push(status);
}

if (startDate) {
    sql += " AND create_time >= ?";
    params.push(startDate + ' 00:00:00');
}

if (endDate) {
    sql += " AND create_time <= ?";
    params.push(endDate + ' 23:59:59');
}

sql += " ORDER BY id DESC";

return db.select(sql, ...params);

模板语法

var sql = """
    SELECT * FROM eb_user
    WHERE is_del = 0
    ?{keyword, AND (nickname LIKE concat('%', #{keyword}, '%') OR phone LIKE concat('%', #{keyword}, '%'))}
    ?{status != null, AND status = #{status}}
    ?{startDate, AND create_time >= #{startDate}}
    ?{endDate, AND create_time <= #{endDate}}
    ORDER BY id DESC
""";

return db.select(sql);

常用查询示例

统计查询

// 订单统计
var stats = db.selectOne("""
    SELECT
        COUNT(*) as total_orders,
        SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as pending_orders,
        SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as paid_orders,
        SUM(pay_price) as total_amount
    FROM eb_store_order
    WHERE is_del = 0
""");

return stats;

关联查询

// 订单带商品信息
var orders = db.select("""
    SELECT
        o.id,
        o.order_id as order_no,
        o.pay_price,
        o.status,
        u.nickname as user_name,
        m.name as merchant_name
    FROM eb_store_order o
    LEFT JOIN eb_user u ON o.uid = u.id
    LEFT JOIN eb_merchant m ON o.mer_id = m.id
    WHERE o.is_del = 0
    ORDER BY o.id DESC
    LIMIT 20
""");

return orders;

分组查询

// 商户销售排行
var ranking = db.select("""
    SELECT
        m.id,
        m.name,
        COUNT(o.id) as order_count,
        COALESCE(SUM(o.pay_price), 0) as total_sales
    FROM eb_merchant m
    LEFT JOIN eb_store_order o ON m.id = o.mer_id AND o.paid = 1 AND o.is_del = 0
    WHERE m.is_del = 0
    GROUP BY m.id
    ORDER BY total_sales DESC
    LIMIT 10
""");

return ranking;

下一步

• 内置函数 - 常用内置函数
• 调试技巧 - 调试方法
• 接口开发 - 接口开发指南