MySQL left join with '-' character in a column

Question

这是我的文件位置表中的一些数据:

Filelocation_table
+-----+-------------+---------------------+
| id  | f_id        | f_location          |
+-----+-------------+---------------------+
|   1 | 1           | App. Box March 2017 |
|   2 | 2           | App. Box March 2017 |
|   3 | 3           | App. Box March 2017 |
|   4 | 4           | App. Box March 2017 |
|   5 | 5           | App. Box March 2017 |
|   6 | 6           | App. Box March 2017 |
|   7 | 7           | App. Box March 2017 |
|   8 | 8           | App. Box March 2017 |
|   9 | 9           | App. Box March 2017 |
|  10 | 10          | App. Box March 2017 |
|  11 | 11          | App. Box March 2017 |

and so on...

| 294 | 79-EC       | EC B1               |
| 295 | 80-EC       | EC B1               |
| 296 | 81-EC       | EC B1               |
| 297 | 82-EC       | EC B1               |
| 298 | 83-EC       | EC B1               |
| 299 | 84-EC       | EC B1               |
| 300 | 85-EC       | EC B1               |
| 301 | 86-EC       | EC B1               |
| 302 | 87-EC       | EC B1               |
| 303 | 1-processed | Active 1            |
| 304 | 2-processed | Active 1            |
| 305 | 3-processed | PR 1                |
| 306 | 4-processed | PR 1                |
+-----+-------------+---------------------+

我在下面的查询中遇到了问题:

SELECT  docu_id.id, /* For checking purposes if it match with the f_id */
        filelocation.f_id, /* For checking purposes if it match with the id */
        docutype,
        f_location,
        bpnum,
        lastname,
        firstname,
        middlename,
        ponum,
        claimtype
FROM docu_id

left join filelocation on
        filelocation.f_id = docu_id.id

left join f_dates on 
        filelocation.f_id = f_dates.f_id 

结果如下:

+-----+-------------+-------------+---------------------+------------+--------------+----------------+------------+-------+---------------------------------+
| id  | f_id        | docutype    | f_location          | bpnum      | lastname     | firstname      | middlename | ponum | claimtype                       |
+-----+-------------+-------------+---------------------+------------+--------------+----------------+------------+-------+---------------------------------+
|   1 | 1           | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | JULIO          | -          | -     | FUNERAL                         |
|   2 | 2           | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | ELEUTERIO      |            |       | COMPULSORY LIFE                 |
|   3 | 3           | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | ZORAIDA        |            |       | EMPLOYEES COMPENSATION          |

 /* and so on... */

|  86 | 86-EC       | APPLICATION | EC B1               | xxxxxxxxxx | xxxxxxxxxx   | DANILO         | -          | -     | REQUEST                         |
|  87 | 87-EC       | APPLICATION | EC B1               | xxxxxxxxxx | xxxxxxxxxx   | LIBERATA       | -          | -     |                                 |
|   1 | 1-processed | APPLICATION | Active 1            | xxxxxxxxxx | xxxxxxxxxx   | JULIO          | -          | -     | FUNERAL                         |
|   2 | 2-processed | APPLICATION | Active 1            | xxxxxxxxxx | xxxxxxxxxx   | ELEUTERIO      |            |       | COMPULSORY LIFE                 |
|   3 | 3-processed | APPLICATION | PR 1                | xxxxxxxxxx | xxxxxxxxxx   | ZORAIDA        |            |       | EMPLOYEES COMPENSATION          |
|   4 | 4-processed | APPLICATION | PR 1                | xxxxxxxxxx | xxxxxxxxxx   | FLORA          |            |       | PRE-NEED                        |
+-----+-------------+-------------+---------------------+------------+--------------+----------------+------------+-------+---------------------------------+
306 rows in set, 19656 warnings (0.05 sec)

f_id '87-EC' 与 id '87' 匹配

这个查询纠正了这个问题:

SELECT  docu_id.id,  /* For checking purposes if it match with the f_id */
        filelocation.f_id, /* For checking purposes if it match with the id */
        docutype,
        f_location,
        bpnum,
        lastname,
        firstname,
        middlename,
        ponum,
        claimtype
FROM docu_id

left join filelocation on
        concat(filelocation.f_id, 'a') = concat(docu_id.id, 'a')

left join f_dates on 
        filelocation.f_id = f_dates.f_id 

此查询返回预期结果:

+-----+------+-------------+---------------------+------------+--------------+----------------+------------+-------+---------------------------------+
| id  | f_id | docutype    | f_location          | bpnum      | lastname     | firstname      | middlename | ponum | claimtype                       |
+-----+------+-------------+---------------------+------------+--------------+----------------+------------+-------+---------------------------------+
|   1 | 1    | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | JULIO          | -          | -     | FUNERAL                         |
|   2 | 2    | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | ELEUTERIO      |            |       | COMPULSORY LIFE                 |
|   3 | 3    | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | ZORAIDA        |            |       | EMPLOYEES COMPENSATION          |
|   4 | 4    | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | FLORA          |            |       | PRE-NEED                        |

 and so on...

| 214 | 214  | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | ERNESTO        | -          | -     | SEPARATION                      |
| 215 | 215  | APPLICATION | App. Box March 2017 | xxxxxxxxxx | xxxxxxxxxx   | ROLANDO        |            |       | SEPARATION                      |
+-----+------+-------------+---------------------+------------+--------------+----------------+------------+-------+---------------------------------+
215 rows in set (0.04 sec)

我不应该使用“-”字符吗？以及为什么 MySQL 将 '87' 与 '87-EC' 匹配，但是当我最后用'a'连接它时不匹配。 (87a/87-ECa)?

I replace the numbers and last name with xxxxxxx since i am posting confidential data.

Answer 1

我现在解决了。我查看来自 mysql 命令的警告，它显示以下内容:

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1-EC' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1-EC' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1-EC' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1-EC' |
 /* and so on and on */

通过转换 docu_id 表中的 id

cast(docu_id.id as char(10))

现在，所有的都正确匹配了，我不需要再连接它了。