I have tables like,
Table A
field1 | field2 | field3 | field4 | field5
表B
id | field_names
--------------------
1 field2
2 field3
我需要从
表A
中删除field2和field3(即在表B
中的字段名)。有没有办法删除
表B
中存在的字段名。我无法手动删除这些字段。提前致谢。
I have tables like,
Table A
field1 | field2 | field3 | field4 | field5
表B
id | field_names
--------------------
1 field2
2 field3
表A
中删除field2和field3(即在表B
中的字段名)。表B
中存在的字段名。我无法手动删除这些字段。这将有所帮助:
SELECT @s := CONCAT('ALTER TABLE tableA DROP COLUMN ', GROUP_CONCAT(field_names SEPARATOR ', DROP COLUMN '), ';') FROM tableB;
PREPARE stmt FROM @s;
EXECUTE @s;
不确定你为什么需要这个。无论如何,MySQL没有这样的功能。但是如果您真的想这样做,请执行以下查询并执行每行。
SELECT CONCAT('ALTER TABLE TableA DROP ', field_names, ';') FROM TableB;
举例来说,使用PHP(所有错误检查均省略)
$query = "SELECT CONCAT('ALTER TABLE TableA DROP ', field_names, ';') AS stmt FROM TableB;";
$result = mysqli_query($query);
while ($row = mysqli_fetch_assoc($result))
{
mysqli_query($rows['stmt']);
}
DELIMITER //
CREATE PROCEDURE deleteTableAColumns()
BEGIN
DECLARE columnToDelete VARCHAR(16);
DECLARE done INT DEFAULT FALSE;
DECLARE columnsToDeleteCur CURSOR FOR SELECT field_names FROM tableB;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN columnsToDeleteCur;
read_loop: LOOP
FETCH columnsToDeleteCur INTO columnToDelete;
IF done THEN
LEAVE read_loop;
END IF;
SET @dropColumnQuery = CONCAT('ALTER TABLE tableA DROP ', columnToDelete, ';');
PREPARE dropColumnQueryStatement FROM @dropColumnQuery;
EXECUTE dropColumnQueryStatement;
DEALLOCATE PREPARE dropColumnQueryStatement;
END LOOP;
CLOSE columnsToDeleteCur;
END
//
DELIMITER ;
当您想要执行该过程时,请调用该过程:
CALL deleteTableAColumns();