How to specify 'default' in a parameterized PHP PDO insert?

Question

I came up with a workaround for now, but I'm curious for future reference...

If a mysql table column is defined as NOT NULL but has a default value, that default value will be inserted if the column name is not specified in the insert statement, OR if you specify the keyword DEFAULT as the value. If you specifically use NULL as a value on a NOT NULL column, even if the column has a default, it will try to insert the NULL and throw an error.

But is there any way to specify the DEFAULT keyword as a value in a parameterized INSERT statement? I don't want to just omit the column from the insert statement, because I want to use the same statement with multiple data sets, some of which actually have data for that column.

Answer 1

If you want an INSERT statement that treats NULL as the default value for the column, here's a solution:

I created a table:

CREATE TABLE `foo` (
  `x` INT DEFAULT '768'
)

Then I tested a couple of prepared-statement INSERTs with PDO:

$stmt = $pdo->prepare("INSERT INTO foo (x) VALUES (COALESCE(?, DEFAULT(x)))");

$stmt->execute( [ 42 ] ); // inserts a real value

$stmt->execute( [ NULL ] ); // inserts the column's default value 

I confirmed the test:

mysql> select * from foo;
+------+
| x    |
+------+
|   42 |
|  768 |
+------+

Tested with PHP 5.5.12 and MySQL 5.6.17.