我想问mysql中的locate()返回的是什么？

qrverwe2w 2012-09-12 10:44:24 


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吧？
...全文
534 23 打赏 收藏 转发到动态 举报
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
WWWWA 2012-09-17
  • 打赏
  • 举报
 回复
你的版本是多少？17楼的代码参考一下
ACMAIN_CHM 2012-09-17
  • 打赏
  • 举报
 回复
set services = substr(services, comma_pos+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]

我的意思是说结果没有了逗号
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]你的执行及结果不是很正确吗？ 有什么问题？
qrverwe2w 2012-09-14
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 的回复:]

那楼主现在的完整语句是什么？
[/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 ;

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。
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.
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.
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]

。。。。。。。。。。。。。
我的却没有,



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 ;
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> 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;
检查结果，检查字段名与变量名是否重复
qrverwe2w 2012-09-13
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 的回复:]

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到数据库后为什么没有","？
加载更多回复（2）
Linux操作系统基础教程
在这一讲中，我们主要是了解一下 Linux 的概况，以及对 Linux 有一个初步的感性认识。 一．什么是Linux？ Linux 是一个以 Intel 系列 CPU(CYRIX,AMD 的 CPU也可以)为硬件平台，完全免费的 UNIX 兼容系统，完全...
MySQL字符串查找神器：LOCATE()函数5种实战用法详解
本文深入解析MySQL中LOCATE()函数的5种实战用法，涵盖动态子串截取、内容验证、嵌套查找及与其他字符串函数的组合应用。通过具体案例，帮助开发者在数据库层面高效处理字符串查找与解析任务，提升数据处理效率与查询...
MySQL高效实现模糊查询--28条优化建议
14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率...
mysql中Mysql模糊查询like效率，以及更高效的写法和sql优化方法
in_array的效率 in_array函数是个糟糕的选择。应该尽量用isset函数或array_key_exists函数来替代 。...同时检查多个变量时，每个单项都符号上一条要求时才返回TRUE,否则结果为FALSE PHP的empty()函数 格式：.
MySQL知识汇总：讲一讲MySQL中Select语句的执行顺序
摘要：MySQL SELECT语句的逻辑执行顺序与书写顺序不同，关键执行步骤为：FROM→JOIN→WHERE→GROUP BY→聚合→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT。该顺序决定了SQL语句中别名的可用性（如WHERE不能用SELECT...
MySQL

57,062

社区成员

56,762

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章