我想使用plpgsql来执行php序列化字符串或数组的序列化和非序列化。
有人能告诉我以前有没有人试过这个吗？
如果能链接到能够做到这一点的功能，我们将不胜感激！
我来派对有点晚了，但我创建了一个postgresql函数来完成这一任务：
https://gist.github.com/storeman/cecb10bab249f43562cddabc1d9dd7c9
/**
Decode a php serialized value to json. This function only supports basic
data types:
- arrays (will always become a json object)
- booleans
- integers
- floats
- strings
- NULL
The php_unserialize(text) function is a helper function which extracts the first value
found in the string and returns a ROW( vartype, varval, serializedlength)
The php_unserialize_to_json(text) function returns the json value extracted from
the serialized string.
Examples:
SELECT php_unserialize_to_json('a:5:{i:0;d:1;s:1:"k";a:3:{i:0;s:1:"a";i:1;s:1:"b";s:3:"sub";a:3:{i:0;s:1:"a";i:1;s:1:"b";i:2;s:1:"c";}}i:1;N;i:2;b:1;i:3;b:0;}')#>>'{k,sub,2}';
SELECT php_unserialize_to_json('s:8:"a string";');
SELECT php_unserialize_to_json('s:0:"";');
SELECT php_unserialize_to_json('i:1337;');
SELECT php_unserialize_to_json('d:1.234;');
SELECT php_unserialize_to_json('b:1;')::TEXT;
SELECT php_unserialize_to_json('b:0;')::TEXT;
SELECT php_unserialize_to_json('N;')::TEXT;
SELECT php_unserialize_to_json('a:0:{}')::TEXT;
SELECT php_unserialize_to_json('a:1:{i:0;s:5:"array";}')::TEXT;
SELECT php_unserialize_to_json('a:1:{i:0;i:1;}')::TEXT;
SELECT php_unserialize_to_json('a:2:{i:0;i:1;i:1;s:1:"b";}')::TEXT;
SELECT php_unserialize_to_json('a:2:{i:0;d:1;s:1:"k";s:1:"b";}')::TEXT;
SELECT php_unserialize_to_json('a:2:{i:0;d:1;s:1:"k";a:2:{i:0;s:1:"a";i:1;s:1:"b";}}')::TEXT;
*/
---
--- This function is the helper function
---
CREATE OR REPLACE FUNCTION php_unserialize(str text)
RETURNS json AS
$BODY$
DECLARE
vartype CHAR;
varlength INT;
jsonstr TEXT;
varcount INT;
jsonval JSONB;
arrkey JSON;
arrval JSON;
-- String length of the serialized data
serialized_string_length INT;
BEGIN
CASE substring(str, 1, 1)
WHEN 'a' THEN -- array
-- init object
jsonval := '{}'::jsonb;
-- remove the "a" and ":" characters
str := substring(str, 3);
-- Detect number of values in array
varlength := substring(str, 1, position(':' IN str) - 1)::INT;
-- Base size of array is 5 (a:[size]:{})
serialized_string_length := 5 + char_length(varlength::TEXT);
-- If no values, return empty object, as this always returns objects
IF varlength = 0 THEN
return json_build_array('array', jsonval, serialized_var_size)::JSON;
END IF;
-- remove the array size and ":{"
str := substring(str, char_length(varlength::TEXT) + 3);
-- Find the number of variables specified
FOR varcount IN 1 .. varlength LOOP
-- Find the value of the key and remove it from base string
arrkey := php_unserialize(str);
str := substring(str, (arrkey->>2)::INT + 1);
-- Find the value of the value and remove it from base string
arrval := php_unserialize(str);
str := substring(str, (arrval->>2)::INT + 1);
serialized_string_length := serialized_string_length + (arrkey->>2)::INT + (arrval->>2)::INT;
-- Append value
jsonval := jsonval || jsonb_build_object(arrkey->>1, arrval->1);
END LOOP;
return json_build_array('array', jsonval, serialized_string_length);
WHEN 'b' THEN -- boolean
return json_build_array('bool',(CASE substring(str, 3, 1) WHEN '1' THEN TRUE ELSE FALSE END)::TEXT, 4);
WHEN 'd' THEN -- float
return json_build_array('float', substring(str, 3, position(';' IN str) - 3)::TEXT, position(';' IN str));
WHEN 'i' THEN -- int
return json_build_array('int', substring(str, 3, position(';' IN str) - 3)::TEXT, position(';' IN str));
WHEN 'N' THEN -- null
return json_build_array('null', 'null'::TEXT, 2);
WHEN 's' THEN -- string
varlength := substring(str, 3, position(':' IN str) - 1)::INT;
return json_build_array('string', substring(str, char_length(varlength::TEXT) + 5, varlength)::TEXT, position(';' IN str));
ELSE
RAISE EXCEPTION 'Unable to decode serialized value, unsupported type: %', substr(str, 1, 1);
END CASE;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 10;
---
--- The main function
---
CREATE OR REPLACE FUNCTION php_unserialize_to_json(str text)
RETURNS json AS
$BODY$
DECLARE
varlength INT;
BEGIN
CASE substring(str, 1, 1)
WHEN 'a' THEN
return php_unserialize(str)->1;
WHEN 'b' THEN
return php_unserialize(str)->1;
WHEN 'd' THEN
return php_unserialize(str)->1;
WHEN 'i' THEN
return php_unserialize(str)->1;
WHEN 'N' THEN
return php_unserialize(str)->1;
WHEN 's' THEN
return php_unserialize(str)->1;
ELSE
RETURN NULL;
END CASE;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 10;
Martins函数对我不起作用。
- 首先，它不符合postgres9.4。我在引擎盖下使用的是greenplum 6.22和postgresql 9.4
- 其次，它假设所有元素的长度都小于10字节的长度
注意：首先，请不要将php数组添加到关系数据库中。它们不是为这个而设计的。如果你没有其他选择，比如用相关的东西解析php数组，比如php或任何编程语言，请使用这个函数。
我开发了替代马丁斯函数的方法。它不正确地支持php数组对象，但可以很好地处理php数组中的所有其他数据类型
功能
create or replace function php_deserialize(str text) returns json as
$$
DECLARE
def_len int;
data_len int;
total_len int := length(str);
p int := 1;
alpha char := substr(str,1,1);
stack text := '';
bytes bytea;
part text;
k text; -- key
v text; -- value
parsed text := '';
error_message text;
BEGIN
if str is null then return NUll::json; end if;
if total_len = 0 or length(trim(str)) = 0 then return NUll::json; end if;
if alpha != 'a' then raise 'expected array got: %', str; end if;
while p <= total_len loop
alpha := substr(str,p,1);
-- raise info 'p: %', p;
-- raise info 'stack: %', stack;
-- raise info 'alpha: %', alpha;
-- raise info 'parsed: %', parsed;
if alpha = '{'
then
if right(stack, 1) = '}'
then raise 'invalid braces';
else
stack := stack || alpha ;
parsed := parsed || coalesce( k ||':','') || alpha;
k := Null;
p := p + 1;
end if;
elsif alpha = '}'
then
if right(stack,1) = '{'
then
stack := substr(stack,1,length(stack)-1);
if substr(reverse(parsed),1,1) = ','
then
parsed := reverse(substr(reverse(parsed),2));
end if;
if length(stack) < 1
then
parsed := parsed || alpha ;
-- raise info 'stack is closed, exiting parsed data: %', parsed;
return parsed;
exit;
else
parsed := parsed || alpha || ',';
end if;
p := p +1;
else
raise 'invalid braces expected } or none got { str: %' ,substr(str,p);
end if;
-- end if;
-- parsing and skipping
elsif alpha = 's' and substring(substr(str,p) from '^s:[0-9]+:') is not null
then
def_len := length(substring(substr(str,p) from '^s':[0-9]+':'))::int;
data_len := substring(substr(str,p) from '^s':([0-9]+)':')::int;
-- raise info 'part to parse: %', substr(str,p + def_len + 1, data_len );
part := replace(substr(str,p + def_len + 1, data_len),''','''');
bytes := part::bytea; --estimation
bytes := substr(bytes,1,data_len)::bytea; -- true data string in bytes
part := convert_from(bytes, 'UTF-8')::text;
-- raise info 'part parsed: %', part;
data_len := length(part)::int; -- true data length in char
p := p + def_len + 1 + data_len + 1;
-- part := '"' || part || '"';
part := to_json(part);
elsif alpha = 'i' and substring(substr(str,p) from '^i':'-?[0-9]+') is not null
then
-- raise info 'I am i skip block skipping %', substring(substr(str,p) from '^i':[0-9]+');
part := to_json( substring(substr(str,p) from '^i':'-?([0-9]+)'));
p := p + length(substring(substr(str,p) from '^i':'-?[0-9]+'));
elsif alpha = 'b' and substring(substr(str,p) from '^b':[01]') is not null
then
part := substring(substr(str,p) from '^b':([01])')::boolean::text;
p := p + 4;
elsif alpha = 'N' and substring(substr(str,p) from '^N;') is not null
then
part := 'null';
p := p + 1;
elsif alpha = 'd' and substring(substr(str,p) from '^d':'-?[0-9.]+') is not null
then
part := to_json( substring(substr(str,p) from '^d':('-?[0-9.]+)'));
p := p + length(substring(substr(str,p) from '^d':'-?[0-9.]+'));
else
p := p + 1;
end if;
-- add parsed to parsed
if k is null
then
k := part;
elsif v is null
then
v := part;
end if;
if k is not null and v is not null
then
parsed := parsed || k || ':' || v || ',';
k := null;
v := null;
end if;
part := null;
-- raise info 'after skipping block p: %', p;
-- raise info 'after skipping block str: %', substr(str,p);
-- raise info 'end loop iteration';
end loop;
--
if length(stack) > 0
then
-- raise info 'No closing bracket';
return null::json;
else
return parsed::json;
end if;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS error_message := MESSAGE_TEXT;
error_message := error_message || ' [current string] [' || str || ']';
RAISE 'error message: %', error_message;
END;
$$ LANGUAGE plpgsql IMMUTABLE
测试用例
drop table if exists php_arrays;
create temp table php_arrays (id int, str text );
insert into php_arrays values
(1,'a:1:{s:3:"foo";s:3:"bar"} '),
(2,'a:2:{s:3:"foo";a:1:{s:3:"foo";s:3:"bar"}}'),
(3,'a2:{s:3:"foo";a:2:{s:3:"foo";s:3:"bar";s:3:"bar";s:3:"foo";}}'),
(4,'a3:{s:3:"foo";a:2:{s:3:"foo";s:3:"bar";s:3:"bar";s:3:"foo";};s:3:"bar";s:3:"foo"}'),
(5,'a:2:{s:3:"foo";i:3;s:3:"bar";b:1;}'),
(5,'a:3:{s:3:"foo";i:3;s:3:"bar";b:1;s:6:"foobar";N;}'),
(7,'a:3:{s:5:"fooN;";i:3;s:3:"bar";b:1;s:6:"foobar";N;}'),
(8,'a:2:{s:14:"event_partners";a:0:{}s:15:"selective_color";s:7:"#0f68be";}'),
(9,'a:30:{s:16:"template_version";s:8:"20190920";s:9:"templates";a:1:{s:15:"EventController";a:1:{s:5:"index";s:12:"event_page_3";}}s:17:"seatcheck_version";i:1;s:7:"mapAddr";s:37:"+55.837319738620074,+37.6241507694992";s:14:"event_partners";a:0:{}s:15:"selective_color";s:7:"#0f68be";s:14:"to_description";N;}'),
(10,'a:1:{s:3:"foo";s:31:"<p><strong>Информация"}'),
(11,'a:1:{s:3:"foo";s:82:"<p><strong>Информация о событии</strong><br />
Название:"}'),
(12,'a:1:{s:3:"foo";s:0:""} '),
(13,'a:1:{s:11:"hoverFactor";d:2.2;}'),
(14,'a:2:{s:12:"hover'Factor";d:2.2;s:3:"foo";s:4:"b'ar";}'),
(15,'a:1:{s:1:"x";d:-138.7060546875;}'),
(16 , NULL),
(17 , ' '),
(18 , ''),
(19, 'a:1:{s:12:"new_date_end";O:10:"TPDateTime":3:{
s:4:"date";s:26:"2023-02-15 12:30:00.000000";
s:13:"timezone_type";i:3;
s:8:"timezone";s:13:"Europe/Moscow";
}}')
;
对象半支持。对象被转换为键值，其中value是对象的主体。
示例：
input: 'a:1:{
s:12:"new_date_end";O:10:"TPDateTime":3:{
s:4:"date";s:26:"2023-02-15 12:30:00.000000";
s:13:"timezone_type";i:3;
s:8:"timezone";s:13:"Europe/Moscow";
}
}'
output:
{
"new_date_end":
{
"date":"2023-02-15 12:30:00.000000",
"timezone_type":"3",
"timezone":"Europe/Moscow"
}
}
如果给定的数组无效，则返回null。
请随时为我在GitHub上的开源项目做出贡献，或者从GitHub 复制项目
postgre不存在专用的PHP函数Serialize/Unserialize查看：PostgreSQL函数
至于数据库是唯一可能的json，但它在PHP中不支持postgres更多信息：JSON函数和运算符
我的解决方案：
我在PHP中使用了序列化和非序列化。
$array = array('t1' => 1,'t2' => 2,'t3' => 3,'t4' => 4);
$arraySerialize = serialize($array);
print_r($arraySerialize); //a:4:{s:2:"t1";i:1;s:2:"t2";i:2;s:2:"t3";i:3;s:2:"t4";i:4;}
在文本类型的数据库列中保存$arraySerialize
从数据库中获取$arraySerialize并提取（$stringUnserialize）
$stringUnserialize=取消序列化（$arraySerialize）；//阵列（[t1]=>1[t2]=>2[t3]=>3[t4]=>4）