表结构是这样
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列)
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列)
解决方案 »
- 请教一个mysql update数据表更新语句, 高手 help
- 执行mysqldump备份数据库时出现outfile disabled错误
- 请教个sql语句问题
- 想请问一个排序的问题
- mysql创建中文表时总是出错
- 请问MYSQL数据库的org.gjt.mm.mysql.driver应怎样设置才能连接得上MYSQL数据库
- 请教,如何有导入*.sql文件.
- 启动mysql.exe时出现ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
- 求一些SQL语句?
- 如何才能把mysql的安装放到软件的安装程序中
- 谁知道MYSQL配置日志保存路径时能不能用日期变量
- 求这么个SQL语句吧
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语句还不会用
大虾们改下
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;
//
`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:188Error Code : 1242
Subquery returns more than 1 rowExecution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
返回多列怎么处理
SET id=1;
SET id=id+1;
"如果用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>
你的BLOB中存的是什么?字符串还是二进制制LONG型?
注: 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的话就算解决问题了!??
friend [blob] [000100020003]
------------------------
这上面是2种不同的源数据方式了
处理方式自然也不一样啦
所以,建议你提供上面的数据
多谢您的好意, 不过我认为好像没有必要再从头过一遍了, 那样可能又把问题搞复杂了! 现在的问题已经简化很多了就是: MYSQL中 CAST函数如何将BLOB '1\0\0\0' 转成 数字1 ???
[1\0\0\02\0\0\03\0\0\0]不好意思, 我刚发贴时的数据结构有些问题没有具体和跟一下数据内容, 所以误导了大家,
在这里向大家说声对不起, I'm sorry
'S\0\0\0'我在存的时候是以long 类型 83 (对应ASCII码的'S') 存进去的能用CAST再把'S\0\0\0' 这个转回83 这个数就万事大吉了
更怕菜鸟"简化"问题, 殊不知失之毫厘,差之千里。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>
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)
不过还好, 我已经找到解决方法了
使用:ORD('S\0\0\0') 这个方法就可以正常转换 big endian 了第一次在这里问问题, 有很多欠缺的地方,
以后我发问题会给大家发完整的建表建数据过程, 多谢大家的支持
set sFid = concat (sFid,',',CONV(hex(REVERSE(substr(v1,i,4))),16,10) );