653 lines
10 KiB
Markdown
653 lines
10 KiB
Markdown
## Document
|
||
|
||
> One or more configurations can be passed in for instantiation. When there is only one database, the default is the `master library`; when there is more than one database service, the first one is automatically used as the `master library`, and the other `slave libraries`.
|
||
|
||
```javascript
|
||
let Mysqli = require('mysqli')
|
||
|
||
// one config
|
||
let conn = new Mysqli({
|
||
host: '', // IP/domain
|
||
post: 3306, //port, default 3306
|
||
user: '', // username
|
||
passwd: '', // password
|
||
charset: '', // CHARSET of database, default to utf8 【optional】
|
||
db: 'test' // the default database name 【optional】
|
||
})
|
||
|
||
// two or more configs
|
||
let conn = new Mysqli([
|
||
{
|
||
host: 'host1', //
|
||
...
|
||
},
|
||
{
|
||
host: 'host2', //
|
||
...
|
||
},
|
||
...
|
||
])
|
||
|
||
let db = conn.emit() // use master library
|
||
// let db = conn.emit(false, 'test')
|
||
// let db = conn.emit(true, 'test')
|
||
|
||
db.table('test').getAll().then(list => {
|
||
console.log(list)
|
||
})
|
||
|
||
```
|
||
|
||
|
||
|
||
## Static Function
|
||
|
||
### escape(val)
|
||
|
||
> Various types of values of sql can be safely escaped
|
||
|
||
|
||
|
||
## Instance APIs
|
||
|
||
### emit([slave], [db])
|
||
|
||
* slave `<Boolean>` [optional] use the slave libraries, default to false
|
||
* db `<String>` [optional] declare the database's name, default to this config passed.
|
||
|
||
> This method will return a DB instance.
|
||
|
||
|
||
```javascript
|
||
let db = conn.emit(false, 'test')
|
||
|
||
db.debug = true // debugger mode. supported in ^3.1.0
|
||
|
||
db
|
||
.tableList()
|
||
.then(list => {
|
||
console.log(list)
|
||
})
|
||
.catch(err => {
|
||
console.log(err)
|
||
})
|
||
```
|
||
|
||
|
||
## DB APIs
|
||
|
||
> All method will return a `Promise` if not declare.
|
||
|
||
### query(sql)
|
||
|
||
* sql `<String>` sql 语句, [必传]
|
||
|
||
> Execute the sql if you need.
|
||
|
||
```javascript
|
||
db.query('select * from `student` limit 10').then(result => {
|
||
console.log(result)
|
||
})
|
||
|
||
// if not declare a database before. you can code like that.
|
||
db.query('select * from `test`.`student` limit 10').then(result => {
|
||
console.log(result)
|
||
})
|
||
```
|
||
|
||
### drop(db)
|
||
|
||
* db `<String>` [optional] the database's name what you want to delete. It will delete the current connected database if not given.
|
||
|
||
> delete the current connected database or what you want.
|
||
|
||
```javascript
|
||
db.drop() // delete self connected
|
||
|
||
// delete foo
|
||
db.drop('foo')
|
||
```
|
||
|
||
### dbList()
|
||
|
||
> return all the databases, base current account.
|
||
|
||
```javascript
|
||
db.dbList().then(list => {
|
||
console.log(list)
|
||
})
|
||
```
|
||
|
||
### tableList()
|
||
|
||
> return all the tables of current connect database.
|
||
|
||
```javascript
|
||
db.tableList().then(list => {
|
||
console.log(list)
|
||
})
|
||
```
|
||
|
||
### dbCreate(name, options)
|
||
|
||
* name `<String>` [required], database's name
|
||
* options `<Object>`, [optional], optional setting
|
||
- charset `<String>` default to utf8mb4
|
||
|
||
|
||
> create a database, return `true` if success.
|
||
|
||
```javascript
|
||
db.dbCreate('foo', { charset: 'utf8' }) // default to utf8mb4
|
||
```
|
||
|
||
|
||
|
||
### tableCreate(name, columns ,options)
|
||
|
||
* name `<String>` [required], table name
|
||
* columns `<Array>`, [required], table columns
|
||
- name `<String>` field name, Case sensitive. recommend `lowercase` and `_`
|
||
- type `<String>` field type, Not case sensitive
|
||
- primary `<Boolean>` is primary key or not.(Only one)
|
||
- inc `<Boolean>` is auto_increment or not.(must be primary key and int)
|
||
- notnull `<Boolean>` if value can be null
|
||
- index `<Boolean>` if create index
|
||
- unique `<Boolean>` if create unique index
|
||
- default `<Any>` the default value
|
||
- update `<Boolean>` auto update(only `datetime` | `timestamp`)
|
||
* options `<Object>`, [optional], other configs
|
||
- charset `<String>` default to utf8mb4
|
||
- engine `<String>` default to InnoDB
|
||
|
||
|
||
> create a table, return `true` if success.
|
||
|
||
```javascript
|
||
db.tableCreate('student',
|
||
[
|
||
{
|
||
name: 'id',
|
||
type: 'int(5)',
|
||
primary: true,
|
||
inc: true
|
||
},
|
||
{
|
||
name: 'name',
|
||
type: 'varchar(64)',
|
||
index: true
|
||
},
|
||
{
|
||
name: 'age',
|
||
type: 'int(3)'
|
||
},
|
||
{
|
||
name: 'sex',
|
||
type: 'tinyint(1)',
|
||
index: true
|
||
},
|
||
...
|
||
])
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
### table(name)
|
||
|
||
* name `<String>` [required], table name
|
||
|
||
> select a table. return a Table instance.
|
||
|
||
```javascript
|
||
let table = db.table('student') //
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
## TABLE APIs
|
||
|
||
|
||
### leftJoin(tables)
|
||
|
||
* tables `<Array>` left join one or more tables.
|
||
- table `<String>` table name
|
||
- on `<String>` condition
|
||
|
||
|
||
```javascript
|
||
db.table('student')
|
||
.leftJoin([
|
||
{
|
||
table: 'classroom',
|
||
on: 'student.classroom = classroom.id'
|
||
},
|
||
...
|
||
])
|
||
```
|
||
|
||
|
||
|
||
### rightJoin(tables)
|
||
> see leftJoin()
|
||
|
||
|
||
|
||
|
||
### join(tables)
|
||
> see leftJoin()
|
||
|
||
|
||
|
||
### filter(options)
|
||
* options `<Object>`
|
||
|
||
> Some keywords in `options`.
|
||
|
||
> * `$and`
|
||
> * `$or`
|
||
> * `$like` fuzzy query
|
||
> * `$sql` Use the result of a certain sql statement as the query condition
|
||
> * `$in`
|
||
> * `$between`
|
||
> * `$lt`
|
||
> * `$lte`
|
||
> * `$gt`
|
||
> * `$gte`
|
||
> * `$eq` equal
|
||
> * `$ne` not equal
|
||
|
||
|
||
```javascript
|
||
db
|
||
.table('student')
|
||
.filter({ id: 1234 }) // a simple search. find the item which it's id is 1234
|
||
|
||
|
||
// find the items which it's name starts with Lee
|
||
.filter({ name: { $like: 'Lee%' } })
|
||
|
||
// you can use sql to search items if needs
|
||
.filter({ name: { $sql: 'IS NULL' } })
|
||
.filter({ score: { $sql: 'score + 1' } })
|
||
|
||
// find the items which it's id is 11,13,29
|
||
.filter({ id: { $in: [11, 13, 29] } })
|
||
|
||
|
||
// find all items which it's age is between 15 and 17
|
||
.filter({ age: { $between: [15, 17] } })
|
||
|
||
// find all items which it's age is smaller than 16
|
||
.filter({ age: { $lt: 16 } })
|
||
|
||
// find all items which it's age is smaller than 16 and elder than 13
|
||
.filter({ age: { $lt: 16, $gt: 13 } })
|
||
|
||
// ***** multi conditions *********
|
||
// find all which it's name starts with Lee, and it's age is elder than 15, and it'sex is 1
|
||
.filter({ name: { $like: 'Lee%' }, age: { $gt: 15 }, sex: 1 })
|
||
|
||
// find all which it's name starts with Lee, or it's age is elder than 15
|
||
.filter({
|
||
$or: [{ name: { $like: 'Lee%' } }, { age: { $gt: 15 } }]
|
||
})
|
||
|
||
// find all which it's name starts with Lee, or it's age is elder than 15 but it'sex is 1
|
||
.filter({
|
||
$or: [
|
||
{ name: { $like: 'Lee%' } }, // condition 1
|
||
{ age: { $gt: 15 }, sex: 1 } // condition 2
|
||
]
|
||
})
|
||
|
||
// find all which it's name starts with Lee or it's age is elder than 15, but it'sex must be 1
|
||
.filter({
|
||
$and: [
|
||
{
|
||
$or: [{ name: { $like: '李%' } }, { age: { $gt: 15 } }]
|
||
},
|
||
{ sex: 1 }
|
||
]
|
||
})
|
||
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
### sort(keys)
|
||
* keys `<Object>`
|
||
|
||
> sort the result by fields.
|
||
|
||
```javascript
|
||
db
|
||
.table('student')
|
||
.sort({ age: -1 }) // -1: Reverse sequence, 1: Positive sequence
|
||
|
||
// sort the age(Reverse sequence), then sort the id(Positive sequence)
|
||
.sort({ age: -1, id: 1 })
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
### skip(num)
|
||
|
||
* num `<Number>`
|
||
|
||
> skip the nums from match list.
|
||
|
||
```javascript
|
||
db.table('student').skip(10) // return this 10th from match list
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
|
||
### limit(num)
|
||
|
||
* num `<Number>`
|
||
|
||
> limit the nums of the result.
|
||
|
||
|
||
```javascript
|
||
db.table('student').limit(10) // only 10 items will be return
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
### slice(start, end)
|
||
|
||
* start `<Number>`
|
||
* end `<Number>`
|
||
|
||
> truncate the result. a friendly method for `skip` and `limit`
|
||
|
||
```javascript
|
||
db
|
||
.table('student')
|
||
.slice(11, 20)
|
||
|
||
// equal to
|
||
.skip(11)
|
||
.limit(10)
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
|
||
### withFields(fields)
|
||
|
||
* fields `<Array>` [optional], default to all fields;
|
||
|
||
> declare the fields what you need.
|
||
|
||
```javascript
|
||
db.table('student').withFields(['id', 'name', 'sex']) // 只取 学号,姓名,性别3个字段返回
|
||
|
||
// equal to
|
||
db.table('student').withFields('id', 'name', 'sex') // supported in ^3.1.0
|
||
```
|
||
|
||
|
||
|
||
|
||
### getAll([ids])
|
||
|
||
* ids `<Array>` [optional], return all items which you search.
|
||
|
||
|
||
```javascript
|
||
db
|
||
.table('student')
|
||
.withFields(['id', 'name', 'sex'])
|
||
.getAll()
|
||
.then(list => {
|
||
console.log(list)
|
||
})
|
||
|
||
// find the students which it's id is 11,13,28
|
||
db
|
||
.table('student')
|
||
.withFields(['id', 'name', 'sex'])
|
||
.getAll([11, 13, 28])
|
||
.then(list => {
|
||
console.log(list)
|
||
})
|
||
|
||
// equal to
|
||
db
|
||
.table('student')
|
||
.withFields(['id', 'name', 'sex'])
|
||
.filter({ id: { $in: [11, 13, 28] } })
|
||
.getAll()
|
||
.then(list => {
|
||
console.log(list)
|
||
})
|
||
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
### get(id)
|
||
|
||
* id `<Any>` [optional], the ID you want to search。
|
||
|
||
> return only one item.
|
||
|
||
```javascript
|
||
// find the student which it's id is 11
|
||
db
|
||
.table('student')
|
||
.withFields(['id', 'name', 'sex'])
|
||
.getAll(11)
|
||
.then(doc => {
|
||
console.log(doc)
|
||
})
|
||
|
||
// equal to
|
||
db
|
||
.table('student')
|
||
.withFields(['id', 'name', 'sex'])
|
||
.filter({ id: 11 })
|
||
.get()
|
||
.then(doc => {
|
||
console.log(doc)
|
||
})
|
||
```
|
||
|
||
|
||
|
||
|
||
|
||
### count()
|
||
|
||
> count the num of result.
|
||
|
||
```javascript
|
||
// get the num which it's name starts with Lee
|
||
db
|
||
.table('student')
|
||
.filter({name: {$like: 'Lee%'}})
|
||
.count()
|
||
.then(total => {
|
||
console.log(total)
|
||
})
|
||
```
|
||
|
||
|
||
|
||
|
||
### insert(doc)
|
||
* doc `<Object>`
|
||
|
||
> insert data.
|
||
|
||
```javascript
|
||
db
|
||
.table('student')
|
||
.insert({
|
||
name: 'Tom',
|
||
age: 18,
|
||
sex: 1,
|
||
...
|
||
})
|
||
.then(res => {
|
||
console.log(res)
|
||
// {
|
||
// fieldCount: 0,
|
||
// affectedRows: 1,
|
||
// insertId: 4,
|
||
// serverStatus: 2,
|
||
// warningCount: 0,
|
||
// message: '',
|
||
// protocol41: true,
|
||
// changedRows: 0
|
||
// }
|
||
})
|
||
```
|
||
|
||
|
||
|
||
### update(doc)
|
||
* doc `<Object>`
|
||
|
||
> update the data.
|
||
|
||
```javascript
|
||
//
|
||
db
|
||
.table('student')
|
||
.filter({name: 'Tom'})
|
||
.update({
|
||
age: 17
|
||
})
|
||
.then(res => {
|
||
console.log(res)
|
||
})
|
||
```
|
||
|
||
|
||
|
||
### remove()
|
||
> delete items.
|
||
|
||
```javascript
|
||
|
||
db
|
||
.table('student')
|
||
.filter({name: {$sql: 'IS NULL'}})
|
||
.remove()
|
||
.then(res => {
|
||
console.log(res)
|
||
})
|
||
```
|
||
|
||
|
||
|
||
### drop()
|
||
> delete current table. it is `danger action`.
|
||
|
||
```javascript
|
||
//
|
||
db
|
||
.table('student')
|
||
.drop()
|
||
.then(result => {
|
||
console.log(result)
|
||
})
|
||
```
|
||
|
||
|
||
|
||
### renameTo(name)
|
||
> rename current table.
|
||
|
||
```javascript
|
||
db
|
||
.table('student')
|
||
.renameTo('student_bac')
|
||
.then(result => {
|
||
console.log(result)
|
||
})
|
||
```
|
||
|
||
|
||
### indexList()
|
||
> return the index list.
|
||
|
||
```javascript
|
||
db
|
||
.table('student')
|
||
.indexList()
|
||
.then(list => {
|
||
console.log(list)
|
||
// {
|
||
// name,
|
||
// column,
|
||
// unique,
|
||
// cardinality,
|
||
// collation,
|
||
// }
|
||
})
|
||
```
|
||
|
||
|
||
|
||
### indexDrop(name)
|
||
* name `<String>` index name
|
||
|
||
> delete an index
|
||
|
||
```javascript
|
||
//
|
||
db
|
||
.table('student')
|
||
.indexDrop('name_idx')
|
||
.then(result => {
|
||
console.log(result)
|
||
})
|
||
```
|
||
|
||
|
||
|
||
### indexCreate(name, options)
|
||
* name `<String>` 索引名
|
||
* options `<Object>` 索引的配置
|
||
- field `<String>` 该索引绑定的字段
|
||
- unique `<Bollean>` 是否是唯一索引
|
||
|
||
|
||
> create an index for a field.
|
||
|
||
```javascript
|
||
//
|
||
db
|
||
.table('student')
|
||
.indexCreate('name_idx', {field: 'name'})
|
||
.then(result => {
|
||
console.log(result)
|
||
})
|
||
```
|