57,064
社区成员
发帖
与我相关
我的任务
分享我想问mysql中的locate()返回的是什么？
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吧？
...全文
请发表友善的回复…
发表回复
WWWWA 2012-09-17
- 打赏
- 举报
你的版本是多少？17楼的代码参考一下
ACMAIN_CHM 2012-09-17
- 打赏
- 举报
set services = substr(services, comma_pos+1);
你这儿不是使用了 +1 吗？ 自然不会再取到，
你这儿不是使用了 +1 吗？ 自然不会再取到，
qrverwe2w 2012-09-16
- 打赏
- 举报
[Quote=引用 19 楼 的回复:]
引用 18 楼 的回复:
引用 16 楼 的回复:
那楼主现在的完整语句是什么？
SQL code
mysql> delimiter //
mysql> create procedure request_info(client_id int, services varcha
-> begin
-> declare comma_pos int;
-……
[/Quote]
我的意思是说结果没有了逗号
引用 18 楼 的回复:
引用 16 楼 的回复:
那楼主现在的完整语句是什么？
SQL code
mysql> delimiter //
mysql> create procedure request_info(client_id int, services varcha
-> begin
-> declare comma_pos int;
-……
[/Quote]
我的意思是说结果没有了逗号
ACMAIN_CHM 2012-09-14
- 打赏
- 举报
[Quote=引用 18 楼 的回复:]
引用 16 楼 的回复:
那楼主现在的完整语句是什么？
SQL code
mysql> delimiter //
mysql> create procedure request_info(client_id int, services varcha
-> begin
-> declare comma_pos int;
-> dec……
[/Quote]你的执行及结果不是很正确吗？ 有什么问题？
引用 16 楼 的回复:
那楼主现在的完整语句是什么？
SQL code
mysql> delimiter //
mysql> create procedure request_info(client_id int, services varcha
-> begin
-> declare comma_pos int;
-> dec……
[/Quote]你的执行及结果不是很正确吗？ 有什么问题？
qrverwe2w 2012-09-14
- 打赏
- 举报
[Quote=引用 16 楼 的回复:]
那楼主现在的完整语句是什么？
[/Quote]
那楼主现在的完整语句是什么？
[/Quote]
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)
wwwwb 2012-09-14
- 打赏
- 举报
DELIMITER $$
USE `aa`$$
DROP PROCEDURE IF EXISTS `request_info`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `request_info`(client_id INT, services VARCHAR(20))
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 ;
USE `aa`$$
DROP PROCEDURE IF EXISTS `request_info`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `request_info`(client_id INT, services VARCHAR(20))
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>
2399 2012-09-13
- 打赏
- 举报
LOCATE(substr,str) , LOCATE(substr,str,pos)
第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回字符串 str中子字符串substr的第一个出现位置, 起始位置在pos。如若substr 不在str中，则返回值为0。
第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回字符串 str中子字符串substr的第一个出现位置, 起始位置在pos。如若substr 不在str中，则返回值为0。
ACMAIN_CHM 2012-09-13
- 打赏
- 举报
那楼主现在的完整语句是什么？
ACMAIN_CHM 2012-09-13
- 打赏
- 举报
类似问题，可以直接参考MYSQL的官方免费手册中的说明和例子。
ACMAIN_CHM 2012-09-13
- 打赏
- 举报
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);
-> 7
This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.
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);
-> 7
This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.
wwwwb 2012-09-13
- 打赏
- 举报
从1开始，你可以
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);
-> 7
This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.
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);
-> 7
This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.
qrverwe2w 2012-09-13
- 打赏
- 举报
是不是版本问题啊。。。各位
qrverwe2w 2012-09-13
- 打赏
- 举报
[Quote=引用 11 楼 的回复:]
SQL code
root@localhost : test 03:37:05>create procedure request_info(client_id int, services varchar(20))
-> begin
-> declare comma_pos int;
-> declare current_id varchar(30);
……
[/Quote]
。。。。。。。。。。。。。
我的却没有,
SQL code
root@localhost : test 03:37:05>create procedure request_info(client_id int, services varchar(20))
-> begin
-> declare comma_pos int;
-> declare current_id varchar(30);
……
[/Quote]
。。。。。。。。。。。。。
我的却没有,
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)
wwwwb 2012-09-13
- 打赏
- 举报
delimiter $$
输入你的代码
$$
delimiter ;
输入你的代码
$$
delimiter ;
qrverwe2w 2012-09-13
- 打赏
- 举报
[Quote=引用 11 楼 的回复:]
SQL code
root@localhost : test 03:37:05>create procedure request_info(client_id int, services varchar(20))
-> begin
-> declare comma_pos int;
-> declare current_id varchar(30);
……
[/Quote]
请问你在控制台上为什么可以输入declare comma_pos int;也不会返回出错，而可以继续输入？？
我的：
我之前是用MySQL Workbench创建并运行的，是不是workbench的问题？
SQL code
root@localhost : test 03:37:05>create procedure request_info(client_id int, services varchar(20))
-> begin
-> declare comma_pos int;
-> declare current_id varchar(30);
……
[/Quote]
请问你在控制台上为什么可以输入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的问题？
小小小小周 2012-09-13
- 打赏
- 举报
root@localhost : test 03:37:05>create procedure request_info(client_id int, services varchar(20))
-> 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)
有,。
wwwwb 2012-09-13
- 打赏
- 举报
....
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;
检查结果，检查字段名与变量名是否重复
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;
检查结果，检查字段名与变量名是否重复
qrverwe2w 2012-09-13
- 打赏
- 举报
[Quote=引用 8 楼 的回复:]
declare current_id int 定义成 varchar 类型看看。
[/Quote]
结果一样没有","
declare current_id int 定义成 varchar 类型看看。
[/Quote]
结果一样没有","
小小小小周 2012-09-13
- 打赏
- 举报
declare current_id int 定义成 varchar 类型看看。
qrverwe2w 2012-09-13
- 打赏
- 举报
[Quote=引用 2 楼 的回复:]
是从1开始,
mysql> select locate(',',"1,4,6")
-> ;
+---------------------+
| locate(',',"1,4,6") |
+---------------------+
| 2 |
+---------------------+
1 row in set (0.00 se……
[/Quote]
但是locate(',', services)返回的是2，那么substr(services, 1, comma_pos);返回的便是"1,"；那么insert into到数据库后为什么没有","？
是从1开始,
mysql> select locate(',',"1,4,6")
-> ;
+---------------------+
| locate(',',"1,4,6") |
+---------------------+
| 2 |
+---------------------+
1 row in set (0.00 se……
[/Quote]
但是locate(',', services)返回的是2，那么substr(services, 1, comma_pos);返回的便是"1,"；那么insert into到数据库后为什么没有","？
加载更多回复（2）
