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

• Z时代
• 2024-01-10
• 分类：问答

我的MySQL表具有以下结构：

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

我想将以上三列合并为一列，如下所示：

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

我想将此“组合”列添加到表的末尾而不破坏原始的3个字段。

回答：

创建列：

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合并两列并添加到新列中 的全部内容， 来源链接： utcz.com/qa/416780.html