Extracts the value of the corresponding KEY from the string of an array over serialize.

Sometimes a php array variable string that serizlie () has been stored in the data table can be used to retrieve the value of a key in SQL. For example, a: 3: {s: 1: a; s: 12: asdfasdfasdf; s: 1: B; I: 123123123; s: 1: c; s: 8: ASDFASDF;} the key is B. MySQLdelimiter $ createfuncti

Sometimes a php array variable string that serizlie () has been stored in the data table can be used to retrieve the value of a key in SQL. For example, a: 3: {s: 1: a; s: 12: asdfasdfasdf; s: 1: B; I: 123123123; s: 1: c; s: 8: ASDFASDF;} the key is B. MySQL delimiter $ create functi

Sometimes a php array variable string that serizlie () has been stored in the data table can be used to retrieve the value of a key in SQL. For example:
A: 3: {s: 1: "a"; s: 12: "asdfasdfasdf"; s: 1: "B"; I: 123123123; s: 1: "c"; s: 8: "ASDFASDF";} the key is B. MySQL

delimiter $$create function get_from_serialized_json ( in_string varchar(255),   in_key varchar(255) )  returns varchar(255)BEGINreturn 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 latin1BEGINreturn trim( '"' from    substring_index(    substring_index(        substring(trim("}" FROM in_string ), locate(concat("\"",in_key,"\""), in_string  )+1     ) , ";",2    ), ":",-1 )) ;ENDcharacter_set_client: latin1collation_connection: latin1_swedish_ci  Database Collation: latin1_swedish_ci1 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)