在 Laravel 9 中执行数据库迁移报错：SQLSTATE[HY000]: General error: 1366 Incorrect DECIMAL value: ‘0’ for column ” at row -1

1、在 Laravel 9 中执行数据库迁移报错：SQLSTATE[HY000]: General error: 1366 Incorrect DECIMAL value: ‘0’ for column ” at row -1。如图1

图1

PS E:\wwwroot\object> php artisan migrate

   INFO  Running migrations.

  2024_05_20_152915_update_table_stock_out_sort_type ................................................................................ 13,901ms FAIL

   Illuminate\Database\QueryException

  SQLSTATE[HY000]: General error: 1366 Incorrect DECIMAL value: '0' for column '' at row -1 (SQL: ALTER TABLE table CHANGE stock_out_sort stock_out_sort NUMERIC(36, 0) DEFAULT '0' NOT NULL COMMENT '订单缺货计算排序字段')

  at E:\wwwroot\object\vendor\laravel\framework\src\Illuminate\Database\Connection.php:760
    756▕         // If an exception occurs when attempting to run a query, we'll format the error
    757▕         // message to include the bindings with SQL, which will make this exception a
    758▕         // lot more helpful to the developer instead of just the database's errors.
    759▕         catch (Exception $e) {
  ➜ 760▕             throw new QueryException(
    761▕                 $query, $this->prepareBindings($bindings), $e
    762▕             );
    763▕         }
    764▕     }

  1   E:\wwwroot\object\vendor\laravel\framework\src\Illuminate\Database\Connection.php:545
      PDOException::("SQLSTATE[HY000]: General error: 1366 Incorrect DECIMAL value: '0' for column '' at row -1")

  2   E:\wwwroot\object\vendor\laravel\framework\src\Illuminate\Database\Connection.php:545
      PDOStatement::execute()
PS E:\wwwroot\object>

2、将报错的 SQL 复制到 Navicat for MySQL 中执行，仍然报错：1366 – Incorrect DECIMAL value: ‘0’ for column ” at row -1。如图2

图2

3、查看迁移文件的代码实现

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('table', function (Blueprint $table) {
            $table->decimal('stock_out_sort', 36, 0)->default(0)->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('table', function (Blueprint $table) {
            $table->string('stock_out_sort', 50)->default('')->change();
        });
    }
};

4、仔细检查表中的现有数据，发现字段 stock_out_sort 存在值：空字符串。如图3

图3

5、将值：空字符串 手动修改为：99801708945158。然后再次执行数据库迁移，迁移文件执行成功，不再报错。如图4

图4