mysql - 错误 CONCAT MySQL - 没有连接 2 个或更多值

Question

美好的一天!

我尝试执行以下脚本:

SELECT 
concat_ws(", ",if(dep.id_departamento=0,"",ifnull(dep.descripcion,"")),
      if(mun.id_municipio=0,"",ifnull(mun.descripcion,"")) , 
      if(ifnull(vi.zona ,0)=0,"", cast(vi.zona as char))) as concatAddress
FROM visados vi
left join municipios mun on mun.id_municipio = vi.id_municipio
left join departamentos dep on dep.id_departamento = vi.id_departamento

表格信息:

签证 - vi

+-------+--------------------+-----------------+---------+
|  id   |   id_departamento  |   id_municipio  |   zona  |
+-------+--------------------+-----------------+---------+
|   1   |        1           |        1        |    5    |
+-------+--------------------+-----------------+---------+

Municipios - mun

+------------------+---------------+
|   id_municipio   |  descripcion  |
+------------------+---------------+
|         1        | chimaltenango |
+------------------+---------------+

部门 - dep

+---------------------+---------------+
|    id_departamento  |  descripcion  |
+---------------------+---------------+
|           1         | chimaltenango |
+---------------------+---------------+

正确的结果应该是:

+------------------------------------+
|            concatAddress           |      
+------------------------------------+
|  chimaltenango, chimaltenango, 5   | 
+------------------------------------+

但我的结果只是:

+------------------+
|  concatAddress   |      
+------------------+
|  chimaltenango   | 
+------------------+

否连接其他 2 个值字段。

发生了什么？

本地服务器:MySQL 5.6.25版本

谢谢!

Answer 1

问题是每行的字符串末尾都有一个 \r。

Municipios - mun

+------------------+-----------------+
|   id_municipio   |    descripcion  |
+------------------+-----------------+
|         1        | chimaltenango\r |
+------------------+-----------------+

部门 - dep

+---------------------+-----------------+
|    id_departamento  |   descripcion   |
+---------------------+-----------------+
|           1         | chimaltenango\r |
+---------------------+-----------------+

使用Trim我解决了这个问题:

SELECT
concat_ws(", ",if(dep.id_departamento=0,"",ifnull(trim(BOTH "\r" FROM dep.descripcion),"")),
      if(mun.id_municipio=0,"",ifnull(trim(BOTH "\r" FROM mun.descripcion),"")) , 
      if(ifnull(vi.zona ,0)=0,"", cast(vi.zona as char))) as concatAddress
FROM visados vi
left join municipios mun on mun.id_municipio = vi.id_municipio
left join departamentos dep on dep.id_departamento = mun.id_departamento

谢谢大家!