首页 > 编程笔记 > MySQL笔记 阅读:2,587

MySQL如何处理无效数据值?

MySQL处理数据的基本原则是“垃圾进来,垃圾出去”,通俗一点说就是你传给 MySQL 什么样的数据,它就会存储什么样的数据。如果在存储数据时没有对它们进行验证,那么在把它们检索出来时得到的就不一定是你所期望的内容。
 
有几种 SQL 模式可以在遇到“非正常”值时抛出错误,如果你对其他数据库管理系统比较熟悉,会发现这种行为和其他的数据库管理系统很像。
 
下面介绍 MySQL 默认情况下如何处理非正常数据和启用各种 SQL 模式时会对数据处理产生哪些影响。
 
默认情况下,MySQL 会按照以下规则来处理越界(即超出取值范围)的值和其他非正常值:
 
如果在执行增删改查等语句时发生了上述转换,那么 MySQL 会给出警告消息。在执行完其中的某一条语句之后,可以使用 SHOW WARNINGS 语句来查看警告消息的内容。
 
如果需要在插入或更新数据时执行更严格的检查,那么可以启用以下两种 SQL 模式中的一种:

SET sql_mode = 'STRICT_ALL_TABLES' ;
SET sql_mode = 'STRICT_TRANS_TABLES';

对于支持事务的表,这两种模式都是一样的。如果发现某个值无效或缺失,那么会产生一个错误,并且语句会中止执行,并进行回滚,就像什么事都没发生过一样。
 
对于不支持事务的表,这两种模式有以下效果。
 
1) 对于这两种模式,如果在插入或修改第一个行时,发现某个值无效或缺失,那么结果会产生一个错误,语句会中止执行,就像什么事都未发生过一样。 这跟事务表的行为很相似。
 
2) 在用于插入或修改多个行的语句里,如果在第一行之后的某个行出现了错误,那么会出现某些行被修改的情况。这两种模式决定着,这条语句此时此刻是要停止执行,还是要继续执行。
 
通过以下模式可以对输入的数据进行更加严格的检查:
 
简单来说,MySQL 的严格模式就是 MySQL 自身对数据进行的严格校验,例如格式、长度、类型等。比如一个整型字段我们写入一个字符串类型的数据,在非严格模式下 MySQL 不会报错。如果定义了 char 或 varchar 类型的字段,当写入或更新的数据超过了定义的长度也不会报错。
 
虽然我们会在代码中做数据校验,但一般认为非严格模式对于编程来说没有任何好处。MySQL开启严格模式从一定程序上来讲也是对我们代码的一种测试,如果我们没有开启严格模式并且在开发过程中也没有遇到错误,那么在上线或代码移植的时候将有可能出现不兼容的情况,因此在开发过程做最好开启 MySQL 的严格模式。
 
可通过select @@sql_mode;命令查看当前是严格模式还是非严格模式。
 
例如,如果想让所有的存储引擎启用严格模式,并对“被零除”错误进行检查,那么可以像下面这样设置 SQL 模式:

SET sql_mode ‘STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO' ;

 
如果想启用严格模式,以及所有的附加限制,那么最为简单的办法是启用 TRADITIONAL 模式:

SET sql_ mode ‘TRADITIONAL' ;

TRADITIONAL 模式的含义是“启用严格模式,当向 MySQL 数据库插入数据时,进行数据的严格校验,保证错误数据不能插入。用于事务表时,会进行事务的回滚”。
 
可以选择性地在某些方面弱化严格模式。如果启用了 SQL 的 ALLOW_ INVALID_ DATES 模式,那么MySQL将不会对日期部分做全面检查。相反,它只会要求月份值在 1~12 之间,而天数处于 1~31 之间,即允许像‘2000-02-30’或‘2000-06-31’这样的无效值。
 
另一个制止错误的办法是在 INSERT 或 UPDATE 语句里使用 IGNORE 关键字。这样那些会因无效值而导致错误的语句,将只会导致警告的出现。这些选项能让你灵活地为你的应用选择正确的有效性检查级别。

编程帮,一个分享编程知识的公众号。跟着站长一起学习,每天都有进步。

通俗易懂,深入浅出,一篇文章只讲一个知识点。

文章不深奥,不需要钻研,在公交、在地铁、在厕所都可以阅读,随时随地涨姿势。

文章不涉及代码,不烧脑细胞,人人都可以学习。

当你决定关注「编程帮」,你已然超越了90%的程序员!

编程帮二维码
微信扫描二维码关注

所有教程

优秀文章