为什么不能将MYSQL函数传递到准备好的PDO语句中? [英] Why cant you pass MYSQL functions into prepared PDO statements?

问题描述

在我看来，以下脚本应该有效:

In my mind, the following script should work:

$stmt = $db->prepare("UPDATE table SET status = ?, date_modified = ?");
$stmt->execute(array(1, 'NOW()'));

，但是将NOW()传递到准备好的语句中时，什么也没有发生.将NOW()替换为实际日期(即2010-11-23)就可以了.

but when passing NOW() into the prepared statement, nothing happens. Replacing NOW() with an actual date (i.e. 2010-11-23) works just fine.

我无法在线找到说明.有什么想法吗?

I am unable to find explanation online. Any ideas?

为了进一步澄清和消除问题中的任何混乱，我想将一个变量实际传递到准备好的语句中，但是，该变量将被设置为mysql的五个可能的日期/时间函数之一.

Just to further clarify and rid of any confusion in the question, I want to actually pass a variable into the prepared statement HOWEVER, the variable will be set to one of five possible date/time functions for mysql.

例如

$ var ='NOW()';

$var = 'NOW()';

$ var ='LAST_DAY(DATE_ADD(CURDATE()， 间隔1个月))'；

$var = 'LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))';

$ var ='LAST_DAY(CURDATE())';

$var = 'LAST_DAY(CURDATE())';

...等等...

准备好的语句变成:

$stmt->execute(array(1, $var));

我知道这将返回相同的NULL结果，但是我担心是否只是将sql语句更改为:

I know this will return the same NULL results, but I am worried if I simply change the sql statement to:

更新表SET status =?， date_modified = $ var

UPDATE table SET status = ?, date_modified = $var

我要自我打针吗?

推荐答案

您无需传递NOW()作为参数，因为它是内置的SQL函数，因此无需对其进行任何处理，因此只需将其包含在实际的查询中，如下所示.

You do not need to pass NOW() as a parameter as there is no need to do any processing on it, given it is a built in SQL Function, so just include it in the actual query like below.

$stmt = $db->prepare("UPDATE table SET status = ?, date_modified = NOW()");

或者，您可以将date_modified设置为TIMESTAMP字段，它将在SQL Update上自动更新date_modified字段.

Alternatively, you can just set the date_modified to a TIMESTAMP field and it will automatically update the date_modified field on a SQL Update.

这篇关于为什么不能将MYSQL函数传递到准备好的PDO语句中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！