准备具有DEFAULT值的MySQL INSERT/UPDATE语句

问题描述

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

Quoting MySQL INSERT manual - same goes for UPDATE:

简而言之，如果我写

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), "\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语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！