MYSQL 将另一个表中的值插入一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14226585/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL Insert values from another table into one column
提问by zuc0001
I am wanting to move items from one table into another using mySQL.
我想使用 mySQL 将项目从一个表移动到另一个表。
This is what I currently use:
这是我目前使用的：
INSERT INTO items_rooms (item_id, room_id,x,y,n)
SELECT id, room_id,x,y,z
FROM `items_phoenix`
This works, however I am wanting to insert multiple values into one column instead of each values in different columns.
这有效，但是我想将多个值插入一列而不是不同列中的每个值。
For example:
例如：
In table items_rooms, I would like values x and y from items_phoenix to be placed into one column in items_rooms.
在表 items_rooms 中，我希望将 items_phoenix 中的值 x 和 y 放入 items_rooms 中的一列。
If x = 5 and y = 2, can I save it into items_rooms like this: one column: 5.2 instead of different columns for each values?
如果 x = 5 和 y = 2，我可以像这样将它保存到 items_rooms 中：一列：5.2 而不是每个值的不同列？
Sorry if this is confusing!
对不起，如果这令人困惑！
回答by Bill Karwin
You can use expressions in your SELECT column-list. As long as the columns in the select-list and the columns in the destination table match in number and data type, you can do something like this:
您可以在 SELECT 列列表中使用表达式。只要选择列表中的列和目标表中的列在数量和数据类型上匹配，您就可以执行以下操作：
INSERT INTO items_rooms (item_id, room_id, x_dot_y, n)
SELECT id, room_id, CONCAT(x,'.',y), z
FROM `items_phoenix`
回答by Chamila Chulatunga
You can use a concatenation for a simple case, e.g:
您可以对简单情况使用串联，例如：
INSERT INTO items_rooms (
item_id,
room_id,
x_and_y,
n)
SELECT
id,
room_id,
CONCAT(x, '.', y),
z
FROM `items_phoenix`
回答by Sir Rufo
Yes, if the column can store text information
是的，如果该列可以存储文本信息
Just CONCATor CONCAT_WSthe values
只是CONCAT或CONCAT_WS值
INSERT INTO items_rooms ( item_id, room_id, my_value, n )
SELECT id, room_id, CONCAT_WS( '.', x, y ), z
FROM `items_phoenix`
回答by DWright
You definitely cando this. See the helpful answers on this. However, you should consider whether this is a good idea. Once you denormalize distinct columns into some kind of string expression in one column, you've created something that is very difficult to maintain and also to query against. You are making your data less usable to yourself in doing this.
你绝对可以做到这一点。请参阅有关此问题的有用答案。但是，您应该考虑这是否是一个好主意。一旦您将不同的列非规范化为一列中的某种字符串表达式，您就创建了一些非常难以维护和查询的东西。这样做会使您的数据对您自己的可用性降低。
回答by Jonny Barry
$table_To_Get_From = mysql_query("SELECT * FROM items_phoenix (id, room_id,x,y,z)");
if($table_To_Get_From){
$table = mysql_fetch_assoc($table_To_Get_From);
$values = array($table['id'],$table['room_id'],$table['x'],$table['y'],$table['z']);
mysql_query("INSERT INTO items_rooms (item_id, room_id,x,y,n)
VALUES ($values[0],$values[1],$values[2],$values[3],$value[4])");
}
I didn't make them strings, so you may need to use '$values[num]'
我没有把它们做成字符串，所以你可能需要使用 '$values[num]'
This will insert it like you wanted, if there is any values.
如果有任何值，这将按照您的需要插入它。
