I can't seem to figure out how to get mysql to store an int value as 'null', instead it continues to give it a zero based value. In my case zero has a meaning to the app. Anybody know how I can store actual nulls?
10
-
possible duplicate of Insert NULL value into INT columnJohn Ruddell– John Ruddell2014-09-14 16:35:02 +00:00Commented Sep 14, 2014 at 16:35
-
Or this questionJohn Ruddell– John Ruddell2014-09-14 16:36:46 +00:00Commented Sep 14, 2014 at 16:36
-
No that is very different. I can insert a null value, however it converts it to zero. I can't have it converting to zero and my column is setup to accept nulls.Code Junkie– Code Junkie2014-09-14 16:37:31 +00:00Commented Sep 14, 2014 at 16:37
-
Define converts it to 0? In the database?John Ruddell– John Ruddell2014-09-14 16:39:03 +00:00Commented Sep 14, 2014 at 16:39
-
When I run this sql statement UPDATE my_table SET field1=null WHERE field1 = 0; The values remain zero. I can not get the database to show null, it only shows zero and they mean two completely different things.Code Junkie– Code Junkie2014-09-14 16:41:12 +00:00Commented Sep 14, 2014 at 16:41
|
Show 5 more comments
2 Answers
I'm on my mobile phone so I can't test this... Also this is too long to be a comment... first thing try selecting the rows in question.. Then if they arent returned try updating a select when the rows are equal to 0.
Maybe it is null but you have a setting in mysql workbench that shows a 0 instead
SELECT id, field1
FROM table
WHERE field1 IS NULL;
If that doesn't return the rows then do this
SELECT id, field1
FROM table
WHERE field1 =0
If that does then try an update with these exact rows
UPDATE table t,
( SELECT id, field1
FROM table
WHERE field1 = 0
) temp
SET t.field1 = NULL
WHERE t.id = temp.id
Hope that is helpful
2 Comments
Code Junkie
I have absolutely no clue what I did, but for some reason, it is finally working now. Thank You John.
John Ruddell
Sure thing... Interesting issue you had
You just write null:
mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.15 sec)
mysql> INSERT INTO t VALUES (null);
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM t;
+------+
| i |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t WHERE i IS NULL;
+------+
| i |
+------+
| NULL |
+------+
1 row in set (0.03 sec)
3 Comments
Code Junkie
believe me I did, I have no idea what was causing this issue.
Code Junkie
I did up above under my question
Code Junkie
But it's working now, it could have been a workbench glitch for all I know.