MySQL中字符串和整数到底是怎么比较的?

1、业务背景

我需要检索出在某些部门下的所以员工信息。其中传入的参数是部门id列表。

select * from x_table where y_file in ();

对于mybatis来说,部门id列表需要mybatis中的foreach迭代器去迭代该参数。

我认为foreach遍历也是将id列表拼接为以英文逗号(,)隔开的字符串,所以我就在程序中多做了一层转换,直接在mybatis中的sql为:

select * from x_table where y_file in (#{string});

string即是我把id列表手动转化的以逗号隔开的字符串。

大家觉得我这样做有什么问题吗?该sql是否可以执行?如果可以,查询的数据是否正确呢?

2、出现的问题

在测试接口的阶段,发现该sql是可以执行的,但是查询的结果却不正确!于是我在控制台打印出mybatis执行的sql语句,我将该sql拷贝出来后,在数据库执行一次,验证该sql是否有误,令人莫名其妙的是数据库执行后的结果是正确。

在反复定位问题的过程中,发现了一个令我很费解的规律。这条sql只能检索出in条件中的第一个参数的数据。例如我的sql是select * from x_table where y_file in (1,2,3);查询结果中只能检索到 y_file=1的数据。

3、揭晓答案

我相信有的小伙伴已经注意到我在描述背景的时候,我在mybatis中写的sql时用的#{}字符,那么你们是否了解#字符和$字符有什么区别呢?

#{}符号和${}符号的区别:

#实现的是sql语句的预处理参数、之后执行sql中用?号代替、使用时不需要关注数据类型、mybatis自动实现数据转换,优点就是可以防止SQL注入。$实现是sql语句的直接拼接、不做数据类型转换。需要自行判断数据类型,缺点不能防止sql注入(ps 使用order by时,就要用${})

所以mybatis中执行的sql是:select * from x_table where y_file in ('1,2,3');

哦~它把我传入的参数添加了引号,所以才导致查询数据错误!但是为什么我可以检索到 y_file=1的数据呢?真让人头大!

4、解决方法

尽然是引号"惹来的问题,那就让它原样填充到sql里就行啦~

简单粗暴的解决方案:把#号字符改成$符号就o了!

而最标准的做法还是用mybatis中的foreach迭代器让它自己遍历填入参数把~毕竟$符号不能防止恶意sql注入的问题

5、能够检索出部分数据的原因:

下面我将以举案例的形式为大家讲解为什么能查询出部分数据的原因

表结构如下:

图1-1 表结构

表内数据如下:

图1-2  表数据

执行SQL1如下:

图1-3 SQL1

有的小伙伴可能要抓狂了,可能都看不下去了,内心ps(你上面都说了数据库存的是字符串,你为啥还要拿一个整数类型的去比较。。。没事找事吧)先别急,咱们猜猜这个sql可以执行成功吗?执行结果是什么?

图1-4 SQL1的执行结果

what?此处假装有一个黑人问号脸。

执行SQL2如下:

图1-5 SQL2

我们再猜一猜执行结果

图1-6 SQL2执行结果


数据库中比较整数和字符串的(粗略)规则:

挨个的拿字符串进行转换为整数类型后,逐个与整数类型进行比较,直至遇到第一个为非数字的字符为止。

比如案例1中,首先筛选出string_x为89abc的字符串和整数类型的89比较,遍历字符串的第一个字符8,将其转换成整数类型后,将其与整数类型的第一个字符8进行比较,通过后,比较字符串的第二个字符9...通过后,比较第三个字符a,发现其不是数字类型,就停止比较了。

那它是mysql的一个bug吗?

mysql说:这个锅我不背!让不同类型进行比较本来就是一种“冒险”的做法。你在不了其比较规则的情况下,为什么要瞎用别的类型。

6、总结

1、#符号和$符号的区别,#符号是执行的sql预编译处理参数,能够防止sql注入,而$符号是动态拼装参数,将参数原样填充到sql语句里。

2、mysql中字符串和整数的比较规则是将字符串转化为整数类型后,进行比较,直到遇到第一个非数字的字符为止。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 215,723评论 6 498
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,003评论 3 391
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 161,512评论 0 351
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,825评论 1 290
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,874评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,841评论 1 295
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,812评论 3 416
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,582评论 0 271
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,033评论 1 308
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,309评论 2 331
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,450评论 1 345
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,158评论 5 341
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,789评论 3 325
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,409评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,609评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,440评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,357评论 2 352

推荐阅读更多精彩内容

  • 1. 简介 1.1 什么是 MyBatis ? MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的...
    笨鸟慢飞阅读 5,505评论 0 4
  • ORA-00001: 违反唯一约束条件 (.) 错误说明:当在唯一索引所对应的列上键入重复值时,会触发此异常。 O...
    我想起个好名字阅读 5,300评论 0 9
  • 一、Python简介和环境搭建以及pip的安装 4课时实验课主要内容 【Python简介】: Python 是一个...
    _小老虎_阅读 5,739评论 0 10
  • 官网 中文版本 好的网站 Content-type: text/htmlBASH Section: User ...
    不排版阅读 4,380评论 0 5
  • pyspark.sql module Module context Spark SQL和DataFrames中的重...
    盗梦者_56f2阅读 5,426评论 0 19