一个简单的存储过程问题,请帮忙看下

[xihuluyou]

CREATE PROCEDURE `p4`()
BEGIN
set sql_mode='ansi';
set @a='haha' || 'wawa';
END

这样一个过程,究竟错在哪?

执行 call p4()
select @a;
返回的居然是0,而不是 我想要的字符串 hahawawa

[hy2003fly]

你的函数用错了，应该是concat(string1,string2,...)
mysql> create procedure `p4`()
-> begin
-> set sql_mode='ansi';
-> set @a=concat('haha','wawa');
-> end
-> //
Query OK, 0 rows affected (0.34 sec)

mysql> call p4()//
Query OK, 0 rows affected (0.04 sec)

mysql> select @a//
+----------+
| @a |
+----------+
| hahawawa |
+----------+
1 row in set (0.00 sec)

[xihuluyou]

太谢谢了:)

可是我在命令行里测试,
mysql>set @a='haha';
mysql>set @b='wawa';
mysql>set sql_mode='ansi';
mysql>set @c=@a || @b;

返回的就是正确的
hahawawa

这两者是不同的吗?

[loveflea]

你在存储过程的外边设置sql_mode,mysql会自动记录sql_mode状态的,不管你以后运行的sql_mode是什么，存储过程都能得到正确的结果！

mysql> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE `p4`()
-> BEGIN
-> set @a='haha' || 'wawa';
-> END
-> //
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;

mysql> call p4();
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+----------+
| @a |
+----------+
| hahawawa |
+----------+
1 row in set (0.00 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> set @a='';
Query OK, 0 rows affected (0.00 sec)

mysql> call p4();
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+----------+
| @a |
+----------+
| hahawawa |
+----------+
1 row in set (0.00 sec)

mysql> show create procedure p4;
+-----------+-------------------------------------------------------------+-----
--------------------------------------------------------------------------------
--+
| Procedure | sql_mode | Crea
te Procedure
|
+-----------+-------------------------------------------------------------+-----
--------------------------------------------------------------------------------
--+
| p4 | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI | CREA
TE DEFINER="root"@"localhost" PROCEDURE "p4"()
BEGIN
set @a='haha' || 'wawa';
END |
+-----------+-------------------------------------------------------------+-----
--------------------------------------------------------------------------------
--+
1 row in set (0.00 sec)

[xihuluyou]

up 谁知道区别吗

[littleboys]

学习了