mysql存储过程中怎么动态构造含表名的生气了sql语句

mxc851001 2010-07-22 03:32:26
mysql存储过程中怎么动态构造含表名的生气了sql语句, 我要是拼接的变量不是表名可以正确执行 要是拼接的是数据库中的表名就不能执行 这个问题怎么解决呀?是不是mysql中不让拼接表名呀
...全文
352 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
mxc851001 2010-07-22
  • 打赏
  • 举报
回复
好 行了 谢谢你们啦
ACMAIN_CHM 2010-07-22
  • 打赏
  • 举报
回复
DELIMITER $$

是MYSQL工具中的指令,如果你的Navicat for MySQL 8.0则可以定义一下delimiter


[Quote]delimiter str, \d str

Change the string that mysql interprets as the separator between SQL statements. The default is the semicolon character (“;”).

The delimiter can be specified as an unquoted or quoted argument. Quoting can be done with either single quote (') or douple quote (") characters. To include a quote within a quoted string, either quote the string with the other quote character or escape the quote with a backslash (“\”) character. Backslash should be avoided outside of quoted strings because it is the escape character for MySQL. For an unquoted argument, the delmiter is read up to the first space or end of line. For a quoted argument, the delimiter is read up to the matching quote on the line.

When the delimiter recognized by mysql is set to something other than the default of “;”, instances of that character are sent to the server without interpretation. However, the server itself still interprets “;” as a statement delimiter and processes statements accordingly. This behavior on the server side comes into play for multiple-statement execution (see Section 20.9.12, “C API Support for Multiple Statement Execution”), and for parsing the body of stored procedures and functions, triggers, and events (see Section 18.1, “Defining Stored Programs”).

[/Quote]
WWWWA 2010-07-22
  • 打赏
  • 举报
回复
DELIMITER $$
改变结束符,你在Navicat下,直接选择存储过程,新建,输入代码试试,你的SP连名字都没有
mxc851001 2010-07-22
  • 打赏
  • 举报
回复
我想问问哈 DELIMITER $$ 是什么意思 我之前从来没有用过mysql 还有就是我用的工具是Navicat for MySQL 8.0的
ACMAIN_CHM 2010-07-22
  • 打赏
  • 举报
回复
你想实现的功能是什么?通过PREPARE可以这样实现,但可能无法与你的功能进行集成啊。
ACMAIN_CHM 2010-07-22
  • 打赏
  • 举报
回复
你是不是没加DELIMITER啊,另外加上DEALLOCATE PREPARE

DELIMITER $$
CREATE PROCEDURE proc1( table_name varchar(50))
begin
set @sql=concat('select * from `',table_name,'`');
PREPARE stmt FROM @sql;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
end
$$
DELIMITER ;

call proc1('table1');
wwwwb 2010-07-22
  • 打赏
  • 举报
回复
CALL tt('a') 调用
wwwwb 2010-07-22
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 mxc851001 的回复:]
CREATE PROCEDURE ( table_name varchar(50))
begin

set @sql=concat('select * from ',table_name);

PREPARE stmt FROM @sql;

EXECUTE stmt ;

end
[/Quote]

DELIMITER $$
CREATE PROCEDURE tt( table_name VARCHAR(50))
BEGIN
SET @SQL=CONCAT('select * from ',table_name);
PREPARE stmt FROM @SQL;
EXECUTE stmt ;
END;
$$

测试通过,SP的过程名没有写
mxc851001 2010-07-22
  • 打赏
  • 举报
回复
版本是5.0
mxc851001 2010-07-22
  • 打赏
  • 举报
回复
CREATE PROCEDURE ( table_name varchar(50))
begin

set @sql=concat('select * from ',table_name);

PREPARE stmt FROM @sql;

EXECUTE stmt ;

end
wwwwb 2010-07-22
  • 打赏
  • 举报
回复
代码贴出来 ,MYSQL版本多少?
mxc851001 2010-07-22
  • 打赏
  • 举报
回复
大哥 这样还是会报错呀 我是传入参数就是表名 但是想你们的方法做了还是会报错1054错误
wwwwb 2010-07-22
  • 打赏
  • 举报
回复
set @tablename='tt';
set @sql=concat('select * from ',@tablename);
prepare dd from @sql;
execute dd;
wwwwb 2010-07-22
  • 打赏
  • 举报
回复
set @sql=concat('select * from ',@tablename);
prepare dd from @sql;
execute dd;

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧