ali-sdk / ali-rds Goto Github PK
View Code? Open in Web Editor NEWAliyun RDS client
License: Other
Aliyun RDS client
License: Other
有计划升级,支持mysql8.0吗
作者你好!
不知道有没有提供方法输出当前执行的sql语句,方便日志调试。
select a.A,b.B from a,b where a.A=b.B
在node环境中,该库mysql poll每次使用完select/query/update之类的操作,是否都需要释放连接?
例如:
db.select('table').then(function(res){
//优先release连接????
}).catch(function(err){
//优先release链接????
})
如果没有释放连接,是否连接资源一直被占用呢?还是有特殊的处理方式
怎么 select、count 方法 模糊查询
just fix a doc miss: 'Can't not ...'
src: lib/operator.js: Line 152
throw new Error('Can\'t not auto detect update condition, please set options.where, or make sure obj.id exists');
数据库格式:2018-03-12 14:46:31
通过egg-mysql查询出来的格式:2018-03-12T06:46:31.000Z
自动加上了时区而且还是错误的时区。
数据库字段类型为timestamp
在 /lib/operator.js 文件中的 269 行,数组直接与字符相加,并且 255 行的 SQL 就没必要声明成数组,运行是能运行,但这么神仙的代码,我对 Egg.js 很担忧啊
let SQL = [ 'UPDATE ?? SET ' ];
let VALUES = [ table ];
const TEMPLATE = [];
for (const key in SQL_CASE) {
let templateSql = ' ?? = CASE ';
VALUES.push(key);
templateSql += SQL_CASE[key].when.join(' ');
VALUES = VALUES.concat(SQL_CASE[key].then);
templateSql += ' ELSE ?? END ';
TEMPLATE.push(templateSql);
VALUES.push(key);
}
SQL += TEMPLATE.join(' , ');
能支持ts吗
mysql.update('tablename', {id, name:"a'b"})
转义为
update tablename set name = 'a\\'b' where id=1
导致执行mysql报错
kpi的产物。我能想象到当年的晋级答辩上,你们是如何吹的天花乱坠。。。
select 不支持比较操作啊
'use strict'
const rds = require('ali-rds');
const db = rds({
host: '',
user: '',
password: '',
pool: true,
port: 3306,
database: "test"
});
require('co')(function *() {
//例子1
var result = yield db.beginTransactionScope(function*(conn) {
yield conn.update('user', {
email: '7'
}, {
where: {id: 54},
});
yield conn.insert('123', {a: 1});
return {success: true};
});
//例子2
let tran = yield db.beginTransaction();
try {
yield tran.update('user', {
email: '2'
}, {
where: {id: 54},
});
throw new Error('123');
yield tran.commit();
} catch (err) {
yield tran.rollback(); // rollback call won't throw err
throw err;
}
})
在上面2段代码中,如果事务中间执行出错期间并不会回滚, 自己也在打算写类似这样的库, 但是一直困扰mysql事务回滚的问题
pg中这么写是没有问题的
不知道你有研究过么,有什么好的解决方案, 麻烦你了
使用 this.app.mysql.get方法时,如果有字段 a 是 null,生成的 sql 代码是:
"SELECT * FROM page
WHERE a
= NULL"
这会导致匹配不到正确的数据,需要生成:
"SELECT * FROM page
WHERE a
is NULL"
假如我有 A, B两个数据库
A库有个admin_account表用于存管理员数据
B库有个comment_info表用于存评论数据
B库得comment_info表中有一个字段adminId对应着A库admin_account的id
现在我查询B库comment_info中的评论数据的时候,想联合查询出A表中id=adminId的数据。
ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' fund_id
= 2' at line 1 (code: ER_PARSE_ERROR)
let queryResult = await this.app.mysql.count('fund_lp_relation', {
where: {
status: 1,
fund_id: 2
}
});
let queryResult = await this.app.mysql.query(`SELECT COUNT(*) FROM fund_lp_relation WHERE status = 1 and fund_id = 2`)
上面2种写法都没毛病吧,但是用count接口就报如上错误
Lines 163 to 166 in 004713c
比如
update({ is_read: 1, gmt_modified: 'now()' }, {
where: { is_read: 0, user_id: 'xxx' },
});
这种情况 转换的 sql 会是
update xxx set gmt_modified = now() where is_read = 0 and user_id = 'xxx'
预期应该是
update xxx set gmt_modified = now(), is_read = 1 where is_read = 0 and user_id = 'xxx'
when i use egg-mysql , How do I get the SQL statement executed?
如
select fund_id, sum(amount) as totalAmount from fund_token_order group by fund_id
这是合法的sql 但是在这个库里面无法操作
导致应用无法快速计算相同字段的数据和
作者你好!
select 现在还没支持模糊查询?
现在只能query(sql,values) 用sql拼装的形式?
使用 AS 指定列名,比如:
let rows = yield db.select('table-name', {
columns: ['author AS owner'],
});
以上用法会报错:unknown column 'author AS owner'
。
正确的用法是什么?能否支持?
https://github.com/ali-sdk/ali-rds/blob/master/lib/operator.js#L161
code here will filter columns in where.
...
for (let i = 0; i < options.columns.length; i++) {
const column = options.columns[i];
// below here filter column in where
if (column in options.where) {
continue;
}
sets.push('?? = ?');
values.push(column);
values.push(row[column]);
}
...
源码如下:
proto._where = function(where) {
if (!where) {
return '';
}
const wheres = [];
const values = [];
for (const key in where) {
const value = where[key];
if (Array.isArray(value)) {
wheres.push('?? IN (?)');
} else {
wheres.push('?? = ?');
}
values.push(key);
values.push(value);
}
if (wheres.length > 0) {
return this.format(' WHERE ' + wheres.join(' AND '), values);
}
return '';
};
看起来仅支持 IN
和 =
,但是 !=
和其他常用比较运算符也很有用啊,为啥不能顺便支持呢?
还是说,官方推荐,其他比较运算符,自己写 SQL 呢?
kpi产物?
// 这种情况的意思是 查询 userid 等于 12 并且 key 等于 'world' 的然后改成 hello
mysql.update('table',{ key: 'hello' }, { where: { userid:12, key:'world' } })
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `userid` = 1 AND `key` = 'wo' at line 1
at Query.Sequence._packetToError (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/Protocol.js:278:3)
at Parser.write (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/Parser.js:76:12)
at Protocol.write (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/Connection.js:91:28)
at Socket.<anonymous> (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/Connection.js:502:10)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
at Socket.Readable.push (_stream_readable.js:208:10)
at TCP.onread (net.js:597:20)
发现是因为ali-rds 中判断了如果条件和 set 值相同,将会被过滤,导致 set 为空
REPLACE INTO users(id, name, age)
VALUES(123, '赵本山', 50), (134,'Mary',15);
这个replace能使用吗?
谢谢。
如果 where 是一个空的数组会报错,msyql 语法有问题。感觉这种情况是不是可以直接退出,不查询了。一个空的 where 就返回一个空就行了。
Line 193 in e4aed30
sqlMessage: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \')\' at line 1'
sqlState: '42000'
index: 0
sql: 'SELECT * FROM `table` WHERE `tableId` IN ()'
proto.update = function* (table, row, options) {
// TODO: support multi rows
options = options || {};
if (!options.columns) {
options.columns = Object.keys(row);
}
if (!options.where) {
if (!('id' in row)) {
throw new Error('Can not auto detect update condition, please set options.where, or make sure obj.id exists');
}
options.where = {
id: row.id,
};
}
const sets = [];
const values = [];
for (let i = 0; i < options.columns.length; i++) {
const column = options.columns[i];
if (column in options.where) {
continue;
}
sets.push('?? = ?');
values.push(column);
values.push(row[column]);
}
const sql = this.format('UPDATE ?? SET ', [ table ]) +
this.format(sets.join(', '), values) +
this._where(options.where);
debug('update(%j, %j, %j) \n=> %j', table, row, options, sql);
return yield this.query(sql);
};
用例
update(
'table',
{
status: 1,
},
{
status: 0
}
)
把所有未读的 status 改成已读,这里执行下来,sets 是个 []
mysqljs/mysql 不支持 mysql 8 的默认认证插件caching_sha2_password;不支持 JSON 列的对象解析
query('select * from table where name = ? and id = ?', [name, id])
=>
query('select * from table where name = :name and id = :id', {name, id})
rt
支持sqlite3吗?
木有文档,各种没法用
const [{ balance, out_sum }] = await app.mysql .get('admin') .select('user_bamboo', { columns: ['balance, out_sum'], where: { user_uid: uid }, limit: 1, }); console.log(balance, out_sum);
sql: "SELECT
balance, out_sumFROM
user_bambooWHERE
user_uid = 'vic' LIMIT 0, 1"
Error: ER_BAD_FIELD_ERROR: Unknown column 'balance, out_sum' in 'field list'
let list = await this.ctx.app.mysql.select('article',{
where:{title:`%${title}%`},
limit:count,
offset:(p-1)*count
});
如何查看生成的sql语句,用的是egg
Line 18 in e4aed30
should be:
assert.equal(op._where({ id: 1, name2: null }), ' WHERE `id` = 1 AND `name2` IS NULL');
该库需要将 Generator 改成 Async / Await 改造。
请问能加上这个的扩展嘛 谢谢 虽然自己拼也可以解决(因为用了egg.js 然后发现egg-mysql插件又是用的ali-rds。。。)
let rows = yield db.query('SELECT * FROM your_table WHERE id=:id', { id: 123 });
console.log(rows);
如何查看这个sql查询的原生sql,如果sql很复杂,需要获取原生sql查看问题
async sql() {
const db = rds({
host: '',
port:,
user: '',
password: '',
database: ""
});
let res = await db.query('SELECT * FROM HrmResource from id = 3');
return res;
}
Handshake inactivity timeout
at Handshake.<anonymous> (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/protocol/Protocol.js:164:17)
at emitNone (events.js:106:13)
at Handshake.emit (events.js:208:7)
at Handshake._onTimeout (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/protocol/sequences/Sequence.js:129:8)
at ontimeout (timers.js:482:11)
at tryOnTimeout (timers.js:317:5)
at Timer.listOnTimeout (timers.js:277:5)
--------------------
at Protocol._enqueue (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/protocol/Protocol.js:145:48)
at Protocol.handshake (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/protocol/Protocol.js:52:23)
at PoolConnection.connect (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/Connection.js:130:18)
at Pool.getConnection (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/Pool.js:48:16)
at /Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:29:7
at new Promise (<anonymous>)
at Pool.<anonymous> (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:12:10)
at Pool.ret [as getConnection] (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:56:34)
at Pool.query (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/Pool.js:202:8)
at /Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:29:7
at new Promise (<anonymous>)
at Pool.<anonymous> (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:12:10)
at Pool.ret [as query] (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:56:34)
at RDSClient.proto._query (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/lib/client.js:34:20)
rds是基于mysql库开发的,但在readme中没有体现相关配置。这样对于由前端进入nodejs开发的同学来说,可能就会忽略了这些东西。建议添加完善配置说明,或者追加mysql库的说明文档链接。
当supportBigNumbers开启后,查询出来的bigint会转换成字符串,业务层拿到的也是字符串。
当使用这些bigint字段做其他操作时候可能出现问题,比如 WHERE IN,CASE WHEN THEN的时候无法正常匹配到数据
select('table', {
where: {
id: ['882066002862186496', '882066002996400128']
}
})
实际的调用应该是
select('table', {
where: {
id: [882066002862186496, 882066002996400128]
}
})
mysql数据库连接池支持吗
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.