Skip to content

Commit 8f0e00a

Browse files
taozhi8833998taozhi8833998
committed
support mysql insert set syntax in 8.0 version, add on duplicate key update
1 parent e293ed4 commit 8f0e00a

6 files changed

Lines changed: 77 additions & 5 deletions

File tree

‎package.json‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
{
22
"name": "node-sql-parser",
3-
"version": "1.9.1",
3+
"version": "1.9.2",
44
"description": "simple node sql parser",
55
"main": "index.js",
66
"types": "index.d.ts",

‎pegjs/mariadb.pegjs‎

Lines changed: 14 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1196,7 +1196,8 @@ replace_insert_stmt
11961196
KW_INTO? __
11971197
t:table_name __
11981198
p:insert_partition? __ LPAREN __ c:column_list __ RPAREN __
1199-
v:insert_value_clause {
1199+
v:insert_value_clause __
1200+
odp:on_duplicate_update_stmt? __ {
12001201
if (t) {
12011202
tableList.add(`insert::${t.db}::${t.table}`)
12021203
t.as = null
@@ -1214,6 +1215,7 @@ replace_insert_stmt
12141215
columns: c,
12151216
values: v,
12161217
partition: p,
1218+
on_duplicate_update: odp,
12171219
}
12181220
};
12191221
}
@@ -1223,7 +1225,8 @@ insert_no_columns_stmt
12231225
KW_INTO __
12241226
t:table_name __
12251227
p:insert_partition? __
1226-
v:insert_value_clause {
1228+
v:insert_value_clause __
1229+
odp:on_duplicate_update_stmt? __ {
12271230
if (t) {
12281231
tableList.add(`insert::${t.db}::${t.table}`)
12291232
columnList.add(`insert::${t.table}::(.*)`);
@@ -1238,10 +1241,19 @@ insert_no_columns_stmt
12381241
columns: null,
12391242
values: v,
12401243
partition: p,
1244+
on_duplicate_update: odp,
12411245
}
12421246
};
12431247
}
12441248

1249+
on_duplicate_update_stmt
1250+
= KW_ON __ 'DUPLICATE'i __ KW_KEY __ KW_UPDATE __ s:set_list {
1251+
return {
1252+
keyword: 'on duplicate key update',
1253+
set: s
1254+
}
1255+
}
1256+
12451257
replace_insert
12461258
= KW_INSERT { return 'insert'; }
12471259
/ KW_REPLACE { return 'replace'; }

‎pegjs/mysql.pegjs‎

Lines changed: 42 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -218,6 +218,7 @@ crud_stmt
218218
/ update_stmt
219219
/ replace_insert_stmt
220220
/ insert_no_columns_stmt
221+
/ insert_into_set
221222
/ delete_stmt
222223
/ cmd_stmt
223224
/ proc_stmts
@@ -1197,7 +1198,8 @@ replace_insert_stmt
11971198
KW_INTO? __
11981199
t:table_name __
11991200
p:insert_partition? __ LPAREN __ c:column_list __ RPAREN __
1200-
v:insert_value_clause {
1201+
v:insert_value_clause __
1202+
odp:on_duplicate_update_stmt? __ {
12011203
if (t) {
12021204
tableList.add(`insert::${t.db}::${t.table}`)
12031205
t.as = null
@@ -1215,6 +1217,7 @@ replace_insert_stmt
12151217
columns: c,
12161218
values: v,
12171219
partition: p,
1220+
on_duplicate_update: odp,
12181221
}
12191222
};
12201223
}
@@ -1224,7 +1227,8 @@ insert_no_columns_stmt
12241227
KW_INTO __
12251228
t:table_name __
12261229
p:insert_partition? __
1227-
v:insert_value_clause {
1230+
v:insert_value_clause __
1231+
odp: on_duplicate_update_stmt? __ {
12281232
if (t) {
12291233
tableList.add(`insert::${t.db}::${t.table}`)
12301234
columnList.add(`insert::${t.table}::(.*)`);
@@ -1239,10 +1243,46 @@ insert_no_columns_stmt
12391243
columns: null,
12401244
values: v,
12411245
partition: p,
1246+
on_duplicate_update: odp,
12421247
}
12431248
};
12441249
}
12451250

1251+
insert_into_set
1252+
= ri:replace_insert __
1253+
KW_INTO __
1254+
t:table_name __
1255+
p:insert_partition? __
1256+
KW_SET __
1257+
l:set_list __
1258+
odp:on_duplicate_update_stmt? __ {
1259+
if (t) {
1260+
tableList.add(`insert::${t.db}::${t.table}`)
1261+
columnList.add(`insert::${t.table}::(.*)`);
1262+
t.as = null
1263+
}
1264+
return {
1265+
tableList: Array.from(tableList),
1266+
columnList: columnListTableAlias(columnList),
1267+
ast: {
1268+
type: ri,
1269+
table: [t],
1270+
columns: null,
1271+
partition: p,
1272+
set: l,
1273+
on_duplicate_update: odp,
1274+
}
1275+
};
1276+
}
1277+
1278+
on_duplicate_update_stmt
1279+
= KW_ON __ 'DUPLICATE'i __ KW_KEY __ KW_UPDATE __ s:set_list {
1280+
return {
1281+
keyword: 'on duplicate key update',
1282+
set: s
1283+
}
1284+
}
1285+
12461286
replace_insert
12471287
= KW_INSERT { return 'insert'; }
12481288
/ KW_REPLACE { return 'replace'; }

‎src/insert.js‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@ import { tablesToSQL } from './tables'
22
import { exprToSQL } from './expr'
33
import { identifierToSql, commonOptionConnector, hasVal, toUpper, returningToSQL } from './util'
44
import { selectToSQL } from './select'
5+
import { setToSQL } from './update'
56

67
/**
78
* @param {Array} values
@@ -33,14 +34,18 @@ function insertToSQL(stmt) {
3334
columns,
3435
values,
3536
where,
37+
on_duplicate_update: onDuplicateUpdate,
3638
partition,
3739
returning,
40+
set,
3841
} = stmt
3942
const clauses = ['INSERT', toUpper(prefix), tablesToSQL(table), partitionToSQL(partition)]
4043
if (Array.isArray(columns)) clauses.push(`(${columns.map(identifierToSql).join(', ')})`)
4144
clauses.push(commonOptionConnector(Array.isArray(values) ? 'VALUES' : '', valuesToSQL, values))
45+
clauses.push(commonOptionConnector('SET', setToSQL, set))
4246
clauses.push(commonOptionConnector('WHERE', exprToSQL, where))
4347
clauses.push(returningToSQL(returning))
48+
clauses.push(commonOptionConnector(onDuplicateUpdate && onDuplicateUpdate.keyword, setToSQL, onDuplicateUpdate && onDuplicateUpdate.set))
4449
return clauses.filter(hasVal).join(' ')
4550
}
4651

‎src/update.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,4 +33,5 @@ function updateToSQL(stmt) {
3333

3434
export {
3535
updateToSQL,
36+
setToSQL,
3637
}

‎test/insert.spec.js‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -113,6 +113,20 @@ describe('insert', () => {
113113
expect(backSQL).to.be.equal("INSERT INTO `account` PARTITION(`date`, `id`) (`id`, `name`) VALUES (123,'test'),(124,'test2')")
114114
})
115115

116+
it('should support parse insert on duplicate key update', () => {
117+
const sql = 'INSERT into account partition(date, id) (id, name) values(123, "test"), (124, "test2") on duplicate key update id = 123, name = "test"'
118+
const ast = parser.astify(sql)
119+
const backSQL = parser.sqlify(ast)
120+
expect(backSQL).to.be.equal("INSERT INTO `account` PARTITION(`date`, `id`) (`id`, `name`) VALUES (123,'test'),(124,'test2') ON DUPLICATE KEY UPDATE `id` = 123, `name` = 'test'")
121+
})
122+
123+
it('should support parse insert set', () => {
124+
const sql = 'INSERT into account partition(date, id) set id = 234, name="my-name" on duplicate key update id = 123, name = "test"'
125+
const ast = parser.astify(sql)
126+
const backSQL = parser.sqlify(ast)
127+
expect(backSQL).to.be.equal("INSERT INTO `account` PARTITION(`date`, `id`) SET `id` = 234, `name` = 'my-name' ON DUPLICATE KEY UPDATE `id` = 123, `name` = 'test'")
128+
})
129+
116130
it('should support parse insert partition expr', () => {
117131
const sql = 'INSERT into account partition(date = 20191218, id = 2) (id, name) values(123, "test"), (124, "test2")'
118132
const ast = parser.astify(sql)

0 commit comments

Comments
 (0)