MYSQL- function 传参 concat 中文乱码

1. 背景

以前使用 postgresql 写存储过程/function 比较多, 这次工作过程中,需要做数据迁移, 将 MYSQL 某些表的数据转成 pgsql数据库中某些表数据

在转换的过程中,需要有以下的转换SQL

if(@birthday is null) then
   @birthday='null';
else    
   @birthday=concat('\'',@birthday,"\'");
end if;

如果 birthday 没有值,那么将使用 'null' 字符串,如果有值,将添加单引号

除了birthday ,我还需要处理

• @local_real_name
• @login_mobile
• @login_name

基于 DRY (Don't repeat youself) 原则, 这里应该写个function

2. 第一版function

于是乎,我就参考了mysql 文档,写了个 function

drop function if exists ifNullElseWithSigleQuotes;

--  -----------------------------------
create function ifNullElseWithSigleQuotes(
    in_string      varchar(255)
)
returns varchar(255)
begin
    declare resultValue varchar(255);

    if(in_string is null) then
       set resultValue='null';
    else    
       set resultValue=concat('\'',in_string,'\'');
    end if;

    return(resultValue);
end

很简洁吧,肉眼看看,没毛病

执行下, 提示:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

(you *might* want to use the less safe log_bin_trust_function_creators variable)

看了下帮助文档

[NOT] DETERMINISTIC:这个是用于binlog和主从复制等！DETERMINISTIC是确定的，
意思就是写入binlog的时候，写入的是一个指定的常量；如unix_timestamp()获取到的值是1，可能写入binlog的时候，unix_timestamp()获取到的时间戳却成了3了,这个时候会出现数据不一致问题,所以引入了DETERMINISTIC！这是binlog安全的一种机制！一般情况下，NOT DETERMINISTIC不允许使用，会报如下错误：
Error CODE : 1418
This FUNCTION has NONE of DETERMINISTIC, NO SQL, OR READS SQL DATA IN its declaration AND BINARY logging IS enabled (you *might* want TO USE the LESS safe log_bin_trust_function_creators variable)

可以从报错内容里面发现，设置log_bin_trust_function_creators函数就可以使用NOT DETERMINISTIC，但是二进制安全性极差！

CONTAINS SQL表示子程序不包含读或写数据的语句;

NO SQL表示子程序不包含SQL语句。

READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。

MODIFIES SQL DATA表示子程序包含写数据的语句。

如果这些特征没有明确给定，默认的是CONTAINS SQL。

我这个function 里面没有sql语句, 那么加上了 no sql

create function ifNullElseWithSigleQuotes(
    in_string      varchar(255)
)
returns varchar(255)
no sql

begin
    declare resultValue varchar(255);

    if(in_string is null) then
       set resultValue='null';
    else    
       set resultValue=concat('\'',in_string,'\'');
    end if;

    return(resultValue);

end

但是依然执行不了

最终解决方案: 找到我们的DBA,将mysql bin-log 参数调整了

参考: http://www.educity.cn/wenda/402115.html

3. 执行

解决了 function创建异常之后, 我们创建function成功,

3.1 test null

select ifNullElseWithSigleQuotes(null);

结果:

ifNullElseWithSigleQuotes(null) |
--------------------------------|
null                            |

没毛病

3.2 test mobile

select ifNullElseWithSigleQuotes('15001841110');

结果:

ifNullElseWithSigleQuotes('15001841110') |
-----------------------------------------|
'15001841110'                            |

也没毛病

3.3 test local_real_name

select ifNullElseWithSigleQuotes('程序员鼓励师');

结果:

SQL 错误 [1366] [HY000]: Incorrect string value: '\xE7\xA8\x8B\xE5\xBA\x8F...' for column 'in_string' at row 1

  java.sql.SQLException: Incorrect string value: '\xE7\xA8\x8B\xE5\xBA\x8F...' for column 'in_string' at row 1

咦,什么情况, 难道是 程序员鼓励师 太美,导致 function 不能执行?

4. 寻找解决方案

第一感觉是乱码, 是不是哪里的编码没有设置,

4.1 找到文档, 加上 charset utf8

drop function if exists ifNullElseWithSigleQuotes;

--  -------------------------------------
create function ifNullElseWithSigleQuotes(
    in_string      varchar(255) charset utf8
)
returns varchar(255) charset utf8
no sql

begin
    declare resultValue varchar(255);

    if(in_string is null) then
       set resultValue='null';
    else    
       set resultValue=concat('\'',in_string,'\'');
    end if;

    return(resultValue);

end

执行:

select ifNullElseWithSigleQuotes('程序员鼓励师');

结果:

SQL 错误 [1366] [HY000]: Incorrect string value: '\xE7\xA8\x8B\xE5\xBA\x8F...' for column 'resultValue' at row 1

java.sql.SQLException: Incorrect string value: '\xE7\xA8\x8B\xE5\xBA\x8F...' for column 'resultValue' at row 1

涛声依旧, 问题依旧

4.2 咨询了下 我们的DBA

DBA启迪了我一个思路, 是不是内部调用的函数有问题, 那么 我修改了一版

drop function if exists ifNullElseWithSigleQuotes;

--  -----------------------------------
create function ifNullElseWithSigleQuotes(
    in_string      varchar(255) charset utf8
)
returns varchar(255) charset utf8
no sql

begin    
    return('111');    
end

执行:

select ifNullElseWithSigleQuotes('程序员鼓励师');

结果:

ifNullElseWithSigleQuotes('程序员鼓励师') |
------------------------------------|
111                                 |

嘿, 是不是很神奇? 看来传参和 return 部分代码没有问题

4.3 concat 的问题?

那就是 concat 有问题了? 找找资料

google 下 mysql concat Incorrect string value

找到了些资料, 但是大部分资料都是说

concat(str1,str2)

当concat结果集出现乱码时，大都是由于连接的字段类型不同导致，如concat中的字段参数一个是varchar类型，一个是int类型或doule类型，就会出现乱码。

解决方法：利用mysql的字符串转换函数CONVERT将参数格式化为char类型就可以了。

举例： concat('数量:',CONVERT(int1,char),CONVERT(int2,char),'金额:',CONVERT(double1,char),CONVERT(double2,char))

但是我的代码

select concat('\'','程序员鼓励师','\'');

结果 :

concat('\'','程序员鼓励师','\'') |
---------------------------|
'程序员鼓励师'                   |

没毛病啊

4.4 柳暗花明

在我没辙的时候, 我看到

 declare resultValue varchar(255);

我给他也加了 charset

代码 :

drop function if exists ifNullElseWithSigleQuotes;

--  ----------------------------
create function ifNullElseWithSigleQuotes(
    in_string      varchar(255) charset utf8
)
returns varchar(255) charset utf8
no sql

begin
    declare resultValue varchar(255) charset utf8;

    if(in_string is null) then
       set resultValue='null';
    else    
       set resultValue=concat('\'',in_string,'\'');
    end if;

    return(resultValue);

end

执行:

select ifNullElseWithSigleQuotes('程序员鼓励师');

结果 :

ifNullElseWithSigleQuotes('程序员鼓励师') |
------------------------------------|
'程序员鼓励师'                            |

5.总结

• 遇到问题要寻找可能解决的办法(找人问,找资料)
• 多尝试,多总结
• 这个小function 我花费了4个小时, 我觉得有必要做个总结,希望如果遇到相同的问题,看了我的这个文章,4分钟就搞定了
• mysql 想说爱你不容易