Query condtion for values not being the default value?

Question

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.

Answer 1

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