1818IP-服务器技术教程,云服务器评测推荐,服务器系统排错处理,环境搭建,攻击防护等

当前位置:首页 - 运维 - 正文

君子好学,自强不息!

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)

本文来源:1818IP

本文地址:https://www.1818ip.com/post/9853.html

免责声明:本文由用户上传,如有侵权请联系删除!

发表评论

必填

选填

选填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。