MySQL替换换行符

一.问题描述

今天朋友突然问了一个问题，他想把今天hive里面表字段的备注多行转换为单行显示。

例如表t1的col1列，注释为:
1-有效
0-无效

想要的结果是:
1-有效,0-无效

二.解决方案

CHAR(10): 换行符
CHAR(13): 回车符

MySQL中，CHAR(10)是换行符，通过replace函数进行替换即可。

代码:

create table t2(id int,name varchar(100));


insert into t2 (id, name) values (1,'1-有效
0-无效');

select * from t2;

UPDATE t2 SET  name = REPLACE(name, CHAR(10), ',');

select * from t2;

测试记录:

mysql> 
mysql> create table t2(id int,name varchar(100));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 (id, name) values (1,'1-有效
    '> 0-无效');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+--------------------+
| id   | name               |
+------+--------------------+
|    1 | 1-有效

0-无效     |
+------+--------------------+
1 row in set (0.00 sec)

mysql> UPDATE t2 SET  name = REPLACE(name, CHAR(10), ',');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+------+--------------------+
| id   | name               |
+------+--------------------+
|    1 | 1-有效,,0-无效     |
+------+--------------------+
1 row in set (0.00 sec)

mysql> 

可以看到替换成功了，只是为什么会有两个","，这个有待分析.
初步考虑是字符串中的空行用得不对，此处改为concat函数。

代码:

create table t2(id int,name varchar(100));


insert into t2 (id, name) values (1,CONCAT('1-有效',CHAR(10),'0-无效') );


select * from t2;

UPDATE t2 SET  name = REPLACE(name, CHAR(10), ',');

select * from t2;

测试记录:

mysql> create table t2(id int,name varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 (id, name) values (1,CONCAT('1-有效',CHAR(10),'0-无效') );
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | 1-有效
0-无效     |
+------+-------------------+
1 row in set (0.00 sec)

mysql> UPDATE t2 SET  name = REPLACE(name, CHAR(10), ',');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | 1-有效,0-无效     |
+------+-------------------+
1 row in set (0.00 sec)

mysql> 

参考:

1. https://blog.csdn.net/iteye_3033/article/details/82537232