Laravel Migration SQLSTATE[42000]： 语法错误或访问冲突： 1064

Question

对于一个非常旧的迁移（过去运行正常），我收到一个新的迁移错误。

我得到的错误是：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8 NOT NULL COLLATE `utf8_unicode_ci`' at line 1 (SQL: ALTER TABLE rooms CHANGE conversion conversion TINYINT(1) CHARACTER SET utf8 NOT NULL COLLATE `utf8_unicode_ci`)

迁移文件如下所示：

<?php

use Illuminate\Support\Facades\Schema;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Database\Migrations\Migration;

class ChangeRoomsConversionToBoolean extends Migration

{

    /**

     * Run the migrations.

     *

     * @return void

     */

    public function up()

    {

        Schema::table('rooms', function (Blueprint $table) {

            $table->boolean('conversion')->change();

        });

    }

    /**

     * Reverse the migrations.

     *

     * @return void

     */

    public function down()

    {

        Schema::table('rooms', function (Blueprint $table) {

            $table->string('conversion')->change();

        });

    }

}

如果我直接在数据库中运行查询，则会出现错误：ALTER TABLE rooms CHANGE conversion conversion TINYINT(1) CHARACTER SET utf8 NOT NULL COLLATE `utf8_unicode_ci`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8 NOT NULL' at line 1

我与Laravel 5.6一起在Homestead上运行。

任何帮助将不胜感激。

Answer 1

我相信Laravel如何配置DBAL存在一些问题;但是，我认为以下内容将解决您的问题：&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* Run the migrations.&nbsp; &nbsp; &nbsp;*&nbsp; &nbsp; &nbsp;* @return void&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; public function up()&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; Schema::table('rooms', function (Blueprint $table) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $table->boolean('conversion')->charset(null)->collation(null)->change();&nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; }&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* Reverse the migrations.&nbsp; &nbsp; &nbsp;*&nbsp; &nbsp; &nbsp;* @return void&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; public function down()&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; Schema::table('rooms', function (Blueprint $table) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $table->string('conversion')->change();&nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; }我基于查看 的源代码来得出这个答案。您可以在此处看到，在您的实例中，您不希望为 bigint 指定字符集或排序规则。为了跳过这两个选项，我认为唯一的解决方案是手动将这两个值设置为 null。下面是形成MySQL查询该部分的源代码：framework/src/Illuminate/Database/Schema/Grammars/MySqlGrammar.php&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* Append the character set specifications to a command.&nbsp; &nbsp; &nbsp;*&nbsp; &nbsp; &nbsp;* @param&nbsp; string&nbsp; $sql&nbsp; &nbsp; &nbsp;* @param&nbsp; \Illuminate\Database\Connection&nbsp; $connection&nbsp; &nbsp; &nbsp;* @param&nbsp; \Illuminate\Database\Schema\Blueprint&nbsp; $blueprint&nbsp; &nbsp; &nbsp;* @return string&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; protected function compileCreateEncoding($sql, Connection $connection, Blueprint $blueprint)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; // First we will set the character set if one has been set on either the create&nbsp; &nbsp; &nbsp; &nbsp; // blueprint itself or on the root configuration for the connection that the&nbsp; &nbsp; &nbsp; &nbsp; // table is being created on. We will add these to the create table query.&nbsp; &nbsp; &nbsp; &nbsp; if (isset($blueprint->charset)) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $sql .= ' default character set '.$blueprint->charset;&nbsp; &nbsp; &nbsp; &nbsp; } elseif (! is_null($charset = $connection->getConfig('charset'))) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $sql .= ' default character set '.$charset;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; // Next we will add the collation to the create table statement if one has been&nbsp; &nbsp; &nbsp; &nbsp; // added to either this create table blueprint or the configuration for this&nbsp; &nbsp; &nbsp; &nbsp; // connection that the query is targeting. We'll add it to this SQL query.&nbsp; &nbsp; &nbsp; &nbsp; if (isset($blueprint->collation)) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $sql .= " collate '{$blueprint->collation}'";&nbsp; &nbsp; &nbsp; &nbsp; } elseif (! is_null($collation = $connection->getConfig('collation'))) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $sql .= " collate '{$collation}'";&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return $sql;&nbsp; &nbsp; }