DELIMITER //create procedure request_info(client_id int, services varchar(20))
begin
declare comma_pos int;
declare current_id int; svcs: loop
set comma_pos = locate(',', services);
set current_id = substr(services, 1, comma_pos); if current_id <> 0 then
set services = substr(services, comma_pos+1);
else
set current_id = services;
end if; insert into service_info values(null, client_id, current_id); if comma_pos = 0 or current_id = '' then
leave svcs;
end if;
end loop;
end////
DELIMITER ;call request_info("45", "1,4,6");如题
数据库的数组下标究竟从1开始还是0开始,若以1开始那么locate(',', services)应该返回2吧?
解决方案 »
- java程序如何连接使用PL/Proxy方式集群的数据库
- EXPLAIN里的INDEX不好解释,帮忙看看
- 如何给分组数据增加一个id?
- MySQL的root密码忘记了怎么办?
- 不知道有没有这样的sql语句
- 请教前辈们... 关于 数据表 utf8 字符集的问题
- 编译MySQL自带examples目录下的例子,提示找不到typelib.h
- 初学者: 从 RPM 安装MySQL,----NOKEY???
- 高手请进!!!!!!!JDBC对mySQL的驱动程序的问题。
- Mysql 1524错误Plugin未加载
- You have an error in your SQL syntax
- 数据量过大时 使用事务会锁住表 其他用户无法操作 求解决方案
mysql> select locate(',',"1,4,6")
-> ;
+---------------------+
| locate(',',"1,4,6") |
+---------------------+
| 2 |
+---------------------+
1 row in set (0.00 sec)是返回2 .
第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回字符串 str中子字符串substr的第一个出现位置, 起始位置在pos。如若substr 不在str中,则返回值为0。
SELECT locate(',', '1,4,6');
看看结果
没有找到内容,返回0
mysql help:
LOCATE(substr,str), LOCATE(substr,str,pos) The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
-> 7This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
-> 7This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.
但是locate(',', services)返回的是2,那么substr(services, 1, comma_pos);返回的便是"1,";那么insert into到数据库后为什么没有","?
set comma_pos = locate(',', services);
set current_id = substr(services, 1, comma_pos);
SELECT current_id;
if current_id <> 0 then
set services = substr(services, comma_pos+1);
SELECT services;
else
set current_id = services;
SELECT current_id;
end if;
检查结果,检查字段名与变量名是否重复
-> begin
-> declare comma_pos int;
-> declare current_id varchar(30);
-> svcs: loop
-> set comma_pos = locate(',', services);
-> set current_id = substr(services, 1, comma_pos);
-> if current_id <> '' then
-> set services = substr(services, comma_pos+1);
-> else
-> set current_id = services;
-> end if;
-> insert into service_info values(null, client_id, current_id);
-> if comma_pos = 0 or current_id = '' then
-> leave svcs;
-> end if;
-> end loop;
-> end//
Query OK, 0 rows affected (0.00 sec)root@localhost : test 03:37:05>DELIMITER ;
root@localhost : test 03:37:52>select * from service_info;
Empty set (0.00 sec)root@localhost : test 03:37:56>call request_info("45", "1,4,6");
Query OK, 1 row affected (0.00 sec)root@localhost : test 03:38:40>select * from service_info;
+----+-----------+------------+
| id | client_id | current_id |
+----+-----------+------------+
| 1 | 45 | 1, |
| 2 | 45 | 4, |
| 3 | 45 | 6 |
+----+-----------+------------+
3 rows in set (0.00 sec)root@localhost : test 03:38:43>call request_info("45", "999");
Query OK, 1 row affected (0.00 sec)root@localhost : test 03:38:50>select * from service_info;
+----+-----------+------------+
| id | client_id | current_id |
+----+-----------+------------+
| 1 | 45 | 1, |
| 2 | 45 | 4, |
| 3 | 45 | 6 |
| 4 | 45 | 999 |
+----+-----------+------------+
4 rows in set (0.00 sec)
有,。
请问你在控制台上为什么可以输入declare comma_pos int;也不会返回出错,而可以继续输入??
我的:mysql> create procedure request_info(client_id int, services varchar(20))
-> begin
-> declare comma_pos int;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 3我之前是用MySQL Workbench创建并运行的,是不是workbench的问题?
输入你的代码
$$
delimiter ;
。
我的却没有,
mysql> delimiter //
mysql> create procedure request_info(client_id int, services varcha
-> begin
-> declare comma_pos int;
-> declare current_id varchar(30);
-> svcs: loop
-> set comma_pos = locate(',', services);
-> set current_id = substr(services, 1, comma_pos);
-> if current_id <> '' then
-> set services = substr(services, comma_pos+1
-> else
-> set current_id = services;
-> end if;
-> insert into service_info values(null, client_id, cu
-> if comma_pos = 0 or current_id = '' then
-> leave svcs;
-> end if;
-> end loop;
-> end//
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> select * from service_info;
+--------+-----------+---------+
| row_id | client_id | service |
+--------+-----------+---------+
| 49 | 45 | 1 |
| 50 | 45 | 4 |
| 51 | 45 | 6 |
| 52 | 45 | 999 |
| 53 | 45 | 999 |
| 54 | 45 | 4 |
+--------+-----------+---------+
6 rows in set (0.00 sec)mysql> call request_info("45", "1,4,6");
Query OK, 1 row affected (0.08 sec)mysql> select * from service_info;
+--------+-----------+---------+
| row_id | client_id | service |
+--------+-----------+---------+
| 49 | 45 | 1 |
| 50 | 45 | 4 |
| 51 | 45 | 6 |
| 52 | 45 | 999 |
| 53 | 45 | 999 |
| 54 | 45 | 4 |
| 55 | 45 | 1 |
| 56 | 45 | 4 |
| 57 | 45 | 6 |
+--------+-----------+---------+
9 rows in set (0.00 sec)
BEGIN
DECLARE comma_pos INT;
DECLARE current_id VARCHAR(10);
svcs: LOOP
SET comma_pos = LOCATE(',', services);
SET current_id = SUBSTR(services, 1, comma_pos);
IF current_id+0 <> 0 THEN
SET services = SUBSTR(services, comma_pos+1);
ELSE
SET current_id = services;
END IF;
INSERT INTO service_info VALUES(NULL, client_id, current_id);
IF comma_pos = 0 OR current_id = '' THEN
LEAVE svcs;
END IF;
END LOOP;
END$$DELIMITER ;mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.16 |
+-----------+
1 row in set (0.00 sec)mysql> TRUNCATE `service_info`;
Query OK, 0 rows affected (0.03 sec)mysql> CALL request_info("45", "1,4,6");
Query OK, 1 row affected (0.06 sec)mysql> SELECT * FROM `service_info`;
+--------+------------+----------+
| row_id | aclient_id | aservice |
+--------+------------+----------+
| 1 | 45 | 1, |
| 2 | 45 | 4, |
| 3 | 45 | 6 |
+--------+------------+----------+
3 rows in set (0.00 sec)mysql>
mysql> create procedure request_info(client_id int, services varcha
-> begin
-> declare comma_pos int;
-> declare current_id varchar(30);
-> svcs: loop
-> set comma_pos = locate(',', services);
-> set current_id = substr(services, 1, comma_pos);
-> if current_id <> '' then
-> set services = substr(services, comma_pos+1
-> else
-> set current_id = services;
-> end if;
-> insert into service_info values(null, client_id, cu
-> if comma_pos = 0 or current_id = '' then
-> leave svcs;
-> end if;
-> end loop;
-> end//
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> select * from service_info;
+--------+-----------+---------+
| row_id | client_id | service |
+--------+-----------+---------+
| 49 | 45 | 1 |
| 50 | 45 | 4 |
| 51 | 45 | 6 |
| 52 | 45 | 999 |
| 53 | 45 | 999 |
| 54 | 45 | 4 |
+--------+-----------+---------+
6 rows in set (0.00 sec)mysql> call request_info("45", "1,4,6");
Query OK, 1 row affected (0.08 sec)mysql> select * from service_info;
+--------+-----------+---------+
| row_id | client_id | service |
+--------+-----------+---------+
| 49 | 45 | 1 |
| 50 | 45 | 4 |
| 51 | 45 | 6 |
| 52 | 45 | 999 |
| 53 | 45 | 999 |
| 54 | 45 | 4 |
| 55 | 45 | 1 |
| 56 | 45 | 4 |
| 57 | 45 | 6 |
+--------+-----------+---------+
9 rows in set (0.00 sec)