mysql如何处理无效的数据值
mysql对插入的数据有时候不是我们意料中的,这不是bug,是mysql固有的设计。
3.3 How MySQL Handles Invalid Data Values
Historically, the dominant principle for data handling in MySQL has been,“Garbage in, garbage out.” In other words,
MySQL attempts to store any data value you give it, but if you don’t verify the value first before storing it, you may not like what
you get back out.
However, as of MySQL 5.0.2, several SQL modes are available that enable you to reject bad values and cause an error to
occur instead.The following discussion first discusses how MySQL handles improper data by default, and then covers the
changes that occur when you enable the various SQL modes that affect data handling.
By default, MySQL handles out-of-range or otherwise improper values as follows:
- For numeric or TIME columns, values that are outside the legal range are clipped to the nearest endpoint of the range and the resulting value is stored.
- For string columns other than ENUM or SET, strings that are too long are truncated to fit the maximum length of the column.
- Assignments to an ENUM or SET column depend on the values that are listed as legal in the column definition. If you assign to an ENUM column a value that is not listed as an enumeration member, the error member is assigned instead (that is, the empty string that corresponds to the zero-valued member). If you assign to a SET column a value containing substrings that are not listed as set members, those strings drop out and the column is assigned a value consisting of the remaining members.
- For date or time columns, illegal values are converted to the appropriate “zero” value for the type (see Table 3.15).
具体SQL modes详看https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
读书笔记:
MySQL (4th Edition)
作者: Paul DuBois出版社: Addison-Wesley Professional
出版年: 2008-09-08
页数: 1224
定价: USD 49.99
装帧: Paperback
ISBN: 9780672329388