set @state2=
case
when contractCategories='0' then ''
when contractCategories='1' then ' and experimentGroupID is not null'
when contractCategories='2' then concat(' and groupid in(select groupID from contractgroup where description=','研发类)')---出现错误
when contractCategories='3' then concat(' and groupid in(select groupID from contractgroup where description=','贸易类)')
end;
我写的存储过程中,当执行第二行就会出现错误,请问MySQL中有没有像sql server中的convert()函数一样的转换函数。如果没有,我该怎么做。
case
when contractCategories='0' then ''
when contractCategories='1' then ' and experimentGroupID is not null'
when contractCategories='2' then concat(' and groupid in(select groupID from contractgroup where description=','研发类)')---出现错误
when contractCategories='3' then concat(' and groupid in(select groupID from contractgroup where description=','贸易类)')
end;
我写的存储过程中,当执行第二行就会出现错误,请问MySQL中有没有像sql server中的convert()函数一样的转换函数。如果没有,我该怎么做。
解决方案 »
- MYSQL里面如何将两表同列合并
- mysql中的表空间的概念是逻辑概念还是物理概念?
- 大家来看看这个查询怎么写?
- linux下安装mysql(二进制包)的问题
- MySql 记录,乱码。。从服务器上提交的汉子,是?号?
- 急-->关于“您的主机中的软件放弃了一个已建立的连接”问题。
- postgresql中文乱码的问题
- sql server 2000的数据库结构,如何导到mysql中去?有没有这样的工具?
- 紧急求助:关于ecpg预编译时不认识WHERE CURRENT OF cursor 语句的问题
- 我想把mysql数据库迁移到ms sql数据库 如何操作?
- 求教MYSQL无法启动问题
- mysql连接符concat
CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name) The CAST() and CONVERT() functions take a value of one type and produce a value of another type. The type can be one of the following values: BINARY[(N)] CHAR[(N)] DATE DATETIME DECIMAL SIGNED [INTEGER] TIME UNSIGNED [INTEGER]
set state2=case when contractCategories=.....把这个@去掉。
SELECT @state2
看看结果,
第二次运行时,再看看结果
mysql>
mysql> CREATE PROCEDURE simpleproc (IN contractCategories char(1))
-> BEGIN
-> declare state2 varchar(100);
-> set state2=
-> case
-> when contractCategories='0' then ''
-> when contractCategories='1' then ' and experimentGroupID is notnull'
-> when contractCategories='2' then concat(' and groupid in(select groupID from contractgroup where description=','研发类)')
-> when contractCategories='3' then concat(' and groupid in(select groupID from contractgroup where description=','贸易类)')
-> end;
->
-> SELECT state2;
-> END;
-> //
Query OK, 0 rows affected (0.06 sec)mysql>
mysql> delimiter ;
mysql>
mysql> CALL simpleproc('2');
+------------------------------------------------------------------------------+| state2 |+------------------------------------------------------------------------------+| and groupid in(select groupID from contractgroup where description=研发类) |+------------------------------------------------------------------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>
你可以 set names 'bg2312' 试一下。如果可以则在mysql的 my.ini文件中改一下 character_set_server='utf8' 或者gb2312
set @state2=
case
when @contractCategories='0' then ''
when @contractCategories='1' then ' and experimentGroupID is not null'
when @contractCategories='2' then concat(' and groupid in(select groupID from contractgroup where description=\'','研发类\')')
when @contractCategories='3' then concat(' and groupid in(select groupID from contractgroup where description=\'','贸易类\')')
end;
@contractCategories='1'
运行时都有结果,但是到@contractCategories='2'就没有结果了,弹出一个乱码的对话框
show variables like 'char%';
贴结果
结果
OR
检查一下 MY.INI中
[client]default-character-set=utf8
[mysql]default-character-set=utf8
你可以到MySQL的数据文件目录下找到 xxx.err 看一下里面的错误信息。 这样也容易让大家了解你的问题和模拟你的问题。mysql>
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE `simpleproc`()
-> BEGIN
-> set @state2=
-> case
-> when @contractCategories='0' then ''
-> when @contractCategories='1' then ' and experimentGroupID is notnull'
-> when @contractCategories='2' then concat(' and groupid in(select groupID from contractgroup where description=','研发类)')
-> when @contractCategories='3' then concat(' and groupid in(select groupID from contractgroup where description=','贸易类)')
-> end;
->
-> END//
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;
mysql>
mysql> set @contractCategories='1';
Query OK, 0 rows affected (0.00 sec)mysql> call simpleproc();
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> select @state2
-> ;
+-----------------------------------+
| @state2 |
+-----------------------------------+
| and experimentGroupID is notnull |
+-----------------------------------+
1 row in set (0.00 sec)mysql> set @contractCategories='2';
Query OK, 0 rows affected (0.00 sec)mysql> call simpleproc();
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> select @state2;
+------------------------------------------------------------------------------+| @state2 |+------------------------------------------------------------------------------+| and groupid in(select groupID from contractgroup where description=研发类) |+------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>
when contractCategories='2' then concat(' and groupid in(select groupID from contractgroup where description=',char(39),'贸易类',char(39),')')
用了ASCII码表的方式。感谢大家热心。