hyperf 二十七数据迁移三

lsswear

已于 2024-03-15 09:42:23 修改

阅读量911

点赞数 23

分类专栏： php 文章标签： php hyperf

于 2024-03-15 01:00:00 首次发布

本文链接：https://blog.csdn.net/lsswear/article/details/136703602

版权

php 专栏收录该内容

71 篇文章 3 订阅

订阅专栏

教程：Hyperf

之前文章:

hyperf 二十五数据迁移一-CSDN博客

hyperf 二十六数据迁移二-CSDN博客

一索引

1.1 创建索引

#定义索引
$table->string('name')->unique();

$table->string('name')->unique()中unique()方法仅定义Hyperf\Utils\Fluent类属性。

以上内容，意思为先常见字段，再创建索引。其中unique()若设置参数为索引名。

即不用再调用$table->unique('name','index_name')。

这俩仅是创建索引的不同方式，一起调用会报错。

1.2 唯一索引


#创建索引
$table->unique('name','index_name');
# 创建一个复合索引
$table->index(['account_id', 'created_at'], 'index_account_id_and_created_at');

$table->unique($columns, $name = null, $algorithm = null);执行Hyperf\Database\Schema\Blueprint::unique()。Blueprint::unique()执行Hyperf\Database\Schema\Grammars\Grammar子类的compileUnique()函数拼接成sql字符串。

Blueprint::unique()中name参数是索引的名字。

$table->string('name')->unique()详见：hyperf 二十六数据迁移二-CSDN博客中测试字段部分。

$table->index()调用Blueprint::index($columns, $name = null, $algorithm = null)，Grammar子类的compileIndex()。

compileUnique()、compileIndex()实际上仅在Hyperf\Database\Schema\Grammars\MySqlGrammar中存在。

Blueprint::index()中$columns是对应索引的字段，$name是索引的名字。这些参数将作为Fluent类的属性。

可用索引类型

命令	描述
$table->primary('id');	添加主键
$table->primary(['id', 'parent_id']);	添加复合键
$table->unique('email');	添加唯一索引
$table->index('state');	添加普通索引
$table->spatialIndex('location');	添加空间索引

执行原理和Blueprint::unique()差不多。

1.3 重命名索引

$table->renameIndex('from', 'to');

执行Blueprint::unique()，执行MySqlGrammar::compileRenameIndex()函数拼接的sql字符串。

1.4 删除索引

命令	描述
$table->dropPrimary('users_id_primary');	从 users 表中删除主键
$table->dropUnique('users_email_unique');	从 users 表中删除唯一索引
$table->dropIndex('geo_state_index');	从 geo 表中删除基本索引
$table->dropSpatialIndex('geo_location_spatialindex');	从 geo 表中删除空间索引

也可以通过传递字段数组到 dropIndex 方法，迁移程序会根据表名、字段和键类型生成的索引名称。

Schema:table('users', function (Blueprint $table) {
    $table->dropIndex(['account_id', 'created_at']);
});

源码

#Hyperf\Database\Schema\Blueprint
public function dropIndex($index) {
        return $this->dropIndexCommand('dropIndex', 'index', $index);
    }
protected function dropIndexCommand($command, $type, $index) {
        $columns = [];

        // If the given "index" is actually an array of columns, the developer means
        // to drop an index merely by specifying the columns involved without the
        // conventional name, so we will build the index name from the columns.
        if (is_array($index)) {
            $index = $this->createIndexName($type, $columns = $index);
        }

        return $this->indexCommand($command, $columns, $index);
    }
protected function createIndexName($type, array $columns) {
        $index = strtolower($this->prefix . $this->table . '_' . implode('_', $columns) . '_' . $type);

        return str_replace(['-', '.'], '_', $index);
    }

以官方的表名users、参数['account_id', 'created_at']为例，系统自动生成的键名为前缀加“users_account_id_created_at_index”。

1.5 外键约束

1.5.1 创建

#创建数据库层的外键约束
Schema::table('posts', function (Blueprint $table) {
    $table->unsignedInteger('user_id');

    $table->foreign('user_id')->references('id')->on('users');
});

根据官网例子，先创建posts表字段user_id。再将user_id设置为外键，关联users表id。

$table->foreign()返回Fluent类对象，设置其属性references=id、on=users。

再执行Grammar::compileForeign()拼接sql字符串

源码

#Hyperf\Database\Schema\Grammars\Grammar
public function compileForeign(Blueprint $blueprint, Fluent $command) {
        // We need to prepare several of the elements of the foreign key definition
        // before we can create the SQL, such as wrapping the tables and convert
        // an array of columns to comma-delimited strings for the SQL queries.
        $sql = sprintf(
            'alter table %s add constraint %s ',
            $this->wrapTable($blueprint),
            $this->wrap($command->index)
        );

        // Once we have the initial portion of the SQL statement we will add on the
        // key name, table name, and referenced columns. These will complete the
        // main portion of the SQL statement and this SQL will almost be done.
        $sql .= sprintf(
            'foreign key (%s) references %s (%s)',
            $this->columnize($command->columns),
            $this->wrapTable($command->on),
            $this->columnize((array) $command->references)
        );

        // Once we have the basic foreign key creation statement constructed we can
        // build out the syntax for what should happen on an update or delete of
        // the affected columns, which will get something like "cascade", etc.
        if (!is_null($command->onDelete)) {
            $sql .= " on delete {$command->onDelete}";
        }

        if (!is_null($command->onUpdate)) {
            $sql .= " on update {$command->onUpdate}";
        }

        return $sql;
    }

1.5.2 执行所需操作

#指定所需的操作
$table->foreign('user_id')
      ->references('id')->on('users')
      ->onDelete('cascade');

像references()、on()都是调用Fluent::__call()返回$this,所以可以一直调用。如官网例子，继续调用onDelete()设置Fluent::onDelete="cascade"。

如源码所示，可以使用Fluent::onDelete()和Fluent::onUpdate()，其参数为对应的关联表名。

1.5.3 删除外键

#删除外键
$table->dropForeign('posts_user_id_foreign');
$table->dropForeign(['user_id'']);

例子中posts_user_id_foreign是外键的名字，若为数组则会系统生成外键名。字段名近是生成外键名用。

执行MySqlGrammar::compileDropForeign()拼接的sql字符串。

源码

#Hyperf\Database\Schema\Blueprint
public function dropForeign($index) {
        return $this->dropIndexCommand('dropForeign', 'foreign', $index);
    }
protected function dropIndexCommand($command, $type, $index) {
        $columns = [];

        // If the given "index" is actually an array of columns, the developer means
        // to drop an index merely by specifying the columns involved without the
        // conventional name, so we will build the index name from the columns.
        if (is_array($index)) {
            $index = $this->createIndexName($type, $columns = $index);
        }

        return $this->indexCommand($command, $columns, $index);
    }

注意：删除约束要和创建的方法相同，比如创建用index删除也用index，否则会报没有应删除的索引。

1.5.4 约束控制

// 开启外键约束
Schema::enableForeignKeyConstraints();
// 禁用外键约束
Schema::disableForeignKeyConstraints();

执行对应驱动的父类Hyperf\Database\Schema\Builder中的方法，在调用其驱动对应的grammar类。

比如myql驱动，Schema::disableForeignKeyConstraints()调用Hyperf\Database\Schema\Grammars\MySqlGrammar::compileDisableForeignKeyConstraints()。

源码

#Hyperf\Database\Schema\Builder
public function enableForeignKeyConstraints()
    {
        return $this->connection->statement(
            $this->grammar->compileEnableForeignKeyConstraints()
        );
    }
public function disableForeignKeyConstraints()
    {
        return $this->connection->statement(
            $this->grammar->compileDisableForeignKeyConstraints()
        );
    }

#Hyperf\Database\Schema\Grammars\MySqlGrammar
public function compileEnableForeignKeyConstraints() {
        return 'SET FOREIGN_KEY_CHECKS=1;';
    } 
public function compileDisableForeignKeyConstraints() {
        return 'SET FOREIGN_KEY_CHECKS=0;';
    }

二测试

2.1 创建迁移文件

php bin/hyperf.php gen:migration update_userinfo_table
php bin/hyperf.php gen:migration create_articles_table

创建userinfo修改的迁移文件时，没用--table或者--create设置表名则报错。

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name '' in /wj/hyperf/hyperfpro2/vendor/hyperf/database/src/Connection.php:360

因为产生的文件中没有表名。

Schema::table('', function (Blueprint $table) {
        });

#migrations\2024_03_14_073756_update_userinfo_table.php
class UpdateUserinfoTable extends Migration {
    protected $connection = 'default2';
    /**
     * Run the migrations.
     */
    public function up(): void {
        Schema::table('userinfo', function (Blueprint $table) {
            $table->string('name')->unique();
            $table->unique('name');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void {
        Schema::table('userinfo', function (Blueprint $table) {
            $table->dropIndex('name');
        });
    }
}

#migrations\2024_03_14_073908_create_articles_table.php
class CreateArticlesTable extends Migration {
    protected $connection = 'default2';
    /**
     * Run the migrations.
     */
    public function up(): void {
        Schema::create('articles', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('userinfo');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void {
        Schema::dropIfExists('articles');
    }
}

注意： unsignedBigInteger()和bigIncrements()区别在于bigIncrements()会设置自增。用bigIncrements()设置外键会导致系统报错。

2.2 执行迁移

 php bin/hyperf.php migrate --database=default2

报错

Migrating: 2024_03_14_073756_update_userinfo_table
PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes in /wj/hyperf/hyperfpro2/vendor/hyperf/database/src/Connection.php:366

经过调试，执行sql语句如下

alter table `userinfo` add unique `userinfo_name_unique`(`name`)

用客户端实际执行也会报"Specified key was too long; max key length is 1000 bytes"。

所以需要改下索引名。我用的是mysql，user表为MyISAM引擎，改为InnoDB引擎后再操作

本来根据文档，以为需要先定义再设置，但是以下两句一起运行会报错。

$table->string('name')->unique();
$table->unique('name');

报错内容

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'userinfo_name_unique' in /wj/hyperf/hyperfpro2/vendor/hyperf/database/src/Connection.php:366

因为alter table `userinfo` add unique `userinfo_name_unique`(`name`)被执行两次。

修改后再次执行

#migrations\2024_03_14_073756_update_userinfo_table.php
class UpdateUserinfoTable extends Migration {
    protected $connection = 'default2';
    /**
     * Run the migrations.
     */
    public function up(): void {
        Schema::table('userinfo', function (Blueprint $table) {
            $table->string('name')->unique();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void {
        Schema::table('userinfo', function (Blueprint $table) {
            $table->dropUnique(['name']);
            $table->dropColumn('name');
        });
    }
}

执行成功

php bin/hyperf.php  migrate --database=default2

Migrating: 2024_03_14_073756_update_userinfo_table
Migrated:  2024_03_14_073756_update_userinfo_table
Migrating: 2024_03_14_073908_create_articles_table
Migrated:  2024_03_14_073908_create_articles_table

若外键和对应字段类型不同，则回报错：Cannot add foreign key constraint。需要调整成字段类型一样。

show create table userinfo
CREATE TABLE `userinfo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userinfo_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci\


show create table articles
CREATE TABLE `articles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `articles_user_id_foreign` (`user_id`),
  CONSTRAINT `articles_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `userinfo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

2.3 执行回滚

 php bin/hyperf.php migrate:rollback --database=default2 --step=2

执行成功

show create table userinfo
CREATE TABLE `userinfo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


SHOW TABLES LIKE 'articles'
null