mysql启动错误,详解数据库语言中的null值

详解数据库语言中的null值,详解数据库null值

虽然熟练掌握SQL的人对于Null不会有什么疑问,但总结得很全的文章还是很难找,看到一篇英文版的, 感觉还不错。

Tony Hoare 在1965年发明了 null 引用, 并认为这是他犯下的“几十亿美元的错误”. 即便是50年后的今天, SQL中的 null 值还是导致许多常见错误的罪魁祸首.

我们一起来看那些最令人震惊的情况。

 

Null不支持大小/相等判断

下面的2个查询,不管表 users 中有多少条记录,返回的记录都是0行:  

select * from users where deleted_at = null;

– result: 0 rows

select * from users where deleted_at != null;

– result: 0 rows

 

怎么会这样子? 一切只因为 null 是表示一种“未知”的类型。也就是说,用常规的比较操作符(normal conditional operators)来将 null 与其他值比较是没有意义的。 Null 也不等于 Null(近似理解: 未知的值不能等于未知的值,两者间的关系也是未知,否则数学和逻辑上就乱套了)。

– 注意: 下面的SQL适合于MySQL,如果是Oracle,你需要加上 … from dual;

 

select null > 0;

– result: null

select null < 0;

– result: null

select null = 0;

– result: null

select null = null;

– result: null

select null != null;

– result: null

 

将某个值与 null 进行比较的正确方法是使用 is 关键字, 以及 is not 操作符:  

select * from users

where deleted_at is null;

– result: 所有被标记为删除的 users

 

如果想要判断两列的值是否不相同,则可以使用 is distinct from:  

select * from users

where has_address is distinct from has_photo

– result: 地址(address)或照片(photo)两者只有其一的用户

 

not in 与 Null

子查询(subselect)是一种很方便的过滤数据的方法。例如,如果想要查询没有任何包的用户,可以编写下面这样一个查询:  

select * from users 

where id not in (select user_id from packages)

 

但此时假若 packages 表中某一行的 user_id 是 null 的话,问题就来了: 返回结果是空的! 要理解为什么会发生这种古怪的事情, 我们需要理解SQL编译器究竟干了些什么. 下面是一个更简单的示例:  

select * from users 

where id not in (1, 2, null)

 

这个SQL语句会被转换为:  

select * from users 

where id != 1 and id != 2 and id != null

 

我们知道,id != null 结果是个未知值, null. 而任意值和 null 进行 and 运算的结果都是 null, 所以相当于没有其他条件. 那么出这种结果的原因就是 null 的逻辑值不为 true.

如果条件调换过来, 查询结果就没有问题。 现在我们查询有package的用户.  

select * from users 

where id in (select user_id from packages)

 

同样我们可以使用简单的例子:  

select * from users

where id in (1, 2, null)

 

这条SQL被转换为:  

select * from users 

where id = 1 or id = 2 or id = null

因为 where 子句中是一串的 or 条件,所以其中某个的结果为 null 也是无关紧要的。非真(non-true)值并不影响子句中其他部分的计算结果,相当于被忽略了。

 

Null与排序

在排序时, null 值被认为是最大的. 在降序排序时(descending)这会让你非常头大,因为 null值排在了最前面。

下面这个查询是为了根据得分显示用户排名, 但它将没有得分的用户排到了最前面!  

select name, points

from users

order by 2 desc;

– points 为 null 的记录排在所有记录之前!

 

解决这类问题有两种思路。最简单的一种是用 coalesce 消除 null的影响:  

– 在输出时将 null 转换为 0 :

select name, coalesce(points, 0)

from users

order by 2 desc;

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

 

还有一种方式需要数据库的支持,指定排序时将 null 值放在最前面还是最后面:  

select name, coalesce(points, 0)

from users

order by 2 desc nulls last;

当然, null 也可以用来防止错误的发生,比如处理除数为0的数学运算错误。

 

被 0 除

除数为0是一个非常 egg-painfull 的错误。昨天还运行得好好的SQL,突然被0除一下子就出错了。一个常用的解决方法是先用 case 语句判断分母(denominator)是否为0,再进行除法运算。

 

select case when num_users = 0 then 0 

else total_sales/num_users end;

 

ase 语句的方式其实很难看,而且分母被重复使用了。如果是简单的情况还好,如果分母是个很复杂的表达式,那么悲剧就来了: 很难读,很难维护和修改,一不小心就是一堆BUG.

这时候我们可以看看 null 的好处. 使用 nullif 使得分母为0时变成 null. 这样就不再报错, num_users = 0 时返回结果变为 null.  

select total_sales/nullif(num_users, 0);

 
nullif 是将其他值转为 null, 而Oracle的 nvl 是将 null 转换为其他值。

 

如果不想要 null,而是希望转换为 0 或者其他数, 则可以在前一个SQL的基础上使用 coalesce函数:  

select coalesce(total_sales/nullif(num_users, 0), 0);

 
null 再转换回0

 

Conclusion

Tony Hoare 也许会后悔自己的错误, 但至少 null 存在的问题很容易地就解决了. 那么快去练练新的大招吧,从此远离 null 挖出来的无效大坑(nullifying)!

虽然熟练掌握SQL的人对于Null不会有什么疑问,但总结得很全的文章还是很难找,看到一篇英文...

mysql优化总结(一)

一、mysql的sql_mode模式:

(1)、该模式指的是sql模式可控制服务器操作的方式,并且sql模式存在于全局与会话级别。如果想知道全局级或会话级SQL模式的当前值,可以使用的语句为:

mysql> SELECT @@GLOBAL.sql_mode,mysql> SELECT @@SESSION.sql_mode。

(2)、如果想知道SQL模式的当前值,可以使用:SELECT @@sql_mode。

 

二、有的mysql使用的默认的存储引擎为InnoDB,有的 默认的存储引擎为MyISAM。这种两种是最常用的。这两种引擎有六大区别:

 

存储类型: MyISAM InnoDB
构成上的区别: 1.每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。
2..frm文件存储表定义。
3.数据文件的扩展名为.MYD (MYData)。
4.索引文件的扩展名是.MYI (MYIndex)。
基于磁盘的资源是InnoDB表空间数据文件和它的日志文件,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB
事务处理上方面: MyISAM类型的表强调的是性能,其执行速度比InnoDB类型更快,但是不提供事务支持 InnoDB提供事务支持事务,外部键等高级数据库功能
SELECT UPDATE,INSERT,Delete操作 如果执行大量的SELECT,MyISAM是更好的选择 1.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表
2.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用
对AUTO_INCREMENT的操作 1.每表一个AUTO_INCREMEN列的内部处理。
2.MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列,可以出现重使用从序列顶部删除的值的情况)。
3.AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置
4.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引
5.更好和更快的auto_increment处理
1.如果你为一个表指定AUTO_INCREMENT列,在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器,它被用在为该列赋新值。
2.自动增长计数器仅被存储在主内存中,而不是存在磁盘上
3.关于该计算器的算法实现,请参考
4.AUTO_INCREMENT列在InnoDB里如何工作
表的具体行数 select count(*) from table,MyISAM只要简单的读出保存好的行数,注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的 InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行
表锁

提供行锁(locking on row level),提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs),另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “

本文由ca88手机版登录发布于亚洲城ca88手机版官网,转载请注明出处:mysql启动错误,详解数据库语言中的null值

TAG标签: ca88手机版登录
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。

(www.wisdom-shield.com)顶级的信誉评分,亚洲城ca88手机版官网精心打造最专业、最迅捷、最优质的真人娱乐,ca88手机版登录所有场景都由美工手绘完成,是亚洲最佳线上娱乐场。
© 2012-2018 http://www.wisdom-shield.com
网站地图 | xml地图 | 网站标签