准备具有DEFAULT值的MySQL INSERT/UPDATE语句 [英] Preparing a MySQL INSERT/UPDATE statement with DEFAULT values

问题描述

引用MySQL INSERT手册-UPDATE也是如此:

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?

推荐答案

为此，我知道的唯一解决方法"是使用

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), "\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

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