MySQL为Null会导致5个问题，个个致命！
发布时间：2020-12-31 14:00:57
热度： 3214 ℃
<p>在正式开始之前，我们先来看下 MySQL 服务器的配置和版本号信息，如下图所示：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609331580357-f0108445-c85c-449f-9950-4f2b6c075d1e.png#align=left&display=inline&height=279&margin=%5Bobject%20Object%5D&name=image.png&originHeight=558&originWidth=1096&size=70337&status=done&style=none&width=548" alt="image.png" loading="lazy"><br>“兵马未动粮草先行”，看完了相关的配置之后，我们先来创建一张测试表和一些测试数据。</p><pre><code class="language-sql hljs"><span class="hljs-comment">-- 如果存在 person 表先删除</span>
<span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> person;
<span class="hljs-comment">-- 创建 person 表，其中 username 字段可为空，并为其设置普通索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> person (
<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span> auto_increment,
<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
mobile <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">13</span>),
<span class="hljs-keyword">index</span>(<span class="hljs-keyword">name</span>)
) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-string">'innodb'</span>;
<span class="hljs-comment">-- person 表添加测试数据</span>
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> person(<span class="hljs-keyword">name</span>,mobile) <span class="hljs-keyword">values</span>(<span class="hljs-string">'Java'</span>,<span class="hljs-string">'13333333330'</span>),
(<span class="hljs-string">'MySQL'</span>,<span class="hljs-string">'13333333331'</span>),
(<span class="hljs-string">'Redis'</span>,<span class="hljs-string">'13333333332'</span>),
(<span class="hljs-string">'Kafka'</span>,<span class="hljs-string">'13333333333'</span>),
(<span class="hljs-string">'Spring'</span>,<span class="hljs-string">'13333333334'</span>),
(<span class="hljs-string">'MyBatis'</span>,<span class="hljs-string">'13333333335'</span>),
(<span class="hljs-string">'RabbitMQ'</span>,<span class="hljs-string">'13333333336'</span>),
(<span class="hljs-string">'Golang'</span>,<span class="hljs-string">'13333333337'</span>),
(<span class="hljs-literal">NULL</span>,<span class="hljs-string">'13333333338'</span>),
(<span class="hljs-literal">NULL</span>,<span class="hljs-string">'13333333339'</span>);
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> person;
</code></pre><p>构建的测试数据，如下图所示：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609334360387-b21af2f9-ed74-413d-bbea-6c0aa0b00869.png#align=left&display=inline&height=333&margin=%5Bobject%20Object%5D&name=image.png&originHeight=666&originWidth=530&size=55894&status=done&style=none&width=265" alt="image.png" loading="lazy"><br>有了数据之后，我们就来看当列中存在 <code>NULL</code> 值时，究竟会导致哪些问题？</p><h1 id="1count-数据丢失">1.count 数据丢失</h1><p>当某列存在 <code>NULL</code> 值时，再使用 <code>count</code> 查询该列，就会出现数据“丢失”问题，如下 SQL 所示：</p><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*),<span class="hljs-keyword">count</span>(<span class="hljs-keyword">name</span>) <span class="hljs-keyword">from</span> person;
</code></pre><p>查询执行结果如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609334410752-bc9bd6ca-9aee-4d1c-a930-c0d098751bf7.png#align=left&display=inline&height=56&margin=%5Bobject%20Object%5D&name=image.png&originHeight=112&originWidth=326&size=5890&status=done&style=none&width=163" alt="image.png" loading="lazy"><br>从上述结果可以看出，当使用的是 <code>count(name)</code> 查询时，就丢失了两条值为 <code>NULL</code> 的数据丢失。</p><h4 id="解决方案">解决方案</h4><p>如果某列存在 <code>NULL</code> 值时，就是用 <code>count(*)</code> 进行数据统计。</p><h4 id="扩展知识：不要使用-count常量">扩展知识：不要使用 count(常量)</h4><blockquote><p>阿里巴巴《Java开发手册》强制规定：不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。</p><p>说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。</p></blockquote><h1 id="2distinct-数据丢失">2.distinct 数据丢失</h1><p>当使用 <code>count(distinct col1, col2)</code> 查询时，如果其中一列为 <code>NULL</code>，那么即使另一列有不同的值，那么查询的结果也会将数据丢失，如下 SQL 所示：</p><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(<span class="hljs-keyword">distinct</span> <span class="hljs-keyword">name</span>,mobile) <span class="hljs-keyword">from</span> person;
</code></pre><p>查询执行结果如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609334455638-43ff8d29-4963-44bd-b331-174b93832f1e.png#align=left&display=inline&height=54&margin=%5Bobject%20Object%5D&name=image.png&originHeight=108&originWidth=336&size=5988&status=done&style=none&width=168" alt="image.png" loading="lazy"><br>数据库的原始数据如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609334659475-5a4e8691-d083-4244-96fb-8900fbf3d1dc.png#align=left&display=inline&height=335&margin=%5Bobject%20Object%5D&name=image.png&originHeight=670&originWidth=538&size=58717&status=done&style=none&width=269" alt="image.png" loading="lazy"><br>从上述结果可以看出手机号一列的 10 条数据都是不同的，但查询的结果却为 8。</p><h1 id="3select-数据丢失">3.select 数据丢失</h1><p>如果某列存在 <code>NULL</code> 值时，如果执行非等于查询（<>/!=）会导致为 <code>NULL</code> 值的结果丢失。<br>比如以下这个数据：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609336414923-78962e7f-e789-4fa1-9445-077a597e7327.png#align=left&display=inline&height=339&margin=%5Bobject%20Object%5D&name=image.png&originHeight=678&originWidth=580&size=57554&status=done&style=none&width=290" alt="image.png" loading="lazy"><br>我需要查询除 name 等于“Java”以外的所有数据，预期返回的结果是 id 从 2 到 10 的数据，但当执行以下查询时：</p><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> person <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span><><span class="hljs-string">'Java'</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">id</span>;
<span class="hljs-comment">-- 或</span>
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> person <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span>!=<span class="hljs-string">'Java'</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">id</span>;
</code></pre><p>查询结果均为以下内容：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609336573893-50feecb0-e944-46fe-b4e5-ab4ce7845dec.png#align=left&display=inline&height=269&margin=%5Bobject%20Object%5D&name=image.png&originHeight=538&originWidth=536&size=41888&status=done&style=none&width=268" alt="image.png" loading="lazy"><br>可以看出为 <code>NULL</code> 的两条数据凭空消失了，这个结果并不符合我们的正常预期。</p><h3 id="解决方案-1">解决方案</h3><p>要解决以上的问题，只需要在查询结果中拼加上为 <code>NULL</code> 值的结果即可，执行 SQL 如下：</p><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> person <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span><><span class="hljs-string">'Java'</span> <span class="hljs-keyword">or</span> <span class="hljs-keyword">isnull</span>(<span class="hljs-keyword">name</span>) <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">id</span>;
</code></pre><p>最终的执行结果如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609336783906-9c4663ef-af27-4af0-b96d-cf4b18ebfb80.png#align=left&display=inline&height=307&margin=%5Bobject%20Object%5D&name=image.png&originHeight=614&originWidth=552&size=52225&status=done&style=none&width=276" alt="image.png" loading="lazy"></p><h1 id="4导致空指针异常">4.导致空指针异常</h1><p>如果某列存在 <code>NULL</code> 值时，可能会导致 <code>sum(column)</code> 的返回结果为 <code>NULL</code> 而非 0，如果 <code>sum</code> 查询的结果为 <code>NULL</code> 就可以能会导致程序执行时空指针异常（NPE），我们来演示一下这个问题。</p><p>首先，我们先构建一张表和一些测试数据：</p><pre><code class="language-sql hljs"><span class="hljs-comment">-- 如果存在 goods 表先删除</span>
<span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> goods;
<span class="hljs-comment">-- 创建 goods 表</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> goods (
<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span> auto_increment,
<span class="hljs-keyword">num</span> <span class="hljs-built_in">int</span>
) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-string">'innodb'</span>;
<span class="hljs-comment">-- goods 表添加测试数据</span>
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> goods(<span class="hljs-keyword">num</span>) <span class="hljs-keyword">values</span>(<span class="hljs-number">3</span>),(<span class="hljs-number">6</span>),(<span class="hljs-number">6</span>),(<span class="hljs-literal">NULL</span>);
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> goods;
</code></pre><p>表中原始数据如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609335198639-dbce071a-81e6-4dab-97e7-591e40e2e7e2.png#align=left&display=inline&height=146&margin=%5Bobject%20Object%5D&name=image.png&originHeight=292&originWidth=318&size=11106&status=done&style=none&width=159" alt="image.png" loading="lazy"><br>接下来我们使用 <code>sum</code> 查询，执行以下 SQL：</p><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> <span class="hljs-keyword">sum</span>(<span class="hljs-keyword">num</span>) <span class="hljs-keyword">from</span> goods <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span>><span class="hljs-number">4</span>;
</code></pre><p>查询执行结果如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609335521443-859a5429-bd87-46cc-a054-f8acf2fa2f01.png#align=left&display=inline&height=51&margin=%5Bobject%20Object%5D&name=image.png&originHeight=102&originWidth=192&size=4774&status=done&style=none&width=96" alt="image.png" loading="lazy"><br>当查询的结果为 <code>NULL</code> 而非 0 时，就可以能导致空指针异常。</p><h3 id="解决空指针异常">解决空指针异常</h3><p>可以使用以下方式来避免空指针异常：</p><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> <span class="hljs-keyword">ifnull</span>(<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">num</span>), <span class="hljs-number">0</span>) <span class="hljs-keyword">from</span> goods <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span>><span class="hljs-number">4</span>;
</code></pre><p>查询执行结果如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609335659208-1181702b-66d9-474a-8e92-8da30c8e36bc.png#align=left&display=inline&height=59&margin=%5Bobject%20Object%5D&name=image.png&originHeight=118&originWidth=286&size=4975&status=done&style=none&width=143" alt="image.png" loading="lazy"></p><h1 id="5增加了查询难度">5.增加了查询难度</h1><p>当某列值中有 <code>NULL</code> 值时，在进行 <code>NULL</code> 值或者非 <code>NULL</code> 值的查询难度就增加了。</p><p>所谓的查询难度增加指的是当进行 <code>NULL</code> 值查询时，必须使用 <code>NULL</code> 值匹配的查询方法，比如 <code>IS NULL</code> 或者 <code>IS NOT NULL</code> 又或者是 <code>IFNULL(cloumn)</code> 这样的表达式进行查询，而传统的 <code>=、!=、<>...</code> 等这些表达式就不能使用了，这就增加了查询的难度，尤其是对小白程序员来说，接下来我们来演示一下这些问题。</p><p>还是以 <code>person</code> 表为例，它的原始数据如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609337488772-bdd4465b-0b13-4c9e-b39c-d4e8aed9630a.png#align=left&display=inline&height=340&margin=%5Bobject%20Object%5D&name=image.png&originHeight=680&originWidth=598&size=57387&status=done&style=none&width=299" alt="image.png" loading="lazy"></p><h4 id="错误用法-1：">错误用法 1：</h4><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> person <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span><><span class="hljs-literal">null</span>;
</code></pre><p>执行结果为空，并没有查询到任何数据，如下图所示：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609337528911-4349d7e3-987b-4a64-a9c0-f53a4991b127.png#align=left&display=inline&height=75&margin=%5Bobject%20Object%5D&name=image.png&originHeight=150&originWidth=592&size=5715&status=done&style=none&width=296" alt="image.png" loading="lazy"></p><h4 id="错误用法-2：">错误用法 2：</h4><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> person <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span>!=<span class="hljs-literal">null</span>;
</code></pre><p>执行结果也为空，没有查询到任何数据，如下图所示：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609337528911-4349d7e3-987b-4a64-a9c0-f53a4991b127.png#align=left&display=inline&height=75&margin=%5Bobject%20Object%5D&name=image.png&originHeight=150&originWidth=592&size=5715&status=done&style=none&width=296" alt="image.png" loading="lazy"></p><h4 id="正确用法-1：">正确用法 1：</h4><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> person <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>;
</code></pre><p>执行结果如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609337620037-806f72a0-e7d5-44ea-9a86-5701bef85ec1.png#align=left&display=inline&height=275&margin=%5Bobject%20Object%5D&name=image.png&originHeight=550&originWidth=616&size=46381&status=done&style=none&width=308" alt="image.png" loading="lazy"></p><h4 id="正确用法-2：">正确用法 2：</h4><pre><code class="language-sql hljs"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> person <span class="hljs-keyword">where</span> !<span class="hljs-keyword">isnull</span>(<span class="hljs-keyword">name</span>);
</code></pre><p>执行结果如下：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609337620037-806f72a0-e7d5-44ea-9a86-5701bef85ec1.png#align=left&display=inline&height=275&margin=%5Bobject%20Object%5D&name=image.png&originHeight=550&originWidth=616&size=46381&status=done&style=none&width=308" alt="image.png" loading="lazy"></p><h4 id="推荐用法">推荐用法</h4><p><strong>阿里巴巴《Java开发手册》推荐我们使用 <code>ISNULL(cloumn)</code> 来判断 <code>NULL</code> 值</strong>，原因是在 SQL 语句中，如果在 null 前换行，影响可读性；而 <code>ISNULL(column)</code> 是一个整体，简洁易懂。从性能数据上分析 <code>ISNULL(column)</code> 执行效率也更快一些。</p><h1 id="扩展知识：null-不会影响索引">扩展知识：NULL 不会影响索引</h1><p>细心的朋友可能发现了，我在创建 <code>person</code> 表的 <code>name</code> 字段时，为其创建了一个普通索引，如下图所示：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609337990811-051a80ec-8cfd-495c-b3a0-af2f66c4ddc4.png#align=left&display=inline&height=191&margin=%5Bobject%20Object%5D&name=image.png&originHeight=382&originWidth=1256&size=63266&status=done&style=none&width=628" alt="image.png" loading="lazy"><br>然后我们用 <code>explain</code> 来分析查询计划，看当 <code>name</code> 中有 <code>NULL</code> 值时是否会影响索引的选择。</p><p><code>explain</code> 的执行结果如下图所示：<br><img src="https://cdn.nlark.com/yuque/0/2020/png/92791/1609338106540-8fd804cf-0cef-4d42-b122-e5d6d12e93d5.png#align=left&display=inline&height=180&margin=%5Bobject%20Object%5D&name=image.png&originHeight=360&originWidth=1960&size=64177&status=done&style=none&width=980" alt="image.png" loading="lazy"><br>从上述结果可以看出，即使 <code>name</code> 中有 <code>NULL</code> 值也不会影响 MySQL 使用索引进行查询。</p><h1 id="总结">总结</h1><p>本文我们讲了当某列为 <code>NULL</code> 时可能会导致的 5 种问题：丢失查询结果、导致空指针异常和增加了查询的难度。因此在最后提倡大家在创建表的时候尽量设置 <code>is not null</code> 的约束，如果某列确实没有值，可以设置空值（''）或 0 作为其默认值。</p><blockquote><p>最后：大家还有因为 NULL 而造成的各种坑吗？欢迎评论区补充留言。</p></blockquote><h5 id="参考--鸣谢">参考 & 鸣谢</h5><p>阿里巴巴《Java开发手册》</p><blockquote><p>关注公众号「Java中文社群」发现更多干货。查看 Github 发现更多精彩：<a href="https://github.com/vipstone/algorithm" target="_blank">https://github.com/vipstone/algorithm</a></p></blockquote>
