关于blob, 一个灰常菜的菜鸟问题, 快来抢分

[xqk]

表结构是这样
id[int,primarykey] name[varchar] friend [blob]
1 a [000100020003]
2 b [0001]
3 c [0002]
4 d [0003]

问题: 如果用FOR分别取出id 1 中BLOB字段中的每一个LONG类型值? (如上:1,2,3)
并在存储过程中返回 2列的表 id(friend中的所有long值) , name(friend中long值对应的name) (如上, 返回3列)

[nianzhang747]

DELIMITER //
CREATE PROCEDURE ttt()
BEGIN
DECLARE id INT;
DECLARE @idlist;
SET @idlist=(SELECT friend FROM tb WHERE id=1);
FOR (id=1;id<LENGTH(@idlist)/4;i++)
SELECT id,name FROM tb WHERE id=REPLACE(SUBSTRING(@idlist,id,4*id,'0','');
END
//

这个存储过程有错误
for语句还不会用
大虾们改下

[nianzhang747]

修改了一下

DELIMITER //
CREATE PROCEDURE ttt()
BEGIN
DECLARE id INT;
SET id=1;
WHILE id<LENGTH((SELECT b FROM dd WHERE id=1))/4 DO
SET id=id+1;
SELECT id,a FROM dd WHERE id=REPLACE(SUBSTRING((SELECT b FROM dd WHERE id=1),id,4*id),'0','');
END WHILE;
END;
//

[xqk]

谢谢, 我去试下看看

[nianzhang747]

CREATE TABLE `dd` (
   `id` int(11) DEFAULT NULL,
   `a` int(10) DEFAULT NULL,
   `b` blob
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT * FROM dd;
id,a,b
1,8,000100020003
2,4,0001
3,12,0002
4,12,0003
5,3,0004
DROP PROCEDURE IF EXISTS tttt ;
DELIMITER //
CREATE PROCEDURE tttt()
BEGIN
DECLARE id INT;
SET id=1;
WHILE id<=CEIL(LENGTH((SELECT b FROM dd WHERE id=1))/4) DO
SELECT id,a FROM dd WHERE id=REPLACE(SUBSTRING((SELECT b FROM dd WHERE id=1),id,4*id),'0','');
SET id=id+1;
END WHILE;
END;
//
CALL tttt()//
(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:01:093
Total Time     : 00:00:01:093

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:188
Total Time     : 00:00:00:188

Error Code : 1242
Subquery returns more than 1 row

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

返回多列怎么处理

[xqk]

倒, 你给写成固定的了, 不符合题目要求 ;(
SET id=1;
SET id=id+1;

[xqk]

咦, 不好意思我看错了, 什么破烂网站, 竟然不能编辑贴子

[vinsonshen]

其实不是很清楚你的需求，猜 一个。
"如果用FOR分别取出id 1"----是指把id某个值传进去存储过程吗？
你的字段friend都是以"["开头和以"]"结尾的？每一段的long值都是4位为一段的？
如果是的话，这样来写吧：

mysql> select * from tb_name;
+----+------+----------------+
| id | name | friend         |
+----+------+----------------+
|  1 | a    | [000100020003] |
|  2 | b    | [0001]         |
|  3 | c    | [0002]         |
|  4 | d    | [0003]         |
+----+------+----------------+
4 rows in set (0.00 sec)

mysql> delimiter //
mysql> drop procedure if exists sp_test//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure sp_test
    -> (
    -> in i_num int
    -> )
    -> begin
    ->  declare v_friend varchar(500);
    ->  declare v_str varchar(500);
    ->  declare v_i int;
    ->  set v_i =1;
    ->  set v_str = '';
    ->  select friend into v_friend from tb_name where id=i_num;
    ->  set v_friend = replace(v_friend,'[','');
    ->  set v_friend = replace(v_friend,']','');
    ->  while v_i <= (char_length(v_friend)/4) do
    ->          set v_str = concat(v_str,cast(substring(v_friend,(v_i-1)*4+1,4)
as unsigned),',');
    ->          set v_i = v_i + 1;
    ->  end while;
    ->  set v_str = left(v_str,char_length(v_str)-1);
    ->  set @str = concat('select id,name from tb_name where id in (',v_str,')')
;
    ->  prepare st from @str;
    ->  execute st;
    ->  deallocate prepare st;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call sp_test(1);
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

[ACMAIN_CHM]

建议楼主给出你的CREATE TABLE 和 insert 语句，这样别人好直接帮你测试。
你的BLOB中存的是什么？字符串还是二进制制LONG型？

[xqk]

距解决问题就差一步之遥了, 现在还有一点小问题
注: blob 中的内容为 long[] 数组, C语言定义为long f[n];

我在C++程序中跟了一下, blob 中的内容为: '1\0\0\0' ...

用楼上所诉的cast(substring(v_friend,(v_i-1)*4+1,4)as unsigned 转换的方法是不行的,
结果都为0!
如果能转换成1的话就算解决问题了!??

[vinsonshen]

说清楚点你的需求吧
建议列出源表数据及要求的结果格式
这样才容易帮你分析哦

[vinsonshen]

我在C++程序中跟了一下, blob 中的内容为: '1\0\0\0' ...


friend [blob]

[000100020003]


------------------------
这上面是2种不同的源数据方式了
处理方式自然也不一样啦
所以，建议你提供上面的数据

[xqk]

回楼上:
多谢您的好意, 不过我认为好像没有必要再从头过一遍了, 那样可能又把问题搞复杂了!

现在的问题已经简化很多了就是： MYSQL中 CAST函数如何将BLOB '1\0\0\0' 转成 数字1 ？？？

[xqk]

我刚才又用C++跟了一下源始数据, 其真正的数据结构是这样的:

friend [blob]
[1\0\0\02\0\0\03\0\0\0]

不好意思, 我刚发贴时的数据结构有些问题没有具体和跟一下数据内容, 所以误导了大家,
在这里向大家说声对不起, I'm sorry

[xqk]

friend [blob]
'S\0\0\0'

我在存的时候是以long 类型 83 (对应ASCII码的'S') 存进去的

能用CAST再把'S\0\0\0' 这个转回83 这个数就万事大吉了

[ACMAIN_CHM]

最怕菜鸟提问，总以为问题很简单，然后还说不清楚。
更怕菜鸟"简化"问题, 殊不知失之毫厘，差之千里。

mysql> select *,hex(friend)  from t_xqk;
+----+------+--------------+--------------------------+
| id | name | friend       | hex(friend)              |
+----+------+--------------+--------------------------+
|  1 | a    |           | 000000010000000200000003 |
|  2 | b    |             | 00000001                 |
|  3 | c    |             | 00000002                 |
|  4 | d    |             | 00000003                 |
+----+------+--------------+--------------------------+
4 rows in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE sp_xqk (IN vID INT)
    -> BEGIN
    ->  DECLARE v1 BLOB;
    ->  DECLARE v2,vLen,i INT;
    ->  DECLARE sFid varchar(10000);
    ->
    ->  select friend into v1 from t_xqk where id=vID;
    ->  set vLen=length(v1);
    ->  set i=1;
    ->  set sFid='0';
    ->
    ->  WHILE i < vLen DO
    ->          set sFid = concat (sFid,',',CONV(hex(substr(v1,i,4)),16,10) );
    ->          SET i=i+4;
    ->  END WHILE;
    ->  select * from t_xqk where find_in_set(id,sFid);
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> CALL sp_xqk(1);
+----+------+--------------+
| id | name | friend       |
+----+------+--------------+
|  1 | a    |           |
|  2 | b    |             |
|  3 | c    |             |
+----+------+--------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

[nianzhang747]

二进制
mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010    |
+---------+
1 row in set (0.00 sec)
十六进制
mysql> select hex(10);
+---------+
| hex(10) |
+---------+
| A       |
+---------+
1 row in set (0.00 sec)
把10从五进制转到2进制
mysql> select conv(10,5,2);
+--------------+
| conv(10,5,2) |
+--------------+
| 101          |
+--------------+
1 row in set (0.00 sec)

[xqk]

15F 帮主的转换方式是little endian 的, 不对, 白回答了
不过还好, 我已经找到解决方法了


使用:ORD('S\0\0\0') 这个方法就可以正常转换 big endian 了

第一次在这里问问题, 有很多欠缺的地方,
以后我发问题会给大家发完整的建表建数据过程, 多谢大家的支持

[ACMAIN_CHM]

哦，没注意，你的数据是小数在前。用REVERSE反一下。

set sFid = concat (sFid,',',CONV(hex(REVERSE(substr(v1,i,4))),16,10) );