如何在MySQL数据库中将Doctrine type='array'转换为type='json'

错误原因 #

你执行CAST(value AS JSON)报错，是因为Doctrine的array类型存储的是PHP序列化后的字符串（比如a:1:{i:0;s:5:"test";}），并非标准JSON格式，MySQL无法将其直接识别为有效JSON，因此抛出Invalid JSON text错误。

正确转换步骤 #

因为MySQL没有内置解析PHP序列化字符串的函数，这里提供两种可行方案：

方案一：用PHP脚本处理（最可靠） #

直接连接数据库，反序列化原数据再转成JSON格式，最后修改列类型：

<?php
// 连接数据库
$pdo = new PDO('mysql:host=localhost;dbname=pharmaciedevdata', '你的用户名', '你的密码');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 遍历数据并转换
$stmt = $pdo->query('SELECT id, value FROM user_parameter');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $array = unserialize($row['value']);
    $json = json_encode($array);
    
    $updateStmt = $pdo->prepare('UPDATE user_parameter SET value = ? WHERE id = ?');
    $updateStmt->execute([$json, $row['id']]);
}

// 修改列类型为JSON
$pdo->exec('ALTER TABLE user_parameter MODIFY COLUMN value JSON NOT NULL');

方案二：MySQL自定义函数+SQL语句处理 #

如果必须用纯SQL处理，先创建一个解析简单PHP序列化数组的自定义函数（仅支持一维数组，复杂多维数组建议用方案一）：

DELIMITER //
CREATE FUNCTION php_serialize_to_json(serialized_str TEXT) RETURNS JSON
BEGIN
    DECLARE json_str TEXT;
    -- 替换数组标识
    SET json_str = REPLACE(REPLACE(serialized_str, 'a:', '['), '}', ']');
    -- 转换字符串元素格式
    SET json_str = REGEXP_REPLACE(json_str, 's:[0-9]+:"([^"]+)";', '"\\1",');
    -- 转换整数元素格式
    SET json_str = REGEXP_REPLACE(json_str, 'i:([0-9]+);', '\\1,');
    -- 清理末尾多余逗号
    SET json_str = REGEXP_REPLACE(json_str, ',$', '');
    RETURN CAST(json_str AS JSON);
END //
DELIMITER ;

然后执行转换流程：

1. 添加临时JSON列（先允许为空，避免转换失败）

ALTER TABLE user_parameter ADD ma_variable_tmp JSON;

2. 使用自定义函数转换数据

UPDATE user_parameter 
SET ma_variable_tmp = php_serialize_to_json(value) 
WHERE value IS NOT NULL;

3. 验证数据无误后替换原列

ALTER TABLE user_parameter DROP COLUMN value;
ALTER TABLE user_parameter RENAME COLUMN ma_variable_tmp TO value;
-- 按需设置NOT NULL约束
ALTER TABLE user_parameter MODIFY COLUMN value JSON NOT NULL;

注意：自定义函数仅支持简单一维数组，若数据包含多维数组、布尔值、NULL等复杂类型，用方案一能避免数据损坏。

内容的提问来源于stack exchange，提问作者BePi