Knex RAW MySQL查询添加新行

Question

我是MySQL的新手，以前使用PSQL的方式与我使用MySQL相同，但下面的代码是错误的。

return await db
.raw(
`INSERT INTO users(firstName, 
lastName, 
email, 
password, 
type,
addressLine1, 
addressLine2, 
addressLine3, 
mobileNumber,
townCity, 
postcode)
VALUES (
'${firstName}',
'${lastName}',
'${email}',
'${hashedPassword}', 
'${mobileNumber}',
${type}, 
'${addressLine1}', 
'${addressLine2}', 
'${addressLine3}', 
'${townCity}', 
'${postcode}'); 
SELECT * from users WHERE id = LAST_INSERT_ID()`,
)
.then(() => ...)

返回以下错误。

code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
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 'SELECT * from users WHERE id = LAST_INSERT_ID()' at line 24",
sql: 'INSERT INTO users(firstName, n' +
'            lastName, n' +
'            email, n' +
'            password, n' +
'            type,n' +
'            addressLine1, n' +
'            addressLine2, n' +
'            addressLine3, n' +
'            mobileNumber,n' +
'            townCity, n' +
'            postcode)n' +
'            VALUES (n' +
"            'Jane',n" +
"            'Doe',n" +
"            '[e-mail]',n" +
"            'password', n" +
"            '07123456789',n" +
'            100, n' +
"            '1', n" +
"            'Test road', n" +
"            'Westminster', n" +
"            'London', n" +
"            'L1 1TG'); n" +
'            SELECT * from users WHERE id = LAST_INSERT_ID()'
}

我怀疑它与模板文字有关，但不确定解决这个问题的最佳方法，它在PSQL中工作得很好(不包括最后一行)。是否有更好的方法使用原始SQL，我对所有高级ORM命令不感兴趣。

Answer 1

您需要启用multipleStatements

const mysql = require('mysql')
connection = mysql.createConnection({
host: 'localhost',
user: 'youruser',
password: 'yourpassword',
database: 'yourdb',
multipleStatements: true
})
connection.connect()
connection.query('INSERT INTO users(firstName, 
lastName, 
email, 
password, 
type,
addressLine1, 
addressLine2, 
addressLine3, 
mobileNumber,
townCity, 
postcode)
VALUES (
'${firstName}',
'${lastName}',
'${email}',
'${hashedPassword}', 
'${mobileNumber}',
${type}, 
'${addressLine1}', 
'${addressLine2}', 
'${addressLine3}', 
'${townCity}', 
'${postcode}'); 
SELECT * from users WHERE id = LAST_INSERT_ID();', function(err, results) {
if (err) {
throw err
}
console.log(results[0]])
})
connection.end()

Answer 2

看起来你不能在一个查询中做太多。在插入正常工作后，将SELECT * from users WHERE id = LAST_INSERT_ID()提取到它自己的查询中

Answer 3

尝试分别执行第一个查询。如果它没有执行，检查你的表结构。似乎结构缺少主键设置或在您的迁移