0

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?

Code Junkie
  • 7,602
  • 26
  • 79
  • 141
  • possible duplicate of [Insert NULL value into INT column](http://stackoverflow.com/questions/5338529/insert-null-value-into-int-column) – John Ruddell Sep 14 '14 at 16:35
  • Or [this question](http://stackoverflow.com/questions/18449708/how-to-insert-null-in-mysql-especially-int-datatype) – John Ruddell Sep 14 '14 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 Sep 14 '14 at 16:37
  • Define converts it to 0? In the database? – John Ruddell Sep 14 '14 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 Sep 14 '14 at 16:41
  • default is NULL ALTER TABLE `my_database`.`my_table` CHANGE COLUMN `field1` `field1` INT(7) DEFAULT NULL ; – Code Junkie Sep 14 '14 at 16:43
  • Have you tried inserting into the table without inserting into that column? – John Ruddell Sep 14 '14 at 16:45
  • Yeah, my varchar columns show null. Is there is a sql configuration setting that is causing this?. – Code Junkie Sep 14 '14 at 16:49
  • If you insert into the varchar and not the int column does it put a null in there?? Like dont insert anything into that column... Honestly ive only seen 0 get converted to null not the other way around.. Cant do much to test it as is – John Ruddell Sep 14 '14 at 16:52
  • I just tried inserting a new record leaving field1 as a null and it displays it as a zero. I did this directly from workbench. – Code Junkie Sep 14 '14 at 16:57

2 Answers2

2

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

John Ruddell
  • 25,283
  • 6
  • 57
  • 86
1

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)
Mureinik
  • 297,002
  • 52
  • 306
  • 350