Laravel将数据从一个表移动到另一个表并删除数据来自的列? [英] Laravel move data from one table to another and drop the column that the data came from?

问题描述

我正在尝试在Laravel迁移中执行以下过程:

I'm trying to do the following process in a Laravel migration:

创建一个名为client_log_partitions的新表. (完成)

Create a new table called client_log_partitions. (DONE)

$table->create();

$table->bigIncrements('id')->unique();
$table->bigInteger('client_log_id');
$table->mediumText('partition_data');

$table->timestamps();

---

• 我已经有一个名为client_logs的表，数据存储在名为log_data的bigText()列中.
• client_logs表中已经存在的每一行都需要每250 KB(或25万个字符，因为它应该是单字节编码)进行分割，然后作为参考插入到新的client_log_partitions表中，作为分区它所属的client_logs条目.

---

• I already have a table called client_logs with data stored in a bigText() column called log_data.
• Each row that already exists in the client_logs table needs to be split every 250 KB (or 250,000 characters since it should be single byte encoded) and then inserted into the new client_log_partitions table as partitions, with a reference to the client_logs entry it belongs to.

我知道我可以使用$table->postExecute()来实现这一目标，但我不知道该怎么做.

I know I may be able to achieve this using $table->postExecute(), I just don't know what I might use to do so.

将数据移动到新表后，我需要从client_logs删除log_data列.

Once the data is moved to the new table, I then need to drop the log_data column from client_logs.

通常，我会使用PHP脚本或类似的工具来执行此操作.但不幸的是，我在无法进行操作的情况下进行操作.

Normally, I would do this with a PHP script or something similar. But unfortunately I'm operating under circumstances where I'm unable to.

我的问题是，使用Laravel Migrations可以做到吗?如果可以，怎么办?

My question is, Is this doable using Laravel Migrations, and if so, how?

编辑:

尽管我不确定，因为我只是即时进行了测试，还没有对其进行测试，但是我想这是通过SQL来实现的，就像这样:

Although I'm unsure since i only whipped this up on the fly and haven't tested it, this is what I imagine the SQL for achieving this would look something like:

DROP PROCEDURE IF EXISTS PROCESS_LOG_DATA;
DROP PROCEDURE IF EXISTS PROCESS_LOG_ENTRIES;
DELIMITER ;;

## Procedure for processing a specific log entry
## and moving its data to the new table.
CREATE PROCEDURE PROCESS_LOG_DATA(log_id bigint, partition_size int)
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    SELECT LENGTH(log_data)/partition_size FROM client_logs where id=log_id INTO n;
    SET i=0;
    WHILE i<n DO

      # Move the characters to the new table.
      INSERT INTO client_log_partitions 
          (client_log_id, partition_data)
      SELECT (id, LEFT(log_data, partition_size))
      FROM client_logs 
      WHERE id=log_id

      # Shift the characters off of the log_data
      UPDATE client_logs
      SET log_data = SUBSTR(
          log_data,
          partition_size,
          LENGTH(log_data) - partition_size
      ) where id=log_id;

      # Update the number of data partitions we've processed for this log entry
      SET i = i + 1;
    END WHILE;
End;
;;


## Procedure for processing all log entries
## and passing each one to the PROCESS_LOG_DATA procedure.
CREATE PROCEDURE PROCESS_LOG_ENTRIES(partition_size int)
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    SELECT COUNT(*) FROM client_logs INTO n;
    SET i=0;
    WHILE i<n DO
        PROCESS_LOG_DATA(i, partition_size)
    END WHILE;
End;
;;

DELIMIETER ;

## Process the log entries.
CALL PROCESS_LOG_ENTRIES(250000);

推荐答案

您需要做的就是运行代码以移动信息并在放置列调用之前重新关联ID.这是因为up函数中的所有内容都是从上到下顺序运行的，而不是异步运行的. https://laravel.com/docs/5.8/migrations

All you should need to do is run your code to move the information and re-associate the ids before the drop column call. This is because everything in the up function is ran sequentially, from top to bottom, and not asynchronously. https://laravel.com/docs/5.8/migrations

您可以使用Eloquent和PHP来移动和更改信息.这样做可能比编写查询要容易.这就是我工作过的公司移动数据然后更改表的方式.有时，由于SoX合规性的愚蠢，我们不得不使用SQL脚本来移动数据，但是您不必担心听起来像是.

You could use Eloquent and PHP to move the information and alter it. Doing so would probably be easier than writing a query. This is how companies that I've worked at moved data and then altered tables. Sometimes, because of SoX compliance stupidity, we had to move data using SQL scripts but you shouldn't have to worry about that it sounds like.

class DropClientLogsLogDataColumn extends Migration
{

    public function up()
    {
        // move data
        $this->moveData()

        Schema::table('client_logs', function (Blueprint $table) {
          // drop the log_data column
          $table->dropColumn('log_data');
        });
    }

    private function moveData()
    {
      // do whatever you need to do here 
      // more than likely you'll use eloquent or query builder
      // this is where you'd associate the id's
    }
}

这篇关于Laravel将数据从一个表移动到另一个表并删除数据来自的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！