doubaoxue5788 2014-12-05 09:26
浏览 102
已采纳

yii2 / mysql中now()和db \ Expression的奇怪行为

I can't make the following code behave like it should (deleting expired records). Simple test code:

test_expire table definition:

CREATE TABLE `test_expire` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `expire` timestamp NOT NULL,
 PRIMARY KEY (`id`)
);

Code:

TestExpire::deleteAll();

$record = new TestExpire();
$record->expire = new Expression("NOW() + INTERVAL 1 MONTH");
$record->save();

$record = new TestExpire();
$record->expire = new Expression("NOW() + INTERVAL 1 YEAR");
$record->save();

$records = TestExpire::find('expire > :expire', ['expire' => date('Y-m-d')])->all();
echo "Records (" . count($records) . "):
";
foreach($records as $record) {
    echo "Expire: " . $record->expire . "
";
}
TestExpire::deleteAll("expire < NOW()");
$records = TestExpire::find('expire > :expire', ['expire' => date('Y-m-d')])->all();
echo "Records after delete 1: " . count($records) . "
";

TestExpire::deleteAll("expire < :expire", [':expire' => date('Y-m-d G:i:s')]);
$records = TestExpire::find('expire > :expire', ['expire' => date('Y-m-d')])->all();
echo "Records after delete 2: " . count($records) . "
";

TestExpire::deleteAll("expire < :expire", [':expire' => new Expression("NOW()")]);
$records = TestExpire::find('expire > :expire', ['expire' => date('Y-m-d')])->all();
echo "Records after delete 3: " . count($records) . "
";

Well I expect all deleteAll expressions be the same, each of them not deleting records. But the last one does. Here's the output:

Records (2):
Expire: 2015-01-05 14:52:02
Expire: 2015-12-05 14:52:02
Records after delete 1: 2
Records after delete 2: 2
Records after delete 3: 0

The question is why it happens and how to make it work as intended?

  • 写回答

1条回答 默认 最新

  • douweiduo7526 2014-12-05 10:17
    关注

    Well, the last sql query is :

    DELETE FROM `test_expire` WHERE expire < 'NOW()';
    

    Instead of

    DELETE FROM `test_expire` WHERE expire < NOW();
    

    Thanks for reporting this bug, I created an issue here : https://github.com/yiisoft/yii2/issues/6382

    EDIT : As explained on Yii issue tracker, parameter binding is a pdo feature, don't use it with db expressions.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图问题
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题