在Laravel应用程序中,mysql import将timestamp null值转换为0000-00-0000:00 00:00

In Laravel application I have a MySQL timestamp field deleted_at which was created for softDeletes and the default value is NULL. If the value is not NULL the Eloquent will not retrive the data. When I import CSV file the NULL value from source file becomes 0000-00-00 00:00:00.

I read Mysql import transforms timestamp null values to 0000-00-00 00:00:00 and \N does not worked for me. I have many tables which I need to import periodically, so I really need to fix this. Please help.

You need to disable strict mode in MySQL. Particularly, the mode name is STRICT_TRANS_TABLES . Open /etc/mysql/my.cnf (or the same file in Windows) find and comment out STRICT_TRANS_TABLES there.

More information is here:

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

I think you should see your config/database.php file. This file contains the variable from where you can set the strict mode - On / Off.

Laravel now gives you the power to enable / disable mysql modes. With this new feature, Laravel now has the ability to do three things: Disable "strict" mode, returning to the <= 5.6 behavior; enable "strict" mode, setting it to the 5.7 behavior; or customizing exactly which modes are enabled.

The code in your file goes like this...

'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'strict' => true, // <----- This variable sets the strict mode.
        'engine' => null,
  ],

set this value to false if you want to disable the strict mode else make it true to enable the strict mode. Hope this help you to solve your problem.