1

I just changed, on phpmyadmin, an int obligatory field to not obligatory (i ticked the empty checkbox) and made default values NULL.

So far so good, the problem is when i try to change this value on already exist records (from phpmyadmin) and delete the number value, i get a warning message : 

Affected 0 records.
Warning: #1366 Incorrect integer value: '' for column 'fktypeId' at row 1

and it puts 0 on the field value. I dont want to have any value at all into that field, neither 0 nor NULL, just empty field.

Can this happen? Thanks in advance.

This is definitelly a phpmyadmin way of manipulating the not obligatory Int fields. We just have to check the checkbox!! otherwise it turns always the field value to 0

Thanks folks, i post the printscreen for those who come up with this 'problem'.

enter image description here

Improve this question
4
  • Is the field of integer type? Commented Feb 29, 2016 at 15:49
  • Yes of course , int(11). But i see now that when i add new record (phpmyadmin) and leave the field empty , it adds NULL instead of 0. Is it just for the edit the problem? Commented Feb 29, 2016 at 15:56
  • If the default is NULL for the field, then a newly created record will have NULL, unless a specific integer value is explicitly entered. Commented Feb 29, 2016 at 15:59
  • 1
    Storing integer value zero is the expected behavior. It's not possible to store a zero length string (or empty string) in an integer type column... the only possible values are integer values, or a NULL. MySQL implicitly converts any non-NULL value into an integer value. MySQL evaluates an empty string is as integer value of zero. Commented Feb 29, 2016 at 16:12

2 Answers 2

Reset to default
4

An integer column can only store an integer value, or a NULL. A zero length string cannot be "stored" in an integer column.

The warning is the expected behavior if a string value which can't be converted to an integer value is supplied for an integer column.

If you are running a SQL statement, you can use the keyword NULL

For example:

 UPDATE mytable SET some_int_col = NULL WHERE id = 42 ;

If you are using some kind of "row editor", then there may be a checkbox you need to select for the NULL value.

With a SQL statement, such as this:

 UPDATE mytable SET some_int_col = '' WHERE id = 42 ;

The literal string value (in this case, a zero length string) is being assigned to a column of integer datatype, MySQL will implicitly perform a conversion. In a numeric context, the zero length string evalutes to zero.

For comparison, consider the behavior when different strings are implicitly converted to numeric.

 SELECT '' AS foo,  '' + 0 AS foo_int
 UNION ALL SELECT ' b'     , ' b'      + 0
 UNION ALL SELECT '1A'     , '1A'      + 0
 UNION ALL SELECT ' 23DE78', ' 23DE78' + 0
 UNION ALL SELECT NULL     , NULL      + 0

returns

foo       foo_int  
--------  -------
                0
 b              0
1A              1
 23DE78        23
(NULL)     (NULL)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You are right my man, i just saw it on the phpmyadmin , but you got the point. Although my local sql manager software that i have on my PC allows me to clear the field without any checkbox. cheers.
0

Just wanted to add this to the pool even though it already partly was mentioned in a previous answer:

My query was:

UPDATE myTable SET myColumn='NULL';

when it should have been:

UPDATE myTable SET myColumn=NULL;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.