MySQL变量存储数据库名称 [英] MySQL Variables storing database name

问题描述

我有一个长脚本，需要在多个不同的数据库上运行(所有表和字段名都相同).

I have a long script that I need to run on several different databases (all witht he same tables and field names).

我想做的是这样的:

1  SET @TARGET_DATABASE = 'beta'
2  SET @SOURCE_DATABASE = 'sandbox';
3  
4  CREATE DATABASE IF NOT EXISTS @TARGET_DATABASE;
5  USE @TARGET_DATABASE;

...

10 INSERT INTO `tableFoo` SELECT * FROM @SOURCE_DATABASE.`tableFoo`;

第10行出现错误(我并不感到惊讶):脚本行:10您的SQL语法有错误；请查看与您的MySQL服务器版本相对应的手册，以获取在'附近使用的正确语法@SOURCE_DATABASE.tableFoo'在行..."

On line 10 I get an error (I'm not surprised): "Script line: 10 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 '@SOURCE_DATABASE.tableFoo' at line..."

所以我在第10行尝试了此操作:

So I tried this insted for line 10:

10 SET @TABLE=CONCAT('`',@SOURCE_DATABASE,'`','.`tableFoo`');
11 INSERT INTO `tableFoo` SELECT * FROM @TABLE;

...并再次出现错误脚本行:11您的SQL语法有错误；请查看与您的MySQL服务器版本相对应的手册，以获取在行的'@TABLE'附近使用的正确语法..."如您所见，第10行工作正常...

...and again error "Script line: 11 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 '@TABLE' at line..." As you can see, line 10 works fine...

有什么方法可以使用变量引用数据库中的表?

Is there any way to refer to a table in a database with a variable?

//谢谢.

推荐答案

如果连接整个查询，它将起作用.试试:

It will work if you concat the entire query. Try:

set @db = 'mydb';
set @tble = 'table';

set @query = concat('INSERT INTO tablefoo SELECT * FROM ', @db, '.', @tble);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这篇关于MySQL变量存储数据库名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！