如何检查字符串是否为有效的DATE，TIME或DATETIME [英] How to check if a string is a valid DATE, TIME or DATETIME

问题描述

当我尝试将一个值放入无效的DATE字段中时，MySQL似乎改用通讯录.有没有一种方法可以在不更新DATE字段的情况下执行此检查"?并通过PHP来做到这一点?

When I try to put a value into a DATE field which is invalid, MySQL seems to use 0000-00-00 instead. Is there a way I can do this "check" without updating a DATE field? And to do it from for example PHP?

就像，有没有一种方法可以查询MySQL服务器并询问嘿，这个DATE，TIME或DATETIME对您有效吗?"

Like, is there a way I can query the MySQL server and ask "Hey, is this DATE, TIME or DATETIME valid to you?"

或者也许有更好的方法呢?

Or is there maybe an even better way of doing it?

推荐答案

如果您为不允许无效日期值的MySQL服务器选择服务器模式，则包含这种格式错误的日期表示的查询将导致错误，而不是(默默地)假设自己注册
参见 http://dev.mysql.com/doc /refman/5.0/en/server-sql-mode.html

If you choose a server mode for the MySQL server that doesn't allow invalid date values a query containing such a malformed date representation will cause an error instead of (silently) assuming 0000-00-00
see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

例如

$pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly'); 
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$pdo->exec('CREATE TEMPORARY TABLE foo (id int auto_increment, d datetime, primary key(id))');

$query = "INSERT INTO foo (d) VALUES ('2010-02-31 12:15:18')";
foreach( array('ALLOW_INVALID_DATES', 'STRICT_ALL_TABLES') as $mode ) {
  echo $mode, ": "; flush();
  $pdo->exec("SET SESSION sql_mode='$mode'");
  $pdo->exec($query);
  echo "Ok.\n";
}

打印

ALLOW_INVALID_DATES: Ok.
STRICT_ALL_TABLES: 
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2010-02-31 12:15:18' for column 'd' at row 1' in [...]

这篇关于如何检查字符串是否为有效的DATE，TIME或DATETIME的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！