blocks|key|2680115|text|MySQL不知道什么是PHP序列化。你不能这么做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2680116|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|2321225|text|将依赖于编程语言的结构添加到数据库中是非常糟糕的做法。如果你这样做，你总是必须依赖于这种语言。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2321226|最好的方法是具有规范化的表结构(不同的字段或表)。|2321227|下一种方法是将数据保存为分隔字符串(例如：0,US,1,19)。然后，您可以使用MySQL的SUBSTRING()或使用标准序列化机制(如JSON编码)。|offset|length|style|CODE|2321228|entityMap^0|0|0|L|9|1A|B|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@$F|O|G|P|H|I]|$F|Q|G|R|H|I]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|K|$]]

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.: <code>0,US,1,19</code>). Then you can use MySQL's <code>SUBSTRING()</code> or to use standard serialization mechanisms like JSON encode.

blocks|key|2680174|text|您可以使用索引|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2680175|例如，如果您有这样的记录：|2680176|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";}|code-block|syntax|javascript|2680177|您可以使用以下SELECT语句：|2680178|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;|2680179|检查这个以供参考：如何在不使用php的情况下使用mysql取消数据序列化|2680180|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index|1|http://www.russellengland.com/2012/07/how-to-unserialize-data-using-mysql.html^0|5|2|0|0|0|0|0|0|9|R|1|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|17|8|@]|9|@]|C|$I|J]]|$1|O|3|P|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]]|C|$]]|$1|Q|3|-4|5|6|7|1C|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]|Y|$5|T|U|V|C|$W|Z]]]]

You can use <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index" rel="noreferrer">SUBSTRING_INDEX</a>

For example, if you have a record like this:

<pre><code>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";}
</code></pre>

You can use the below SELECT statement:

<pre><code>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;
</code></pre>

Check this for reference: <a href="http://www.russellengland.com/2012/07/how-to-unserialize-data-using-mysql.html" rel="noreferrer">How to unserialize data using mysql without using php</a>

blocks|key|1124086|text|对于序列化数组，可以使用来自这里的函数这里|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1124087|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/KredytyChwilowki/MySQLToolBox|1^0|E|2|0|J|2|1|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@$A|N|B|O|1|P]|$A|Q|B|R|1|S]]|C|$]]|$1|D|3|-4|5|6|7|T|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|K]]]]

For serialized arrays You can use function getPhpSerializedArrayValueByKey from <a href="https://github.com/KredytyChwilowki/MySQLToolBox" rel="nofollow">here</a>

blocks|key|2680206|text|来自http://www.blastar.biz/2013/11/28/how-to-use-mysql-to-search-in-php-serialized-fields/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2680207|标准阵列|2680208|SELECT+*+FROM+table+WHERE+your_field_here+REGEXP+'.*;s:[0-9]%2B:"your_value_here".*'|code-block|syntax|javascript|2680209|相联阵列|2680210|SELECT+*+FROM+table+WHERE+your_field_here+REGEXP+'.*"array_key_here";s+[0-9]%2B:"your_value_here".*'|2680211|entityMap|0|LINK|mutability|MUTABLE|url|http://www.blastar.biz/2013/11/28/how-to-use-mysql-to-search-in-php-serialized-fields/^0|2|2E|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|13|8|@]|9|@]|C|$I|J]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

From <a href="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/</a>

Standard array

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

Associative array

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

blocks|key|2321324|text|正如克奇队所提到的，MySQLToolBox库使用自定义的MySQL函数getPhpSerializedArrayValueByKey+(在https://github.com/KredytyChwilowki/MySQLToolBox/blob/master/getPhpSerializedArrayValueByKey.sql中可用)来实现这个目的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2321325|添加此函数后，可以使用以下语法检索序列化数组中的任何值，|2321326|SELECT+getPhpSerializedArrayValueByKey(column_name,+'array_key')+AS+deseializedArrayValue+FROM+table_name|code-block|syntax|javascript|2321327|给定的数组可以非序列化，例如，|2321328|SELECT+getPhpSerializedArrayValueByKey('a:2:{i:0;s:2:"US";i:1;s:2:"19";}
',+'key_to_retrieve')+AS+key_to_retrieve|2321329|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/4270065/kchteam|1|https://github.com/KredytyChwilowki/MySQLToolBox/blob/master/getPhpSerializedArrayValueByKey.sql^0|10|V|2|3|0|1Y|2O|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@$9|13|A|14|1|15]|$9|16|A|17|1|18]]|E|$]]|$1|F|3|G|5|6|7|19|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1A|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1B|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1C|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]|Y|$5|T|U|V|E|$W|Z]]]]

As mentioned by <a href="https://stackoverflow.com/users/4270065/kchteam">kchteam</a>, MySQLToolBox library comes handy for this purpose using a custom defined MySQL function <code>getPhpSerializedArrayValueByKey</code> available here <a href="https://github.com/KredytyChwilowki/MySQLToolBox/blob/master/getPhpSerializedArrayValueByKey.sql" rel="noreferrer">https://github.com/KredytyChwilowki/MySQLToolBox/blob/master/getPhpSerializedArrayValueByKey.sql</a>. 

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

<pre><code>SELECT getPhpSerializedArrayValueByKey(column_name, 'array_key') AS deseializedArrayValue FROM table_name
</code></pre>

The given array can be unserialized like, 

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

blocks|key|1124149|text|你可以用这种方式加入你的桌子。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1124150|++++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+,';.')+;|code-block|syntax|javascript|1124151|请提一提。我在表中使用从0到9的索引。如果有其他索引，则必须更正regexp。|1124152|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can join your table simply in this way

<pre><code> 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 ,';.') ;
</code></pre>

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

I have a column value stored in the database as:

<blockquote>
 a:2:{i:0;s:2:"US";i:1;s:2:"19";}
</blockquote>

I want to unserialize it during the mysql query rather than using the php <code>unserialize</code> function 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.

Unserialize through query at database level itself

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在数据库中存储了一个列值如下：a:2:{i:0;s:2:"US";i:1;s:2:"19";}我想在mysql查询期间取消它的序列化，而不是在获取数据之后使用php unserialize函数。我希望这样做，这样我就可以用序列化的值连接另一个表了。这将避免仅针对已连接的数据在用php取消序列化之后执行单独的查询。

问通过数据库级别的查询取消序列化
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过数据库级别的查询取消序列化EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过数据库级别的查询取消序列化
EN