使用ALTER TABLE的存储过程 [英] Stored Procedure with ALTER TABLE

问题描述

我需要在同一MySQL服务器上不同数据库中的两个表之间同步auto_increment字段.希望创建一个存储过程，其中管理员的权限将允许Web用户在不授予其权限的情况下运行ALTER TABLE [db1].[table] AUTO_INCREMENT = [num];(这只是SQL注入的味道).

I have a need to sync auto_increment fields between two tables in different databases on the same MySQL server. The hope was to create a stored procedure where the permissions of the admin would let the web user run ALTER TABLE [db1].[table] AUTO_INCREMENT = [num]; without giving it permissions (That just smells of SQL injection).

我的问题是创建存储过程时收到错误.这是MySQL不允许的吗?

My problem is I'm receiving errors when creating the store procedure. Is this something that is not allowed by MySQL?

DROP PROCEDURE IF EXISTS sync_auto_increment;
CREATE PROCEDURE set_auto_increment (tableName VARCHAR(64), inc INT)
BEGIN
ALTER TABLE tableName AUTO_INCREMENT = inc;
END;

DROP PROCEDURE IF EXISTS sync_auto_increment;
CREATE PROCEDURE set_auto_increment (tableName VARCHAR(64), inc INT)
BEGIN
ALTER TABLE tableName AUTO_INCREMENT = inc;
END;

推荐答案

问题似乎是您需要更改定界符.它认为Alter table行是函数的结尾.试试这个:

The problem seems to be that you need to change the delimiter. It thinks that the Alter table line is the end of the function. Try this:

DROP PROCEDURE IF EXISTS sync_auto_increment;
DELIMITER //
CREATE PROCEDURE set_auto_increment (tableName VARCHAR(64), inc INT)
BEGIN
ALTER TABLE tableName AUTO_INCREMENT = inc;
END//

DELIMITER ;

有时候mysql对于允许使用存储过程仍然很挑剔，因此如果仍然无法运行它，可以尝试这样做:

Sometimes mysql is still picky about letting you use stored procedures, so you can do try this if you still can't run it:

DROP PROCEDURE IF EXISTS sync_auto_increment;
DELIMITER //
CREATE PROCEDURE set_auto_increment (tableName VARCHAR(64), inc INT)
DETERMINISTIC
READS SQL DATA
BEGIN
ALTER TABLE tableName AUTO_INCREMENT = inc;
END//

DELIMITER ;

这篇关于使用ALTER TABLE的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！