使用子选择更改表 [英] Alter table using sub select

问题描述

我有两个桌子.一个是字段信息表，另一个是字段数据表.

I have two tables. one which is a field info table and the other is a field data table.

默认情况下，所有字段数据表列均设置为某种文本字段(varchar，text等)

by default all field data table columns are set to text fields of some sort (varchar, text, etc)

我想做的就是这个.代替创建手动的ALTER TABLE查询:

what i would like to do is this. Instead of creating the manual ALTER TABLE query:

ALTER TABLE `field_data` 
CHANGE COLUMN `field_id_2` `field_id_2` decimal NOT NULL, 
CHANGE COLUMN `field_id_3` `field_id_3` decimal NOT NULL,
CHANGE COLUMN `field_id_22` `field_id_22` decimal NOT NULL,
CHANGE COLUMN `field_id_23` `field_id_23` decimal NOT NULL,
CHANGE COLUMN `field_id_25` `field_id_25` decimal NOT NULL,
CHANGE COLUMN `field_id_27` `field_id_27` decimal NOT NULL,
CHANGE COLUMN `field_id_30` `field_id_30` decimal NOT NULL,
CHANGE COLUMN `field_id_66` `field_id_66` decimal NOT NULL,
CHANGE COLUMN `field_id_72` `field_id_72` decimal NOT NULL;

我想基于field_info表编写一个动态的ALTER TABLE查询，现在我通过执行以下查询来获得这些字段ID:

I would like to write a dynamic ALTER TABLE query based on the field_info table, right now I get those field IDs by doing this query:

SELECT field_id FROM `field_info` WHERE `field_type` = 'a_decimal_field'

返回值:2、3、22、23等.

which returns the values: 2, 3, 22, 23, etc.

因此，我需要一个查询(如果可能的话)，该查询根据将sub_selection连接到每个sub select的结果的sub select更改表并将该列的类型更改为十进制.

So I would need a query (if possible) that alters the table based on the sub select concatenating field_id_ to the result of each sub select and changes the type of that column to decimal.

我知道我可以通过构造查询在php中做到这一点，但是如果可能的话，我专门在寻找纯MySQL解决方案.

I know I can do this in php by constructing the query, but I am specifically looking for a pure mysql solution if possible.

推荐答案

SELECT CONCAT('ALTER TABLE `field_data` ', 
  GROUP_CONCAT(' CHANGE COLUMN `field_id_', field_id, '` ',
    ' `field_id_', field_id, '` DECIMAL NOT NULL')) 
FROM `field_info` 
WHERE `field_type` = 'a_decimal_field'
INTO @sql;

PREPARE stmt FROM @sql;

EXECUTE stmt;

这篇关于使用子选择更改表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！