node mysql insert语法_nodejs之MySQL插入数据--提示语法错误

npmmysql版本@2.18.1

创建链接

var mysql = require("mysql");

function createConnection() {

var connection = mysql.createConnection({

host: '127.0.0.1',

port: '3306',

user: 'root',

password: 'root',

database: 'my_blog' // 数据库名称

});

return connection;

}

复制代码

插入数据(使用占位符)

function insertEveryday(content, ctime, success) {

// var insertSql = "INSERT INTO every_day ('content','ctime') VALUES (?,?)";

// var params = [content, ctime];

var insertSql = 'INSERT INTO every_day SET ?';

var params = {

"content": content,

"ctime": ctime

};

// 连接数据库

var connection = createConnection();

connection.connect();

connection.query(insertSql, params, function (error, result) {

if (error == null) {

success(result)

} else {

console.log(error)

}

});

connection.end();

}

复制代码

遇到的bug

{ 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 ''content','ctime') values ('站在巨人的肩膀上',1584351668)' at line 1

at Query.Sequence._packetToError (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)

at Query.ErrorPacket (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)

at Protocol._parsePacket (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\protocol\Protocol.js:291:23)

at Parser._parsePacket (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\protocol\Parser.js:433:10)

at Parser.write (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\protocol\Parser.js:43:10)

at Protocol.write (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\protocol\Protocol.js:38:16)

at Socket. (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\Connection.js:88:28)

at Socket. (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\Connection.js:526:10)

at Socket.emit (events.js:182:13)

at addChunk (_stream_readable.js:283:12)

--------------------

at Protocol._enqueue (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\protocol\Protocol.js:144:48)

at Connection.query (D:\xhn\programs\deltaning.com\node_modules\mysql\lib\Connection.js:198:25)

at Object.insertEveryday (D:\xhn\programs\deltaning.com\dao\EverydayDao.js:16:16)

at IncomingMessage. (D:\xhn\programs\deltaning.com\web\EverydayController.js:11:21)

at IncomingMessage.emit (events.js:182:13)

at IncomingMessage.Readable.read (_stream_readable.js:486:10)

at flow (_stream_readable.js:922:34)

at resume_ (_stream_readable.js:904:3)

at process._tickCallback (internal/process/next_tick.js:63:19)

code: 'ER_PARSE_ERROR',

errno: 1064,

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 \'\'content\',\'ctime\') values (\'站在巨人的肩膀上\',1584351668)\' at line 1',

sqlState: '42000',

index: 0,

sql:

'insert into every_day (\'content\',\'ctime\') values (\'站在巨人的肩膀上\',1584351668)' }

复制代码

看着上面问题一大串，其实就是提示我说语法错误，我在网上找了好久，插入语句的语法都是下面这个，别人写的也没问题

var insertSql = "INSERT INTO every_day ('content','ctime') VALUES (?,?)";

var params = [content, ctime];

connection.query(insertSql, params, function (error, result) {

……

});

复制代码

后来还是去mysql的npm网站上找到，新版本(mysql@2.18.1)的插入语法改了，

原来插入语法是"INSERT INTO 表名 (字段1, 字段2, ...) VALUES (?, ?, ...)"

新版语法使用的是"INSERT INTO 表名 SET ?"，如下

var insertSql = 'INSERT INTO every_day SET ?';

var params = {

"content": content,

"ctime": ctime

};

connection.query(insertSql, params, function (error, result) {

……

});

复制代码