php – 使用DEFAULT值准备MySQL INSERT / UPDATE语句

引用MySQL INSERT手册 – 同样适用于UPDATE：

Use the keyword DEFAULT to set a column explicitly to its default value. This makes it easier to write INSERT statements that assign values to all but a few columns, because it enables you to avoid writing an incomplete VALUES list that does not include a value for each column in the table. Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES list. 

所以简而言之,如果我写

INSERT INTO table1 (column1,column2) values ('value1',DEFAULT);

一个新的行,column2被设置为其默认值 – 无论它是什么 – 被插入.

但是,如果我在 PHP 中准备和执行语句：

$statement = $pdoObject->
    prepare("INSERT INTO table1 (column1,column2) values (?,?)");
$statement->execute(array('value1','DEFAULT'));

新行将包含“DEFAULT”作为其文本值 – 如果列能够存储文本值.

现在我已经写了一个PDO的抽象层(我需要它),并解决这个问题我正在考虑介绍一个

const DEFAULT_VALUE = "randomstring";

所以我可以执行这样的语句：

$statement->execute(array('value1',mysql::DEFAULT_VALUE));

然后在做绑定的方法中,我将通过发送绑定的值,如果有些等于self :: DEFAULT_VALUE,则相应地执行.

我很确定有一个更好的方式来做到这一点.有人遇到类似情况吗？

例如.

$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,
    x int NOT NULL DEFAULT 99,
    y DATETIME NOT NULL DEFAULT '2010-03-17 01:00:00',
    z varchar(64) NOT NULL DEFAULT 'abc',
    primary key(id)
  )
");


$stmt = $pdo->prepare('
  INSERT INTO
    foo
    (x,y,z)
  VALUES
    (
      Coalesce(:x, Default(x)),
      Coalesce(:y, Default(y)),
      Coalesce(:z, Default(z))
    )
');
$stmt->bindParam(':x', $x);
$stmt->bindParam(':y', $y);
$stmt->bindParam(':z', $z);


$testdata = array(
  array(null, null, null),
  array(1, null, 'lalala'),
  array(null, '2009-12-24 18:00:00', null)
);
foreach($testdata as $row) {
  list($x,$y,$z) = $row;
  $stmt->execute();
}
unset($stmt);
foreach( $pdo->query('SELECT id,x,y,z FROM foo', PDO::FETCH_NUM) as $row) {
  echo join(', ', $row), "\n";
}

版画

1, 99, 2010-03-17 01:00:00, abc
2, 1, 2010-03-17 01:00:00, lalala
3, 99, 2009-12-24 18:00:00, abc