mysql if语句判断表

Question

我想知道是否可以使用 MySQL IF 来选择要更新的表。我正在尝试这样的事情:

UPDATE IF(:listId = 'global', `profanity`,`accountProfanity`) SET `active` = IF(`active` = 1,0,1) WHERE `id` = 1

:listId 可以是字符串global 或数字。当 :listId 为数字时，我想更新表 profanity，否则，我想更新 accountProfanity。当我运行此查询时，我得到以下结果:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF('global' = 'global', `profanity`,`accountProfanity`) SET `active` = IF(`activ' at line 1

还有其他方法可以做到这一点吗？

Answer 1

您可以为此使用准备好的语句:

SET @id = 1;    
SET @table_name = IF(:listId = 'global', `profanity`,`accountProfanity`);
SET @update_query = CONCAT('UPDATE ', @table_name, '
                              SET active = IF(active = 1,0,1) 
                                WHERE id = ', @id);
PREPARE stmt FROM @update_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;