如何将所有空行更改为NULL？

Question

I have a table like this:

// mytable
+----+--------+
| id |  name  |
+----+--------+
| 1  |  Jack  |
| 2  |        |
| 3  |  Peter |
| 4  |        |
| 5  |        |
| 6  |  Alvin |
+----+--------+

Also the output of this query is always 1:

SELECT ... (mytable.name IS NOT NULL) as col ... FROM ...

//=> echo result["col"]; -> always it will be "1"

Now, how can I change empty records to NULL? Because I want it returns 1 just for where there is a name and if there is not any name I want to it returns 0.

Actually I want something like this:

// newtable
+----+--------+
| id |  name  |
+----+--------+
| 1  |  Jack  |
| 2  |  Null  |
| 3  |  Peter |
| 4  |  Null  |
| 5  |  Null  |
| 6  |  Alvin |
+----+--------+

How can I convert my table to this ^ ?

Answer 1

If your table column name is nullable you can update your data:

UPDATE mytable
SET name = NULL
WHERE name = '';

If your column name is NOT NULL you need to alter it first to allow NULL.

or if you need to create new table you can use:

CREATE TABLE newtable
AS
SELECT id, CASE WHEN TRIM(name) = '' THEN NULL ELSE name END AS name
FROM mytable;