如何将Doctrine类型从’array’转换为在MySQL数据库中的’json’类型?

huangapple go评论53阅读模式
英文:

How to convert Doctrine type='array' to Doctrine type='json' in a MySQL database?

问题

我有一个使用Doctrine生成列的数据库。其中一个列使用type='array'。我想将其转换为type='json'。如何使用MySQL查询来实现这个目标?

我尝试了以下MySQL命令:

ALTER TABLE user_parameter ADD ma_variable_tmp JSON NOT NULL;*/
UPDATE user_parameter SET ma_variable_tmp = CAST(pharmaciedevdata.user_parameter.value AS JSON) WHERE id>=1;
ALTER TABLE user_parameter DROP ma_variable;
ALTER TABLE user_parameter RENAME COLUMN ma_variable_tmp TO value;

我收到以下响应:
错误代码: 3141。函数cast_as_json的参数1中的JSON文本无效: "无效的值",位置0。 0.000秒

英文:

I have a database that uses doctrine to generate columns. One columns is using the type='array'. I would like to convert it to a type='json'. How can I do this using MySQL query?

I tried the following MySQL command:

ALTER TABLE user_parameter ADD ma_variable_tmp JSON NOT NULL;*/
UPDATE user_parameter SET ma_variable_tmp = CAST(pharmaciedevdata.user_parameter.value AS JSON) WHERE id>=1;
ALTER TABLE user_parameter DROP ma_variable;
ALTER TABLE user_parameter RENAME COLUMN ma_variable_tmp TO value;

And I got this response:
Error Code: 3141. Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0. 0.000 sec

答案1

得分: 1

以下是您提供的代码的中文翻译:

根据 @A.L. 的想法,我已经完成了这段代码。这个解决方案主要使用了 PHP,可以使用 Doctrine 迁移来完成。

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * 自动生成的迁移:请根据您的需求进行修改!
 */
final class Version20230622200546 extends AbstractMigration
{
    public function getDescription(): string
    {
        return '';
    }

    public function up(Schema $schema): void
    {
        $connection = $this->connection;

        // 从表中获取现有的行
        $rows = $connection->fetchAll('SELECT * FROM user_parameter');

        // 遍历每一行
        foreach ($rows as $row) {
            $id = $row['id'];
            $data = $row['value'];

            // 首先反序列化数据,因为类型为数组的数据是序列化数据
            $encodedData = unserialize($data);

            // 使用 json_encode 对数据进行编码
            $encodedData = json_encode($encodedData);

            $connection->executeUpdate('UPDATE user_parameter SET value = :encodedData WHERE id = :id', [
                'encodedData' => $encodedData,
                'id' => $id,
            ]);
        }
    }

    public function down(Schema $schema): void
    {}
}

请注意,代码中的注释也已经被翻译。

英文:

Following @A.L. idea, I've end up we this code. The solutions uses, mostly PHP and can be done using Doctrine Migration.

&lt;?php
    
    declare(strict_types=1);
    
    namespace DoctrineMigrations;
    
    use Doctrine\DBAL\Schema\Schema;
    use Doctrine\Migrations\AbstractMigration;
    
    /**
     * Auto-generated Migration: Please modify to your needs!
     */
    final class Version20230622200546 extends AbstractMigration
    {
        public function getDescription(): string
        {
            return &#39;&#39;;
        }
    
        public function up(Schema $schema): void
        {
            $connection = $this-&gt;connection;
    
            // Fetch the existing rows from the table
            $rows = $connection-&gt;fetchAll(&#39;SELECT * FROM user_parameter&#39;);
    
            // Iterate over each row
            foreach ($rows as $row) {
                $id = $row[&#39;id&#39;];
                $data = $row[&#39;value&#39;];
    
//Unserialize data first since type array is serialize data
                $encodedData=unserialize($data);
    
    
                // Update the row with the new encoded data
                $encodedData = json_encode($encodedData); // Encode the data using json_encode
    
    
    
                $connection-&gt;executeUpdate(&#39;UPDATE user_parameter SET value = :encodedData WHERE id = :id&#39;, [
                    &#39;encodedData&#39; =&gt; $encodedData,
                    &#39;id&#39; =&gt; $id,
                ]);
            }
        }
    
        public function down(Schema $schema): void
        {}
    }

huangapple
  • 本文由 发表于 2023年6月21日 23:54:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76525126.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定