文档章节

MYSQL- function 传参 concat 中文乱码

飞天奔月
 飞天奔月
发布于 2017/05/17 12:02
字数 1339
阅读 19
收藏 0

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 想说爱你不容易

© 著作权归作者所有

飞天奔月

飞天奔月

粉丝 31
博文 14
码字总数 13550
作品 2
闸北
架构师
私信 提问
javaweb前台对参数加密,后台解密,避免出现中文乱码

废话不多说,在web开放中遇到前台向后台传值,注意:中文值的时候,后台如果单纯的用request.getParamter(name);接收,得到的是一个乱码,如何避免出现中文乱码?这里简单的介绍下,我自己的...

什么是程序员
2015/12/17
192
0
Java URL传参中文乱码问题

我们经常会遇到这样的场景:在URL中传递参数,如果该参数为中文,如果设置不当,会出现乱码问题。 URL传参所使用的编码为服务器中设置的编码,而ajax请求的编码为页面编码。 如果不设置tomca...

程序猿之小菜鸟
2015/08/28
623
0
关于jsp访问路径带中文值需encodeURI两次的原因

jsp往后台传值的时候,一般可以通过路径传值和ajax传值。 其中通过ajax post传值不会出现中文乱码现象,但路径传值如果不通过特殊的编码,后台可能获取到的是一串乱码。 encodeURI可以帮助我...

文文1
2016/07/12
307
0
jquery获取URL中参数解决中文乱码问题的两种方法

从A页面通过url传参到B页面时,获取URL中参数出现中文乱码问题,解析url参数的正确方法如下,感兴趣的朋友可以参考下 从A页面通过url传参到B页面时,解析url参数可以用下面两种方法: 方法一...

linghangp
2013/12/19
1K
1
oracle列插入字符串+自增(people001、people002格式)

update 表 set id = concat(“people”,to_char(rownum,’fm999900’)); 从oracle导出cvs文件导入mysql,如出现中文乱码,可以用文档编辑器打开cvs文件,更改字符集再保存,然后导入mysql;...

alantuling_jt
2017/02/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

家庭作业——苗钰婷

2 编写一个程序,发出一声警报,然后打印下面的文本: Startled by the sudden sound, Sally shouted, "By the Great Pumpkin, what was that! #include<stdio.h>int main(){......

OSC_Okruuv
22分钟前
4
0
经典系统设计面试题解析:如何设计TinyURL(一)

原文链接: https://www.educative.io/courses/grokking-the-system-design-interview/m2ygV4E81AR 编者注:本文以一道经典的系统设计面试题:《如何设计TinyURL》的参考答案和解析为例,帮助...

APEMESH
23分钟前
2
0
2.面向对象设计原则(7条)

开闭原则 开闭原则的含义是:当应用的需求改变时,在不修改软件实体的源代码或者二进制代码的前提下,可以扩展模块的功能,使其满足新的需求。 实现方法 可以通过“抽象约束、封装变化”来实...

Eappo_Geng
25分钟前
4
0
8086汇编基础 debug P命令 一步完成loop循环

    IDE : Masm for Windows 集成实验环境 2015     OS : Windows 10 x64 typesetting : Markdown    blog : my.oschina.net/zhichengjiu    gitee : gitee.com/zhichengjiu   ......

志成就
29分钟前
3
0
使用nodeJS实现前端项目自动化之项目构建和文件合并

本文转载于:专业的前端网站➜使用nodeJS实现前端项目自动化之项目构建和文件合并 前面的话   一般地,我们使用构建工具来完成项目的自动化操作。本文主要介绍如何使用nodeJS来实现简单的项...

前端老手
43分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部