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?

Wilhite

Since PDO is processing your values as strings to be input, it looks like you may have to change the code a bit, if that's possible. You might change it so that you would type ...execute(array('\'value1\'', 'DEFAULT')) , but I'm guessing that PDO is processing these values for you with mysql_real_escape_string(), etc. This would defeat that function. You will probably have to just insert it directly into the prepare() argument. – Vernice

Why not just include columns in your INSERT statement that you are setting to a value other than the default? – Palestine

The problem is with UPDATE and an empty submitted input. I need it to be the default one, not for example 0000-00-00 for date fields when '' is set as the new value. – Wilhite

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

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

Chondriosome

Default(x) can be DEFAULT, there is no need to mention the columnname if you want the default value for this column in the same column. It's errorprone. UPDATE t SET x = DEFAULT; – Importance

Great thinking! Not perfect but beats my solution! – Wilhite

@Frank: It has to be Default(fieldname) when used as an argument to Coalesce(). – Chondriosome

Sadly it gives an General error: 1364 Field 'column1' doesn't have a default value, if the default value is not set on each column – Wilhite

Of course. What would be the default value if there is no default value? – Chondriosome

I mean it throws an error, even if a non-NULL value is submitted, so each and every column in your DB must have a default value. I guess the lesson here is to avoid using DEFAULT values at all - or at least on columns that the user can modify. – Wilhite

You would only use the Coalesce(val, default(x))-thingy on fields that have a default value of course. You can't throw it in there just... anywhere ;-) But that should only matter if your script builds/assembles those (arbitrary) statements at runtime and then you could still use the TABLE_INFORMATION_SCHEMA to find out whether a field has a default value or not. – Chondriosome

@Chondriosome Is there any way to make this happen with Mysqli? You can't pass a command trough a prepered statement in Mysqli, but it would be nice to have a fallback to the default value. – Thrashing

Answer 2

I tried replying to VolkerK answer, but couldnt find how. :( I'm kinda new to all this.

Anyway, I created a mysql function to use in conjuction with his COALESCE idea

CREATE FUNCTION NULLDEFAULT(colname VARCHAR(64), tablename VARCHAR(64), dbname VARCHAR(64)) RETURNS longtext DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE retval longtext;
    SELECT
        COLUMN_DEFAULT INTO retval 
    FROM
        information_schema.COLUMNS
    WHERE
        TABLE_NAME = tablename
    AND
        COLUMN_NAME = colname
    AND
        TABLE_SCHEMA = dbname;

    RETURN retval;
END

You would use it like this:

$stmt = $pdo->prepare("
  INSERT INTO
    foo
    (x,y,z)
  VALUES
    (
      Coalesce(:x, NULLDEFAULT('x', 'foo', 'database')),
      Coalesce(:y, NULLDEFAULT('y', 'foo', 'database')),
      Coalesce(:z, NULLDEFAULT('z', 'foo', 'database'))
    )
");

That will return null if the column has no default value, and won't trigger the "Column has no default value" Error.

Of course you could modify it to not require the database parameter

Knisley

I dont think it would hold out performance-wise. AFAIK mysql won't cache the results of functions, so you'd be calling your query for every row and every column in the insert query. It's much better to get the default values via PHP beforehand, but as I already concluded, my lesson learnt here is to not count on default mysql values at all. I'll still give you a +1 for the effort. – Wilhite

According to the documentation the DETERMINISTIC keyword tells mysql that the function always return the same values with the same parameters, so that would lead me to believe that it will cache the result. But I haven't tasted it extensively, I'm using it in a small intranet without any noticeable decrease in speed. – Knisley

Answer 3

Try changing this:

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

To this:

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

It seems to me that your original code will give you this:

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

My code should give you this:

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

Rate

Who could ever want other values but default anyway. – Wilhite

Answer 4

i think that it is writing the String 'DEFAULT ' because it is escaped by pdo so there are parametres for bindvalue where you can specify the type of the value given so you can send a null with no quotes and it will be PDO::PARAM_NULL; and then default values will be put , but i'm not sure if there are similar parameters when binding with execute

  if(is_int($param)){$pdoParam = PDO::PARAM_INT;}
      elseif(is_bool($param)){$pdoParam = PDO::PARAM_BOOL;}
      elseif(is_null($param)){ $pdoParam = PDO::PARAM_NULL;}
      elseif(is_string($param)){$pdoParam = PDO::PARAM_STR;}
                                    else{$pdoParam = FALSE;}
              $this->_query->bindValue($k,$param,$pdoParam);

Lichtenfeld

What's $pdoParam = FALSE; supposed to achieve? The default is PARAM_STR. – Priestley