Why laravel inserts "0" instead of "NULL" in mysql database?

Question

Why do i have different behaviour for following code?

\DB::table('Test')->insert(
    array(
        'NullableInt' => "NULL", // gives 0
        'NullableVarchar' => "NULL" // gives NULL
    )
);

I need to parse csv file to seed a table. fgetcsv parses everything as string, for text/varchar fields "NULL" as string works, for integer - not. Why?

try null as in PHP's actual null type and not as a string? The fact its a string probably means at some point in the chain its cast to an int which results in 0 and not null. — Wader
– Wader, Commented Jul 24, 2015 at 15:47
@Wader yes, null (non-string) works. I am interested in this behaviour. Is this a bug? Or why does laravel does that? — avasin
– avasin, Commented Jul 24, 2015 at 15:48
@avasin as with all non-strictly typed languages type juggling becomes a pain at some point. In PHP when you cast a string to an int you'll likely get 0 or at least a number which PHP has attempted to extrapolate out of the string. When you cast "NULL" to an int, thus you get 0. — Wader
– Wader, Commented Jul 24, 2015 at 15:50
Agreed. But laravel's developers are quite experienced people, i believe they have thought about that. Just interested why does it work this way. BTW, seems that mysql converts "NULL" string to NULL itself. — avasin
– avasin, Commented Jul 24, 2015 at 15:54

avasin · Accepted Answer · 2015-07-24 15:56:14Z

1

It seems that MySQL sets text/varchar field as NULL, when you try to write there "NULL" (string) value.

When you will try to write "NULL" string to int field in mysql client directly, you will get zero.

answered Jul 24, 2015 at 15:56

avasin

9,79619 gold badges86 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ceejayoz · Accepted Answer · 2015-07-24 16:30:11Z

1

https://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

When inserting a string into a numeric column, it will be cast to an integer automatically.

mysql> SELECT CAST('NULL' AS UNSIGNED INTEGER)
        -> 0

mysql> SELECT CAST(NULL AS UNSIGNED INTEGER)
        -> NULL

More interesting cases:

mysql> SELECT CAST('1NULL' AS UNSIGNED INTEGER)
        -> 1

mysql> SELECT CAST('NULL1' AS UNSIGNED INTEGER)
        -> 0

mysql> SELECT CAST('1,000' AS UNSIGNED INTEGER)
        -> 1

If you want null, you should do either PHP's null or DB::raw('NULL') (which prevents Laravel from treating it as the string you provided it as):

\DB::table('Test')->insert(
    array(
        'NullableInt' => null,
        'NullableVarchar' => DB::raw('NULL'),
    )
);

edited Jul 24, 2015 at 16:30

answered Jul 24, 2015 at 16:01

ceejayoz

181k42 gold badges311 silver badges382 bronze badges

Collectives™ on Stack Overflow

Why laravel inserts "0" instead of "NULL" in mysql database?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related