yamlling-node-mysql
Advanced tools
一个MYSQL数据库的常用操作封装，使用连续点操作，实现语义化的数据库操作。
npm i @hyoga/mysql
const mysql = require('@hyoga/mysql');
const inst = new mysql({
host: '127.0.0.1',
user: 'root',
password: '',
database: 'test-db',
port: 3306,
});
const users = await inst.table('user').where({ status: 1 }).select();
console.log(users);
见下方API
Mysql数据库实例，封装了常用操作方式
Author: lq9328@126.com
Promise.<any>MysqlMysqlMysqlMysqlMysqlMysqlMysqlMysqlMysqlMysqlPromise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>stringKind: inner class of @hyoga/mysql
Promise.<any>MysqlMysqlMysqlMysqlMysqlMysqlMysqlMysqlMysqlMysqlPromise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>Promise.<any>string创建Mysql实例
| Param | Type | Description |
|---|---|---|
| config | object | 数据库连接配置 |
Promise.<any>直接执行sql语句
Kind: instance method of Mysql
Returns: Promise.<any> - sql执行结果
| Param | Type | Description |
|---|---|---|
| sql | string | sql语句 |
Mysql设置表名
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| tableName | string | 表名 |
Mysql设置表的别名
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| tableAlias | string | 主表别名 |
Mysql设置需要选取的字段，字符串或数组格式
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| fields | string | Array | 需要选取的字段 |
Example
// SELECT `admins`.`id`, `admins`.`name` FROM `admins` limit 1
mysql.table('admins').field('id, name').find();
// SELECT `admins`.`id`, `admins`.`name` as a, `admins`.`status` as b FROM `admins` limit 1
mysql.table('admins').field(['id', 'name as a', { status: 'b' }]).find();
Mysqlgroup by 操作
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| columns | Array | string | 分组列名，可为数组或字符串，字符串以逗号分隔 |
Mysqlwhere条件设置，接受字符串或者对象形式，可以多次调用，每次调用都作为一个整体，多次调用使用 AND 连接
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| where | object | string | where条件 |
Example
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`status` = 'on') limit 1
mysql.table('admins').where({ status: 'on' }).find();
// SELECT `admins`.`*` FROM `admins` WHERE (id = 10 OR id < 2) limit 1
mysql.table('admins').where('id = 10 OR id < 2').find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` != 1) limit 1
mysql.table('admins').where({id: ['!=', 1]}).find();
// NULL操作
SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` IS NULL) limit 1
mysql.table('admins').where({id: null}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` IS NOT NULL) limit 1
mysql.table('admins').where({id: [ '!=', null ]}).find();
// LIKE 操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` LIKE '%admin%') limit 1
mysql.table('admins').where({name: [ 'like', '%admin%' ]}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` NOT LIKE '%admin%') limit 1
mysql.table('admins').where({name: [ 'notlike', '%admin%' ]}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` LIKE '%admin%' OR `admins`.`email` LIKE '%admin%') limit 1
mysql.table('admins').where({'name|email': [ 'like', '%admin%' ]}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` LIKE '%admin%' AND `admins`.`email` LIKE '%admin%') limit 1
mysql.table('admins').where({'name&email': [ 'like', '%admin%' ]}).find();
// 一对多操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` = 'admin' OR `admins`.`name` = 'editor') limit 1
mysql.table('admins').where({name: [ '=', [ 'admin', 'editor' ] ]}).find();
// IN 操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` IN (5,10)) limit 1
mysql.table('admins').where({'id': [ 'in', [5, 10] ]}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` IN (5, 10)) limit 1
mysql.table('admins').where({'id': [ 'in', '5, 10' ]}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` NOT IN (5,10)) limit 1
mysql.table('admins').where({'id': [ 'notin', [5, 10] ]}).find();
// BETWEEN 操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` BETWEEN 5 AND 10) limit 1
mysql.table('admins').where({'id': [ 'between', [5, 10] ]}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` BETWEEN 5 AND 10 AND `admins`.`name` = 'admin') limit 1
mysql.table('admins').where({'id': [ 'between', [5, 10] ], 'name': 'admin'}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` BETWEEN 5 AND 10 OR `admins`.`name` = 'admin') limit 1
mysql.table('admins').where({'id': [ 'between', [5, 10] ], 'name': 'admin', '_logic': 'OR'}).find();
// 多字段操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`status` = 'on') AND (`admins`.`id` >= 1 AND `admins`.`id` <= 10) limit 1
mysql.table('admins').where({'status': 'on'}).where({'id': {'>=': 1, '<=': 10}}).find();
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`status` = 'on') AND (`admins`.`id` >= 1 OR `admins`.`id` <= 10) limit 1
mysql.table('admins').where({'status': 'on'}).where({'id': {'>=': 1, '<=': 10, '_logic': 'OR'}}).find();
Mysql设置结果的条数限制
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| limit | number | 结果的条数限制 |
Mysql分页操作
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Default | Description |
|---|---|---|---|
| page | number | 1 | 当前页数 |
| pageSize | number | 1 | 每页大小 |
Mysql设置数据
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| data | object | 数据 |
Mysql排序
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| order | array | string | 排序 |
Example
// SELECT `article_categorys`.`*` FROM `article_categorys` ORDER BY id desc
mysql.table('article_categorys').order('id desc').select();
//SELECT `article_categorys`.`*` FROM `article_categorys` ORDER BY id desc, name asc
mysql.table('article_categorys').order([ 'id desc', 'name asc' ]).select();
Mysql设置join条件，可以多次join
Kind: instance method of Mysql
Returns: Mysql - 实例
| Param | Type | Description |
|---|---|---|
| join | object | join条件 |
Example
// SELECT `a`.`*`, `b`.`*` FROM `article_posts` as a LEFT JOIN `article_categorys` AS b ON (a.`category_id`=b.`id`) limit 1
mysql.table('article_posts').alias('a').field([ 'a.*', 'b.*' ]).join({
article_categorys: {
as: 'b',
on: { category_id: 'id' }
}
}).find();
// SELECT `a`.`*`, `article_categorys`.`*` FROM `article_posts` as a LEFT JOIN `article_categorys` ON (a.`category_id`=article_categorys.`id`) limit 1
mysql.table('article_posts').alias('a').field([ 'a.*', 'article_categorys.*' ]).join({
article_categorys: {
// as: 'b',
on: { category_id: 'id' }
}
}).find();
Promise.<any>查找一条数据
Kind: instance method of Mysql
Returns: Promise.<any> - 查询结果
| Param | Type | Default | Description |
|---|---|---|---|
| where | object | string | | where条件 |
Promise.<any>查找数据
Kind: instance method of Mysql
Returns: Promise.<any> - 查询结果
| Param | Type | Default | Description |
|---|---|---|---|
| where | object | string | | where条件 |
Promise.<any>更新操作
Kind: instance method of Mysql
Returns: Promise.<any> - 更新结果
| Param | Type | Default | Description |
|---|---|---|---|
| column | object | {name: value} 更新的字段与值 | |
| where | object | string | | where条件，参见[where]方法 |
Promise.<any>一次性更新多条数据
Kind: instance method of Mysql
Returns: Promise.<any> - 更新结果
| Param | Type | Description |
|---|---|---|
| columnList | Array.<object> | [{id: 1, name: value}] 更新的字段与值，必须包含主键 |
| where | object | string | where条件，参见[where]方法 |
Promise.<any>自增操作
Kind: instance method of Mysql
Returns: Promise.<any> - 更新结果
| Param | Type | Default | Description |
|---|---|---|---|
| field | string | 字段名 | |
| step | number | 1 | 自增数，默认1 |
Promise.<any>自减操作
Kind: instance method of Mysql
Returns: Promise.<any> - 更新结果
| Param | Type | Default | Description |
|---|---|---|---|
| field | string | 字段名 | |
| step | number | 1 | 自减数，默认1 |
Promise.<any>新增数据
Kind: instance method of Mysql
Returns: Promise.<any> - 操作结果
| Param | Type | Default | Description |
|---|---|---|---|
| column | object | 字段键值对 | |
| duplicate | object | false | 出现重复则更新，{id : 100, name : VALUES('test')} |
Promise.<any>批量新增数据
Kind: instance method of Mysql
Returns: Promise.<any> - 操作结果
| Param | Type | Default | Description |
|---|---|---|---|
| columnList | object | 字段键值对数组 | |
| duplicate | object | false | 出现重复则更新，{id : 100, name : VALUES('test')} |
Promise.<any>删除操作，彻底删除一条数据，一般不建议删除数据，可以通过字段开关控制
Kind: instance method of Mysql
Returns: Promise.<any> - 操作结果
| Param | Type | Description |
|---|---|---|
| where | object | string | where条件，参见[where]方法 |
string打印生成的sql语句，用于调试
Kind: instance method of Mysql
Returns: string - 生成的sql语句
FAQs
重新封装hyoga/mysql,一个MYSQL数据库的常用操作封装，操作方式类似THINKPHP的数据库操作
The npm package yamlling-node-mysql receives a total of 16 weekly downloads. As such, yamlling-node-mysql popularity was classified as not popular.
We found that yamlling-node-mysql demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Security News
Socket’s threat research team has detected six malicious npm packages typosquatting popular libraries to insert SSH backdoors.
Security News
MITRE's 2024 CWE Top 25 highlights critical software vulnerabilities like XSS, SQL Injection, and CSRF, reflecting shifts due to a refined ranking methodology.
Security News
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.