MySQL 游标和准备语句 - 出了什么问题
我正在尝试在 MySQL 中创建一个使用游标和执行的过程。我想内省架构并更新所有具有版本列的表，将版本设置为 0。我认为我缺少一个循环（在此处寻求帮助），但我的动态 SQL 也出现错误。有什么想法吗？
DELIMITER $$
CREATE PROCEDURE update_version_number(schemaName int)
BEGIN
DECLARE the_table_name VARCHAR(200);
DECLARE version_cursor CURSOR
FOR
SELECT TABLE_NAME FROM information_schema.columns WHERE table_schema = "myschema" AND COLUMN_NAME = "version" AND TABLE_NAME <> "db_info";
OPEN version_cursor;
FETCH version_cursor into the_table_name;
PREPARE run_version_update From 'UPDATE ? SET version = 0';
SET @a = the_table_name;
EXECUTE run_version_update USING @a;
DEALLOCATE PREPARE run_version_update;
CLOSE version_cursor;
END
I am trying to create a procedure in MySQL that uses a cursor and an execute. I would like to introspect the schema and update all tables that have a version column, setting the version to 0. I think I am missing a loop (looking for help here), but I am also getting an error with my dynamic SQL. Any ideas?
DELIMITER $
CREATE PROCEDURE update_version_number(schemaName int)
BEGIN
DECLARE the_table_name VARCHAR(200);
DECLARE version_cursor CURSOR
FOR
SELECT TABLE_NAME FROM information_schema.columns WHERE table_schema = "myschema" AND COLUMN_NAME = "version" AND TABLE_NAME <> "db_info";
OPEN version_cursor;
FETCH version_cursor into the_table_name;
PREPARE run_version_update From 'UPDATE ? SET version = 0';
SET @a = the_table_name;
EXECUTE run_version_update USING @a;
DEALLOCATE PREPARE run_version_update;
CLOSE version_cursor;
END
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不再是问题了。我改变了解决问题的方式。
No longer an issue. I changed how I approached the problem.