MySQL: Replacing 'NULL' as string with actual NULL values in whole table with single query

Question

When I try to query:

SELECT * 
FROM my_table
where my_column is Null

it returns 0 results. The column is integer.

But when I do:

SELECT * 
FROM my_table
where my_column = 'Null'

it returns expected results. And interestingly returned rows has 0 value.

And before I did

update my_table set my_column = Null where my_column = '0';

it used to return '0'.

What could be the reason of this, and what would be the possible solution to have them NULL instead of 'NULL' or '0' or 0.

This is driving me crazy and I spend more then 4 hours trying to fix this. My table has these non-sense values all around. So if any method to fix this for my table instead of single column that would be better.

here is a picture with is NULL

here is a picture with 'NULL'

and here is a picture with is NULL working as expected in different column.

@Tim Biegeleisen I have edited my question with example link and pictures. Can you please check it again. — erikci, May 21 '20 at 17:23
you should not use `'Null'` - that is a string. Given it's an integer column it may be deem `'Null'` to be 0? — Bernard, May 21 '20 at 17:25
If you want the values to be null set the relevant field to null. `UPDATE table SET field=NULL `. What is the default value for the field. do you allow NULLs? — Bernard, May 21 '20 at 17:26
@berdard this is not something I would prefer :D (to have null values as string) the one created this table did it i think, and I need to fix it in whole table. — erikci, May 21 '20 at 17:32

score 2 · Accepted Answer · answered May 21 '20 at 17:38

2

You say that the column my_column's data type is INTEGER, so it is impossible that the column contains the value 'NULL'.
When you apply this condition:

where my_column = 'Null'

the string literal 'Null' is implicitly converted to an integer and since this conversion can not succeed the result is 0 and your condition is equivalent to:

where my_column = 0

You can update your table like this:

update tablename
set my_column = null
where my_column = 0

answered May 21 '20 at 17:38

forpas

160,666
10
38
76

forpas got it posted first, but here's a (very) simple [Rextester demo](https://rextester.com/UAF93953) that proves this out. – Eric Brandt May 21 '20 at 17:42
It is odd and sometimes confusing but it is documented: https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html – forpas May 21 '20 at 17:46
@forpas so as my understanding, even though i can query it with 'null' they are actually 0. – erikci May 21 '20 at 17:46
You can query with anything like 'abc'. The string literal 'abc' in order to be compared to an integer will be implicitly converted to an integer which will be 0. But if you query with '123abc' the result of the conversion is 123. Check the link in my previous comment. – forpas May 21 '20 at 17:49
Most important question for me will be (since im going to use this data for modeling purposes) if I export it as csv and upload it to somewhere else, are my values going to be 0 or null or 'null' ? :D man this is so confusing – erikci May 21 '20 at 17:51
If you import data to an integer column the result will be integers. So if that data does not contain valid integers the result would be 0 or null. Take special care to match correctly the columns. Here is a link that may help you: https://www.mysqltutorial.org/import-csv-file-mysql-table/ – forpas May 21 '20 at 17:55
@EgeÇıkrıkçı, the issue you're having isn't related to the data in the table; it's related to how MySQL is interpreting the search term in your query. The value in the table is still the integer `0`. In the query, the string `'null'` is being evaluated to `0` and generating a (confusing, but valid) match to the `0` in the table. – Eric Brandt May 21 '20 at 17:58
Ahh now I get it. See before I changed manually, I mean when I first imported these columns, mysql thought they were text (Even though they were just integer and null values) – erikci May 21 '20 at 18:02

MySQL: Replacing 'NULL' as string with actual NULL values in whole table with single query

1 Answers1