Empty column does not return NULL or empty

Question

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.

If `LOAD DATA INFILE` populates the column, the `DEFAULT` value does not apply. Given it's a CSV, I assume the column is an empty string or possibly a string with one or more whitespace characters. — Mark Rotteveel, Feb 26 '22 at 09:21
@dcolazin - i tried both DESC and ASC and my top 10 Context is the same, strange... However, this top 10 has a different order to the table's original order. — Truc Tran, Feb 26 '22 at 09:52
@MarkRotteveel - i imported the CSV to excel to check for whitespaces and there wasn't any. May I ask what's the difference btw empty string and '' - because I check for '' in the column (in mysql) and it returned nothing. — Truc Tran, Feb 26 '22 at 09:54
Please provide a [mre]. And you have checked for `''`, but you haven't checked for blanks (e.g. spaces, tabs, linebreaks, etc) — Mark Rotteveel, Feb 26 '22 at 09:54
Does this thread solve your problem? https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data — ino, Feb 26 '22 at 09:55
@MarkRotteveel - thanks for bringing up tabs and linebreaks, etc. It was either one. I have fixed the problem and the solution is below. — Truc Tran, Feb 26 '22 at 10:24

score 0 · Answer 1 · answered Feb 26 '22 at 10:22

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.

score 0 · Accepted Answer · answered Feb 26 '22 at 10:25

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

Empty column does not return NULL or empty

2 Answers2