Skip to content

Commit 7b2efec

Browse files
taozhi8833998taozhi8833998
committed
bug: support on duplicate key update set values
1 parent 9b7cef5 commit 7b2efec

10 files changed

Lines changed: 36 additions & 5 deletions

File tree

‎pegjs/db2.pegjs‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1264,6 +1264,9 @@ set_item
12641264
= tbl:(ident __ DOT)? __ c:column __ '=' __ v:additive_expr {
12651265
return { column: c, value: v, table: tbl && tbl[0] };
12661266
}
1267+
/ tbl:(ident __ DOT)? __ c:column __ '=' __ KW_VALUES __ LPAREN __ v:column_ref __ RPAREN {
1268+
return { column: c, value: v, table: tbl && tbl[0], keyword: 'values' };
1269+
}
12671270
insert_value_clause
12681271
= value_clause
12691272
/ select_stmt_nake

‎pegjs/flinksql.pegjs‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1756,6 +1756,10 @@ set_item
17561756
// => { column: ident; value: additive_expr; table?: ident;}
17571757
return { column: c, value: v, table: tbl && tbl[0] };
17581758
}
1759+
/ tbl:(ident __ DOT)? __ c:column __ '=' __ KW_VALUES __ LPAREN __ v:column_ref __ RPAREN {
1760+
return { column: c, value: v, table: tbl && tbl[0], keyword: 'values' };
1761+
}
1762+
17591763
returning_stmt
17601764
= k:KW_RETURNING __ c:(STAR / column_ref_list) {
17611765
// => { type: 'returning'; columns: column_ref_list | column_ref; }

‎pegjs/hive.pegjs‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1184,6 +1184,10 @@ set_item
11841184
= tbl:(ident __ DOT)? __ c:column __ '=' __ v:additive_expr {
11851185
return { column: c, value: v, table: tbl && tbl[0] };
11861186
}
1187+
/ tbl:(ident __ DOT)? __ c:column __ '=' __ KW_VALUES __ LPAREN __ v:column_ref __ RPAREN {
1188+
return { column: c, value: v, table: tbl && tbl[0], keyword: 'values' };
1189+
}
1190+
11871191
insert_value_clause
11881192
= value_clause
11891193
/ select_stmt_nake

‎pegjs/mariadb.pegjs‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1184,6 +1184,9 @@ set_item
11841184
= tbl:(ident __ DOT)? __ c:column __ '=' __ v:additive_expr {
11851185
return { column: c, value: v, table: tbl && tbl[0] };
11861186
}
1187+
/ tbl:(ident __ DOT)? __ c:column __ '=' __ KW_VALUES __ LPAREN __ v:column_ref __ RPAREN {
1188+
return { column: c, value: v, table: tbl && tbl[0], keyword: 'values' };
1189+
}
11871190

11881191
insert_value_clause
11891192
= value_clause

‎pegjs/mysql.pegjs‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1257,7 +1257,11 @@ set_list
12571257
set_item
12581258
= tbl:(ident __ DOT)? __ c:column __ '=' __ v:additive_expr {
12591259
return { column: c, value: v, table: tbl && tbl[0] };
1260-
}
1260+
}
1261+
/ tbl:(ident __ DOT)? __ c:column __ '=' __ KW_VALUES __ LPAREN __ v:column_ref __ RPAREN {
1262+
return { column: c, value: v, table: tbl && tbl[0], keyword: 'values' };
1263+
}
1264+
12611265
insert_value_clause
12621266
= value_clause
12631267
/ select_stmt_nake

‎pegjs/postgresql.pegjs‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1833,6 +1833,10 @@ set_item
18331833
// => { column: ident; value: additive_expr; table?: ident;}
18341834
return { column: c, value: v, table: tbl && tbl[0] };
18351835
}
1836+
/ tbl:(ident __ DOT)? __ c:column __ '=' __ KW_VALUES __ LPAREN __ v:column_ref __ RPAREN {
1837+
// => { column: ident; value: column_ref; table?: ident; keyword: 'values' }
1838+
return { column: c, value: v, table: tbl && tbl[0], keyword: 'values' };
1839+
}
18361840
returning_stmt
18371841
= k:KW_RETURNING __ c:(STAR / column_ref_list) {
18381842
// => { type: 'returning'; columns: column_ref_list | column_ref; }

‎pegjs/transactsql.pegjs‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1490,6 +1490,9 @@ set_item
14901490
= tbl:(ident __ DOT)? __ c:column __ '=' __ v:additive_expr {
14911491
return { column: c, value: v, table: tbl && tbl[0] };
14921492
}
1493+
/ tbl:(ident __ DOT)? __ c:column __ '=' __ KW_VALUES __ LPAREN __ v:column_ref __ RPAREN {
1494+
return { column: c, value: v, table: tbl && tbl[0], keyword: 'values' };
1495+
}
14931496
insert_value_clause
14941497
= value_clause
14951498
/ select_stmt_nake

‎src/insert.js‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -39,13 +39,14 @@ function insertToSQL(stmt) {
3939
returning,
4040
set,
4141
} = stmt
42+
const { keyword, set: duplicateSet } = onDuplicateUpdate || {}
4243
const clauses = ['INSERT', toUpper(prefix), tablesToSQL(table), partitionToSQL(partition)]
4344
if (Array.isArray(columns)) clauses.push(`(${columns.map(identifierToSql).join(', ')})`)
4445
clauses.push(commonOptionConnector(Array.isArray(values) ? 'VALUES' : '', valuesToSQL, values))
4546
clauses.push(commonOptionConnector('SET', setToSQL, set))
4647
clauses.push(commonOptionConnector('WHERE', exprToSQL, where))
4748
clauses.push(returningToSQL(returning))
48-
clauses.push(commonOptionConnector(onDuplicateUpdate && onDuplicateUpdate.keyword, setToSQL, onDuplicateUpdate && onDuplicateUpdate.set))
49+
clauses.push(commonOptionConnector(keyword, setToSQL, duplicateSet))
4950
return clauses.filter(hasVal).join(' ')
5051
}
5152

‎src/update.js‎

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
import { tablesToSQL } from './tables'
22
import { exprToSQL, orderOrPartitionByToSQL } from './expr'
33
import { limitToSQL } from './limit'
4-
import { hasVal, identifierToSql, commonOptionConnector, returningToSQL } from './util'
4+
import { hasVal, identifierToSql, commonOptionConnector, returningToSQL, toUpper } from './util'
55

66
/**
77
* @param {Array} sets
@@ -12,9 +12,13 @@ function setToSQL(sets) {
1212
const clauses = []
1313
for (const set of sets) {
1414
let str = ''
15-
const { table, column, value } = set
15+
const { table, column, value, keyword } = set
1616
str = [table, column].filter(hasVal).map(info => identifierToSql(info)).join('.')
17-
if (value) str = `${str} = ${exprToSQL(value)}`
17+
if (value) {
18+
const prefix = keyword && `${toUpper(keyword)}(` || ''
19+
const suffix = keyword && ')' || ''
20+
str = `${str} = ${prefix}${exprToSQL(value)}${suffix}`
21+
}
1822
clauses.push(str)
1923
}
2024
return clauses.join(', ')

‎test/insert.spec.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -118,6 +118,7 @@ describe('insert', () => {
118118
const ast = parser.astify(sql)
119119
const backSQL = parser.sqlify(ast)
120120
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+
expect(parser.sqlify(parser.astify(`INSERT INTO user (id, name, age) VALUES (1, 'user1', 50) ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age)`))).to.be.equal("INSERT INTO `user` (`id`, `name`, `age`) VALUES (1,'user1',50) ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `age` = VALUES(`age`)")
121122
})
122123

123124
it('should support parse insert set', () => {

0 commit comments

Comments
 (0)