时间戳

首页 > 解决方案 > 错误：SQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误；

php - 错误：SQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误；

问题描述

我想使用 laravel 迁移我的数据库。
我尝试这个命令：php artisan migrate:fresh --seed并接受这个错误： 

Migrating: 2019_10_28_130723_alter_users_table

   Illuminate\Database\QueryException  : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB ser
ver version for the right syntax to use near 'CHARACTER SET utf8mb4 DEFAULT 0 NOT NULL COLLATE `utf8mb4_unicode_ci`' at line 1 (SQL: ALTER TABLE users CHANGE deductible_amount deductible_a
mount BIGINT UNSIGNED CHARACTER SET utf8mb4 DEFAULT 0 NOT NULL COLLATE `utf8mb4_unicode_ci`)

  at C:\xampp\htdocs\project\vendor\laravel\framework\src\Illuminate\Database\Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

  Exception trace:

  1   Doctrine\DBAL\Driver\PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB s
erver version for the right syntax to use near 'CHARACTER SET utf8mb4 DEFAULT 0 NOT NULL COLLATE `utf8mb4_unicode_ci`' at line 1")
      C:\xampp\htdocs\project\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOConnection.php:63

  2   PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the
 right syntax to use near 'CHARACTER SET utf8mb4 DEFAULT 0 NOT NULL COLLATE `utf8mb4_unicode_ci`' at line 1")
      C:\xampp\htdocs\project\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOConnection.php:61

  Please use the argument -v to see more details.

这是我的2019_10_28_130723_alter_users_table迁移文件的内容：

<?php

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

class AlterUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('active', 'is_active');
            $table->dropColumn([
                                    'settings',
                                    'google_id',
                                    'github_id',
                                    'telegram_notif',
                                    'income',
                                    'email_notif',
                                    'sms_notif',
                                    'temppass',
                                    'storm_id',
                               ]);
            $table->unsignedBigInteger('deductible_amount')->change();
            $table->softDeletes();
        });
    }



    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('is_active', 'active');
            $table->text('settings');
            $table->string('google_id')->nullable();
            $table->string('github_id')->nullable();
            $table->boolean('telegram_notif')->default(true);
            $table->string('income')->nullable();
            $table->boolean('email_notif')->default(true);
            $table->boolean('sms_notif')->default(false);
            $table->string('temppass')->nullable();
            $table->string('storm_id')->default(null)->nullable();
            $table->string('deductible_amount')->after('wallet')->default('0')->change();
            $table->dropSoftDeletes();
        });
    }
}

我不知道如何解决这个问题。我已经搜索过了，但大多数答案都提到了一个json必须转换为的字段，我的桌子上string没有这个字段。问题出在哪里，我该如何解决？jsonusers

标签： phpmysqldatabaselaravelmariadb

解决方案

此行会导致您的迁移出错。

$table->unsignedBigInteger('deductible_amount')->change();

尝试将其更改为

$table->string('deductible_amount')->unsigned()->change();

针对您的情况更新了答案。

        Schema::table('users', function (Blueprint $table) {
            $table->unsignedBigInteger('deductible_amount_new');
        });

        DB::statement('UPDATE `users` SET deductible_amount_new=deductible_amount ');

        Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('active', 'is_active');
            $table->dropColumn([
                                    'settings',
                                    'google_id',
                                    'github_id',
                                    'telegram_notif',
                                    'income',
                                    'email_notif',
                                    'sms_notif',
                                    'temppass',
                                    'storm_id',
                               ]);
            $table->dropColumn('deductible_amount');
            $table->softDeletes();
        });

        Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('deductible_amount_new', 'deductible_amount');
        });

我希望它可以帮助。

推荐阅读