mysql - 我无法在 Mysql 过程中使用输入

Question

我如何在 MySql 中定义一个过程，它有一个输入，输入将是将添加到表“test1”的列的名称，但 sql 命名该列输入而不是使用输入的值。我该怎么做以正确的方式？

DELIMITER 
CREATE PROCEDURE p1
(IN input CHAR(20))
BEGIN
ALTER TABLE test1
ADD COLUMN input char(20);
END 
DELIMITER ;

Answer 1

你应该谷歌动态sql并阅读手册https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html同时，这是一个示例。

drop procedure if exists  p;
alter table users 
    drop column abc;
DELIMITER  $$
CREATE PROCEDURE p
(IN input CHAR(20))
BEGIN
#ALTER TABLE test1
#ADD COLUMN input char(20);
set @sql = concat('alter table users add column ',input,' char(20);');
select @sql;
prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;
END $$
DELIMITER ;

call p('abc');
describe users;

MariaDB [sandbox]> call p('abc');
+--------------------------------------------+
| @sql                                       |
+--------------------------------------------+
| alter table users add column abc char(20); |
+--------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.42 sec)

MariaDB [sandbox]> describe users;
+---------------------+-------------+------+-----+-------------------+-------+
| Field               | Type        | Null | Key | Default           | Extra |
+---------------------+-------------+------+-----+-------------------+-------+
| id                  | int(11)     | NO   | PRI | NULL              |       |
| userName            | varchar(60) | NO   |     | NULL              |       |
| photo               | varchar(50) | YES  |     | NULL              |       |
| status              | int(11)     | YES  |     | NULL              |       |
| ts                  | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
| events_participated | int(11)     | YES  |     | NULL              |       |
| fb_uid              | int(11)     | YES  |     | NULL              |       |
| Column_name         | varchar(10) | YES  |     | NULL              |       |
| post_type           | varchar(10) | YES  |     | NULL              |       |
| password            | varchar(8)  | YES  |     | NULL              |       |
| abc                 | char(20)    | YES  |     | NULL              |       |
+---------------------+-------------+------+-----+-------------------+-------+
11 rows in set (0.03 sec)