MySQL从表中删除存储过程，其中包含表名和ID号作为参数

I need to write a MySQL delete-from-table-by-id stored procedure with a table name and an ID number as parameters sent to the procedure when invoked. The table name parameter tells which table to delete from and the ID parameter tells which row in the table to be deleted.

我需要编写一个MySQL delete-from-table-by-id存储过程,其中包含一个表名和一个ID号作为调用时发送给过程的参数。 table name参数告诉要从哪个表中删除,ID参数告诉要删除表中的哪一行。

Here's my non-working solution:

这是我的非工作解决方案:

CREATE PROCEDURE delete_from_table_by_id(IN IN_TABLE_NAME VARCHAR(255),
                                         IN IN_ROW_ID     INT UNSIGNED)
BEGIN
    DELETE FROM IN_TABLE_NAME
    WHERE CONCAT(IN_TABLE_NAME, '_id') = IN_ROW_ID;
END$$

It should be working like this: I have tables named book, author, publisher, et cetera. The ID column in each table in my database is made up of two parts, namely, table_name and _id. So, in the author table the ID column is named author_id, in the book table it's named book_id. For example, the following code should get rid of the row with ID 456 from the book table:

它应该像这样工作:我有名为book,author,publisher等的表。我的数据库中每个表中的ID列由两部分组成,即table_name和_id。因此,在author表中,ID列名为author_id,在book表中它名为book_id。例如,以下代码应该从书表中删除ID为456的行:

CALL delete_from_table_by_id('book', 456);

Thank you all in advance.

谢谢大家。

2 个解决方案

#1

You can not use a variable for a table name or for column name.

您不能将变量用于表名或列名。

The simplest solution for you is to use prepare and execute.

最简单的解决方案是使用prepare和execute。

SET @s = 'DELETE FROM ? WHERE CONCAT(?, '_id') = ?';

PREPARE stmt2 FROM @s;

SET @inTableName = 'some_table';
SET @inRowId = 42;

EXECUTE stmt2 USING @inTableName, @inTableName, @inRowId;

I pulled this code out of my head; I haven't tried it out. But I'm pretty sure it should work.

我把这个代码从脑子里拿出来了;我还没试过。但我很确定它应该有效。

#2

Thanks. Here's my code that works:

谢谢。这是我的代码:

CREATE PROCEDURE delete_from_table_by_id(IN IN_TABLE_NAME VARCHAR(255),
                                         IN IN_ROW_ID     INT UNSIGNED)
BEGIN
    SET @SQL = CONCAT('DELETE FROM ', IN_TABLE_NAME,
                      ' WHERE ', IN_TABLE_NAME,
                      '_id = ', IN_ROW_ID);
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

#1