問題描述

引用 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 語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！