MySQL 合并两列并添加到一个新列中

问题描述

我的 MySQL 表具有以下结构:

I have the following structure with a MySQL table:

+----------------+----------------+----------+
|    zipcode     |      city      |   state  |
+----------------+----------------+----------+
|     10954      |     Nanuet     |    NY    |
+----------------+----------------+----------+

我想将上述 3 列合并为一列，如下所示:

I want to combine the above 3 columns into one column like this:

+---------------------+
|      combined       |
+---------------------+
| 10954 - Nanuet, NY  |
+---------------------+

并且我想在不破坏原始 3 个字段的情况下将此组合"列添加到表的末尾.

And I want to add this "combined" column to the end of the table without destroying the original 3 fields.

推荐答案

创建列:

ALTER TABLE yourtable ADD COLUMN combined VARCHAR(50);

更新当前值:

UPDATE yourtable SET combined = CONCAT(zipcode, ' - ', city, ', ', state);

自动更新所有未来值:

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);

这篇关于MySQL 合并两列并添加到一个新列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！