php mysql 在序列化数组中选择查询

mysql select query within a serialized array

提问by Daelan

I'm storing a list of items in a serialized array within a field in my database (I'm using PHP/MySQL).

我将项目列表存储在我的数据库字段中的序列化数组中（我使用的是 PHP/MySQL）。

I want to have a query that will select all the records that contain a specific one of these items that is in the array.

我想要一个查询，该查询将选择包含数组中这些项目中的特定项目的所有记录。

Something like this:

像这样的东西：

select * from table WHERE (an item in my array) = '$n'

Hopefully that makes sense.

希望这是有道理的。

Any ideas would be greatly appreciated.

任何想法将不胜感激。

Thanks

谢谢

采纳答案by thomaspaulb

So you mean to use MySQL to search in a PHP array that has been serialized with the serializecommand and stored in a database field? My first reaction would be: OMG. My second reaction would be: why? The sensiblething to do is either:

所以你的意思是使用 MySQL 在已经用serialize命令序列化并存储在数据库字段中的 PHP 数组中进行搜索？我的第一反应是：天啊。我的第二反应是：为什么？在理智的事情可以是：

1. Retrieve the array into PHP, unserialize it and search in it
2. Forget about storing the data in MySQL as serialized and store it as a regular table and index it for fast search

1. 将数组检索到 PHP 中，对其进行反序列化并在其中进行搜索
2. 忘记将数据存储在 MySQL 中作为序列化并将其存储为常规表并将其索引以进行快速搜索

I would choose the second option, but I don't know your context.

我会选择第二个选项，但我不知道你的背景。

Of course, if you'd really want to, you could try something with SUBSTRINGor another MySQL function and try to manipulate the field, but I don't see why you'd want to. It's cumbersome, and it would be an unnecessary ugly hack. On the other hand, it's a puzzle, and people here tend to like puzzles, so if you really want tothen post the contents of your field and we can give it a shot.

当然，如果您真的想这样做，您可以尝试使用SUBSTRING其他 MySQL 函数并尝试操作该字段，但我不明白您为什么要这样做。这很麻烦，而且这将是一个不必要的丑陋黑客。另一方面，这是一个谜题，这里的人往往喜欢谜题，所以如果你真的想发布你的领域的内容，我们可以试一试。

回答by timdev

As GWW says in the comments, if you need to query things this way, you really ought to be considering storing this data as something other than a big-ole-string (which is what your serialized array is).

正如 GWW 在评论中所说，如果您需要以这种方式查询事物，您真的应该考虑将此数据存储为 big-ole-string 以外的其他内容（这是您的序列化数组）。

If that's not possible (or you're just lazy), you can use the fact that the serialized array is just a big-ole-string, and figure out a LIKE clause to find matching records. The way PHP serializes data is pretty easy to figure out (hint: those numbers indicate lengths of things).

如果这是不可能的（或者您只是懒惰），您可以使用序列化数组只是一个 big-ole-string 的事实，并找出一个 LIKE 子句来查找匹配的记录。PHP 序列化数据的方式很容易弄清楚（提示：这些数字表示事物的长度）。

Now, if your serialized array is fairly complex, this will break down fast. But if it's a flat array, you should be able to do it.

现在，如果您的序列化数组相当复杂，这将很快崩溃。但是如果它是一个平面阵列，你应该能够做到。

Of course, you'll be using LIKE '%...%', so you'll get no help from any indicies, and performance will be very poor.

当然，您将使用 LIKE '%...%'，因此您将无法从任何索引中获得帮助，并且性能将非常差。

Which is why folks are suggesting you store that data in some normalized fashion, if you need to query "inside" it.

这就是为什么人们建议您以某种规范化的方式存储该数据，如果您需要查询“内部”数据。

回答by Mixologic

If you have control of the data model, stuffing serialized data in the database will bite you in the long run just about always. However, oftentimes one does nothave control over the data model, for example when working with certain open source content management systems. Drupal sticks a lotof serialized data in dumpster columns in lieu of a proper model. For example, ubercart has a 'data' column for all of its orders. Contributed modules need to attach data to the main order entity, so out of convenience they tack it onto the serialized blob. As a third party to this, I still need a way to get at some of the data stuffed in there to answer some questions.

如果您可以控制数据模型，那么从长远来看，在数据库中填充序列化数据几乎总是会咬您。然而，通常人们无法控制数据模型，例如在使用某些开源内容管理系统时。Drupal在垃圾箱列中粘贴了大量序列化数据来代替适当的模型。例如，ubercart 的所有订单都有一个“数据”列。贡献模块需要将数据附加到主订单实体，所以为了方便他们将它附加到序列化的 blob 上。作为第三方，我仍然需要一种方法来获取其中的一些数据以回答一些问题。

a:4:{s:7:"cc_data";s:112:"6"CrIPY2IsMS1?blpMkwRj[XwCosb]gl<Dw_L(,Tq[xE)~(!$C"9Wn]bKYlAnS{[Kv[&Cq$xN-Jkr1qq<z](td]ve+{Xi!G0x:.O-"=yy*2KP0@z";s:7:"cc_txns";a:1:{s:10:"references";a:1:{i:0;a:2:{s:4:"card";s:4:"3092";s:7:"created";i:1296325512;}}}s:13:"recurring_fee";b:1;s:12:"old_order_id";s:2:"25";}

see that 'old_order_id'? thats the key I need to find out where this recurring order came from, but since not everybody uses the recurring orders module, there isnt a proper place to store it in the database, so the module developer opted to stuff it in that dumpster table.

看到'old_order_id'了吗？这就是我需要找出这个重复订单来自哪里的关键，但由于不是每个人都使用重复订单模块，因此没有合适的地方将它存储在数据库中，因此模块开发人员选择将其填充到该垃圾箱表中。

My solution is to use a few targeted SUBSTRING_INDEX's to chisel off insignificant data until I've sculpted the resultant string into the data gemstone of my desires. Then I tack on a HAVING clause to find all that match, like so:

我的解决方案是使用一些有针对性的 SUBSTRING_INDEX 来剔除无关紧要的数据，直到我将结果字符串雕刻成我想要的数据宝石。然后我添加一个 HAVING 子句来查找所有匹配项，如下所示：

SELECT uo.*,
SUBSTRING_INDEX(
 SUBSTRING_INDEX(
  SUBSTRING_INDEX( uo.data, 'old_order_id' , -1 ),
 '";}', 1),
'"',-1) 
AS `old order id`
FROM `uc_orders AS `uo`
HAVING `old order id` = 25

The innermost SUBSTRING_INDEX gives me everything past the old_order_id, and the outer two clean up the remainder.

最里面的 SUBSTRING_INDEX 为我提供了 old_order_id 之后的所有内容，而外面的两个则清理了其余部分。

This complicated hackery is not something you want in code that runs more than once, more of a tool to get the data out of a table without having to resort to writing a php script.

在多次运行的代码中，这种复杂的技巧不是您想要的，而是一种无需编写 php 脚本即可从表中获取数据的工具。

Note that this couldbe simplified to merely

请注意，这可以简化为仅

SELECT uo.*,
SUBSTRING_INDEX(
  SUBSTRING_INDEX( uo.data, '";}' , 1 ),
'"',-1) 
AS `old order id`
FROM `uc_orders` AS `uo`
HAVING `old order id` = 25

but that would only work in this specific case (the value I want is at the end of the data blob)

但这仅适用于这种特定情况（我想要的值位于数据 blob 的末尾）

回答by Donatas Olsevi?ius

You can do it like this:

你可以这样做：

SELECT * FROM table_name WHERE some_field REGEXP '.*"item_key";s:[0-9]+:"item_value".*'

But anyway you should consider storing that data in a separate table.

但无论如何，您应该考虑将该数据存储在单独的表中。

回答by ryanlahue

How about you serialize the value you're searching for?

你如何序列化你正在搜索的值？

$sql = sprintf("select * from tbl WHERE serialized_col like  '%%%s%%'", serialize($n));

or

或者

$sql = sprintf("select * from tbl WHERE serialized_col like  '%s%s%s'", '%', serialize($n), '%');

回答by Alan M. Maziero

Well, i had the same issue, and apparently it's a piece of cake, but maybe it needs more tests.

嗯，我有同样的问题，显然这是小菜一碟，但也许它需要更多的测试。

Simply use the IN statement, but put the field itself as array! Example:

只需使用 IN 语句，但将字段本身作为数组！例子：

SELECT id, title, page FROM pages WHERE 2 IN (child_of)

~ where '2' is the value i'm looking for inside the field 'child_of' that is a serialized array.

~ 其中 '2' 是我在作为序列化数组的字段 'child_of' 中寻找的值。

This serialized array was necessary because I cannot duplicate the records just for storing what id they were children of.

这个序列化数组是必要的，因为我不能复制记录只是为了存储它们的子代。

Cheers

干杯

回答by Jeyakumar T

If I have attribute_dump field in log table and the value in one of its row has

如果我在日志表中有 attribute_dump 字段并且其中一行中的值有

a:69:{s:9:"status_id";s:1:"2";s:2:"id";s:5:"10215"}

If I want to fetch all rows having status_id is equal to 2, then the query would be

如果我想获取 status_id 等于 2 的所有行，那么查询将是

SELECT * FROM log WHERE attribute_dump REGEXP '.*"status_id";s:[0-9]+:"2".*'

回答by Jeremy Jumeau

Working with php serialized data is obviously quite ugly, but I've got this one liner mix of MySQL functions that help to sort that out:

使用 php 序列化数据显然非常难看，但我有一个 MySQL 函数的线性组合，可以帮助解决这个问题：

select REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(searchColumn, 'fieldNameToExtract', -1), ';', 2), ':', -1), '"', '') AS extractedFieldName
from tableName as t 
having extractedFieldName = 'expressionFilter';

Hope this can help!

希望这可以帮助！

回答by shafi

Select * from table where table_field like '%"enter_your_value"%'

回答by jocull

You may be looking for an SQL IN statement.

您可能正在寻找 SQL IN 语句。

http://www.w3schools.com/sql/sql_in.asp

http://www.w3schools.com/sql/sql_in.asp

You'll have to break your array out a bit first, though. You can't just hand an array off to MySQL and expect it will know what to do with it. For that, you may try serializing it out with PHP's explode.

不过，您必须先将数组拆分一下。您不能只是将数组交给 MySQL 并期望它知道如何处理它。为此，您可以尝试使用 PHP 的爆炸将其序列化。

http://php.net/manual/en/function.explode.php

http://php.net/manual/en/function.explode.php