更新mysql中的序列化数组(不反序列化吗?) [英] Updating serialised array in mysql (without unserialising?)

问题描述

我读过的所有内容都说在Mysql中存储序列化数组是一个坏主意-我已经知道了；)不幸的是，我正在使用使用此方法的开源脚本，并且更改结构不是一种选择在这种情况下.

Everything I've read says that storing serialised arrays in Mysql is a bad idea - I know that already ;) Unfortunately I'm working with an open source script that uses this method, and changing the structure isn't an option in this scenario.

是否可以在不首先进行反序列化的情况下更新此URL?

Is it possible to update this URL without first unserialising?

我最初尝试使用replace，但是会引发错误:

I originally tried using replace, however it throws an error:

  $rssquery = "UPDATE config SET `array` = replace(`array`, \"http://www.oldurl.com\", \"http://www.newurl.com\") WHERE name='config'";
  $insert = $db->insert($rssquery);

无法更新UPDATE config SET array = replace('array'，' http://www.oldurl.com '，' http://www.newurl.com ')，因为提供的变量必须是数组.

Could not update UPDATE config SET array = replace('array', 'http://www.oldurl.com', 'http://www.newurl.com') as variable supplied must be an array.

表名称:config
列:名称|数组
行需要更新的名称:config
需要更新的单元格名为:array

Table name: config
Columns: name | array
Row Needing Updated named: config
Cell Needing Updated named: array

任何其他想法或方法将不胜感激:)谢谢！

Any other ideas or approaches would be appreciated :) Thanks!

推荐答案

如果只是简单地进行搜索和替换，则将使序列化的数据变得不稳定.这是您需要做的:

If you simply search and replace like that, you will render the serialized data ununsable. Here's what you need to do:

$old = 'http://www.google.com';
$new = 'http://www.someplace.com';

$search = 's:' . strlen($old) .':"' . $old . '"';
$replace = 's:' . strlen($new) .':"' . $new . '"';

$query = "UPDATE config SET array=REPLACE(array, '{$search}', '{$replace}');";

用当前和目标URL替换$old和$new，运行脚本并执行生成的$query.

Replace $old and $new with your current and target url's, run the script and execute the generated $query.

这是一个纯SQL解决方案:

Here's a pure SQL solution:

SET @search := 'http://www.original.com';
SET @replace := 'http://www.target.com';
UPDATE config SET array=REPLACE(array, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));

请注意，这将替换序列化数组中每次出现的搜索字符串.如果您要替换特定的密钥，则必须更多，更具体.

Note that this will replace EVERY occurrence of the search string in your serialized array. If you are looking to replace a specific key, you have to be more, huh, specific.

这篇关于更新mysql中的序列化数组(不反序列化吗?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！