mysql序列化后存数组,更新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.

Is it possible to update this URL without first unserialising?

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);

Could not update UPDATE config SET array = replace('array', 'http://www.oldurl.com', 'http://www.newurl.com') as variable supplied must be an 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}');";

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

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.