mysql - 反转表列中的 CSV 字符串

Question

我在表中有一列是 varchar(100)，其中包含 CSV 格式的单个字符串。仅在 1->100 范围内的数字。例如

66,61,47
64
91,82,86,78,88,4,2,15,87,3
69,71,70,97,3,45

顺序与应有的顺序相反。例如

47,61,66
64
3,87,15,2,4,88,78,86,82,91
etc

我知道 MySQL 中有一个 REVERSE 操作数，但它会反转数字。例如

REVERSE(47,61,66) = 66,16,74

如果数字限制在 0->9 的数值范围内，它就会起作用。不在这里。

所以我想知道 MySQL 是否具有与 PHP 的 explode/implode 和 array_reverse() 等价的功能，我认为我必须依靠这些功能？

谢谢!

Answer 1

就像 Gordon 提到的那样，我建议对表格进行规范化。如果那不可能，您可以使用以下存储过程的再现来实现这一目的。

我已经创建了一个测试表:

create table test (field1 varchar(100));
-- inserted some data
select * from test
+----------------------------+
| field1                     |
+----------------------------+
| 66,61,47                   |
| 91,82,86,78,88,4,2,15,87,3 |
| 64                         |
| 69,71,70,97,3,45           |
+----------------------------+

目标是在说完之后得到相反的顺序。

proc.sql

此过程的目标是执行以下操作

• 提取第一行66,61,47
• 修改该行，使其看起来像 (66),(61),(47)
• 创建一个临时表以按顺序保存这些值
• 像这样创建一个插入语句:insert into temptable (field1) values (66), (61), (47)
• 以相反的顺序提取这些数据。使用 group_concat 将数据输出为 47,61,66
• 用新收到的数据更新表

程序是这样的

delimiter //

drop procedure if exists reverse_data//

create procedure reverse_data()
begin

    -- holds modified field1_data and original data
    declare field1_data varchar(100);
    declare field1_orig_data varchar(100);
    declare finished int default 0;

    -- if data was '1,2,3', field1_data will be
    -- (1),(2),(3) so that it can be inserted into
    -- temptable
    declare cursor_csv cursor for
    select
        concat(
            '(',
            replace(field1, ',', '),('),
            ')') as field_data,
        field1
    from test;

    declare continue handler for
    not found set finished = 1;

    -- create a table that will hold ID and each individually
    -- inserted data from the CSV field
    create table if not exists temptable (
        id int auto_increment primary key,
        field1 int
    );
    truncate table temptable;

    open cursor_csv;

    fetcher: loop
        fetch cursor_csv into field1_data, field1_orig_data;
        if finished = 1 then
            leave fetcher;
        end if;

        -- insert data into the temporary table
        set @sql = concat('insert into temptable(field1) values ', field1_data);
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

        -- extract data from temptable ensuring that reverse order
        -- is selected and group concatenation is done with comma separator
        select group_concat(field1 order by id desc separator ',') into @newdata
        from temptable;

        -- update the table with reverse ordered data
        update test
        set field1 = @newdata
        where field1 = field1_orig_data;

        -- truncate the table so that clean data can be inserted
        truncate table temptable;
    end loop fetcher;

    close cursor_csv;

    -- remove the temporary table
    drop table temptable;

end//

delimiter ;

调用过程:

call reverse_data();

结果:

select * from test
+----------------------------+
| field1                     |
+----------------------------+
| 47,61,66                   |
| 3,87,15,2,4,88,78,86,82,91 |
| 64                         |
| 45,3,97,70,71,69           |
+----------------------------+

如果它不起作用，请创建一个名为 templogger 的表，其中包含一个名为 field1 varchar(500) 的字段。在存储过程的战略位置，将有关填充变量的信息插入到 templogger 中。