MySQL 如果数字选择其他列

Question

mysql 是否有函数来检查值是否仅为数字？

我不想连接 2 列，如果 tbl3.column2 是数字，则与其他表列连接，如果是字符串，则同一个表的其他列即可。

SELECT tbl1.column1 as Column1, tbl2.column2 as Column2, 
IF tbl3.column2 REGEXP '^[0-9]+$' THEN CONCAT(tbl3.column1, ' ', tbl4.column1) ELSE CONCAT(tbl3.column1, ' ', tbl3.column2) END IF as Combined
FROM table1 tbl1
LEFT JOIN table2 tbl2 ON tbl1.id = tbl2.id
LEFT JOIN table3 tbl3 ON tbl3.id = tbl1.id
LEFT JOIN table4 tbl4 ON tbl4.id = tbl1.id
LEFT JOIN table5 tbl5 ON tbl5.id = tbl1.id 
WHERE
tbl5.column3 = ?
ORDER BY Column1 ASC

当前代码给出错误:

You have an error in your SQL syntax; check the manual that corresponds
 to your MySQL server version for the right syntax to use near 'tbl3.column2 REGEXP '^[0-9]+$'
    THEN CONCAT(tbl3.column1, ' ', tbl4.column1' at line

Answer 1

请尝试一下:

SELECT tbl1.column1 AS Column1, tbl2.column2 AS Column2, 
IF(tbl3.column2 REGEXP '^[0-9]+$'
    , CONCAT(tbl3.column1, ' ', tbl4.column1)
    , CONCAT(tbl3.column1, ' ', tbl3.column2)
    ) AS Combined
FROM table1 tbl1
LEFT JOIN table2 tbl2 ON tbl1.id = tbl2.id
LEFT JOIN table3 tbl3 ON tbl3.id = tbl1.id
LEFT JOIN table4 tbl4 ON tbl4.id = tbl1.id
LEFT JOIN table5 tbl5 ON tbl5.id = tbl1.id 
WHERE
tbl5.column3 = ?
ORDER BY Column1 ASC