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

问题描述

引用 MySQL INSERT 手册 - 更新也一样:

Quoting MySQL INSERT manual - same goes for UPDATE:

使用关键字 DEFAULT 将列显式设置为其默认值.这使得编写将值分配给除少数列之外的所有列的 INSERT 语句变得更容易，因为它使您能够避免编写不包括表中每一列的值的不完整 VALUES 列表.否则，您必须写出与 VALUES 列表中的每个值对应的列名列表.

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 设置为其默认值的新行 - 无论它是什么 - .

A new row with column2 set as its default value - whatever it may be - is inserted.

但是，如果我在 PHP 中准备并执行语句:

However if I prepare and execute a statement in PHP:

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

新行将包含DEFAULT"作为其文本值 - 如果该列能够存储文本值.

The new row will contain 'DEFAULT' as its text value - if the column is able to store text values.

现在我已经为 PDO 编写了一个抽象层(我需要它)，为了解决这个问题，我正在考虑引入一个

Now I have written an abstraction layer to PDO (I needed it) and to get around this issue am considering to introduce a

const DEFAULT_VALUE = "randomstring";

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

So I could execute statements like this:

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

然后在进行绑定的方法中，我将检查发送给绑定的值，如果某些值等于 self::DEFAULT_VALUE，则采取相应的行动.

And then in method that does the binding I'd go through values that are sent to be bound and if some are equal to self::DEFAULT_VALUE, act accordingly.

我很确定有更好的方法来做到这一点.有没有其他人遇到过类似的情况?

I'm pretty sure there's a better way to do this. Has someone else encountered similar situations?

推荐答案

我知道的唯一解决方法"是使用 Coalesce() 和 默认(字段名)

The only "workaround" I know for this is to use Coalesce() and Default(fieldname)

例如

$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), "
";
}

印刷品

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

这篇关于使用 DEFAULT 值准备 MySQL INSERT/UPDATE 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！