[基操篇]mysql：你以为你想要的就是你以为的么？

<p><span/></p><p><span>接上篇</span></p><p><span>1、熟悉下这个表结构</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、查看数据</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结果</span></p><blockquote><p><span>select * from t_user where name = 0;</span></p></blockquote><p>
</p><p><span>看下面明明name的值没有为0的 但是却能查询出来这么多数据</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>到了这里大家应该都发现问题了，</span><span>在mysql查询中，当查询条件左右两侧类型不匹配的时候会发生隐式转换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要被转换成浮点型小数，然而转换失败（字母被截断），可以认为转成了 0，整数11被转成浮点型还是它自己，所以11 + 'aa' = 11。</span></p><p><span/></p><p><span>0.01a转成double型也是被截断成0.01，所以11 + '0.01a' = 11.01。</span></p><p><span/></p><p><span/></p><p><span/></p>