How to check if a column value is NULL or has a DEFAULT value in Mysql

Question

I want to get records for a column value not equal to null and a column not equal to default value. I know we can use

SELECT * FROM table WHERE column_name is NOT NULL AND column_name != 'some_default_value'; 

but what if someone changes this some_default_value value in the table in the future? Is there a flexible solution?

Answer 1

You can use the DEFAULT function:

 SELECT * FROM table WHERE IFNULL(column_name, DEFAULT(column_name)) <> DEFAULT(column_name); 

Answer 2

Try using rows.count, this will help you find out if the table is null or, thus, it has the same value.

Answer 3

You can use the DEFAULT function in MySQL:

 SELECT * FROM grouptable WHERE column_name is NOT NULL and column_name <> DEFAULT( column_name ) 

Answer 4

You can use the information_schema.COLUMNS table to find default values, including all table definitions.

Sample table

 CREATE TABLE `Matches` ( `FootballerName` varchar(100) DEFAULT NULL, `Goal` int(11) DEFAULT '5', `Tournament` varchar(100) DEFAULT NULL ) ; 

The source data for the table

 insert into Matches(FootballerName,Goal,Tournament) select 'Messi ', 3 ,'La liga' union select 'Ronaldo ', 5 ,'UEFA' union select 'Surez ', 2 ,'La liga'; 

Required request to achieve the required.

 SELECT * FROM Matches WHERE Goal not in( select COLUMN_DEFAULT from information_schema.COLUMNS where TABLE_NAME='Matches' and COLUMN_NAME='Goal'); 

Read more about "information_schema.COLUMNS" here