当我尝试将无效的值放入DATE字段时,MySQL似乎会使用0000-00-00。有没有一种方法可以在不更新DATE字段的情况下进行此“检查”?例如,从PHP中进行操作?
比如说,是否有一种方法可以查询MySQL服务器并询问“嘿,这个DATE、TIME或DATETIME对你来说是否有效”?
或者也许有更好的方法吗?
比如说,是否有一种方法可以查询MySQL服务器并询问“嘿,这个DATE、TIME或DATETIME对你来说是否有效”?
或者也许有更好的方法吗?
我在我的应用程序中使用此语法,它的工作效果非常好!
SELECT DATE('2013-09-31') AS valid;
我的系统是Win7x64,使用PHP 5.5和MySQL 5.6.16。
由于问题中提到了 MySQL,以下是一个 MySQL 的解决方案:
由于需要考虑到许多不同的日期格式,因此要验证字段是否为日期非常困难。但是,如果你知道字段日期格式为以下格式之一:
'yyyy-mm-dd'
'yyyy-mm-dd hh:mm:ss'
'yyyy-mm-dd whatever'
SELECT count(*) FROM `table`
WHERE DATE(STR_TO_DATE(`column`, '%Y-%m-%d')) IS NOT NULL
AND `column` NOT REGEXP '^[0-9\.]+$'
基本上:
DATE(STR_TO_DATE(**1**, '%Y-%m-%d')) = '2001-00-00'
如果count(*)
大于0
,那么它就是日期,如果是0
,那么它就不是日期。
注意:此方法适用于任何日期格式的字符串,只要您预先知道它们遵循的格式(我知道这并不总是情况,但仍然有帮助)。只需在查询中a priori
替换格式即可。
mysql> select day('2010-02-31 00:00:00');
+----------------------------+
| day('2010-02-31 00:00:00') |
+----------------------------+
| NULL |
+----------------------------+
Incorrect datetime value: '2012-09-13T17:58:23+02:00'
,但是select day('2012-09-13T17:58:23+02:00')
返回了13。 - Peter Taylor$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 [...]
SELECT WEEK(col) IS NOT NULL AS valid;
或者以下其中之一:
SELECT DAYNAME(col) IS NOT NULL AS valid;
SELECT TO_DAYS(col) IS NOT NULL AS valid;
SELECT TO_SECONDS(col) IS NOT NULL AS valid;
SELECT WEEKDAY(col) IS NOT NULL AS valid;
SELECT WEEKOFYEAR(col) IS NOT NULL AS valid;
SELECT YEARWEEK(col) IS NOT NULL AS valid;
要检查一个值是否为日期,您可以使用day(dateToCheck) = 0
if(strtotime(trim($str)) {
// persist trim($str) in db.
}
也许你需要使用 BLACKHOLE
引擎创建一个数据库,请参考这里。并检查由 PDOException 抛出的异常。
$dbh = new PDO('mysql:host=localhost;dbname=test', 'username', 'p@s5W0Rd!!!', array(PDO::ATTR_PERSISTENT => true));
// CREATE DATABASE test;
// CREATE TABLE test.foo ( bar DATETIME ) ENGINE=BLACKHOLE;
try {
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$dateTime = new DateTime();
$dateTime = $dateTime->format('c');
$dbh->exec("INSERT INTO foo (bar) VALUES ('".$dateTime."')");
$dbh->commit();
} catch (PDOException $e) {
$errorInfo = $dbh->errorInfo();
if ($e->getCode() === '22007'
and $dbh->errorCode() === '22007'
and $errorInfo[0] === '22007'
and preg_match('/^incorrect datetime/', strtolower($errorInfo[2])) === 1) {
throw new Exception($errorInfo[2], (int) $e->getCode());
}
}
它应该解决MySQL中的问题。
对于纯日期和时间场景(使用DATETIME值,您可以按原样进行) - 只需将初始值与任何时间或日期字符串值连接起来以匹配您的DB DATETIME格式(例如CONCAT(date_value,“00:00:00”)或CONCAT(“2021-12-31”,time_value))- 此示例中的变量应该是DATETIME格式。
# declare variables
SET @invalid_date:='2021-02-30 25:64:64', @valid_date:='2021-02-28 04:01:01';
# check valid DATE scenario: invalid_date -> NULL; valid_date -> NOT NULL - correct value
SELECT DATE( @invalid_date );
SELECT DATE( @valid_date );
# check valid TIME scenario: invalid_date -> NULL; valid_date -> NOT NULL - correct value
SELECT TIME( @invalid_date );
SELECT TIME( @valid_date );
# check valid DATETIME scenario: invalid_date -> NULL; valid_date -> NOT NULL - correct value
SELECT CAST( @invalid_date AS DATETIME );
SELECT CAST( @valid_date AS DATETIME );
# purge variables
SET @invalid_date:= NULL, @valid_date:=NULL;
享受吧。