Can I reference a columns default value directly in a query?

Something like this: SELECT column FROM table WHERE column <> column.DEFAULT_VALUE

Background: I'm writing a application test, and I want to easily find out which columns have non-default values. I could parse out the default value from the schema definition which is stored on disc, or figure out what the default is in various more or less convoluted ways, but I'm hoping there's a syntax for asking the server directly.

  • @a_horse_with_no_name alas, there is ;) – ypercubeᵀᴹ Oct 16 '14 at 12:46

Yes, there is, in MySQL. You can use the DEFAULT() function:

SELECT column 
FROM table 
WHERE NOT (column <=> DEFAULT(column)) ;

Instead of <>, the NOT (.. <=> ..) construct that uses the null-safe equal operator <=> is needed for nullable columns, to have values returned when the default is NULL and null values returned when the default is not null.

Tested in SQLFiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.