MySQL 在数据库级别本身通过查询反序列化

Unserialize through query at database level itself

提问by Angelin Nadar

I have a column value stored in the database as:

我在数据库中存储了一个列值：

a:2:{i:0;s:2:"US";i:1;s:2:"19";}

a:2:{i:0;s:2:"US";i:1;s:2:"19";}

I want to unserialize it during the mysql query rather than using the php unserializefunction after fetching the data. I want to do it this way so I can join another table with the serialized value. This would avoid executing a separate query after unserializing it with php, just for the joined data.

我想在 mysql 查询期间反序列化它，而不是unserialize在获取数据后使用 php 函数。我想这样做，这样我就可以用序列化值加入另一个表。这将避免在使用 php 反序列化之后执行单独的查询，仅针对连接的数据。

回答by Pentium10

MySQL doesn't know what a PHP serialization is. You can't do it.

MySQL 不知道 PHP 序列化是什么。你不能这样做。

回答by Rami Sedhom

You can use SUBSTRING_INDEX

您可以使用SUBSTRING_INDEX

For example, if you have a record like this:

例如，如果您有这样的记录：

a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:5;s:14:"broughtforward";i:3;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}

You can use the below SELECT statement:

您可以使用以下 SELECT 语句：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',1),':',-1) AS fieldname1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS fieldvalue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',3),':',-1) AS fieldname2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS fieldvalue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',5),':',-1) AS fieldname3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS fieldvalue3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',7),':',-1) AS fieldname4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS fieldvalue4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',9),':',-1) AS fieldname5,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS fieldvalue5
FROM table;

Check this for reference: How to unserialize data using mysql without using php

检查此以供参考：How to unserialize data using mysql without using php

回答by nam

How about this? This is a MySQL user-defined function with embedded php:

这个怎么样？这是一个带有嵌入式 php 的 MySQL 用户定义函数：

CREATE FUNCTION unserialize_php RETURNS STRING SONAME 'unserialize_php.so';

Usage example:

用法示例：

SELECT unserialize_php('O:8:"stdClass":2:{s:1:"a";s:4:"aaaa";s:1:"b";s:4:"bbbb";}', "$obj->a") 
AS 'unserialized';

+--------------+
| unserialized |
+--------------+
| aaaa         |
+--------------+
1 row in set (0.00 sec)

drop function unserialize_php;

Source: https://github.com/junamai2000/mysql_unserialize_php

来源：https: //github.com/junamai2000/mysql_unserialize_php

You can create a MySQL user-defined function and call zend_eval_stringinside of the function so that you can bring back PHP variables to a MySQL result. I implemented a sample program. You can try it.

您可以创建一个 MySQL 用户定义函数并zend_eval_string在该函数内部调用，以便您可以将 PHP 变量带回 MySQL 结果。我实现了一个示例程序。你可以试试看。

回答by david-giorgi

From http://www.blastar.biz/2013/11/28/how-to-use-mysql-to-search-in-php-serialized-fields/

来自http://www.blastar.biz/2013/11/28/how-to-use-mysql-to-search-in-php-serialized-fields/

Standard array

标准阵列

SELECT * FROM table WHERE your_field_here REGEXP '.*;s:[0-9]+:"your_value_here".*'

Associative array

关联数组

SELECT * FROM table WHERE your_field_here REGEXP '.*"array_key_here";s [0-9]+:"your_value_here".*'

回答by Amil Waduwawara

It's a very bad practice to add programming language dependent structures to database. If you do so, you always have to rely on that language.

将依赖于编程语言的结构添加到数据库是一种非常糟糕的做法。如果这样做，则必须始终依赖该语言。

The best approach is to have normalized table structure (different fields or tables).

最好的方法是标准化表结构（不同的字段或表）。

The next approach is to save data as a delimited string (e.g.: 0,US,1,19). Then you can use MySQL's SUBSTRING()or to use standard serialization mechanisms like JSON encode.

下一个方法是将数据保存为分隔字符串（例如：）0,US,1,19。然后您可以使用 MySQLSUBSTRING()或使用标准序列化机制，如 JSON 编码。

回答by Stranger

As mentioned by kchteam, MySQLToolBox library comes handy for this purpose using a custom defined MySQL function getPhpSerializedArrayValueByKeyavailable here https://github.com/KredytyChwilowki/MySQLToolBox/blob/master/getPhpSerializedArrayValueByKey.sql.

正如kchteam所提到的，MySQLToolBox 库为此目的使用自定义定义的 MySQL 函数非常方便，getPhpSerializedArrayValueByKey这里提供https://github.com/KredytyChwilowki/MySQLToolBox/blob/master/getPhpSerializedArrayValueByKey.sql。

After adding this function, you can retrieve any value in the serialized array by using the following syntax,

添加此函数后，您可以使用以下语法检索序列化数组中的任何值，

SELECT getPhpSerializedArrayValueByKey(column_name, 'array_key') AS deseializedArrayValue FROM table_name

The given array can be unserialized like,

给定的数组可以像这样反序列化，

SELECT getPhpSerializedArrayValueByKey('a:2:{i:0;s:2:"US";i:1;s:2:"19";}
', 'key_to_retrieve') AS key_to_retrieve

回答by Olexandr Denischuk

You can join your table simply in this way

您可以通过这种方式简单地加入您的桌子

    SELECT 
    table_to_join.ID as table_to_join_ID ,
    serialized_table.ID AS   serialized_table_ID,
FROM
    table_to_join
        LEFT JOIN
    serialized_table  ON  serialized_table.array_field  REGEXP CONCAT_WS('','.s:[0-9];s:', table_to_join.ID ,';.') ;

Take mention. I use index from 0 to 9 in table. If you have other indexes you must correct regexp

提一下。我在表中使用从 0 到 9 的索引。如果您有其他索引，则必须更正正则表达式

回答by kchteam

For serialized arrays You can use function getPhpSerializedArrayValueByKey from here

对于序列化数组您可以从这里使用函数 getPhpSerializedArrayValueByKey