<p><span/></p><p><span>接上篇</span></p><p><span>1勤家、熟悉下這個表結(jié)構(gòu)</span></p><blockquote><p><span>CREATE TABLE t_user ( </span></p><p> id int(10) unsigned NOT NULL AUTO_INCREMENT,</p><p> name varchar(60) DEFAULT NULL, </p><p> age int(4) DEFAULT NULL, </p><p> sex tinyint(2) DEFAULT NULL,</p><p> like varchar(255) DEFAULT NULL, PRIMARY KEY (id), </p><p> KEY idx_name_age_like (name,age,like)</p><p> ) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8;
</p></blockquote><p>
</p><p><span>2抵卫、查看數(shù)據(jù)</span></p><blockquote><p><span>select * from t_user;</span></p></blockquote><p>
</p><p><span/></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-e94b1dc03b5f341a.jpg" img-data="{"format":"jpeg","size":36011,"height":470,"width":910}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span>3、先來看這個sql結(jié)果</span></p><blockquote><p><span>select * from t_user where name = 0;</span></p></blockquote><p>
</p><p><span>看下面明明name的值沒有為0的 但是卻能查詢出來這么多數(shù)據(jù)</span></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-53c387ff879a4e8b.jpg" img-data="{"format":"jpeg","size":36396,"height":470,"width":910}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span>4抒巢、再來看下這個sql</span></p><blockquote><p><span>select * from t_user where name = 1;</span></p></blockquote><p>
</p><p><span>那么name明明是varchar類型的 并且值是1user 為什么會能查到呢贫贝？</span></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-0883249accd1ea3b.jpg" img-data="{"format":"jpeg","size":3892,"height":68,"width":956}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><blockquote><p><span>EXPLAIN select * from t_user where name = 1;</span></p></blockquote><p>
</p><p><span>而且name明明是有索引的為什么會全表掃描呢？</span></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-4f316cc6cfa0288d.jpg" img-data="{"format":"jpeg","size":12120,"height":86,"width":956}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span/></p><p><span>5蛉谜、再來看下這個sql</span></p><blockquote><p><span>select * from t_user where age ='a0';</span></p></blockquote><p>
</p><p><span>age是 int類型的 為什么a0 也能搜到</span></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-82f4d7fe64c3e0cf.jpg" img-data="{"format":"jpeg","size":3892,"height":79,"width":956}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span/></p><blockquote><p><span>select * from t_user where age ='a1';</span></p></blockquote><p>
</p><p><span>為什么a0能搜到 a1卻搜不到呢</span></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-cf68257c758a1880.jpg" img-data="{"format":"jpeg","size":3892,"height":71,"width":956}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span/></p><p><span/></p><p><span>到了這里大家應(yīng)該都發(fā)現(xiàn)問題了稚晚，</span><span>在mysql查詢中，當查詢條件左右兩側(cè)類型不匹配的時候會發(fā)生隱式轉(zhuǎn)換cast(index_filed as signed) </span></p><blockquote><p><span>select cast('1a' as signed),cast('a1' as signed),cast('1' as signed);</span></p></blockquote><p>
</p><p><span/></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-a41be3f77595cabd.jpg" img-data="{"format":"jpeg","size":5257,"height":86,"width":956}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span/></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-7743281366a093bf.jpg" img-data="{"format":"jpeg","size":57966,"height":355,"width":837}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span/></p><blockquote><p><span>select 11 + '11', 11 + 'aa', 'a1' + 'bb', 11 + '0.01a';</span></p></blockquote><p>
</p><p><span/></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-afb2d8aae9aa5e69.jpg" img-data="{"format":"jpeg","size":4808,"height":85,"width":956}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span/></p><blockquote><p><span>show warnings;</span></p></blockquote><p>
</p><p><span/></p><p/><div class="image-package"><img src="https://upload-images.jianshu.io/upload_images/8965049-2f03499b4b6ae357.jpg" img-data="{"format":"jpeg","size":18151,"height":178,"width":956}" class="uploaded-img" style="min-height:200px;min-width:200px;" width="auto" height="auto"/>
</div><p><span/></p><p><span>11 + 'aa'型诚，由于操作符兩邊的類型不一樣且符合第g條客燕，aa要被轉(zhuǎn)換成浮點型小數(shù)，然而轉(zhuǎn)換失斦帷（字母被截斷）也搓，可以認為轉(zhuǎn)成了 0，整數(shù)11被轉(zhuǎn)成浮點型還是它自己暮现，所以11 + 'aa' = 11还绘。</span></p><p><span/></p><p><span>0.01a轉(zhuǎn)成double型也是被截斷成0.01，所以11 + '0.01a' = 11.01栖袋。</span></p><p><span/></p><p><span/></p><p><span/></p>