Mysql – Update only changed columns with Mysql prepared statements

MySQL

In the following procedure I have simple prepared "update" statement. I have some questions about using this.

  1. Main question: How do I update only the changed columns in a stored procedure? It seems like setting update on all columns will overwrite data if another user changes a different column at the right time. In Zend Framework 2, I typically use TableGateway, which allows me to send in the form array and I think it will only update the columns that I have sent. How do I build a stored procedure to take a variable set to columns to update?
  2. With the above in mind, I know I can build a statement with concat and skip the "prepare .. using" bound parameters, but if I do that, then I can't protect against SQL Injection, unless I build my own filtering functions. I'd really prefer to keep the bound parameters. How do I keep those, while still only building a statement that updates only changed columns? Because I don't see how to change the USING portion to have dynamic input parameters.
  3. Also, just a side question. Why is it in Mysql I have to make all those duplicate input parameters? ("set @col1 = v_val1") Because I can't put v_val1 directly in the USING statement I have to make session variables. I think that really odd we can't put them in directly.
  4. Last little question. Should I unset each session variable at the end of the procedure? "set @col1 = null" Wouldn't it be safer to clear those out? I do not use pooled connections now, but I may use this procedure from more than just PHP web server, and other systems may have pooled connections.

I'm probably missing something obvious in this, and/or I'm just asking too much. I do know there will always be some level of timing with user edits. That's not critical, and if it occurs in my current situation, I'll have a log to show users what and who changes things. But I do wonder what I can do at the Mysql procedure level to work around it. Also it seems like Mysql is really lacking in procedure capabilities compared to PostgreSQL or MSSQL, but I am stuck with Mysql for now because of backend system requirements.

Sorry about all the questions. Having hard time finding answers. Thanks so much for you help.

create procedure sp_update_mytable (
    in v_id int,
    in v_val1 varchar(10),
    in v_val2 varchar(10),
    in v_val2 varchar(10)
)
begin

    set @id = v_id;
    set @col1 = v_val1;
    set @col2 = v_val2;
    set @col3 = v_val3;

    set @sql = 'UPDATE mytable
                SET
                col1 = ?,
                col2 = ?,
                col3 = ?
                WHERE id = ?';

    prepare stmt from @sql;
    execute stmt using @col1, @col2, @col3, @id;
    deallocate prepare stmt;

end$$

Edit: A little background.
It occurs to me I may be trying to solve this problem the wrong way or wrong place. Here's why I am asking. In ZF2 I have TableGateway,and I can send just an array to update and only the columns I want to update. Its super easy and convenient. Example:

$result = $this->db->update('mytable', 
            array('col2' => 'my new value 2'),
            array('id' => 2)
);

I'd like to make a stored procedure to do this update, but I can't send only the columns I want. In one system I may want to send col1 and col3, in another, col1 only. The columns to update are dynamic.

We are building systems that will integrate more than just a web server, which will include some ODBC, some Node.js maybe, possibly others. I wanted to be open to use other systems by building a Stored Procedure to handle certain table updates.

What's occurring to me now is that maybe I can't do this with Stored Procedures. Maybe I need "Middleware" to solve this. However, the question still stands, is this possible to do in Mysql. (if not, PostgreSQL?)

Best Answer

If you are concerned about issuing an UDPATE with lots of columns that don't happen to change, don't worry about it.

If you don't have all the possible "new values" and want to build a custom UPDATE every time, then do just that.

$sets = array();
$binds = array();

if (...) {
    $sets[] = "foo = :foo";
    $binds['foo'] = $foo_value;
}
if (...) {
    $sets[] = "bar = :bar";
    $binds['bar'] = $bar_value;
}
...

$sql = "UPDATE ... SET " .
       implode(', ', $sets) .
       " WHERE ...";
$sth = $pdo->prepare($sql);
$sth=>execute($binds);

Stored Procedure

When using CONCAT() to construct a query, be sure to use QUOTE() around any values that as suspect -- such as might have quotes in them, or might be subject to 'sql injection'.

See: String Functions in the MySQL 5.7 Reference Manual.

Related Question