Preparing a MySQL Insert/Update Statement with Default Values

Preparing a MySQL INSERT/UPDATE statement with DEFAULT values

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

mysql prepared statement set default value for insert

No, it's not possible to "alias" a value multiple times. Each placeholder is unique and must be bound to exactly one value. (It is not even possible to do this reliably with named parameters in PDO.)

If automatically building SQL dynamically (with placeholders) and associated data array, then there is no issue with such "duplication" as it's already handled by the generator.

If transactions are correctly used - which they should be anyway! - then simply calling INSERT in a loop, once for each pair, will likely have similar performance. This avoids needing to build the dynamic SQL (with placeholders) itself, but can increase latency as each command needs to round-trip to the SQL server.

If finding a bunch of such repetition, it might be time to use a Data Access Layer to hind such details; write it, test it, move onto something interesting.

---

In response to the comment about using SQL variables:

While the following will not improve performance, excepting possibly for ridiculously large values of @v, and makes the code more difficult to understand - it ought to be possible to use user-defined variables in a single insert statement. For instance, consider:

INSERT INTO t VALUES (@v := ?, ?), (@v, ?), (@v, ?)

This is "valid" in MySQL (and is also MySQL-specific), where the placeholders are valid expressions; if it works will depend on how/if such a binding is allowed in a prepared statement.

PHP MySQL - How to insert default value in a prepared statement

Your question addresses an interesting subject, in my opinion.

By a rapid search, I don't have found any predefined solution, but I will continue search in the next days.

I have elaborate a solution, but it is a bit twisted and not totally clean.

First of all, I redefine the query with placeholders:

$query = "
    INSERT INTO table
                (
                    tag,
                    month_interval,
                    month_interval_lastupdated,
                    date_due,
                    date_due_lastupdated,
                    date_completion,
                    date_completion_lastupdated,
                    activeNotification,
                    date_lastmodified
                )
         VALUES (<tag>,<month_interval>,NOW(),<date_due>,NOW(),<date_completion>,NOW(),<activeNotification>,NOW())
";

In this example, I use <> to surround placeholder, but you can choose your preferred placeholder, making sure that they do not confuse with others elements of the query.

Then, I init an associative array with keys as placeholders names and values to be bind, and a variable ($types) with the complete set of bind types:

$values = array( 'tag'=>$tag, 'month_interval'=>$month_interval, 'date_due'=>$date_due, 'date_completion'=>$date_completion, 'activeNotification'=>$activeNotification );
$types = 'sisss';

Then, the most important line. I init an array in this strange way:

$bind = array( Null, '' );

The index 0 is set to Null, because is reserved for the stmt object (created below); the index 1 is an empty string that will be filled with necessary bind types.

Now I perform a foreach loop through all elements of array $values:

$i = 0;
foreach( $values as $key => $val )
{
    if( is_null( $val ) )
    {
        $query = str_replace( '<'.$key.'>', 'DEFAULT', $query );
    }
    else
    {
        $query    = str_replace( '<'.$key.'>', '?', $query );
        $bind[1] .= $types[$i];
        $bind[]   = &$val;
    }
    $i++;
}

In that loop, if the value is null I replace the corresponding query's placeholder with mySQL keyword DEFAULT. Otherwise, I replace placeholder with a ?, I add the corresponding $types substring to $bind[1], and I append the value (by reference) to $bind array.

At this point, the real query is ready to go, and I can prepare it:

$stmt = $db->prepare( $query ) or die( $stmt->error );

and, if there are not default values (count($bind)>2), I can bind it:

if( count($bind)>2 )
{
    $bind[0] = $stmt;
    call_user_func_array( 'mysqli_stmt_bind_param', $bind );
}

As said before, I set $bind[0] to stmt object returned by ->prepare, then I use call_user_func_array to perform mysqli_stmt_bind_param. I can't directly call ->bind_param because I have a variable arguments number.

At the end of this eccentric process, finally I can execute the query:

$stmt->execute() or die( $stmt->error );

I have tested this code, and it works.

The main problem reside in the is_null( $val ): using an array, I can't use isset as test, because every element of an array is even set. The code works if all the field with placeholder that allows Null value has Null ad default value.

MySQL insert row with only default values

This will do it :

INSERT INTO `myTable` (`id`)
VALUES 
    (null),
    (null);
-- or 
INSERT INTO `myTable` () 
VALUES();

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`id` int AUTO_INCREMENT PRIMARY KEY, `title` varchar(5) DEFAULT '***')
;

INSERT INTO Table1
    (`id`, `title`)
VALUES
    (1, 'hi'),
    (2, 'hello')
;

INSERT INTO Table1
    (`id`)
VALUES
    (null),
    (null)
;
INSERT INTO Table1 () VALUES();

Query 1:

SELECT * from Table1

Results:

| id | title |
|----|-------|
|  1 |    hi |
|  2 | hello |
|  3 |   *** |
|  4 |   *** |
|  5 |   *** |

Using DEFAULT in an INSERT/UPDATE statement vs CASE

You cannot do that. You can use an expression there or the DEFAULT keyword.

SET { column = { expression | DEFAULT } | ... }

However, you can split your query into 2 updates:

UPDATE some_table
SET some_column = DEFAULT
WHERE id = 1 AND some_condition;

UPDATE some_table
SET some_column = 'foo'
WHERE id = 1 AND NOT some_condition;

This will do the desired changes. If you really want to do it in one query, you can use CTEs:

WITH upd1 AS (
  UPDATE some_table
  SET some_column = DEFAULT
  WHERE id = 1 AND some_condition
  RETURNING *
),
upd2 AS (
  UPDATE some_table
  SET some_column = 'foo'
  WHERE id = 1
  RETURNING *
)
SELECT * FROM upd1
UNION ALL
SELECT * FROM upd2

Note: the AND NOT some_condition skipped intentionally. The last CTE can work exactly as the ELSE branch in the CASE expression, because if you use multiple data modifying CTEs, maximum one of them can affect every row within a table.

---

Related Topics