数据插入MYSQL即使格式错误 [英] Data inserted in MYSQL even if it is in wrong format

问题描述

你好，我使用mysql

当我在日期栏中插入错误的值时

它插入了
而不是向我显示错误

例如：在出生日期栏中插入'hello'



i当我做这样的行为并尝试在日期列中插入错误的值

i需要我的sql向我显示错误，告诉我我插入了错误的数据，并且插入操作没有发生。

i不需要插入稿件

i尝试

set_mode ='NO_ZERO_DATE'



但仍然是同样的问题

解决方案

根据 MySQL文档： [ ^ ]

< blockquote class =quote>

Quote：

请注意MySQL中日期值解释的某些属性：



MySQL允许为指定为字符串的值使用宽松格式，其中任何标点字符都可以用作日期部分或时间部分之间的分隔符。在某些情况下，这种语法可能是欺骗性的。例如，由于：分隔符，10：11：12之类的值可能看起来像时间值，但如果在日期上下文中使用，则会被解释为年份2010-11-12。值'10：45：15'被转换为'0000-00-00'，因为'45'不是合法月份。



自5.0.2起，服务器要求月和日值合法，而不仅仅分别在1到12和1到31的范围内。禁用严格模式后，2004-04-31等无效日期将转换为0000-00-00，并生成警告。启用严格模式后，无效日期会生成错误。要允许此类日期，请启用ALLOW_INVALID_DATES。有关更多信息，请参见第5.1.7节服务器SQL模式。

访问服务器SQL模式 [ ^ ]了解如何设置为严格模式。

但是，如果查询是来自应用程序，日期验证应在编码方面完成。或者您可以考虑使用数据贴纸。

hello , i use mysql
when i insert wrong value in a date column
it inserted as 0000-00-00 instead of showing me an error
for example : insert 'hello' in birth date column

i need when i do a behavior like that and try to insert wrong values in date columns
i need my sql to show me error that tell me i am insert wrong data ,and insertion operation not happened .
i don't need to insert 0000-00-00
i try
set_mode='NO_ZERO_DATE'

but still same problem

解决方案

According to the MySQL Documentation: [^]

Quote:

Be aware of certain properties of date value interpretation in MySQL:

MySQL permits a "relaxed" format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the ":" delimiter, but is interpreted as the year '2010-11-12' if used in a date context. The value '10:45:15' is converted to '0000-00-00' because '45' is not a legal month.

As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 5.1.7, "Server SQL Modes", for more information.

Visit Server SQL Modes[^] to find out how to set to strict mode.
However, if the query is coming from an application, the date validation should be done at the coding side. Or you may consider using a datapicker.

这篇关于数据插入MYSQL即使格式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！