MySQL 合并两列并添加到新列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5774532/
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 combine two columns and add into a new column
提问by stewart715
I have the following structure with a MySQL table:
我的 MySQL 表具有以下结构：
+----------------+----------------+----------+
| zipcode | city | state |
+----------------+----------------+----------+
| 10954 | Nanuet | NY |
+----------------+----------------+----------+
I want to combine the above 3 columns into one column like this:
我想将上述 3 列合并为一列，如下所示：
+---------------------+
| combined |
+---------------------+
| 10954 - Nanuet, NY |
+---------------------+
And I want to add this "combined" column to the end of the table without destroying the original 3 fields.
我想在不破坏原始 3 个字段的情况下将此“组合”列添加到表的末尾。
回答by squawknull
Create the column:
创建列：
ALTER TABLE yourtable ADD COLUMN combined VARCHAR(50);
Update the current values:
更新当前值：
UPDATE yourtable SET combined = CONCAT(zipcode, ' - ', city, ', ', state);
Update all future values automatically:
自动更新所有未来值：
CREATE TRIGGER insert_trigger
BEFORE INSERT ON yourtable
FOR EACH ROW
SET new.combined = CONCAT(new.zipcode, ' - ', new.city, ', ', new.state);
CREATE TRIGGER update_trigger
BEFORE UPDATE ON yourtable
FOR EACH ROW
SET new.combined = CONCAT(new.zipcode, ' - ', new.city, ', ', new.state);
回答by MikeTheReader
Are you sure you want to do this? In essence, you're duplicating the data that is in the three original columns. From that point on, you'll need to make sure that the data in the combined field matches the data in the first three columns. This is more overhead for your application, and other processes that update the system will need to understand the relationship.
你确定要这么做吗？实质上，您是在复制三个原始列中的数据。从那时起，您需要确保组合字段中的数据与前三列中的数据相匹配。这对您的应用程序来说是更多的开销，更新系统的其他进程将需要了解这种关系。
If you need the data, why not select in when you need it? The SQL for selecting what would be in that field would be:
如果您需要数据，为什么不在需要时选择？用于选择该字段中内容的 SQL 将是：
SELECT CONCAT(zipcode, ' - ', city, ', ', state) FROM Table;
This way, if the data in the fields changes, you don't have to update your combined field.
这样，如果字段中的数据发生变化，您就不必更新组合字段。
回答by zerkms
Add new column to your table and perfrom the query:
向表中添加新列并执行查询：
UPDATE tbl SET combined = CONCAT(zipcode, ' - ', city, ', ', state)
回答by Preston Owuor
SELECT CONCAT (zipcode, ' - ', city, ', ', state) AS COMBINED FROM TABLE
SELECT CONCAT (zipcode, ' - ', city, ', ', state) AS COMBINED FROM TABLE
