php - Preparing a MySQL INSERT/UPDATE statement with DEFAULT values

Question

Quoting MySQL INSERT manual - same goes for 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.

So in short if I write

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

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

However if I prepare and execute a statement in PHP:

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

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

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));

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?

Answer 1

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

E.g.

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

prints

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