mysql5.7 主键null_Mysql NULL导致的神坑

最新推荐文章于 2023-08-16 17:03:51 发布
最新推荐文章于 2023-08-16 17:03:51 发布
阅读量307 收藏
点赞数
文章标签： mysql5.7 主键null
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_34917128/article/details/113297454
版权

比较运算符中使用NULL

mysql> select 1>NULL;

+--------+

| 1>NULL |

+--------+

| NULL |

+--------+

1 row in set (0.00 sec)

mysql> select 1

+--------+

| 1

+--------+

| NULL |

+--------+

1 row in set (0.00 sec)

mysql> select 1<>NULL;

+---------+

| 1<>NULL |

+---------+

| NULL |

+---------+

1 row in set (0.00 sec)

mysql> select 1>NULL;

+--------+

| 1>NULL |

+--------+

| NULL |

+--------+

1 row in set (0.00 sec)

mysql> select 1

+--------+

| 1

+--------+

| NULL |

+--------+

1 row in set (0.00 sec)

mysql> select 1>=NULL;

+---------+

| 1>=NULL |

+---------+

| NULL |

+---------+

1 row in set (0.00 sec)

mysql> select 1<=NULL;

+---------+

| 1<=NULL |

+---------+

| NULL |

+---------+

1 row in set (0.00 sec)

mysql> select 1!=NULL;

+---------+

| 1!=NULL |

+---------+

| NULL |

+---------+

1 row in set (0.00 sec)

mysql> select 1<>NULL;

+---------+

| 1<>NULL |

+---------+

| NULL |

+---------+

1 row in set (0.00 sec)

mysql> select NULL=NULL,NULL!=NULL;

+-----------+------------+

| NULL=NULL | NULL!=NULL |

+-----------+------------+

| NULL | NULL |

+-----------+------------+

1 row in set (0.00 sec)

mysql> select 1 in (null),1 not in (null),null in (null),null not in (null);

+-------------+-----------------+----------------+--------------------+

| 1 in (null) | 1 not in (null) | null in (null) | null not in (null) |

+-------------+-----------------+----------------+--------------------+

| NULL | NULL | NULL | NULL |

+-------------+-----------------+----------------+--------------------+

1 row in set (0.00 sec)

mysql> select 1=any(select null),null=any(select null);

+--------------------+-----------------------+

| 1=any(select null) | null=any(select null) |

+--------------------+-----------------------+

| NULL | NULL |

+--------------------+-----------------------+

1 row in set (0.00 sec)

mysql> select 1=all(select null),null=all(select null);

+--------------------+-----------------------+

| 1=all(select null) | null=all(select null) |

+--------------------+-----------------------+

| NULL | NULL |

+--------------------+-----------------------+

1 row in set (0.00 sec)

结论：任何值和NULL使用运算符(>、=、<=、!=、<>)或者(in、not in、any/some、all)比较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0。

准备数据

mysql> create table test1(a int,b int);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values (1,1),(1,null),(null,null);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test1;

+------+------+

| a | b |

+------+------+

| 1 | 1 |

| 1 | NULL |

| NULL | NULL |

+------+------+

3 rows in set (0.00 sec)

上面3条数据，认真看一下，特别是注意上面NULL的记录。

IN、NOT IN和NULL比较

IN和NULL比较

mysql> select * from test1;

+------+------+

| a | b |

+------+------+

| 1 | 1 |

| 1 | NULL |

| NULL | NULL |

+------+------+

3 rows in set (0.00 sec)

mysql> select * from test1 where a in (null);

Empty set (0.00 sec)

mysql> select * from test1 where a in (null,1);

+------+------+

| a | b |

+------+------+

| 1 | 1 |

| 1 | NULL |

+------+------+

2 rows in set (0.00 sec)

结论：当IN和NULL比较时，无法查询出为NULL的记录。

NOT IN 和NULL比较

mysql> select * from test1 where a not in (1);

Empty set (0.00 sec)

mysql> select * from test1 where a not in (null);

Empty set (0.00 sec)

mysql> select * from test1 where a not in (null,2);

Empty set (0.00 sec)

mysql> select * from test1 where a not in (2);

+------+------+

| a | b |

+------+------+

| 1 | 1 |

| 1 | NULL |

+------+------+

2 rows in set (0.00 sec)

结论：当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空。

EXISTS、NOT EXISTS和NULL比较

mysql> select * from test2;

+------+------+

| a | b |

+------+------+

| 1 | 1 |

| 1 | NULL |

| NULL | NULL |

+------+------+

3 rows in set (0.00 sec)

mysql> select * from test1 t1 where exists (select * from test2 t2 where t1.a = t2.a);

+------+------+

| a | b |

+------+------+

| 1 | 1 |

| 1 | NULL |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from test1 t1 where not exists (select * from test2 t2 where t1.a = t2.a);

+------+------+

| a | b |

+------+------+

| NULL | NULL |

+------+------+

1 row in set (0.00 sec)

上面我们复制了表test1创建了表test2。

查询语句中使用exists、not exists对比test1.a=test2.a，因为=不能比较NULL，结果和预期一致。

判断NULL只能用IS NULL、IS NOT NULL

mysql> select 1 is not null;

+---------------+

| 1 is not null |

+---------------+

| 1 |

+---------------+

1 row in set (0.00 sec)

mysql> select 1 is null;

+-----------+

| 1 is null |

+-----------+

| 0 |

+-----------+

1 row in set (0.00 sec)

mysql> select null is null;

+--------------+

| null is null |

+--------------+

| 1 |

+--------------+

1 row in set (0.00 sec)

mysql> select null is not null;

+------------------+

| null is not null |

+------------------+

| 0 |

+------------------+

1 row in set (0.00 sec)

看上面的效果，返回的结果为1或者0。

结论：判断是否为空只能用IS NULL、IS NOT NULL。

聚合函数中NULL的坑

示例

mysql> select count(a),count(b),count(*) from test1;

+----------+----------+----------+

| count(a) | count(b) | count(*) |

+----------+----------+----------+

| 2 | 1 | 3 |

+----------+----------+----------+

1 row in set (0.00 sec)

count(a)返回了2行记录，a字段为NULL的没有统计出来。

count(b)返回了1行记录，为NULL的2行记录没有统计出来。

count(*)可以统计所有数据，不论字段的数据是否为NULL。

再继续看

mysql> select * from test1 where a is null;

+------+------+

| a | b |

+------+------+

| NULL | NULL |

+------+------+

1 row in set (0.00 sec)

mysql> select count(a) from test1 where a is null;

+----------+

| count(a) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

上面第1个sql使用is null查询出了结果，第2个sql中count(a)返回的是0行。

结论：count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行。

NULL不能作为主键的值

mysql> create table test3(a int primary key,b int);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test3 values (null,1);

ERROR 1048 (23000): Column 'a' cannot be null

上面我们创建了一个表test3，字段a未指定不能为空，插入了一条NULL的数据，报错原因：a 字段的值不能为NULL，我们看一下表的创建语句：

mysql> show create table test3;

+-------+------------+

| Table | Create Table |

+-------+------------+

| test3 | CREATE TABLE `test3` (

`a` int(11) NOT NULL,

`b` int(11) DEFAULT NULL,

PRIMARY KEY (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

+-------+------------+

1 row in set (0.00 sec)

从上面的脚本可以看出，当字段为主键的时候，字段会自动设置为not null。

结论：当字段为主键的时候，字段会自动设置为not null。

看了上面这些还是比较晕，NULL的情况确实比较难以处理，容易出错，最有效的方法就是避免使用NULL。所以，强烈建议创建字段的时候字段不允许为NULL，设置一个默认值。

总结

NULL作为布尔值的时候，不为1也不为0

任何值和NULL使用运算符(>、=、<=、!=、<>)或者(in、not in、any/some、all)，返回值都为NULL

当IN和NULL比较时，无法查询出为NULL的记录

当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空

判断是否为空只能用IS NULL、IS NOT NULL

count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行

当字段为主键的时候，字段会自动设置为not null

NULL导致的坑让人防不胜防，强烈建议创建字段的时候字段不允许为NULL，给个默认值

到此这篇关于Mysql NULL导致的神坑的文章就介绍到这了,更多相关Mysql NULL导致坑内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

点点0605
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql使用in查null值的解决方案,适用集合in
小冷猫的博客
06-09 5227
日常开发中可能会遇到需要在in里面添加null条件的需求。这个需求更多的使用在集合in查询需求中.解决方案如下, 使用 SELECT * FROM user WHERE password in (‘44’,null) 这样是无法将zhangsan 这条数据查出来但使用ifnull就能够解决这个问题 SELECT * FROM user WHERE IFNULL(password,‘’) in (‘44’,‘’)使用ifnull将Null值转换成 空串’’ 然后条件添加空串就能将null值查询出来
Mysql中实现IN查询出NULL使用IFNULL关键字
m1s2q3l4的博客
08-04 1718
Mysql中实现IN查询出NULL使用IFNULL关键字
MySQL中IN对NULL的处理
卖克的专栏
01-08 8829
　　先说结论，MySQL中NULL是不参与计算的，对NULL进行计算，只可以使用预设的IS NULL和IS NOT NULL来操作 　　student表中，有三条数据，id分别是1、2、3 SELECT * FROM student WHERE id IN (1,2,NULL) 　　会查询出来id=1和id=2的记录 SELECT * FROM ...
mysql in null 报错_Mybatis中sql语句中的in查询，一定要判断null的情况
weixin_33168819的博客
01-27 1202
不严谨的写法，可能会报错：in ()，这种情况不符合mysql的语法。select from loanwhere LOAN_ID in#{item}要么在Mybatis的sql文件中，要么在Java程序中。sql中的写法：select *from p2p_loan_variation where 1= 1and LOAN_ID inseparator="," close=")">#{item...
MySQL学习笔记
最新发布
qq_45911139的博客
08-16 186
方式一：使用图形界面工具方式二：使用命令行工具（需要以管理员身份运行）启动 MySQL 服务命令停止 MySQL 服务命令。
MySQL笔记-InnoDB物理及逻辑存储结构
12-14
在InnoDB引擎中，创建表没有主键，InnoDB会把not null中unique作为主键，若这样的列也没有，那么InnoDB会生成6个字节的不可见的rowid。 在InnoDB中如果是独立表空间，创建一个表会生成2个文件，一个是.frm文件，一...
mysql中datetime类型设置默认值方法
12-15
通过navicat客户端修改datetime默认值时，遇到了问题。 数据库表字段类型... `id` bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID', `app_id` varchar(30) DEFAULT NULL COMMENT '应用编码', `ap
MySQL 的覆盖索引与回表的使用方法
01-19
使用的存储引擎：MySQL5.7 InnoDB 聚簇索引 * 如果表设置了主键，则主键就是聚簇索引 * 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引 * 以上都没有，则会默认创建一个隐藏的row_id...
《Oracle Database 11g & MySQL 5.6开发手册
06-28
5.7 小结115 5.8 习题115 第II部分SQL开发 第6章 创建用户和结构119 6.1 用户120 6.1.1 Oracle数据库的用户120 6.1.2 MySQL数据库的用户126 6.2 数据库131 6.2.1 Oracle的模式131 6.2.2 MySQL的数据库132 6.3 表 134...
bumbelbee:使用Spring，Spring Security（Java配置），Spring Data JPA，MySQL的错误报告工具
05-16
------ db create ----- --- mysql版本-5.7--创建数据库bumbelbee / *！40100默认字符集utf8 * /; CREATE TABLE attachment （ attachment_id bigint（20）NOT NULL AUTO_INCREMENT， attachment_data longblob， ...
【MySQL】使用 IN 或 NOT IN，数据中有 NULL 出现的查询异常情况
weixin_50223520的博客
04-19 3830
MySQL 使用 IN 或 NOT IN，数据中有 NULL 出现的查询异常情况
SQL中的in、not in语句遇到null时的坑点
热门推荐
shenzhou_yh的博客
12-24 1万+
背景介绍 前两天做问题排查的时候，写了一条sql，但是并没有如期地查到数据（确实是有数据的），SQL如下： SELECT tar.* FROM tb_account_relation tar WHERE tar.customer_id NOT IN ( SELECT customer_id FROM TB_SMS_SEND_DETAIL WHERE TYPE = '2' ) ...
mysql学习总结五：约束
weixin_45795182的博客
02-28 958
一、什么是表约束 表约束是在创建表的时候，设计的一些表的约束条件，用来保证数据的合法性和数据的正确性。 二、为什么需要表约束 表约束是为了规范程序员正确使用表的，但表约束是预先设置的，设置之后就对所有插入和修改立即生效，比如非空约束设置好之后，如果添加和修改为null值就会报错。 三、约束类型 1.非空约束 语法： 字段 字段类型 not null 非空约束的作用： 注意： 1.学习了约束之后，我们设计表时，就应该注意约束条件。（字段 字段类型 约束…） 2.一个表中的非空约束时没有数量限制的，可以有
mysql in null 报错,为什么MYSQL IN关键字不考虑NULL值
weixin_32121931的博客
01-21 366
I am using the following query:select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and Error not in ('Timeout','Connection Error');Surprisingly, this statement doesnot in...
MySQL关于NULL值，常见的几个坑
wdw18668109050的博客
02-25 946
mysql中NULL值的注意避免的坑。
mysql in null 报错_MySQL加载数据Infile - Null导致错误1265：数据被截断
weixin_39928106的博客
02-26 339
我在将数据从csv导入mysql表时遇到问题。我试图使用“加载数据填充”，但每次尝试运行我的代码时"Error Code: 1265. Data Truncated for column 'DIP20' at row 237"这个问题源于这样一个事实：第237行的列dip20是csv文件中的第一个空条目，但是我的研究表明，mysql应该将空条目读取为0s。这将停止运行整个导入，并且没有数据进入我的...
mysql in和null_解决mysql使用not in 包含null值的问题
weixin_28870939的博客
01-18 2716
这篇文章主要介绍了解决mysql使用not in 包含null值的问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧注意！！！select * from user where uid not in (a,b,c,null);这个sql不回返回任何结果。要避免not in的list中出现null的情况。另外：–如果null参与算术运算，则该算术表达式的值为null。(例如：+，-，...
MySQL 字段为 NULL 的5大坑，大部分人踩过
Rocky006的博客
05-04 2380
数据库字段允许空值(null)的问题，小伙伴你遇到过吗？MySQL 字段为 NULL 的5大坑，大部分人踩过
MySQL为Null导致莫名其妙的5大坑
LoveSummer
11-05 2319
正式开始之前，我们先来看下 MySQL 服务器的配置和版本号信息，如下图所示： “兵马未动粮草先行”，看完了相关的配置之后，我们先来创建一张测试表和一些测试数据。 -- 如果存在 person 表先删除 DROP TABLE IF EXISTS person; -- 创建 person 表，其中 username 字段可为空，并为其设置普通索引 CREATE TABLE person ( id INT PRIMARY KEY auto_increment, name VARCHAR(20), mo
mysql5.7 开启 events_transactions_
02-06
MySQL 5.7 开启 events_transactions_history 功能的方法如下： 1. 在 MySQL 安装目录下找到 my.cnf 配置文件，打开并编辑。 2. 在 [mysqld] 部分中加入下面这行： ``` event_scheduler=ON ``` 3. 保存并退出。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值