我可以在'insert ... on duplicate update ...'语句中重用bind变量吗？

I am attempting to run a query that uses bind variables against a mysql database engine. I am wondering how I can tell the engine to "reset" the bind variable assignments. I'm sure an example will explain much better than my poor brain.

我试图运行一个使用绑定变量对mysql数据库引擎的查询。我想知道如何告诉引擎“重置”绑定变量赋值。我敢肯定,一个例子会比我的大脑更好地解释。

Here is the query:

这是查询:

INSERT INTO site_support_docs
      (
          ASSET_ID,
          TIME_STAMP,
          SITE_NAME,
          DOCUMENT_NAME,
          DOCUMENT_LOCATION,
          DOCUMENT_CONTENT,
          DOCUMENT_LAST_MODIFIED
      )
VALUES (?, ?, ?, ?, ?, ?, STR_TO_DATE(?, '%M %e, %Y %r'))
ON DUPLICATE KEY UPDATE asset_id   = ?,
                        time_stamp   = ?,
                        site_name   = ?,
                        document_name   = ?,
                        document_location   = ?,
                        document_content   = ?,
                        document_last_modified   =
                           STR_TO_DATE(?, '%M %e, %Y %r')

My problem is that the eighth "?" is interpreted as a new bind variable when there are only seven. Anyway, I guess I can revert to using the actual values... but, I'm sure there is a better way. Matt

我的问题是第八个“?”当只有七个时,它被解释为一个新的绑定变量。无论如何,我想我可以恢复使用实际值...但是,我确信有更好的方法。马特

1 个解决方案

#1

MySQL offers a "VALUES()" function that provides the value which would have been inserted had the duplicate key conflict not existed. You don't need to repeat the placeholder then.

MySQL提供了一个“VALUES()”函数,它提供了在不存在重复键冲突的情况下插入的值。您不需要重复占位符。

INSERT INTO t VALUES (?) ON DUPLICATE KEY UPDATE x = VALUES(x);

在DUPLICATE KEY UPDATE x = VALUES(x)中插入t值(?);

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_values

#1