英文:
I am trying to concat variable and string in stored procedure in mysql
问题
我正在尝试在MySQL存储过程中将变量和字符串连接起来以用作SELECT语句中的表名。
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE demo1 VARCHAR(100);
DECLARE demo2 VARCHAR(100);
DECLARE concatenated VARCHAR(100);
DECLARE concatenated2 VARCHAR(100);
SET demo1 = ('tour_');
SET demo2 = ('companytb');
SET concatenated = CONCAT('tour_', 'companytb');
SET concatenated2 = CONCAT_WS(demo1, demo2);
SELECT DISTINCT company_name FROM concatenated;
END
但我尝试了这个，但是我收到了以下错误信息：
Error Code: 1146. Table 'codimbii_live.concatenated' doesn't exist
英文:
I am trying to concatenate variable and string in mysql stored procedure for table name in select
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE demo1 VARCHAR(100);
DECLARE demo2 VARCHAR(100);
DECLARE concatenated VARCHAR(100);
DECLARE concatenated2 VARCHAR(100);
SET demo1 = ('tour_');
SET demo2 = ('companytb');
SET concatenated = CONCAT('tour_', 'companytb');
SET concatenated2 = CONCAT_WS(demo1, demo2);
SELECT DISTINCT company_name FROM concatenated;
END
I tried this, but I am getting this error:
>01911:41:29 call test()
>
> Error Code: 1146. Table 'codimbii_live.concatenated' doesn't exist
答案1
得分: 0
如@akina在评论中提到的，您需要使用一些预处理语句，这里是一个示例：
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE demo1 VARCHAR(100);
DECLARE demo2 VARCHAR(100);
DECLARE concatenated VARCHAR(100);
DECLARE concatenated2 VARCHAR(100);
SET demo1 = ('tour_');
SET demo2 = ('companytb');
SET @sql = CONCAT('SELECT DISTINCT company_name FROM ', demo1, demo2);
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END
英文:
As mentioned by @akina in comments you will need to use some prepared statements, here is an exemple :
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE demo1 VARCHAR(100);
DECLARE demo2 VARCHAR(100);
DECLARE concatenated VARCHAR(100);
DECLARE concatenated2 VARCHAR(100);
SET demo1 = ('tour_');
SET demo2 = ('companytb');
SET @sql = CONCAT('SELECT DISTINCT company_name FROM ', demo1, demo2);
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论