mysql那些事(1) WHERE条件 字符串的引号

<p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">前言：所谓的坑，两个意思，一个是软件本身的bug，一个是使用者常犯的错误。</p><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">phper在日常开发中经常和mysql打交道。特别是在没有分层的中小应用中，phper开发要关注sql语句的实现。</p><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: "><img src="http://p2.pstatp.com/large/1852000503b102d91b0a" alt="mysql那些事(1) WHERE条件 字符串的引号"/></p><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">入正题，上建表语句：</p><blockquote style="margin: 1em 0px; padding: 12px 10px; position: relative; line-height: 1.5; color: rgb(153, 153, 153); background: rgb(244, 245, 246); border: 1px solid rgb(232, 232, 232); font-family: "><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">DROP TABLE IF EXISTS test_sql;</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">CREATE TABLE test_sql (</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">name varchar(32) NOT NULL DEFAULT ‘0’ COMMENT ‘名字’,</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">PRIMARY KEY (id)</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=’测试表’;</p></blockquote><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">上数据：</p><blockquote style="margin: 1em 0px; padding: 12px 10px; position: relative; line-height: 1.5; color: rgb(153, 153, 153); background: rgb(244, 245, 246); border: 1px solid rgb(232, 232, 232); font-family: "><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">INSERT INTO test_sql</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">VALUES</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">(‘1’, ‘0’),</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">(‘2’, ‘www.haodaquan.com’),</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">(‘3’, ‘0’),</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">(‘4’, ‘123’),</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">(‘5’, ‘123php’),</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">(‘6’, ‘123php在路上’),</p><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">(‘7’, ‘php在路上’);</p></blockquote><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">好了，看到的是这张表：</p><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: "><img src="http://p3.pstatp.com/large/17f1000645bc4cf52262" alt="mysql那些事(1) WHERE条件 字符串的引号"/></p><h1 style="margin: 36px 0px 22px; padding: 0px; font-size: 24px; line-height: 32px; color: rgb(34, 34, 34); font-family: ">题目来了：请查找name值为0的数据。<br/></h1><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">于是错误的sql出来了：</p><blockquote style="margin: 1em 0px; padding: 12px 10px; position: relative; line-height: 1.5; color: rgb(153, 153, 153); background: rgb(244, 245, 246); border: 1px solid rgb(232, 232, 232); font-family: "><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">select id,name from test_sql where name=0</p></blockquote><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">结果与预期不符，如下图：</p><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: "><img src="http://p3.pstatp.com/large/18520004f2ce61f9b61b" alt="mysql那些事(1) WHERE条件 字符串的引号"/></p><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">为什么？</p><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">sql也是弱类型啊，比较0和字符串的时候，也是返回true的。</p><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: ">正确的sql:</p><blockquote style="margin: 1em 0px; padding: 12px 10px; position: relative; line-height: 1.5; color: rgb(153, 153, 153); background: rgb(244, 245, 246); border: 1px solid rgb(232, 232, 232); font-family: "><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">select id,name from test_sql where name=‘0’</p></blockquote><p style="margin-top: 18px; margin-bottom: 0px; padding: 0px; color: rgb(34, 34, 34); font-family: "><img src="http://p3.pstatp.com/large/18500005d971098a7ebc" alt="mysql那些事(1) WHERE条件 字符串的引号"/></p><blockquote style="margin: 1em 0px; padding: 12px 10px; position: relative; line-height: 1.5; color: rgb(153, 153, 153); background: rgb(244, 245, 246); border: 1px solid rgb(232, 232, 232); font-family: "><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px;"><span style="font-weight: 700;">结论：字符类型的字段做where 条件时一定要加引号呀。</span></p></blockquote><p><br/></p>