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`)迁移文件如下所示：<?phpuse 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存在一些问题;但是，我认为以下内容将解决您的问题：

    /**

     * Run the migrations.

     *

     * @return void

     */

    public function up()

    {

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

            $table->boolean('conversion')->charset(null)->collation(null)->change();

        });

    }

    /**

     * Reverse the migrations.

     *

     * @return void

     */

    public function down()

    {

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

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

        });

    }

我基于查看 的源代码来得出这个答案。您可以在此处看到，在您的实例中，您不希望为 bigint 指定字符集或排序规则。为了跳过这两个选项，我认为唯一的解决方案是手动将这两个值设置为 null。下面是形成MySQL查询该部分的源代码：framework/src/Illuminate/Database/Schema/Grammars/MySqlGrammar.php

    /**

     * Append the character set specifications to a command.

     *

     * @param  string  $sql

     * @param  \Illuminate\Database\Connection  $connection

     * @param  \Illuminate\Database\Schema\Blueprint  $blueprint

     * @return string

     */

    protected function compileCreateEncoding($sql, Connection $connection, Blueprint $blueprint)

    {

        // First we will set the character set if one has been set on either the create

        // blueprint itself or on the root configuration for the connection that the

        // table is being created on. We will add these to the create table query.

        if (isset($blueprint->charset)) {

            $sql .= ' default character set '.$blueprint->charset;

        } elseif (! is_null($charset = $connection->getConfig('charset'))) {

            $sql .= ' default character set '.$charset;

        }

        // Next we will add the collation to the create table statement if one has been

        // added to either this create table blueprint or the configuration for this

        // connection that the query is targeting. We'll add it to this SQL query.

        if (isset($blueprint->collation)) {

            $sql .= " collate '{$blueprint->collation}'";

        } elseif (! is_null($collation = $connection->getConfig('collation'))) {

            $sql .= " collate '{$collation}'";

        }

        return $sql;

    }