使用MySQL用户定义的变量在UPDATE语句上连接表名 [英] Concatenate table name on UPDATE statement using MySQL user-defined variable
本文介绍了使用MySQL用户定义的变量在UPDATE语句上连接表名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试为 UPDATE
语句的表名设置前缀。我必须在多个数据库上运行此 UPDATE
语句数十次,每个数据库都有不同的表前缀。
I am trying to set the prefix on table names for an UPDATE
statement. I have to run this UPDATE
statement dozens of times on multiple databases, each has a different table prefix.
下面的代码不起作用,但这是我要完成的工作。
The code below does not work, but this is the idea of what I am trying to accomplish.
SET @prefix = 'prefix_';
SET @old = "old_value";
SET @new = "new_value";
UPDATE CONCAT(@prefix, 'table1') SET some_field = REPLACE(some_field, @old, @new);
UPDATE CONCAT(@prefix, 'table2') SET some_field = REPLACE(some_field, @old, @new);
UPDATE CONCAT(@prefix, 'table3') SET some_field = REPLACE(some_field, @old, @new);
手动编写将是...
UPDATE prefix_table1 SET some_field = REPLACE(some_field, 'old_value', 'new_value');
UPDATE prefix_table2 SET some_field = REPLACE(some_field, 'old_value', 'new_value');
UPDATE prefix_table3 SET some_field = REPLACE(some_field, 'old_value', 'new_value');
推荐答案
使用动态SQL
,
SET @prefix = 'prefix_';
SET @old = "old_value";
SET @new = "new_value";
SET @sql1 = CONCAT('UPDATE ', @prefix, 'table1 SET some_field = REPLACE(some_field,?,?)');
SET @sql2 = CONCAT('UPDATE ', @prefix, 'table2 SET some_field = REPLACE(some_field,?,?)');
SET @sql3 = CONCAT('UPDATE ', @prefix, 'table3 SET some_field = REPLACE(some_field,?,?)');
PREPARE stmt1 FROM @sql1;
PREPARE stmt2 FROM @sql2;
PREPARE stmt3 FROM @sql3;
EXECUTE stmt1 USING @old, @new;
EXECUTE stmt2 USING @old, @new;
EXECUTE stmt3 USING @old, @new;
DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
DEALLOCATE PREPARE stmt3;
- 准备好的语句
- Prepared Statements
这篇关于使用MySQL用户定义的变量在UPDATE语句上连接表名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文