将 MySQL 警告转换为错误
有什么方法可以将 MySQL 发出的有关无效日期时间的警告转换为硬错误吗？我尝试过使用 SET sql_mode='TRADITIONAL'; 显然应该将（某些）警告变成错误，但它在这里没有任何效果。这是 MySQL 5.1.56。在会话级别上有效的东西将是理想的，但我会采取我能得到的。
mysql> describe test_table2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| value | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| sometime | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from test_table2;
+-------+-------+---------------------+
| value | name | sometime |
+-------+-------+---------------------+
| 1 | one | 2002-09-01 10:00:00 |
| 2 | two | 2002-09-02 11:00:00 |
| 3 | three | 2002-09-03 12:00:00 |
| 4 | four | 2002-01-04 13:00:00 |
| 5 | five | 2002-01-05 14:00:00 |
+-------+-------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from test_table2 where sometime = 'foo';
Empty set, 2 warnings (0.00 sec)
Warning (Code 1292): Incorrect datetime value: 'foo' for column 'sometime' at row 1
Warning (Code 1292): Incorrect datetime value: 'foo' for column 'sometime' at row 1
Is there any way to convert the warning that MySQL is issuing about an invalid datetime into a hard error? I've tried using SET sql_mode='TRADITIONAL'; which apparently is supposed to turn (some) things that are warnings into errors, but it does not have any effect here. This is MySQL 5.1.56. Something that works on a session-level would be ideal, but I'll take what I can get.
mysql> describe test_table2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| value | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| sometime | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from test_table2;
+-------+-------+---------------------+
| value | name | sometime |
+-------+-------+---------------------+
| 1 | one | 2002-09-01 10:00:00 |
| 2 | two | 2002-09-02 11:00:00 |
| 3 | three | 2002-09-03 12:00:00 |
| 4 | four | 2002-01-04 13:00:00 |
| 5 | five | 2002-01-05 14:00:00 |
+-------+-------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from test_table2 where sometime = 'foo';
Empty set, 2 warnings (0.00 sec)
Warning (Code 1292): Incorrect datetime value: 'foo' for column 'sometime' at row 1
Warning (Code 1292): Incorrect datetime value: 'foo' for column 'sometime' at row 1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
使用
SET sql_mode='TRADITIONAL'时，使用无效日期执行INSERT会导致错误，但使用无效日期执行SELECT仍会导致错误一个警告。您可以通过首先将（可能无效的）日期值传递给此查询来触发错误：其中“foo”是您要验证的日期值。
With
SET sql_mode='TRADITIONAL', doing anINSERTwith an invalid date causes an error, but doing aSELECTwith an invalid date still causes a warning. You can trigger the error by passing the (possibly invalid) date value to this query first:where 'foo' is the date value you want to validate.
谁应该看到错误？
如果这是一个固定字符串“foo”，只需尝试将“foo”转换为日期，看看是否可以获得有效结果（即不是 00-00-000）。进行预查询以检查日期的有效性，然后继续。
在这种情况下，我无法让 MySQL 给出错误（或者甚至将无效日期转换为 NULL - 它坚持将其设置为 00-00-0000）。
Who is supposed to see the error?
If this is a fixed string 'foo' just try converting 'foo' to a date and see if you can a valid result (i.e. not 00-00-000). Do a pre-query to check the validity of the date, and then continue after.
I have not been able to make MySQL give an error in this case (or even convert the invalid date to a NULL - it insists on making it 00-00-0000).