从一个serialize过的array的字符串中取出中取对应KEY的value

有时会在数据表中保存一个serizlie()过的一个php的array变量字符串，当需要使用SQL语言取出某个key的值时可以用到。比如: a:3:{s:1:a;s:12:asdfasdfasdf;s:1:b;i:123123123;s:1:c;s:8:ASDFASDF;} 需要从中取出key是b的内容。 MySQL delimiter $$create functi

有时会在数据表中保存一个serizlie()过的一个php的array变量字符串，当需要使用SQL语言取出某个key的值时可以用到。比如:
a:3:{s:1:"a";s:12:"asdfasdfasdf";s:1:"b";i:123123123;s:1:"c";s:8:"ASDFASDF";} 需要从中取出key是b的内容。 MySQL

delimiter $$

create function get_from_serialized_json 
( in_string varchar(255), 
  in_key varchar(255) )
  returns varchar(255)
BEGIN
return trim( '"' from 
   substring_index(
    substring_index(
        substring(trim("}" FROM in_string ), locate(concat("\"",in_key,"\""), in_string  )+1 
    ) , ";",2 
   ), ":",-1 )) ;
END$$

mysql> show create function get_from_serialized_json\G
*************************** 1. row ***************************
            Function: get_from_serialized_json
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `get_from_serialized_json`( in_string varchar(255), 
  in_key varchar(255) ) RETURNS varchar(255) CHARSET latin1
BEGIN
return trim( '"' from 
   substring_index(
    substring_index(
        substring(trim("}" FROM in_string ), locate(concat("\"",in_key,"\""), in_string  )+1 
    ) , ";",2 
   ), ":",-1 )) ;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

 'asdfasdfasdf', 'b'=> 123123123, 'c'=> 'ASDFASDF');
echo serialize($a);

mysql> set @a ='a:3:{s:1:"a";s:12:"asdfasdfasdf";s:1:"b";i:123123123;s:1:"c";s:8:"ASDFASDF";}';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select get_from_serialized_json(@a,"a");
+----------------------------------+
| get_from_serialized_json(@a,"a") |
+----------------------------------+
| asdfasdfasdf                     |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select get_from_serialized_json(@a,"b");
+----------------------------------+
| get_from_serialized_json(@a,"b") |
+----------------------------------+
| 123123123                        |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select get_from_serialized_json(@a,"c");
+----------------------------------+
| get_from_serialized_json(@a,"c") |
+----------------------------------+
| ASDFASDF                         |
+----------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> drop function get_from_serialized_json ;
Query OK, 0 rows affected (0.00 sec)