mysql-将字段连接到附加字段

rqcrx0a6 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(349)

简单的一个真的-或者应该是。
我有多个字段要压缩并附加到一个长文本字段。
我猜这是一个concat函数，但我不知道如何将串联的值附加到具有现有值的同一行中的字段。
是有一个mysql语句可以实现这个技巧，还是我必须运行一个循环php脚本？
例子。。。

ID | NAME | POSITION | NOTES
1 | Richard | Programmer | Not paid enough
2 | David | Manager | Lazy
3 | Hilary | Personnel Manager | Doesn't care

最后，我想删除name和position字段，但我想预先存储这些值，以便连接“-.name.”和“-.position并将其附加到notes中，而不会丢失该字段中的任何数据，因此结果将是。。。

ID | NAME | POSITION | NOTES
1 | Richard | Programmer | Not paid enough - Richard - Programmer
2 | David | Manager | Lazy - David - Manager
3 | Hilary | Personnel Manager | Doesn't care - Hilary - Personnel Manager

这有道理吗？

mysql concat Append

来源：https://stackoverflow.com/questions/51081395/mysql-concatenating-fields-to-appended-field

2条答案

按热度按时间

oogrdqng1#

drop table if exists t;
create table t(ID int, NAME varchar(20), POSition varchar(20),NOTES varchar(500));
insert into t values
(1 , 'Richard' , 'Programmer' ,      'Not paid enough'),
(2 , 'David'   , 'Manager'    ,      'Lazy'),
(3 , 'Hilary'  , 'Personnel Manager','Doesnt care');

update t
set notes =
    CONCAT(NOTES, ' - ', NAME, ' - ', POSITION) 
where 1 = 1;

select * from t;

+------+---------+-------------------+------------------------------------------+
| ID   | NAME    | POSition          | NOTES                                    |
+------+---------+-------------------+------------------------------------------+
|    1 | Richard | Programmer        | Not paid enough - Richard - Programmer   |
|    2 | David   | Manager           | Lazy - David - Manager                   |
|    3 | Hilary  | Personnel Manager | Doesnt care - Hilary - Personnel Manager |
+------+---------+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

最后，我想删除姓名和职位字段，你确定要这样做吗？再次取出碎片是件痛苦的事。
如果任何元素为null，concat将返回null如果一个元素不能保证有值，那么ifnull测试是必要的

update t
    set notes =
        CONCAT(IFNULL(NOTES,''), ' - ', IFNULL(NAME,''), ' - ', IFNULL(POSITION,'')) 
    where 1 = 1;

drop table if exists t;
create table t(ID int, NAME varchar(20), POSition varchar(20),NOTES varchar(500));
insert into t values
(1 , 'Richard' , 'Programmer' ,      'Not paid enough'),
(2 , 'David'   , 'Manager'    ,      'Lazy'),
(3 , 'Hilary'  , 'Personnel Manager','Doesnt care'),
(4 , 'HILARY'  , NULL,'DOES NOW') ;

update t
set notes =
    CONCAT(IFNULL(NOTES,''), ' - ', IFNULL(NAME,''), ' - ', IFNULL(POSITION,'')) 
where 1 = 1;

+------+---------+-------------------+------------------------------------------+
| ID   | NAME    | POSition          | NOTES                                    |
+------+---------+-------------------+------------------------------------------+
|    1 | Richard | Programmer        | Not paid enough - Richard - Programmer   |
|    2 | David   | Manager           | Lazy - David - Manager                   |
|    3 | Hilary  | Personnel Manager | Doesnt care - Hilary - Personnel Manager |
|    4 | HILARY  | NULL              | DOES NOW - HILARY -                      |
+------+---------+-------------------+------------------------------------------+
4 rows in set (0.00 sec)

赞(0）回复(0）举报 2021-06-24

n3h0vuf22#

这个 CONCAT 函数可以在mysql中轻松处理此问题：

SELECT
    ID,
    NAME,
    POSITION,
    CONCAT(NOTES, ' - ', NAME, ' - ', POSITION) AS NOTES
FROM yourTable;

赞(0）回复(0）举报 2021-06-24

我来回答

mysql-将字段连接到附加字段

2条答案

相关问题

热门标签

最新问答