Empty column does not return NULL or empty-CodePudding

I imported a CSV file using LOAD DATA INFILE and have this column called Context.

The entire column appears empty - which it shouldn't be, because I already set the DEFAULT to be NULL. So I expected NULL to appear in the entire column.

I have tried if it is empty or NULL but the code below returns 0

SELECT COUNT(*) FROM table 
WHERE Context IS NULL OR Context = '';

So now I do not know where the problem is.

FYI: This column is in TEXT datatype and I could change it to VARCHAR, but NOT INT - I don't know if it's related to the problem.

CodePudding user response：

You can manipulate the empty values while importing the data using the SET col_name={expr | DEFAULT}, ... statement while importing the data combined with the function NULLIF(expr1,expr2).

So your query may looks like this:

LOAD DATA INFILE 'file.txt'
  INTO table t1
  (column1, @var1)
  SET Context = NULLIF(@var1, '');

Or you can manipulate all records after the import

UPDATE `table` SET Context2 = NULL WHERE Context2 IS NULL;

LOAD DATA

NULLIF

CodePudding user response：

Based on this thread, I modified my query to the following and was able to solve the problem.

UPDATE table SET Context = NULLIF(Context,' ');
UPDATE table SET Context = NULLIF(Context, '\t');
UPDATE table SET Context = NULLIF(Context, '\n');
UPDATE table SET Context = NULLIF(Context, '\r');

So the original problem could be caused by either \t , \n or \r.