一个简单的SQL

Question

一个字段是text类型，值如下
a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"mobile";s:11:"18655667722";}}
这样一个字符串
想要取到
userfield
realname
Jack
gender
1
mobile
18655667722
这些内容  一个SQL搞定 如何？
谢谢

Answer 1

LSB1，字段ID，内容1-10000SELECT *,LEFT(b1,INSTR(b1,';')-1) FROM (
SELECT *,SUBSTRING_INDEX(
SUBSTRING_INDEX(a1.ss,':',b.id),':',-1) AS b1 FROM (SELECT 'a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"mobile";s:11:"18655667722";}}' AS Ss FROM DUAL) a1 LEFT JOIN zz.lsb1 b
ON (LENGTH(a1.ss)-LENGTH(REPLACE(a1.Ss,':','')))+1>=b.id) a3
WHERE LEFT(b1,1)="\""

Answer 2

LSB1，字段ID，内容1-10000
 什么意思？

Answer 3

a表有个b字段 字段类型是text的~b的值的格式如：
a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"mobile";s:11:"18655667722";}}取出上面的结果的也

Answer 4

LSB1，字段ID，内容1-10000：表结构及内容

Answer 5

在PHPMYADMIN中 执行了 提示
“
您运行的 SQL 语句已经成功运行了。 (查询花费 0.0103 秒)”未显示出结果

Answer 6

直接在MYSQL命令行下运行， SQL语句测试通过

Answer 7

语句没问题
只是text类型的数值 这样可以显示出来么？

Answer 8

在oracle中这个可以直接用一个instr就可以搞定了 呵呵
mysql text的我这边测试显示不出来结果~

Answer 9

你将TEXT的长度减少到255以内，再测试一下

Answer 10

内容值就是a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"mobile";s:11:"18655667722";}}
 这些  是小于255的

Answer 11

怎么没有结果，在MYSQL命令行下运行没有
[color=#FF0000]mysql> SELECT * FROM TT;
+-------------------------------------------------------------------------------
------------------------------+------------+
| ss
                              | DD         |
+-------------------------------------------------------------------------------
------------------------------+------------+
| a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"
mobile";s:11:"18655667722";}} | 2147483647 |
+-------------------------------------------------------------------------------
------------------------------+------------+
1 row in set (0.02 sec)mysql> SELECT *,LEFT(b1,INSTR(b1,';')-1) FROM (
    -> SELECT *,SUBSTRING_INDEX(
    -> SUBSTRING_INDEX(a1.ss,':',b.id),':',-1) AS b1 FROM TT a1 LEFT JOIN zz.lsb
1 b
    -> ON (LENGTH(a1.ss)-LENGTH(REPLACE(a1.Ss,':','')))+1>=b.id) a3
    -> WHERE LEFT(b1,1)="\"";
+-------------------------------------------------------------------------------
------------------------------+------------+------+------------------+----------
----------------+
| ss
                              | DD         | id   | b1               | LEFT(b1,I
NSTR(b1,';')-1) |
+-------------------------------------------------------------------------------
------------------------------+------------+------+------------------+----------
----------------+
| a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"
mobile";s:11:"18655667722";}} | 2147483647 |    5 | "userfield";a    | "userfiel
d"              |
| a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"
mobile";s:11:"18655667722";}} | 2147483647 |    9 | "realname";s     | "realname
"               |
| a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"
mobile";s:11:"18655667722";}} | 2147483647 |   11 | "Jack";s         | "Jack"
                |
| a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"
mobile";s:11:"18655667722";}} | 2147483647 |   13 | "gender";s       | "gender"
                |
| a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"
mobile";s:11:"18655667722";}} | 2147483647 |   15 | "1";s            | "1"
                |
| a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"
mobile";s:11:"18655667722";}} | 2147483647 |   17 | "mobile";s       | "mobile"
                |
| a:1:{s:9:"userfield";a:3:{s:8:"realname";s:6:"Jack";s:6:"gender";s:1:"1";s:6:"
mobile";s:11:"18655667722";}} | 2147483647 |   19 | "18655667722";}} | "18655667
722"            |
+-------------------------------------------------------------------------------
------------------------------+------------+------+------------------+----------
----------------+
7 rows in set (0.00 sec)mysql>[/color]

Answer 12

用substring subBSTRING_INDEX  length三个函数解决了问题
但仍然非常感谢哈

Answer 13

该死的MYSQL中怎么length的时候把汉字按照2个字节算， substring的时候按照一个字节算？

Answer 14

用CHAR_LENGTH 解决 
HOHO