进阶-数据库

Z1h内嵌了数据库操作, 最主要的包括mysqlsqlite3

如需支持Postgres / Tidb / MsSql / Oracle, 请联系作者

开启

参考运行配置, 并配置好db信息, 即可连接数据库

...
    {
        "db": {
            "type": "mysql", // 数据库类型
            "address": "" // 数据库连接地址,sqlite3时可写文件名
        }
    }
...

有两种方式可以执行数据库操作: SQL 方式和 ORM方式

SQL操作

通过$db可以操作SQL

// 建表
assert($db(`CREATE TABLE test_db(name VARCHAR)`))

// 插入
assert($db(`INSERT INTO test_db(name) VALUES("aaa")`))
// 插入时用占位符
assert($db(`INSERT INTO test_db(name) VALUES(?)`, "bbb"))

// 删除
assert($db(`DELETE FROM test_db WHERE name = ?`, "aaa"))

// 查询
assert($db(`SELECT * FROM test_db WHERE name != ?`, "aaa"))

// 修改
assert($db(`UPDATE test_db SET name = ? WHERE name != ?`, "aaa", "aaa"))

// ...
// 更多操作请自学SQL

ORM

对象关系映射(Object Relational Mapping, 简称ORM)是通过使用描述对象和数据库之间映射的元数据, 将面向对象语言程序中的对象自动持久化到关系数据库中

$db对象有以下函数:

  • 建表结构: sync
  • 插入数据: insert
  • 查询相关: find, count, rows, select
  • 更新数据: update
  • 删除数据: delete
  • 直接执行: exec, query
  • 事务相关: begin, commit, rollback,
  • 语句支持: expr, where

以下示例代码的运行环境是递进的, 请一步步跟着示例代码来测试(尤其是建表和插入数据):

sync - 创建表

参数为表名、结构

// 简单模式
assert($db.sync('test', {
    name: '',
    age: 0,
}))

// 复杂模式
assert($db.sync('test', {
    name: `VARCHAR(32) NOT NULL DEFAULT ''`,
    age: `INT(11) NOT NULL DEFAULT 18`,
}))

insert - 插入数据

参数为表名、数据(可多条数据)

// 插入两行数据, 返回的是插入成功的行数
assert($db.insert('test', {
    name: 'zwr',
    age: 22,
}, {
    name: 'zq',
    age: 21,
}))

// 插入一行数据时, 返回的是id
assert($db.insert('test', {
    name: 'xxx',
    age: 10,
}))

查询相关

find

find函数传入参数为表名、过滤条件、其它参数, 返回的数据格式为(datas []map[string]string, error)

assert($db.find('test')) // 查询全表数据
assert($db.find('test', 1)) // 指定id查询
assert($db.find('test', [1, 2])) // 指定多个id查询

assert($db.find('test', {age: 22})) // 匹配条件
assert($db.find('test', {age: {$ne: 22}})) // 比较条件(不等于22)
assert($db.find('test', {age: {$lt: 22}})) // 比较条件(小于22)
assert($db.find('test', {name: $db.expr(`LIKE 'z%'`)})) // 复杂条件

// 第三个参数的用法
assert($db.find('test', {}, {
    desc: 'age', // 按照字段从大到小排列(asc为从小到大)
    offset: 1, // 跳过几行数据
    limit: 2, // 查询返回几行数据
    keys: ['name', 'age'], // 只要某些字段,
}))

count

以上的find示例(除指定单个/多个id外)均可替换成count, 返回值为吻合条件的行数, 例如

assert($db.count('test')) // 统计全表数据行数
assert($db.count('test', {age: {$ne: 22}})) // 统计年龄不为22的数据数
assert($db.count('test', {age: {$lt: 22}})) // 统计年龄不足22的数据数

select

find函数是一次性将数据完整请求下来, 但如果符合条件的数据量过大并且没有设置合理的limit, 一次性请求下来容易造成内存爆满(OOM), 此时建议使用select函数

select函数的最后一个参数为一个处理函数, 用于处理逐行扫描的结果(其它参数与find相同)

第三个参数可以指定reuse/empty/result(均为true/false), 分别代表: 是否重用一个指针、是否需要NULL字段、是否需要返回全部处理结果(类似array.map方法)

// 查全表, 逐行处理
$db.select(`test`, e=>{
    print(e)
})

// 带条件或排序等其它限制
$db.select(`test`, {
    age: {$gt: 2}, // 年龄 > 2
}, {
    keys: ['name'], // 只要name字段
    asc: 'age', // 按年龄升序
    limit: 2, // 只要2条
    reuse: true, // 复用指针
    empty: false, // 空字段是否也要
    result: true, // 是否需要返回值
}, ({name}, index) => { // 展开对象
    print(index, name)
})

update - 更新数据

传入参数为表名、过滤条件(可为id或id列表)、更新内容

// 返回更新成功的行数
assert($db.update('test', 3, {
    name: 'aaaaa',
}))

// 根据条件
print(`修改前:`, assert($db.find(`test`, 3))[0].age)
assert($db.update('test', {
    name: 'aaaaa',    
}, {
    age: $db.expr(` = age - 3`),
}))
print(`修改后:`, assert($db.find(`test`, 3))[0].age)

delete - 删除数据

传入参数为表名、过滤条件(也可以是id)

// 删除id=3的数据
assert($db.delete(`test`, 3))

// 删除符合筛选条件的数据
assert($db.delete(`test`, {
    age: 88,
}))

执行SQL

Exec 方法直接执行一条SQL

Query 方法进行一次SQL查询

assert($db.Exec(`alter table test add column tall INT(11) NOT NULL DEFAULT 0`))

assert($db.Query(`SELECT COUNT(*) FROM test`))

begin、commit、rollback

通过begin函数开启一个事务, 返回一个和$db有相同函数的对象, 最后通过调用commit来提交修改或调用rollback回滚所有操作

示例代码:

// 场景: 修改zwr的age为100, 当zq的age为22时commit, 否则回滚

// 创建事务
var tx = assert($db.begin())
// 默认进行回滚(如果commit成功, 回滚会被无视)
defer tx.rollback()
// 尝试第一次
assert(tx.update('test', 1, {
    age: 100
}))
// 看一下修改情况
print(
    `事务内数据: ${assert(tx.find('test', 1))}
事务外数据: ${assert($db.find('test', 1))}`,
)
if (int(assert(tx.find('test', 2))[0].age) == 22) {
    assert(tx.commit())
    print(`查询到id=2的年龄吻合, 提交修改成功`)
    print(`事务外数据: ${assert($db.find('test', 1))}`)
} else {
    print(`查询到id=2的年龄不吻合, 不提交修改, 会触发defer的回滚`)
}

然后修改数据, 再运行一次上面的代码

assert($db.update('test', 2, {
    age: 22
}))
// 再运行上面那一段代码

语句支持

expr

expr 可以实现(不安全的)SQL修改

使用示例

// 企图让id=2的数据的age字段自增3, 以下做法是错误的
assert($db.update('test', 2, {
    age: 'age + 3',
}))
// 在mysql时, 会报错: 类型错误
// 在sqlite3下, age的内容会变成"age + 3"
// 都是不符合我们预期的

// 先把age设置回数字
assert($db.update('test', 2, {
    age: 23
}))

// 正确的做法是用$db.expr
assert($db.update('test', 2, {
    age: $db.expr('= age + 3'),
}))

// 看一看修改后的数据
assert($db.find('test', 2))

同理, 在find、update的时候也可以

// 模糊查询
assert($db.find('test', {
    name: $db.expr(`LIKE 'z%'`),
}))

// 更新时使用子查询或者内置函数
assert($db.update('test', 1, {
    age: $db.expr(`= (SELECT COUNT(*) FROM test)`),
    // create_at: $db.expr(`= datetime()`), // sqlite时间函数
    // update_at: $db.expr(`= now()`), // mysql时间函数
}))

where(非必学)

where 可以查看筛选条件的编译结果

$db.where({
    $or: [{
        age: {$gt: 2},
        name: $db.expr(`LIKE 'z%'`),
        id: {$in: [1, 2, 3]},
    }, {
        age: {$between: [6, 10]},
    }],
})
// 返回值第一项是SQL语句, 第二项是占位符的实参, 最后一项是error

go-xorm

如果你使用的是Go版Z1h, 环境内置了https://github.com/go-xorm/xorm 第三方库

通过$xorm可以用ORM的方式操作数据库

// 建表
var table_struct = new('struct', {
    id: 0,
    username: ``,
    password: ``,
    age: 0,
    updateAt: now(),
    createAt: now(),
}, {
    id: `xorm:"not null pk autoincr INT(11)"`,
    username: `xorm:"not null VARCHAR(64) unique(openid)"`,
    password: `xorm:"not null VARCHAR(64)"`,
    age: `xorm:"not null int(11) default 18"`,
    updateAt: `xorm:"updated"`,
    createAt: `xorm:"created"`,
});
assert($xorm.Table('test_db2').Sync2(table_struct))

// 插入
assert($xorm.Table('test_db2').Insert({
    username: 'admin',
    password: md5("Password").hex(),
    age: 12,
}))
// 插入时带创建时间
assert($xorm.Table('test_db2').Insert(new(table_struct, {
    username: 'admin2',
    password: md5("Password2").hex(),
    age: 12,
})))

// 删除
assert($xorm.Table('test_db2').Id(1).Delete(new(table_struct)))
assert($xorm.Table('test_db2').Where('username = ?', 'admin').Delete(new(table_struct)))

// 查询
var results = &new(table_struct, '[]');
assert($xorm.Table('test_db2').Where('username = ?', 'admin1').Find(results));
results = *results;
print(results.map(e=>{e.username}).join(' and '));

// 修改
assert($xorm.Table('test_db2').Id(2).Update(new(table_struct, {
    username: 'hello',    
})))

// ...
// 更多操作请参考xorm官方文档 https://github.com/go-xorm/xorm