在INSERT ON DUPLICATE KEY UPDATE中，检测指定插入值时是否使用默认值

Question

suppose i have this query (in conjunction with PHP)

INSERT INTO table (field1, field2, field3) VALUES (a, $field2, c) ON DUPLICATE KEY UPDATE field2 = IF(VALUES(field2) [ends up using DEFAULT(field2)], field2, VALUES(field2))

where $field2 can either be

a. legitimate normal value to be inserted into field2 or b. The string "DEFAULT(field2)" which would insert whatever mysql default value of the field is (suppose default is 22)

now my goal is to make it so that on the UPDATE part of the query:

if case a.) occurred then update the column with the user specified $field2 value accordingly. othwerwise, if case b.) occurred then keep the row as it is (ie. don't modify the data)

basically i need to fill in the "[ends up using DEFAULT(field2)]" part of the query to accomplish the above

now my first impulse is to perform ON DUPLICATE KEY UPDATE field2 = IF(VALUES(field2) = DEFAULT(field2), field2, VALUES(field2))

but then this would also ends up keeping the row as it is for case a.) in which $field2 = 22 since 22 is the default value of the mysql hence VALUES(field2) = DEFAULT(field2) is TRUE despite the fact in case a.) we want 22 to be updated accordingly instead of keeping the current value

in other words, how do I modify this query so that it'll only perform the update if a value was explicitly inserted in, but don't perform the update if "DEFAULT(field2)" ends up being used

Answer 1

You can run MySQL in strict mode and set this column to NOT NULL definition. Then instead of trying to insert a default value into the column when this case is triggered, you can insert NULL. This would cause an error, causing the query to fail.

I think this is a bit hacky though. I would need to understand your use case better to really see if you should be using a different call pattern altogether.