php - 如何在整个mysql数据库php中替换字符串

This question already has answers here:

Find and replace entire mysql database
                                
                                    （11个答案）
                                
                        
                        
                            Replace all fields in MySQL
                                
                                    （4个答案）
                                
                        
                                去年关闭。
            
                    
我有一个php网站，该图像文件以前曾存储在/ uploads /文件夹中，现在我已经创建了一个新文件夹/ files1 /，并且图像存储在新文件夹中，以前的图像也已移至新文件夹中。但是数据库中以前的图像链接仍然包含旧路径www.example.com/uploads/text.png，有什么方法可以在整个数据库中使用mysql，php将“ / uploads /”替换为“ / files1 /”吗？

谢谢

最佳答案

如果您不反对在数据库上创建和运行stored procedure，则可以完全不用使用PHP来完成它。

create procedure `speditalltables`()
begin
    declare _strsql varchar(512);
    declare _table varchar(128);
    declare done int default false;
    declare res integer default 0;

    declare _cursor cursor for select `table_name` as 'name'
            from `information_schema`.`tables`
            where `table_type`='base table' and `table_schema`=database();

    declare continue handler for not found set done=true;



    open _cursor;

        repeat

        fetch _cursor into _table;

        if not done then


            /* different column names ~ edit as appropriate */
            set @strsql=concat("update `",database(),"`.`",_table,"`
                set
                `filepath`=replace( `filepath`, '/uploads/', '/files1/' ),
                `filepath2`=replace( `filepath`, '/uploads/', '/files1/' ),
                `filepath3`=replace( `filepath`, '/uploads/', '/files1/' ),
                `filepath4`=replace( `filepath`, '/uploads/', '/files1/' )");

            prepare stmt from @strsql;
            execute stmt;
            deallocate prepare stmt;


        end if;


        until done end repeat;

    close _cursor;

    select 'finished' as 'result';

    set done=null;
    set @res=null;
    set @_table=null;
end