deserialize and serialize data in mysql – Woocommerce

I have been struggling with a similar issue: Deserialising and serialising data from table wp_postmeta, to do search-and-replace on certain parts of it (I’m translating WooCommerce-sites and I would prefer to do it all within MySQL, rather than using PHP). In my case, the search-and-replace always happened on the fourth s-element. I developed a sproc for this. It probably doesn’t suit your exact use case, but maybe it’s close enough for adapting it to your needs:

CREATE DEFINER=`strompf`@`localhost` PROCEDURE `replace_serial`(
        in table_name_in            tinytext,   # Source table name
        in col_serial_data_src_in   tinytext,   # Column with source serialised data
        in col_serial_data_dst_in   tinytext,   # Column with destination serialised data
        in col_src_in               tinytext,   # Column with source text strings
        in col_dst_in               tinytext    # Column with desination text string
    )
BEGIN
#
# Find-and-replace in element s4 within serialised content
################################################################################
#
# * This sproc "replace_serial" needs as input pointers to a table 
#   ("pointer table") with the following columns:
#   * Column with the source serialised data
#   * Column for storing the updated serialised data
#   * Column with find-string
#   * Column with replace-strings
# * It can be any table: The arguments for replace_serial() contains the name
#   of this table and the names of those four columns
# * Internally, data from this input-table is copied to temporary table 
#   "serial_tmp"
# * The designated serialised data is decomposed into several fields (again,
#   in table "serial_tmp") - Currently, this sproc only works for serialised 
#   data with 1 a-element, followed by 6 s-elements. This sproc has not been 
#   tested with other configurations
# * Find-and-replace is only performed on element s4 (=the 4th s-element)
# * Subsequently, the new length of s4 is calculated, and the serialised string
#   is written to the designated column in the "pointer table"
# * Subsequently, it's up to the programmer to process the resulting data in
#   the pointer table
# * This approach with a "pointer table" works really well: Easy to grasp,
#   flexible and easy to debug. It's especially handy for debugging or further
#   processing that the output is in a separate column from the input.
# * Copyright Jeroen Strompf - De Vliegende Brigade - 2021.03.09
#
#
# History
########################################
#
# * 2021.03.09: Created, tested and pushed to "bal_dwh_org"
#
#
################################################################################
# Define working table "serial_tmp"
################################################################################
#
drop table if exists 
    serial_tmp;
create temporary table
    serial_tmp
    (
        serial_data_src     varchar(255)    comment "Actual serialised data",   
        serial_data_dst     varchar(255)    comment "Resulting serialised data. This is a separate field, to make debugging easier",
        find_string         varchar(255)    comment "String to be found within serial_data_src",
        replace_string      varchar(255)    comment "Replacement string for within serial_data_src, resulting in serial_data_dst",

        a_len           smallint,
        a_string        varchar(255)    comment "The string within the accolade",
        s1              varchar(255)    comment "Complete s-part, including length statement and string",
        s2              varchar(255),
        s3              varchar(255),
        s4              varchar(255)    comment "Complete s4-string",
        s4_len_src      smallint        comment "Original length of s4",
        s4_string_src   varchar(255)    comment "Original s4 string",
        s4_len_dst      smallint        comment "New length",
        s4_string_dst   varchar(255)    comment "New string",
        s5              varchar(255),
        s6              varchar(255)
    );


################################################################################
# Copy working data to working table
################################################################################
#
set @ddl=concat
(
    "insert into serial_tmp 
    (
        serial_data_src,
        find_string,
        replace_string
    )
    select ",
        col_serial_data_src_in, ", ",
        col_src_in, ", ",
        col_dst_in, " ",
    "from ",
        table_name_in, ";"
);

prepare stmt from @ddl;
execute stmt;
deallocate prepare stmt;


################################################################################
# Decompose serialsed data
################################################################################
#
# Leading a-field
########################################
#
update
    serial_tmp
set
    a_len       = substring_index(substring_index(serial_data_src, ":", 2), ":", -1),
    a_string    = substring_index(substring_index(serial_data_src, "}",1), "{", -1);


# Complete s-fields
########################################
#
update
    serial_tmp
set
    s1 = substring_index(a_string, ";", 1),
    s2 = substring_index(substring_index(a_string, ";", 2), ";", -1),
    s3 = substring_index(substring_index(a_string, ";", 3), ";", -1),
    s4 = substring_index(substring_index(a_string, ";", 4), ";", -1),
    s5 = substring_index(substring_index(a_string, ";", 5), ";", -1),
    s6 = substring_index(substring_index(a_string, ";", 6), ";", -1);


# Further decompose s4
########################################
#
update
    serial_tmp
set
    s4_len_src      = substring_index(substring_index(s4, ":", 2), ":", -1),
    s4_string_src   = substring_index(substring_index(s4, '"', 2), '"', -1);


################################################################################
# Find-and-replace
################################################################################
#
# Finally! The heart of the matter
#
update
    serial_tmp
set
    s4_string_dst   =   replace(s4_string_src, find_string, replace_string),
    s4_len_dst      =   length(s4_string_dst);


################################################################################
# Reassemble
################################################################################
#
update
    serial_tmp
set
    serial_data_dst = concat
    (
        "a:",a_len, ":",
        "{",
            s1, ";", s2, ";", s3, ";",
            "s:",s4_len_dst,":",
            char(34),
                s4_string_dst,
            char(34),
            ";", s5, ";", s6, ";",
        "}"
    );


################################################################################
# Copy results back to the original input table
################################################################################
#
# * Fill in the result in column "col_serial_data_dst_in"
#
set @ddl2=concat
(
    "update ", table_name_in, " 
    join 
       serial_tmp
       on
       serial_tmp.serial_data_src = ", table_name_in, ".",col_serial_data_src_in," 
    set
       ",table_name_in, ".",col_serial_data_dst_in, " = serial_tmp.serial_data_dst;"
);

prepare stmt from @ddl2;
execute stmt;
deallocate prepare stmt;

END