SQL 写的秒,涨薪呱呱叫!就在前不久公司新来的实习生因为写错了一条SQL把数据库搞炸了。
图片来自 Pexels
新来的实习生小杨写了一条 SQL 语句:
SELECTwx_idfrom`user`WHEREwx_id=2
当小杨迫不及待准备下班回家的时候,隔壁的王经理一把抓住了小杨,并用 EXPLAIN 命令教育了小杨,小杨流下了没有文化的泪水。
这条 SQL 语句中,wx_id 是具有索引的,但是王经理查出来的结果却是这样的:
王经理的教育
小杨仔细一瞅 key 字段显示为 Null,很明显这条 SQL 语句没有走索引。
小杨心想“糟糕,又写错 SQL 语句了,这下又要面临运维和经理的混合双打了, 不行我得立马改下这条 SQL 语句,让我想想哪里出错了”!
小杨脑袋瓜疯狂乱撞,仔细回想表结构,忽然想到,wx_id 字段是 varchar 类型,自己查询的时候竟然没有加引号。
小杨一把抢过经理手里的键盘,往 wx_id 的查询条件上加了引号,结果:
果然这条 SQL 语句开始走了索引。小杨沾沾自喜以为解决了个天大的 Bug。
经理微微一笑问道“你知道为什么为什么加了引号就走了索引吗?如果字段是 int 类型,那么查询的时候需不需要加引号呢?又是为什么呢?”
正餐来了
小杨被问的呆在原地,无法回答。
经过小杨研究发现,如果字段是 varchar类型,等号右侧必须加引号才走索引;如果字段是 int 类型,那么等号右侧加不加引号都是会走索引的。
什么?你不相信小杨说的话,有图有真相。(bonus 字段类型为int)
真相图
但是结论出来,还是无法回答经理的夺命三连问。
小杨搬来了答案
在 MySQL 查询中,当查询条件左右两侧类型不匹配的时候会发生隐式转换:
也就是说 SELECTwx_idfrom`user`WHEREwx_id=2 等价于 SELECTwx_idfrom`user`WHERECAST(wx_idASsignedint)=2
一旦对索引字段做函数操作,MySQL 会放弃使用索引。
所以如果字段是 varchar 类型,等号右侧必须加引号才走索引,否则由于隐式转换,MySQL 会放弃使用索引。那么凭什么 int 加不加引号都可以使用索引呢?
那是因为 int 类型的数字只有 2 能转化为’2’,是唯一确定的。所以虽然需要隐式转换,但不影响使用索引
小杨追问:“你还能在告诉我一些隐式转换的知识吗?”
我反手就是一个英文文档:
IfoneorbothargumentsareNULL,theresultofthecomparisonisNULL,exceptfortheNULL-safe<=>equalitycomparisonoperator.ForNULL<=>NULL,theresultistrue.Noconversionisneeded. Ifbothargumentsinacomparisonoperationarestrings,theyarecomparedasstrings. Ifbothargumentsareintegers,theyarecomparedasintegers. Hexadecimalvaluesaretreatedasbinarystringsifnotcomparedtoanumber. IfoneoftheargumentsisaTIMESTAMPorDATETIMEcolumnandtheotherargumentisaconstant,theconstantisconvertedtoatimestampbeforethecomparisonisperformed.ThisisdonetobemoreODBC-friendly.NotethatthisisnotdonefortheargumentstoIN()!Tobesafe,alwaysusecompletedatetime,date,ortimestringswhendoingcomparisons.Forexample,toachievebestresultswhenusingBETWEENwithdateortimevalues,useCAST()toexplicitlyconvertthevaluestothedesireddatatype. Asingle-rowsubqueryfromatableortablesisnotconsideredaconstant.Forexample,ifasubqueryreturnsanintegertobecomparedtoaDATETIMEvalue,thecomparisonisdoneastwointegers.Theintegerisnotconvertedtoatemporalvalue.TocomparetheoperandsasDATETIMEvalues,useCAST()toexplicitlyconvertthesubqueryvaluetoDATETIME. Ifoneoftheargumentsisadecimalvalue,comparisondependsontheotherargument.Theargumentsarecomparedasdecimalvaluesiftheotherargumentisadecimalorintegervalue,orasfloating-pointvaluesiftheotherargumentisafloating-pointvalue. Inallothercases,theargumentsarecomparedasfloating-point(real)numbers.
贴心的我帮你们翻译成了中文:
1,两个参数至少有一个是NULL时,比较的结果也是NULL,例外是使用<=> 对两个NULL做比较时会返回1,这两种情况都不需要做类型转换 2,两个参数都是字符串,会按照字符串来比较,不做类型转换 3,两个参数都是整数,按照整数来比较,不做类型转换 4,十六进制的值和非数字做比较时,会被当做二进制串 5,有一个参数是TIMESTAMP或DATETIME,并且另外一个参数是常量,常量会被转换为timestamp 6,有一个参数是decimal类型,如果另外一个参数是decimal或者整数会将整数转换为decimal后进行比较, 如果另外一个参数是浮点数,则会把decimal转换为浮点数进行比较 7,所有其他情况下,两个参数都会被转换为浮点数再进行比较
再分享一个隐式转换的坑:你是否偶尔删除了一些不知道的数据?
mysql>select*fromtest; +----+-------+-----------+ |id|name|password| +----+-------+-----------+ |1|test1|password1| |2|test2|password2| |3|aaa|aaaa| |4|55aaa|55aaaa| |5|1212|aaa| |6|1212a|aaa| +----+-------+-----------+ 6rowsinset(0.00sec) mysql>select*fromtestwherename=1212; +----+-------+----------+ |id|name|password| +----+-------+----------+ |5|1212|aaa| |6|1212a|aaa| +----+-------+----------+ 2rowsinset,5warnings(0.00sec) mysql>select*fromtestwherename='1212'; +----+------+----------+ |id|name|password| +----+------+----------+ |5|1212|aaa| +----+------+----------+ 1rowinset(0.00sec)
上面的例子本意是查询 id 为 5 的那一条记录,结果把 id 为 6 的那一条也查询出来了。我想说明什么情况呢?
有时候我们的数据库表中的一些列是 varchar 类型,但是存储的值为‘1123’这种的纯数字的字符串值,一些同学写 SQL 的时候又不习惯加引号。
这样当进行 Select,Update或者 Delete 的时候就可能会多操作一些数据。所以应该加引号的地方别忘记了。
总而言之
隐式类型转换有无法命中索引的风险,在高并发、大数据量的情况下,命不中索引带来的后果可不止被运维和经理混合双打哦!且写 SQL 且 EXPLAIN!
作者:isysc1
编辑:陶家龙
出处:转载自微信公众号码儿嘟嘟骑(ID:maer_duduqi)